SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restoring System Databases


Restoring System Databases

Author
Message
mJai
mJai
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 114
Hi ,

Im using an Active/Passive SQL Cluster 2008 R2 Standard Edition.
In my task to relocate the System Databases, I messed up the db names and file paths ( so stupid of me ) of msdb and model.


Just prior to start, I have taken a backup of the 3 databases model, msdb and maste.


Since i realised that I made a mistake and I was still connected to SSMS , i restored the backup of msdb but the SQL database fails to start.

I tried connecting using :

net start mssqlserver /f /m /t3608

That started the database. Then i proceeded to connect to the database using:

sqlcmd -e

This resulted in the following error.

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
stance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see
SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.



I then navigated to the SQL logs and found the following erros logged. Please advice how i should proceed or How i can get the database up and running (SQL cluster) and restore the backup for msdb and model if necessary.

Error Log:
2013-04-24 08:53:50.73 Server Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

2013-04-24 08:53:50.73 Server (c) Microsoft Corporation.
2013-04-24 08:53:50.73 Server All rights reserved.
2013-04-24 08:53:50.73 Server Server process ID is 4428.
2013-04-24 08:53:50.73 Server System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
2013-04-24 08:53:50.73 Server Authentication mode is WINDOWS-ONLY.
2013-04-24 08:53:50.73 Server Logging SQL Server messages in file 'P:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2013-04-24 08:53:50.73 Server This instance of SQL Server last reported using a process ID of 1424 at 4/24/2013 8:53:49 AM (local) 4/24/2013 12:53:49 AM (UTC). This is an informational message only; no user action is required.
2013-04-24 08:53:50.73 Server Registry startup parameters:
-d P:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-e P:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l P:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2013-04-24 08:53:50.73 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2013-04-24 08:53:50.73 Server Detected 4 CPUs. This is an informational message; no user action is required.
2013-04-24 08:53:51.44 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2013-04-24 08:53:51.60 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-04-24 08:53:51.96 spid7s Starting up database 'master'.
2013-04-24 08:53:52.03 spid7s CHECKDB for database 'master' finished without errors on 2013-04-02 13:52:09.170 (local time). This is an informational message only; no user action is required.
2013-04-24 08:53:52.06 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2013-04-24 08:53:52.08 spid7s SQL Trace ID 1 was started by login "sa".
2013-04-24 08:53:52.09 spid7s Starting up database 'mssqlsystemresource'.
2013-04-24 08:53:52.10 spid7s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
2013-04-24 08:53:52.37 spid7s Server name is 'SQLCLUSTER'. This is an informational message only. No user action is required.
2013-04-24 08:53:52.37 spid7s The NETBIOS name of the local node that is running the server is 'NODE02'. This is an informational message only. No user action is required.
2013-04-24 08:53:52.44 spid11s Starting up database 'model'.
2013-04-24 08:53:52.46 spid11s Error: 5173, Severity: 16, State: 1.
2013-04-24 08:53:52.46 spid11s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2013-04-24 08:53:52.46 spid11s Error: 5173, Severity: 16, State: 1.
2013-04-24 08:53:52.46 spid11s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2013-04-24 08:53:52.46 spid11s Log file 'P:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
2013-04-24 08:53:52.46 spid11s Error: 945, Severity: 14, State: 2.
2013-04-24 08:53:52.46 spid11s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2013-04-24 08:53:52.46 spid11s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2013-04-24 08:53:52.46 spid11s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.



Many Thanks,
Jai
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231339 Visits: 46350
Have a read through this, see if it helps
https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

It may be easier to just rebuild the system databases then restore the backups after.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55853 Visits: 17719
Do you have another backup of model you can use?

If not grab a copy of model from another server with the same collation and version.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
mJai
mJai
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 114
[font="Courier New"]Yes, I have the backup of all the three....

msdb, model and master

How can i proceed....? Im unable to get connected using SQLCMD to be able to do anything....I see the following error :



C:\Windows\system32>net start mssqlserver /f /m /t3608
The SQL Server (MSSQLSERVER) service is starting..
The SQL Server (MSSQLSERVER) service was started successfully.


C:\Windows\system32>SC QUERY MSSQLSERVER

SERVICE_NAME: MSSQLSERVER
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

C:\Windows\system32>SQLCMD -E -SADMIN:MSSQLSERVER
HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: An error occurred while obtaining the dedicated a
dministrator connection (DAC) port. Make sure that SQL Browser is running, or ch
eck the error log for the port number [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
stance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see
SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

C:\Windows\system32>
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231339 Visits: 46350
Did you read the article I referenced?

At this point, it may be easier to rebuild the system tables and then restore master, model and msdb.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mJai
mJai
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 114
Yes, Extemely informative article.

I just hope that i had a quick way to restore my 3 backups in a single go (master, model and msdb) with something like Sqlservr.exe –T3608 if I could just get them to point recovery to my backups.
Alternatively if i could just generate the mdf and ldf from those 3 backups then I could just chuck them into the folder and try to restart the db?

I unfortunately do not have the actual mdf and ldf files for master. I just have the backups of those three.

Also, if I understand correctly the article says that it will rebuild the database(something like blank db) using SETUP.EXE and then we will need to restore the three databases(master,model and msdb) from the backups. right?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231339 Visits: 46350
Yes, rebuild the system DBs sets them all back to default and you will need to restore all three backups later, and the process to restore system databases is documented in Books Online

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55853 Visits: 17719
C:\Windows\system32>net start mssqlserver /f /m /t3608
The SQL Server (MSSQLSERVER) service is starting..
The SQL Server (MSSQLSERVER) service was started successfully.


Firstly don't start sql server as a service, also -f implies -m, you just need the following command from the Windows command prompt

SQLSERVR.EXE –f –T 3608



If you have backups of the 3 dataabses, restore them to another similar instance as user databases. So restore them as

Usermaster
Usermodel
Usermsdb

Detach them from the instance and rename the disk files back, then plug them back into the original instance.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
mJai
mJai
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 114
Hi Gila,

Thank you very much. I was able to successfully restore back using the method indicated by you. Many Thanks!
mJai
mJai
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 114
Hi Perry,

Thank you very much. Defenitely helped me get through this restoration phase.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search