I used Enterpise manager to make a backup of a SQL Database. When I
restored it on another machine, I did not have any of my indexes. I
checked the backup wizard and I could not find any reference to
including or not indexes.
Are my indexes there, but not built? Maybe a stored procedure I need
to run to rebuild them?
TIA
Rob
how to take backup of indexes for a particular table.
View 1 Replies View RelatedHow to take backup of indexes for a particular table.
View 5 Replies View RelatedWindows 2003 backup utility uses the shadow copy option that allows it to copy open files.
Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database?
I can then attach the .mdf files if I need to restore the database to another server.
Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
Hi all,
Recently, I upgraded one of our database servers from SQL 2000 to SQL 2005. At the moment, all the databases are in 80 compatibility mode. I upgraded them based on the false pretenses that I could perform online reindexing right off the bat. Apparently, that isn't the case, and it requires switching to 90 compatibility. My question is if I switch to 90, will all indexes be invalidated/unusable? Or will I be able to switch to 90 and perform the online reindexing right away with no downtime?
We have a few 24/7 production VLDB's in the mix (one of which is on this newly upgraded server), and only reasonable downtime is possible. The largest window of downtime I have is about 10 PM - 5 AM with prior notice.
The upgrade was performed using SAN-based LUN's with a detach/reattach.
Thanks,
Matt
Recently moved a couple of dbs from SQL 2000 -> SQL 2005 (different machine). Just restored the dbs on the new server from a full backup of the old one. Everything is fine but...
Some queries are returning out-of-order results (for records added since the migration). I tracked this down to the indexes. I completely rebuilt and reorganized the indexes (online) through the manager interface. This helped provide correct ordering for existing records, but new records added since the re index are still out-of-order.
Looking for a way to permanently fix the indexes so I don't have to constantly rebuild them.
Thanks.
How do i find indexes of the columns of all the tables of thedatbase...........most importantly in SQL server 2000Thanks a lot
View 3 Replies View RelatedI want to know how people are backup up their win2k system and SQL 2000 server. If you want a run backup once a night and first backup win2k file system and then run backup using sql agent will that work?
Thanks.
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Any ideas?
Thanks
Mike Mattix
Hi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.
I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.
What am I missing?
What other ways are there to upgrade a SS2000 DB to 2005?
Thank you.
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
Hi All,
Is there a way to back only specific tables from a database? I need to filter a few tables as they are not needed for backup.
Is there any way to do this?
Thanks in advance.
HiI'm not really a MS SQL Admin. I have only onesystem in our house holding an MS SQL DB.I need to made a backup of the DB.Can someone tell me what is the cheapest way?To buy Backup Exec is not an option.And I can't shut down the service while the backup isrunning...Regards and thanks in advanceDominik
View 5 Replies View RelatedHi,I am working in a sql server 2000 environment but my customer uses sql server 7. Is it possible to use backup and restore to transfer the database or should I set up a sql server 7 for development?The database only contains tables and views...ThanksKoen
View 1 Replies View RelatedHi!
Is it possible to restore a backup made with sql server 7.0
on a SQL Server 2000 without any problems?
I`d need that because I tried to upgrade a 7.0 and got an errormessage
that says, that sp_vupgrade_replication culd't run successfully...
Any Hints?
Thanx in advance
I am trying to backup a database thro' enterprise manager and the msg I am getting is 'SQL Server Agent on target server (local) is stopped. Make sure it is running during the scheduled execution of this job.'
I tried logging into Query Analyzer and I am able to - so the server is up and running. I am not sure what I am missing.
Please help.
Thank you,
If it can, is there some special way to set it up? I can only be successful setting up a tape drive.
Thanks,
Brian
Wondering if SQL 2000 offers any compression of logical backups vs. no software compression in SQL 7.5. I've been asked to evaluate a 3rd party tool that compresses SQL 7.5 backups nicely, but this tool does not allow concatenating all databases into a single logical device file. I'm thinking I won't recommend the tool if SQL 2000 does compression on the fly as we will be upgrading sometime over the next handful of months.
TIA
I am trying to set a backup of my database on every hour. I know I need to create a job first but I don't know how to create one! Can someone help me out, please ?
View 6 Replies View RelatedHiWe are running SQL 2000 Enterprice version with SP3a on a DellPoweedge 5600 server running Advance Server. It has Tape drivePowerVault 110T (DLT VS80 Tape Drive) & uses DLT tapeIV tapes. A DBmaintenance job had been specified to backup on to tape daily. I havefollowing issues;1.The backup job sometimes fails with the following error in Eventlog(Event ID:17055)3041: Backup failed to complete the command BACKUP DATABASE [UNIQRS]TO TAPE = N"\.Tape0 WITH NOINIT, NOUNLOAD, NOSKIP, STATS = 10,NOFORMAT. The job is specified with the Verification option.2. If I try to restore a succeeded backup it asks for a initial tapewith a past date which does not exists3. Is there a way to view the content of the tape & free space etc.4. It is recommended to put a disk backup & transfer it to tape usingWindows backup utility. Is there a way to automate this by initiatingthe Backup utility when the SQL finishes the disk backup.ThanksChirath
View 2 Replies View Related
Dear sir,
SQL SERVER 2000 €“ BACKUP PLAN
I have used scheduled backup plan in my SQL Server 2000.In case, suppose users are entering data during the backup time. What my doubt is, at the time of entering data by the users, all the opened data tables are to be took for backup or not.
Regards,
v.annadurai
I am running a Microsoft SQL 2000 Server. Our original Systems Admins had implemented a strange backup system where they were backing up the databases to the same SQL DB server. I am in the process of moving our backups of off the box we are backing up to a new backup server. I have configured my backup jobs and have been running the backups tandem for the last couple of days. Since doing this I have noticed an issue with failing backups. The backups don't fail on some backup jobs but on others they do. I know SQL is supposed to allow for backups while the database is active. I believe my backups are failing because the database is active and there are two backup jobs accessing the database at the same time.
Has anyone else run into this issue? Can you run backup jobs tandem on an active database?
Hello all,
I would like to be able to backup our databases to a network share mapped drive. Is this possible? When every I go in to setup a maintenance plan it will only allow me to select local drive as the backup location. If my server crashes its not going to do a whole lot of good if my databases are stored locally (I do also backup up to tape every niight). I backup the databases every hour to local disk and would like to send them to one of my MS Storage Servers on the network.
Any help would be greatly appreciated.
thanks
Jason
Hi!!!
I don't know much about SQL Server....
In our office, about 30 clients are connected with SQL server. There are 4 to 5 database each of more than 2 gb.
There are two files of extension LDF and MDF of each database.
* Is it necessary to take back up of both files? LDF files are important???
* Which method / software is best for taking backup?
* Is it possible to take backup while files are used by clients????
* Is it possible to split database in smaller size. because each time I have backup to entire more than 2 gb file....
* Are newer version of SQL 2000 are better than 2000? Should I use it???
please help...
thanks...
Hi,Good morning to All,
I have SQL Server 2005 installed on my system. Recently I have formatted by computer. But I couldn't take backup of my data. Big loss.
For this, I've googled also. but I couldn't get clear information.
I want like the following:I could able to backup entire the Database either into my pen-drive, or some other media.And, latter I could able to put the Data back into my system.
Can anyone please tell how to take backup of my Data, and Restore also.
Thanks in advance,Ashok kumar.
Hai friends,
I am using sql server 2000 as a Backend. I have stored nearly 50 user tables.Now i need backup that(export) , and i need to put that in another pc where sql server 2000 installed(import).
i have a "master" database.
do u have any simple steps , pl reply to me....
THANK U in ADVANCE
Ambrose
5 March 2000
Hai friends.....
I am doing project in ASP.NET(vb) my backend is sql server 2000...in my PC..
I have another pc....where sql server 2000 is installed.....
i have nearly 50 tables...in sql server 2000. in my PC..
I want to backup these tables from my pc and another pc...
can u tell me the steps...
I will try immediately....
Ambrose......
Hi Gurus,
I am new to SQL server and I need your advise on the following .
1. We have two SQL 7.0 Databases at a distant location and we want to move those databases to our location.
2. we plan to take the SQL backup of the databases in tape ( 4Gb and 2 Gb in size) , bring them here, restore them to SQL 2000 server.
- Is this possible , DO SQL 7.0 backups are restored on SQL 2000 without any problems ?.
Also, we plan to take log backups of the DBs at the source after the initial full backups and apply them at the target SQL 2K databases till we cut over to the Target Databases.
Ehat would be the best method of doing this? All your suggestions , pointers to real life scenarios like this, solutions are welcome.
Please let me know if you need any more informations , before suggesting any solution.
Thanks in advance
-Jay
I have set up two Maintenance Plans to do daily backups overnight for our two DBs that are on our SQL server.
As a separate strategy we want to have a second backup done nightly that would involve
#1 detach the db(s)
#2 copy the mdf(s) and ldf(s) to L:xxx
#3 attach the db(s) again
#4 zip up the db(s)
#5 copy the files to a different server for storage.
I created #1, and #3 in query analyzer and saved the script.
I want the process to run at say 2AM and don't know how to schedule them to run - that is #1 ... #2 ... #3. Anyone have sample scripts for this kind of a backup strategy?
This seems like a very simple process especially for a restore and especially since there would not be any trasaction logs involved. This way if we had a KRASH we could take the backup from the separate server and install it on a warm SQL backup server.
Apart from the new servers name then what other steps would I need to cover to get the apps up and running in the quickest time. The app software runs as a client install - Access 97DB] I'm especially curious if I need to have more than a fresh MS2000 SQL server install.
I'm know I'm asking a lot for a first timer.
It seems we want to handle disaster recovery BEFORE we even have our first system crash.
many thanks
Hi I'm an experienced programmer, but just don't have much experience with ASP or MSSQL. I inherited a website and now need to set up an automated backup system for a fairly large (40MB) MSSQL database hosted by a 3rd party provider of reasonable quality with ASP and SQL Server 2000. Let's avoid ASP.NET solutions since the website I inherited is written in "straight up" ASP (old school).
I have access to the database, login etc. I'm thinking I should combine an ASP script with the database connectivity and write daily/weekly backups to a dir on the hosting server.
Where do I begin? Tools? Examples?
Thanks for any assistance.
Stoob
I am experiencing the following problem when running a backup. This process was working really fine for 3 months.
BACKUP DATABASE [DBLIVE] TO DISK = N'C:BACKUPSdblive' WITH INIT , NOUNLOAD , NAME = N'DBLIVE backup C', NOSKIP , STATS = 10, NOFORMAT
Database size 10G
Recovery Mode : Simple
Daily Backup
Error message:
Executed as user: ACC33Administrator. 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up. [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000] (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50 percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up. [SQLSTATE 01000] (Message 3211) Nonrecoverable I/O error occurred on file 'D:sqldbDBLIVE_Data.MDF'. [SQLSTATE 42000] (Error 3271) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Thank You.
Hello!
I have a bug in SQL Server 2000 that I would like to discuss.
The bug is described briefly on http://support.microsoft.com/kb/821334.
"The potential for inconsistency in the backup history tables backupset and backupmediafamily is resolved. This issue may cause the restore process to point to the wrong backup files."
According to my experiences the bug is extremely serious. SQL Server backup and restore operations are not fully reliable. If you have a disaster situation there is a possibility that you can not perform a restore.
Technically a media_family_id is generated in a backup operation and inserted into msdb.dbo.backupmediafamily. What could happen is that a media_family_id, that already exists in backupmediafamily, is generated.
The bug is fixed from version 8.00.0859, but only if you have an special undocumented trace flag (3003) enabled.
Here is the information that I have got from Microsoft about this trace flag.
"Trace flag 3003 changes how SQL generates media_set_id for new backup files. It will guarantee that new media_set_id values in backupset table are unique when you create a new file with your backup. This is the only place in code where TF 3003 is used so it will not impact your SQL Server operations."
I have a SQL Server with 80 databases. I have a maintenance plan with full backup every 24 hours and log backup every 5 minutes.
The error happened on my server a few times every 24 hours. I believe it's a general problem.
Best regards
Ola Hallengren
Hi All
I am using Sql Server 2000.
The database is being used since 5 years and the transaction file (mdf) has increased upto 12Gb.
We are thinking of reducing the size of the database and keeping only the data of the past 1 year. So I would like to know if there is some way of backup or export so that I can get latest 1 year data.
Thanking You All:S :S :S