Rebuilding SQL Server on different hardware after a failure

By:   |   Updated: 2011-08-12   |   Comments (6)   |   Related: > Disaster Recovery


Problem

Your SQL Server has crashed and you need to quickly get it up and running on another physical server. Instead of having to build a new server and install SQL, we look at how to recreate the entire SQL Server environment on an existing server.

Solution

To quickly recreate the environment we are going to use an existing SQL Server that is already setup for maybe standby, QA or something else. The process will involve restoring the master, msdb and user databases. In order for this to work, both the source and target SQL Servers must be on the exact service pack otherwise it will not be possible to successfully restore the master database on the target server.

For our example these are the environments that I am working with:

  • Source: SQL 2008 SP2 (10.0.4000). master, model and msdb databases are located on drive E: while tempdb is on a dedicated drive K: and set with an initial size of 10GB
  • Target: SQL 2008 SP2 (10.0.4000). target server has only one drive C: and it does not have enough disk space to create a 10GB file for tempdb

To get the version of the target environment we can run the following command in SSMS.

SELECT @@version

To get the version of the source environment we can run this command in SSMS against the master backup file and check the following fields: SoftwareVersionMajor, SoftwareVersionMinor, SoftwareVersionBuild

RESTORE HEADERONLY FROM DISK ='X:\Backup\master_backup_2011_02_17_000001_1662523.BAK'

In the image below, we can see the version is 10.0.4000.

run the select@@version command in ssms

Now that we have verified that both environments have the same version of SQL Server we can start the rebuild process on the target server.

Rebuild Steps

Step 1:
On the target SQL Server shutdown the services: SQL Server, Agent, SSIS, etc.. using SQL Server Configuration Manager.

start the rebuild process on the target server

Step 2:
Open a CMD window and start SQL Server in single user mode using this command:

sqlservr -m

start sql server in single user mode

Step 3:
In a new CMD window start SQLCMD and restore the master database using a command similar to the following.

RESTORE DATABASE MASTER
FROM DISK ='X:\Backup\master_backup_2011_02_17_000001_1662523.BAK'
WITH REPLACE,
MOVE 'MASTER' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf',
MOVE 'MASTLOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf'

start sqlcmd and restore the master databaseI

Step 4
As soon as the master database restore is complete, the instance of SQL Server that was started in single user mode stops. If we try to run it again using "sqlservr -m", SQL Server will not start because the master database contains a reference that model and msdb are on the E: drive and tempdb is on the K: drive, but our target server does not have drives E or K.

In order to fix this drive issue, we have to start SQL server as follow from a CMD window:

sqlservr -c -m -f -T3608

These are what the parameters do:

-c: start sql not as a service
-f: start with minimal configuration
-T3608: trace flag 3608 skips automatic recovery for all databases except the master database

Step 5:
In another CMD window start SQLCMD and run the following commands to relocate model and tempdb. Also, let's resize tempdb as well.

ALTER DATABASE model
MODIFY FILE (NAME=modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf')
GO

ALTER DATABASE model
MODIFY FILE (NAME=modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\modellog.ldf')
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf')
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME=templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog.ldf')
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE = 1MB)
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME=templog, SIZE = 1MB)
GO

Step 6:
Stop SQL Server from the CMD window by either closing the CMD window or pressing CTRL+BREAK. Then start SQL Server using "SQL Server Configuration Manager"

sql server configuration manager

Step 7:
Restore msdb database by issuing the following command which can be done using SSMS or SQLCMD.

RESTORE DATABASE msdb
FROM DISK ='X:\Backup\msdb_backup_2011_02_17_000001_1662523.BAK'
WITH REPLACE,
MOVE 'msdbdata' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\msdb.mdf',
MOVE 'msdblog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\msdb.ldf'

Step 8:
Restore your user databases and start SQL Server Agent.

Summary
Since we restored the master and msdb databases we were able to recover the system information such as logins, jobs, schedules, etc... without having to recreate them. This approach is much faster instead of having to build an entire server and install SQL Server, but as mentioned the versions of SQL Server need to be the same in order to restore the system databases.

Next Steps

Here are some related articles.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-08-12

Comments For This Article




Friday, August 12, 2011 - 5:44:06 PM - Matteo Back To Top (14391)

Tipically, in all our critical environemnts, we do a daily backup of master, model and msdb. Once a quater, we run a DR exercise and try to restore them. 

Friday, August 12, 2011 - 11:30:56 AM - Fred Rosenberg Back To Top (14386)

Thank you, excellent article! Though i hope i never need it. :)

Friday, August 12, 2011 - 11:10:58 AM - Greg Robidoux Back To Top (14385)

You really only need backups from master and msdb.  You could also backup model if you customize this for new database settings.  You can not backup TempDB and do not need to since this database is recreated each time you startup SQL Server.

As far as backups go you should backup these databases whenever there are changes such as adding new logins, new databases, new scheduled jobs, etc...  To make it easier you could just do a daily backup.  These databases are generally pretty small compared to user databases.

If you don't have backups of these databases you will need to have the data and objects scripted so you can recreate any of the data that is stored in these system databases for your recovered server.

Friday, August 12, 2011 - 10:43:13 AM - Luis Araya Back To Top (14384)

This means that it is necessary in order to recover from a disaster, to have backups from all the system databases (master, model, msdb, tempdb).  In your opinion, how often these backups has to be done?.

 

 

Friday, August 12, 2011 - 10:06:45 AM - Matteo Back To Top (14382)

Yes, I have tested it using SQL 2008. It works just fine. Thanks

Friday, August 12, 2011 - 9:18:29 AM - Yusuf Back To Top (14379)

 

ever tried this on SQL 8?














get free sql tips
agree to terms