November 29, 2002 at 2:43 pm
Help! I have followed the instructions in Microsoft Knowledge Base Article - 304692 to restore a backup of master on a new sql server.
Once in single user mode, I cannot restore master either through EM or t-sql using the restore command (detailed in the KB article). I get the following error when trying to restore:
Server: Msg 3708, Level 16, State 4, Line 1
Cannot drop the database 'master' because it is a system database.
Server: Msg 3166, Level 16, State 1, Line 1
RESTORE DATABASE could not drop database 'master'. Drop the database and then reissue the RESTORE DATABASE statement.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Thanks for your help. -ajroney
Anybody have any idea what I am doing wrong?
November 30, 2002 at 2:13 pm
How are you trying to restore the database. Are you selecting the restore over existing database. If so try deselecting this. I am only guessing as I haven't seen this before.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
December 1, 2002 at 7:38 pm
Odds are the reason is because you had yourself in the context of master. In TSQL run
USE msdb
Go
then your restore items. I made this mistake previously. You should also do with no EM windows open as EM makes some calls to master when loading info.
December 3, 2002 at 8:48 am
Hmmmmm. Using MSDB to run the restore script still didn't work. I do want to force a restore over the existing MASTER DB, however, I removed the replace option from the script, and this still didn't seem to work. Follwing is the restore script I am using and the error message. Thanks for the response so far....any other ideas?? I have also confirmed that I am in Single User Mode with no hooks from any EM sessions.
use msdb
go
RESTORE DATABASE master FROM disk='\\MyPath\d$\DataMove\Master_11292002.bckup'
WITH MOVE 'master' to 'v:\SQL_DATA\Data\Master.mdf',
MOVE 'mastlog' to 'z:\SQL_DATA\Data\Mastlog.ldf'
go
Server: Msg 3708, Level 16, State 4, Line 1
Cannot drop the database 'master' because it is a system database.
Server: Msg 3166, Level 16, State 1, Line 1
RESTORE DATABASE could not drop database 'master'. Drop the database and then reissue the RESTORE DATABASE statement.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
December 3, 2002 at 2:26 pm
I tested this a while back at our site. Here are my notes.
To restore the master database from a SQL Server backup:
- Stop SQL Server:
- Open an NT DOS window
net stop sqlserveragent
net stop mssqlserver
- Use Windows Explorer to locate the appropriate master database disk location
- Copy the old master database files for safe keeping:
- Return to the NT DOS window
copy master.mdf corrupt_master.*
copy mastlog.ldf corrupt_mastlog.*
- Start SQL Server in single user mode:
- Return to the NT DOS window
- (Set your default directory to the folder in which the master database resides)
- (Make sure that sqlservr.exe is in your command path)
- (Note: If you wish to start a named instance of SQL Server in single user mode, you will need to add the "-s" parameter to the sqlservr.exe command)
sqlservr -c -m
You should see output similar to the following (including a message that SQL Server is starting in single user mode--if you do not see a single user message, try placing a trailing space after the "-m" parameter):
2002-09-04 10:30:03.48 server Microsoft SQL Server 2000 - 8.00.655 (Intel X86)
Jul 3 2002 18:10:57
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
2002-09-04 10:30:05.07 server Copyright (C) 1988-2000 Microsoft Corporation.
2002-09-04 10:30:05.37 server All rights reserved.
2002-09-04 10:30:05.57 server Server Process ID is 1852.
2002-09-04 10:30:05.81 server Logging SQL Server messages in file 'C:\MSSQL7\log\ERRORLOG'.
2002-09-04 10:30:06.23 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2002-09-04 10:30:06.84 server SQL Server configured for fiber mode processing.
2002-09-04 10:30:07.17 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2002-09-04 10:30:07.78 server Attempting to initialize Distributed Transaction Coordinator.
2002-09-04 10:30:10.87 spid4 Warning ******************
2002-09-04 10:30:11.14 spid4 SQL Server started in single user mode. Updates allowed to system catalogs.
2002-09-04 10:30:11.56 spid4 Starting up database 'master'.
2002-09-04 10:30:12.25 server Using 'SSNETLIB.DLL' version '8.0.534'.
2002-09-04 10:30:12.25 spid5 Starting up database 'model'.
2002-09-04 10:30:12.26 spid4 Server name is 'RKEDBA'.
2002-09-04 10:30:12.90 server SQL server listening on 10.57.29.228: 1433.
2002-09-04 10:30:13.09 spid8 Starting up database 'pubs'.
2002-09-04 10:30:13.09 spid7 Starting up database 'msdb'.
2002-09-04 10:30:13.37 server SQL server listening on 127.0.0.1: 1433.
2002-09-04 10:30:13.68 spid9 Starting up database 'Northwind'.
2002-09-04 10:30:13.96 spid10 Starting up database 'ProdDBA'.
2002-09-04 10:30:14.98 server SQL server listening on TCP, Shared Memory, Named Pipes.
2002-09-04 10:30:15.35 server SQL Server is ready for client connections
2002-09-04 10:30:15.68 spid5 Clearing tempdb database.
2002-09-04 10:30:17.46 spid5 Starting up database 'tempdb'.
2002-09-04 10:30:17.96 spid4 Recovery complete.
2002-09-04 10:30:18.17 spid4 SQL global counter collection task is created.
- Open SQL Query Analyzer to restore the database (connect using Windows authentication—note that you apparently cannot use Enterprise Manager for this task because it will think that SQL Server is stopped)
-- Note: For this task, it is easier to review the query results in grid
-- format, rather than in text format
-- Display the server name to verify you are connected with the correct server
select @@servername
-- Display a catalog of the backup device (you need to pay attention to
-- the Position and DatabaseName columns in the output)
restore headeronly from tapedrive
-- Optionally display file information for the file names for a given database
-- (replace nnn with the Position number)
restore filelistonly from tapedrive with file=nnn
-- Restore the master database (replace nnn with the Position number)
restore database master from tapedrive with file=nnn, stats
You should see output similar to the following:
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
- SQL Server should have stopped automatically—if it did not stop, force it down:
- Return to the first NT DOS window
Press [Ctrl/C]
- Start SQL Server:
- Return to the NT DOS window
net start mssqlserver
net start sqlserveragent
- Run a backup on the master database
- If you rebuilt the master database (with the Rebuildm utility), be sure to restore the msdb database to recover backup history, SQL Agent jobs, etc.
December 3, 2002 at 3:42 pm
Shew did a great job there. I am wondering if you have started the server in single user mode? That is this line
sqlservr -c -m
You have to have the server in single user mode so you can do the restore thru QA. When the restore is done it will tell you service stopped and process terminated. Then restart SQL Server.
Also, master cannot be moved during the RESTORE process so it should end up where they currently are. If you need to move afterwards see the following KB article on doing this.
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
December 4, 2002 at 1:36 pm
I think You can not change the master date file path when you restore it.
I made the same mistake previously.
good luck
December 5, 2002 at 10:27 am
Thank you to everyone who responded to this topic. It seems the root of my problem was that I was trying to restore master "WITH MOVE" to a new location.
Once I just restored master without the "WITH MOVE" option, everything worked. Thank you to Antares686 for pointing out you cannot move the files when restoring master.
Interesting enough, KB Article 304692 does illustrate a script that restores master "WITH MOVE". I couldn't get it to work, so maybe it is a mistake.
Thanks again, ajroney.
December 5, 2002 at 2:48 pm
I would suggest reporting to MS as a documentation error.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply