August 14, 2002 at 3:02 pm
I am trying to walk through a DR plan. I have set up a clean install of SQL Server 7 (SP3) on a test server. The first thing I attempt is to restore the master database. I start SQL Server in single user mode. I connect with query analyzer and restore the master. I get a message "The master database has been successfully restored. Shutting down SQL Server". When I attempt to restart SQL Server, I get the following:
2002-07-25 03:33:08.95 spid1 Starting up database 'model'.
2002-07-25 03:33:09.94 spid1 Opening file d:\MSSQL7\DATA\model.mdf.
2002-07-25 03:33:09.96 kernel FCB::Open failed: Could not open device d:\MSSQL
7\DATA\model.mdf for virtual device number (VDN) 1.
2002-07-25 03:33:10.05 spid1 Device activation error. The physical file name
'd:\MSSQL7\DATA\model.mdf' may be incorrect.
2002-07-25 03:33:10.09 spid1 Database 'model' cannot be opened because some o
f the files could not be activated.
The problem is that SQL Server is looking for 'model' at d:\MSSQL7\DATA\model.mdf. That is where it was on the server that the master DB was backed up. On this test server, it is on c:\MSSQL7\DATA\model.mdf. Do the SQL server installations have to be in the same directory on both machines for backup/restore of the master to work?
Also, I tried to recover the master database using rebuildm.exe. It appears to work successfully, and SQL Server starts OK. But when I try to connect from any client using any logon, I get Msg 18456 Level 16 State 1 Login Failed For User ".../..." (I am using Windows NT Authentication").
Any help on either problem would be greatly appreciated. Thanks.
Tom McGehee
August 15, 2002 at 2:56 am
One of the scenarios I use when doing DR tests is
I. Rebuild SQL Server via rebuildm
II. Bring SQL Server up in single user mode
III. Recover master
IV. Restart SQL Server service
V. Modify Sa account to use master as default database
• From MS DOS prompt cd D:\MSSQL7\Binn
• Type isql –Uusername –Ppassword –Sservername (substitute sa etc.)
• Type the following sql script
Use master
Go
Sp_defaultdb sa, master
Go
VI. Set Enterprise Manager to SQL authentication
VII. Restore model and msdb (moving them as necessary)
VIII. Restore user databases
IX. Set Enterprise Manager to NT authentication
I have managed to restore via this method to totally different disk structures.
August 15, 2002 at 3:35 pm
quote:
I. Rebuild SQL Server via rebuildmII. Bring SQL Server up in single user mode
After I rebuild with rebuildm and bring up the server, it will not allow me to log in. Trying to login with any user from any client yields "Login Failure For ...".
quote:
III. Recover masterIV. Restart SQL Server service
If I start from a clean install of SQL Server, restore the master, it will not restart SQL server.
August 15, 2002 at 4:39 pm
Once you restore, you're trying to connect as SA or using NT login?
Andy
August 15, 2002 at 8:46 pm
Using NT login. It was installed with NT authentication only. I did try SA, but got an error that it was not authenticated.
August 16, 2002 at 4:45 am
No idea. I'll try to experiment this weekend if no one solves it, though I dont have SQL7 installed, SQL2K only.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply