September 19, 2005 at 8:58 am
I am attempting to recreate a disaster scenario. I have a new server that I have installed SQL 2000 sp3a. I have placed backup copies of all of my databases on this server. In the event of a disaster this is what I would have to work with.
I am able to restore master by putting the server in single user mode. However, I am unable to restore model or msdb. I can not simply start SQL server because it is looking for my other databases. I have tries starting SQL with various combinations of start up parameters of - c and -f and trace flags of T3608 and T4022. I am successful in starting the service, however when I attempt to run my scripts to restore model or msdb I receive a message indicating that tempdb was not started and this script can not be run.
Can anyone shed some light on how to recover when you have backups of system and user databases? I have read the articles about running scripts to copy items from another server but in a disaster scenario I will not have this luxury. Thanks.
September 19, 2005 at 9:06 am
hm.. I am wondering what if you stop sql server, copy the old current model file to other place and put your old model file in the place and restart the server will that work?
on msdb database you need to make sure to stop sql server agent and then you could restore the database.
mom
September 19, 2005 at 9:12 am
How do I put the old model file in place? All I have is a backup of the database. Don't I need to restore?
Everything that I have read says to backup all of your databases and then you can restore in a disaster situation. I have done this successfully in SQL 7. What is different about SQL 2000 that makes this a difficult process? Should I be doing something other than or in addition to backing up all databases in SQL 2000? Thanks.
September 19, 2005 at 10:11 am
I see now that some of my problem lies in the fact that I am moving the location of my master database when I am restoring it. I have to do this because I am unable to build my new server exactly as my old one. The old box was upgraded to SQL 2000 from SQL 7. All system databases reside in MSSQL7/Data. I have tried many ways but I am unable to make the install of SQL 2000 put the system databases in this new location. If I specify MSSQL7 as the directory path on the install it puts them in MSSQL7\MSSQL. Here in lies my problem.
September 19, 2005 at 10:36 am
I ran into something similar to this before. One thing to check is that the tempdb files are located in the same location as your previous server. If they are not you will need to sychronize your master database to let it know where the files are. You can check to see where master thinks tempdb is by running:
select * from sysdatabases
September 20, 2005 at 1:19 am
Your problem lies not in the different location of master db itself (because on its restore the file locations of the currect server are used, not the ones recorded in the backup set) but in the pointers to the other system dbs in the restored master.
See these articles from MS or search this site (it's been discussed several times before)
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;304692
After restoring master you must follow these steps: 1.start the server with 3608 trace
2.detach model db
3.place model datafiles to the destination location (use the original server files or files from SP3 installation - this assumes that you have no custom settings applied to your model db)
4.attach model
5.change the location of tempdb
6.now sql server should go up without trace flags - restore msdb from backup using MOVE clause
7.restore user dbs
8.optionally change server name using sp_addserver for @@SERVERNAME to work or update sysjobs. originating_server column for jobs to work properly
September 22, 2005 at 11:27 am
I have encountered the exact same problem, except in my case it's not disaster recovery but migrating to a new server (W2K to Win 03, so I can't just backup/restore the whole server). We have about 280 DBs, and the DB files are not all in the same directory because we previously upgraded from 7.0 to 2000, and despite my efforts to the contrary, all new DBs (about 60) were created in a new directory. We create new DBs periodically -- it's the nature of our needs.
When researching the problem, I saw this thread as well as the MS articles describing backup/restore and the Copy Database Wizard. I had intended to do backup/restore, but here's my question 1: Is it better/easier/fewer gotchas to use the Wizard instead? Will it handle multiple DBs in one pass?
Question 2: I've been wanting to move all the DBs in the same directory to be cleaner (and easier to find all of them). I was going to do this during backup/restore, since the system was down anyway, but I'd like opinions as to whether it would be better to make it a whole separate operation.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply