Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Restoring System Databases Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 7:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
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
Post #1445742
Posted Tuesday, April 23, 2013 11:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
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 2008, MVP
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

Post #1445771
Posted Wednesday, April 24, 2013 12:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 6,318, Visits: 13,622
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"
Post #1445772
Posted Wednesday, April 24, 2013 1:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
[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>
Post #1445798
Posted Wednesday, April 24, 2013 3:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
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 2008, MVP
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

Post #1445809
Posted Wednesday, April 24, 2013 3:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
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?
Post #1445817
Posted Wednesday, April 24, 2013 3:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
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 2008, MVP
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

Post #1445819
Posted Wednesday, April 24, 2013 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 6,318, Visits: 13,622
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"
Post #1446048
Posted Wednesday, April 24, 2013 11:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
Hi Gila,

Thank you very much. I was able to successfully restore back using the method indicated by you. Many Thanks!


Post #1446296
Posted Wednesday, April 24, 2013 11:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:04 PM
Points: 46, Visits: 113
Hi Perry,

Thank you very much. Defenitely helped me get through this restoration phase.
Post #1446298
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse