July 15, 2004 at 1:38 pm
I restored several user databases (30 or so) from one server to a test box (for disaster recovery testing purposes). I am running sql server 2000, service pack 3a and the latest security hot fix on both boxes. After all of the user databases were restored, I restored msdb and BCP'd in the Sysxlogins table. I then checked the sysdatabases table in the master database on both the test box and the prod box. I had restored them in alpha order, not necessarily their dbid order. When checking the dbids on both boxes, they were off slightly (about 5 databases had different dbids in the new test environment). I opened the sysdatabases table and changed the dbids on those five or so databases to match the numbers that they were on the prod box (in theory thinking that logins or backup jobs might not work with the conflicting dbids). It seemed to go ok until a couple of days later when I went to wipe out all of the databases to do other testing, I noticed that all of the db's with the changed dbid were in suspect mode. When I tried to delete the databases (in suspect mode and running), the sql 2000 program would hang. Should you change the dbid to match the prod server, does it matter, why did the db's go into suspect mode.
Any help is appreciated.
Paula
July 16, 2004 at 7:28 am
I restore databases from a production server to test and development servers a couple times a week and have nerver had a problem with DBIDs.
July 16, 2004 at 11:32 am
We do the same thing routinely. The issue I have is the restoration of msdb and the fact that the dbid's were not the same on the new box vs. production. On the ones that we routinely back up, most of the db's were created in the same order in prod as in test and/or we don't routinely restore msdb when doing our weekly restores.
Paula
July 16, 2004 at 11:44 am
My DBIDs are not the same. This is no problem. I have been doing it for years.
I don't know what problems you may run into with DBIDs that are in msdb.
July 16, 2004 at 12:02 pm
I restored the master, msdb and 5 user db's in that order for dr testing yesterday. No problems at all.
Yes the non-existant db's where suspect until I restored those, but all where fine in the end.
Steven
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply