April 23, 2013 at 7:29 pm
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
April 24, 2013 at 12:00 am
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
April 24, 2013 at 12:00 am
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" 😉
April 24, 2013 at 1:50 am
[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>
April 24, 2013 at 3:03 am
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
April 24, 2013 at 3:21 am
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?
April 24, 2013 at 3:29 am
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
April 24, 2013 at 9:01 am
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" 😉
April 24, 2013 at 11:24 pm
Hi Gila,
Thank you very much. I was able to successfully restore back using the method indicated by you. Many Thanks!
April 24, 2013 at 11:25 pm
Hi Perry,
Thank you very much. Defenitely helped me get through this restoration phase.
April 25, 2013 at 9:05 pm
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..
April 25, 2013 at 9:15 pm
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" 😉
April 26, 2013 at 12:25 am
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
April 26, 2013 at 1:03 am
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" 😉
April 26, 2013 at 2:59 am
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