Problems restoring master db in SQL 2005
-
Hello -
I am testing out some of my backups (so I don't wind up with garbage data later on), and one of the things I am running into is an issue with restoring the master db. I understand that I have to be in single user mode in order to do this, and have done so from the command line with the -m flag as I execute it.
The problem is that even after I do this (with services off, et al), I still am unable to do a restore of the master db (it errors out whenever I attempt to restore it from my full.bak file).
All the research to date has simply said to be in single user mode in order to restore it properly. Any thoughts, suggestions, or scripts that you might have would be greatly appreciated. Also - I have been able to restore other system db's fine (they have the single user mode icon by them too when I do this, but master does not).
This is on a SQL 2005 box at SP3.
Thank you all for any and all suggestions!
-
You also need to shut down the SQL Server instance, remove the single-user mode parameter and then restart the instance to complete the restore.
Is this where you are facing the problem?
Also, if you can post the error message, that would be wonderful.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.comFollow me on
Twitter: @sqltwins -
Hello Nakul -
Basically - these are the steps I am taking...
1. Close SSMS, and shut down all SQL Services listed (probably even ones that do not need to be shut down, but for the sake of this test - they are all shut down).
2. Navigate to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn, and launch the SQL Server executable sqlservr.exe -m. This produces the following...
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr.exe -m2011-02-14 09:31:15.08 Server Microsoft SQL Server 2005 - 9.00.4035.00 (Int
el X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
2011-02-14 09:31:15.08 Server Error: 17054, Severity: 16, State: 1.
2011-02-14 09:31:15.08 Server The current event was not reported to the Win
dows Events log. Operating system error = 1502(The event log file is full.). You
may need to clear the Windows Events log if it is full.
2011-02-14 09:31:15.08 Server (c) 2005 Microsoft Corporation.
2011-02-14 09:31:15.08 Server All rights reserved.
2011-02-14 09:31:15.08 Server Server process ID is 2836.
2011-02-14 09:31:15.08 Server Authentication mode is WINDOWS-ONLY.
2011-02-14 09:31:15.08 Server Logging SQL Server messages in file 'C:\Progr
am Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2011-02-14 09:31:15.08 Server This instance of SQL Server last reported usi
ng a process ID of 1224 at 2/14/2011 9:28:33 AM (local) 2/14/2011 3:28:33 PM (UT
C). This is an informational message only; no user action is required.
2011-02-14 09:31:15.08 Server Registry startup parameters:
2011-02-14 09:31:15.08 Server -d C:\Program Files\Microsoft SQL Serve
r\MSSQL.1\MSSQL\DATA\master.mdf
2011-02-14 09:31:15.08 Server -e C:\Program Files\Microsoft SQL Serve
r\MSSQL.1\MSSQL\LOG\ERRORLOG
2011-02-14 09:31:15.08 Server -l C:\Program Files\Microsoft SQL Serve
r\MSSQL.1\MSSQL\DATA\mastlog.ldf
2011-02-14 09:31:15.08 Server Command Line Startup Parameters:
2011-02-14 09:31:15.08 Server -m
2011-02-14 09:31:15.09 Server SQL Server is starting at normal priority bas
e (=7). This is an informational message only. No user action is required.
2011-02-14 09:31:15.09 Server Detected 2 CPUs. This is an informational mes
sage; no user action is required.
2011-02-14 09:31:15.85 Server Using dynamic lock allocation. Initial alloc
ation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an infor
mational message only. No user action is required.
2011-02-14 09:31:15.88 Server Attempting to initialize Microsoft Distribute
d Transaction Coordinator (MS DTC). This is an informational message only. No us
er action is required.
2011-02-14 09:31:15.90 Server The Microsoft Distributed Transaction Coordin
ator (MS DTC) service could not be contacted. If you would like distributed tra
nsaction functionality, please start this service.
2011-02-14 09:31:15.90 Server Database Mirroring Transport is disabled in t
he endpoint configuration.
2011-02-14 09:31:15.90 spid5s Warning ******************
2011-02-14 09:31:15.90 spid5s SQL Server started in single-user mode. This
an informational message only. No user action is required.
2011-02-14 09:31:15.91 spid5s Starting up database 'master'.
2011-02-14 09:31:16.02 spid5s Recovery is writing a checkpoint in database
'master' (1). This is an informational message only. No user action is required.
2011-02-14 09:31:16.13 spid5s SQL Trace ID 1 was started by login "sa".
2011-02-14 09:31:16.19 spid5s Starting up database 'mssqlsystemresource'.
2011-02-14 09:31:16.21 spid5s The resource database build version is 9.00.4
035. This is an informational message only. No user action is required.
2011-02-14 09:31:16.43 spid8s Starting up database 'model'.
2011-02-14 09:31:16.43 spid5s Server name is 'BMULLIN_WKSTN'. This is an in
formational message only. No user action is required.
2011-02-14 09:31:16.54 spid8s Clearing tempdb database.
2011-02-14 09:31:17.01 Server A self-generated certificate was successfully
loaded for encryption.
2011-02-14 09:31:17.02 Server Server is listening on [ 'any' <ipv4> 1433].
2011-02-14 09:31:17.02 Server Server local connection provider is ready to
accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2011-02-14 09:31:17.02 Server Server local connection provider is ready to
accept connection on [ \\.\pipe\sql\query ].
2011-02-14 09:31:17.14 Server Server is listening on [ 127.0.0.1 <ipv4> 143
4].
2011-02-14 09:31:17.14 Server Dedicated admin connection support was establ
ished for listening locally on port 1434.
2011-02-14 09:31:17.17 Server The SQL Network Interface library could not r
egister the Service Principal Name (SPN) for the SQL Server service. Error: 0x20
98, state: 15. Failure to register an SPN may cause integrated authentication to
fall back to NTLM instead of Kerberos. This is an informational message. Furthe
r action is only required if Kerberos authentication is required by authenticati
on policies.
2011-02-14 09:31:17.17 Server SQL Server is now ready for client connection
s. This is an informational message; no user action is required.
2011-02-14 09:31:17.20 spid8s Starting up database 'tempdb'.
2011-02-14 09:31:17.25 spid12s Starting up database 'ReportServerTempDB'.
2011-02-14 09:31:17.25 spid10s Starting up database 'msdb'.
2011-02-14 09:31:17.25 spid13s Starting up database 'AdventureWorks'.
2011-02-14 09:31:17.25 spid11s Starting up database 'ReportServer'.
2011-02-14 09:31:17.25 spid14s Starting up database 'aspnetdb'.
2011-02-14 09:31:17.26 spid15s Starting up database 'OnDemand'.
2011-02-14 09:31:17.26 spid16s Starting up database 'Treater'.
2011-02-14 09:31:17.27 spid13s Error: 17207, Severity: 16, State: 1.
2011-02-14 09:31:17.27 spid13s FCB::Open: Operating system error 2(The syste
m cannot find the file specified.) occurred while creating or opening file 'C:\P
rogram Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. D
iagnose and correct the operating system error, and retry the operation.
2011-02-14 09:31:17.27 spid13s Error: 17204, Severity: 16, State: 1.
2011-02-14 09:31:17.27 spid13s FCB::Open failed: Could not open file C:\Prog
ram Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf for fi
le number 1. OS error: 2(The system cannot find the file specified.).
2011-02-14 09:31:17.27 spid13s Error: 5120, Severity: 16, State: 101.
2011-02-14 09:31:17.27 spid13s Unable to open the physical file "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf". Operatin
g system error 2: "2(The system cannot find the file specified.)".
2011-02-14 09:31:17.41 spid13s Error: 17207, Severity: 16, State: 1.
2011-02-14 09:31:17.41 spid13s FileMgr::StartLogFiles: Operating system erro
r 2(The system cannot find the file specified.) occurred while creating or openi
ng file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks
_Log.ldf'. Diagnose and correct the operating system error, and retry the operat
ion.
2011-02-14 09:31:17.41 spid13s File activation failure. The physical file na
me "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.
ldf" may be incorrect.
2011-02-14 09:31:19.18 spid5s Recovery is complete. This is an informationa
l message only. No user action is required.
3. I then restart SSMS, and connect to my default instance.
4. It shows that it is offline (it's red) but I am able to see the db's as I expand the list out.
5. I Right-Click on the master db to do the task of the restore. I navigate to where my Full.bak file is at, select the master db from the list
and then I get the following error when I try to restore it...
Any thoughts would be greatly appreciated!
-
Are you backing up all your databases to the same backup set? If so I would stop doing that.
Looks like the backup file you are selecting is not the master database, use restore filelistonly to find the master database backup with the backup set.
---------------------------------------------------------------------
-
Hello George!
Yes - I am backing up all of my databases (system and user db's) to a single file. Was not aware that this was a bad practice. Should I do the master db all by itself for the full? Or is it okay to put msdb and model in there with it?
Also, I was in fact selecting the master db for restore, and having to type it into the field for which database to restore to (as it never shows up in the list).
When you say "use restore filelistonly", is that another option under Tasks that I was not seeing?
Thanks again, and I appreciate your feedback.
-
backup every database to a separate file, don't mix different backups together. In fact don't use backup sets, I can't see any point in them. As you are finding out it just causes problems when you want to restore.
filelistonly is an option of the restore command, it lists every file in the backup. Is not an option through the GUI so do it as a TSQL command in the query window. Read up on backup and restore in books online and do these commands in TSQL. when you are familiar with them you can revert to the gui when you are in a hurry or just feeling lazy.
Backup your master database to file and then the restore command is simple:
restore database master from disk = 'path to your backup' with replace
then SQL will stop.
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply