Restoring System Databases

  • 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

  • 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
  • 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" 😉

  • [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>

  • 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
  • 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?

  • 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
  • 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" 😉

  • Hi Gila,

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

  • Hi Perry,

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

  • Hi,

    A Question. AFter the rebuild I'm seeing the output of this following query showing 0, earlier it used to be 1. What is it? Am i supposed to enable anything?

    SELECT is_broker_enabled

    FROM sys.databases

    WHERE name = N'msdb'

    Thanks..

  • You had service broker enabled before, you'll need to re enable it again. Ideally you should restoring your current backups of your system databases to retrieve the previous configuration.

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thats right. I did restore my backups(just master,model and msdb) after the rebuild. Anything else?

    What procedure do i need to take to renable it manaully otherwise?

    Thanks

  • mJai (4/26/2013)


    Anything else?

    Yes enable service broker on the database, it's detailed in books online.

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks, That aside, is there a way to verify or compare and ensure that both the msdb databases are same. I mean the msdb backup that i had and the one thats currently active on SQL?

    Just wanting to ensure that both copies are the same and I have restored the right ones. 🙂

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply