An example for using xp_cmdshell… moving user databases. See the script…
It all starts with this error when we try to connect to SQL Server instance:
At SQL Server Configuration Manager, we could see that the SQL Server service is also stopped and, when we try to start the service, we got the following error:
So we must consult the Windows Event Log to see what is the problem of the instance (Use Windows key + R and type eventvwr.exe, as shown in the next picture)
Expand Windows Logs and then click in Application. (I also use a filter on the source column)
It’s now clear that we have a problem in master database (more information about master at MSDN)!
We could also try to start the instance with minimal configuration (the -f startup parameter, more info at MSDN) but the problem remains.
Step 1 – Repair System Databases
So, since we couldn’t start the service we must repair the master database. Repairing master database also rebuilds all of the system databases. This means that is very important to make a restore of this databases after the repair.
To perform a system databases repair we should use the SQL Server installation media but, in version 10 and later versions, the template databases are store at C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates (in the case of the default instance of SQL Server 2014) and avaliable to usage in the repair process.
From command prompt, go to the following path:
- for SQL Server 2008:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008\
- for SQL Server 2008R2:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\
- for SQL Server 2012:
C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\
- for SQL Server 2014:
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\
And then we should run setup.exe with the following logic:
setup.exe /Q /ACTION=REBUILDDATABASE /INSTANCENAME=«InstanceName» /SQLSYSADMINACCOUNTS=«SysSccount» [/SAPWD=«StrongPassword»] [/SQLCOLLATION=«CollationName»]
For this particular case, we don’t want to change the collation (and use the default server collation). So the command is:
setup.exe /Q /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=DELL\Pedro /SAPWD=$trong3$t
After we run this command we don’t get any feedback (unless there are syntax problems or issues with parameters), so we must confirm the correct repair by open the file summary.txt at C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log (in the case of SQL Server 2014). An example of a failled repair operation:
And an exemple of what we are looking for:
INFO: By analysing SQL Server log we can see that were used some startup parameters:
After repairing the system databases we should apply the latest service pack and any applicable hot fixes.
The next step is making a restore of all of system databases, using their latest backup avaliable (if we don’t change the collation in the repair process). In this example we will only be performing the restore of master database. To do that is better to use the command prompt.
Step 2 – Restore Master Database
To do a restore of the master database we must start SQL Server service in single user mode. We can do that by using a NET command and using the -m startup parameter (learn more about SQL Server Service startup options at MSDN).
NET START MSSQLSERVER /m "SQLCMD"
We get this information:
INFO: At this moment if we try to connect to SQL Server with SSMS we will get the following error message:
Now connect to the instance using sqlcmd utility (more information about the usage of sqlcmd at MSDN).
HELP: If you don’t remeber the path of sqlcmd utility use:
dir /s sqlcmd.exe
As we can see in the image above, the path of sqlcmd utility is:
c:\Program Files\Microsoft SQL Serve\Client SQK\ODBC\110\Tools\Binn\
Type sqlcmd and we are ready to make the restore by typping:
RESTORE DATABASE master FROM DISK = 'D:\DBs\Backups\master.bak' WITH REPLACE; GO;
The screen should be like this:
As you can see from the above screen, after the database as been restored, the SQL Server service is stopped.
NOTE: You should also restore MSDB and MODEL database!! But that is not in the scope of this post.
Now we can start the instance in multi-user mode typing:
NET START MSSQLSERVER
The final step is recreating all the missing entries but, if we followed the best pratices for master database backup, this should not be a problem because it is a best practice to do a master database backup every time we change it.
So, at this point, we should be back online without any issues!
A step-by-step guide about how to create a new rule in Windows Firewall for SQL Server.
and the additional information wasn’t that “informative”!
Using SQL Server Configuration Manager, I also couldn’t start SQL Server Service…
So I went to see what Event Viewer had to say about the error (To open Event Viewer we need to open a run box by pressing Win+R, now type eventvwr.msc and press Enter)
I searched on Windows Logs » Application…
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7b9f1759; actual: 0xedad01cc). It occurred during a read of page (1:0) in database ID 3 at offset 0000000000000000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAmodel.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xd0fbd0b1; actual: 0x849ef734). It occurred during a read of page (2:0) in database ID 3 at offset 0000000000000000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAmodellog.ldf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The problem was very clear: Model system database corrupted!
On part 2, I will explain the resolution of the problem…