SQL Server Disaster Recovery

It’s all gone horribly wrong!

You probably backup your system databases at least once a day and you doubtless test your user databases by restoring them elsewhere but do you test your system backups. You should but many people seem a little scared by this. Here’s how to restore the master database from a backup file. Its os easy there is no excuse not too.

Always test your backups by restoring to a test server. When you restore the master database you will restore all the security info in that DB. Make sure you are sa in the access list of the backed up master db file or you wont get in to the test server following the restore.

Perform these steps on a production server at your own risk. If in doubt always test theory elsewhere.

Restoring system databases from backup (e.g. master)

  1. Stop all services (e.g. SQL Server Agent) that can connect to SQL and either disable them or set to Manual. If possible disconnect the server from the network to prevent any outside connections from applications etc. (The very first connection will take the only available connection and you need to make sure it is you). In manual start-up some services may still start if they are called so disabling is better; just remember to re-enable them after.
  2. Start SQL Server Configuration Manager
  3. Navigate to SQL Server Services.
  4. Right click SQL Server and then select Properties.
  5. In the advanced tab edit the “Startup Parameters” by prefixing with -m; in front of any existing start-up parameters – semi-colon is important and don’t put any spaces (after or before) i.e. just put these three characters -m;
  6. You will be warned that changes will only take place after restart so, restart the database engine.
  7. Restore the relevant system database – RESTORE DATABASE master FROM <backup_device> WITH REPLACE where <backup device> is something like DISK=’PATH\BAKFILE’
  8. Remove the -m; from the start-up parameters box
  9. Restart the Database Engine.

If you restore the master database, the instance of SQL Server will be stopped automatically because the master contains the security.

If you need to change other items in single use mode then restart in single user, otherwise remove the –m; before restart. If you have just restored the master DB then it’s generally a good idea to restart in single user mode then have a good look around to make sure everything is fine before you open the server to everyone.

For master database restoration use the sqlcmd utility. E.g.

1> RESTORE DATABASE master FROM DISK='DISK:\PATH\BAKFILE.bak' WITH REPLACE;
2> GO

Processed 999 pages for database 'master', file 'master' on file 1.
Processed 9 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

Here is a full list of sqlcmd parameters…

sqlcmd
[{ { -U login_id [ -P password ] } | –E trusted connection }]
[ -N encrypt connection ][ -C trust the server certificate ]
[ -z new password ] [ -Z new password and exit]
[ -S [protocol:]server[\instance_name][,port] ] [ -H wksta_name ] [ -d db_name ]
[ -l login time_out ] [ -A dedicated admin connection]
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]
[ -u unicode output] [ -r [ 0 | 1 ] msgs to stderr ]
[ -R use client regional settings]
[ -q "cmdline query" ] [ -Q "cmdline query" and exit]
[ -e echo input ] [ -t query time_out ]
[ -I enable Quoted Identifiers ]
[ -v var = "value"...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ]
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ]
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort] [ -V severitylevel ] [ -m error_level ]
[ -a packet_size ][ -c cmd_end ]
[ -L [ c ] list servers[clean output] ]
[ -p [ 1 ] print statistics[colon format]]
[ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit]
[ -? show syntax summary]

If the restore does not work!

You will have to rebuild the system databases. You do this by running the setup program which will be on your SQL servers local disk @ “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2”. Note: This only works after SP1 due to a bug, prior to this use the setup.exe from the original media.

Execute the following commands:

SETUP /ACTION=REBUILDDATABASE /INSTANCENAME=mssqlserver /SQLSYSADMINACCOUNTS=domain\user /SAPWD=*******

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in SQL Stuff and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s