Backing up System Databases

System databases contain information about user databases as well as meta-data about SQL Server, SQL Server Agent, jobs, alerts, DTS packages and more. Therefore, it is crucial to have valid backups of the following databases:

  1. Master
  2. MSDB
  3. Model
  4. Distribution (if using replication)

Another system database, tempdb, is rebuilt each time SQL Server is started. Therefore tempdb does not need to be backed up. The Model database serves as a blue-print for creating new user databases. This database should not have any activity associated with it, other than the database administrator adding or removing objects required in all user databases. Therefore, you should typically use the SIMPLE recovery model for the Model database and only perform full backups for it.

The Master database only allows full backups; differential or transaction log backups of Master are not supported. You should backup the Master database when you:

  • Create or drop a user database
  • Change server level settings
  • Change database configuration options
  • Add or drop linked servers
  • Add or drop remote logins
  • Add, change or drop local logins

MSDB and Distribution databases (the latter is created by SQL Server only if you use replication) do support transaction log and differential backups. Both of these databases should be backed up regularly.

The syntax for backing up and restoring system databases is the same as for backup and restore of user databases.

Recovering System Databases

One of the worst administrative nightmares is losing a system database such as Master or MSDB without having a valid backup. If MSDB fails, you cannot perform any backup / restore operations, schedule jobs or perform any activity associated with SQL Server Agent. If Master fails, SQL Server might not start at all.

If MSDB fails and you cannot restore it from a valid backup you should:

  1. Ensure that you have a full backup of all of your user databases handy. If you don’t have backups you’re out of luck, since you cannot take backups while MSDB is unavailable.
  2. Rebuild the Master database
  3. Re-create user databases
  4. Restore user databases from backup

It is possible to restore the Master database if damage to the Master is not preventing SQL Server from starting. If you need to restore the Master you should start SQL Server in a single-user mode by running the following command from the command prompt:

sqlservr.exe --m

Note: you will have to switch to the appropriate directory where SQL Server is installed prior to running this command. If you are starting a named instance, then use the sqlservr.exe -c -m -s InstanceName syntax to start the named instance in a single-user mode.

Alternatively you can go to the services window and start SQL Server with -c and -m parameters. Next you can restore Master database from a full backup just as you would restore any user database:

RESTORE DATABASE master     FROM DISK = 'e:\master.bak'   WITH RECOVERY

After the Master database is restored, SQL Server will automatically shut down. Then you can start up SQL Server as normal without any parameters.

If the Master database fails and is completely inaccessible, SQL Server will not start – not even in a single-user mode. In such a case you cannot restore the Master even if you have a valid backup. In this extreme situation you have no choice but to rebuild the Master database.

The Master database can be rebuilt using a utility called “Rebuild Master”, which is located in Program Files\Microsoft SQL Server\80\Tools\Binn directory. The file name is rebuilm.exe. Double clicking this file will guide you through the prompts of a wizard that will create a brand new Master database to replace the corrupted one. Once the Master database is rebuilt you can start SQL Server and restore the system and user databases.

Restoring the Master Database in SQL Server 2005

Unlike previous releases, SQL Server 2005 no longer supports rebuildm.exe program for rebuilding the damaged master database. Depending on whether a recent backup of master database is available you have two options for recovering this database (and the entire instance, since SQL Server won’t start if the master database isn’t available). If you don’t have a recent backup of the master database, you must execute SQL Server 2005 setup.exe from command line to rebuild the master database and all other system databases. The syntax for rebuilding system databases is:

Start /wait setup.exe /qn INSTANCENAME=instance_name REINSTALL=SQL_Engine REBUILDDATABASE=1  SAPWD=new_system_administrator_password

Once the system databases are rebuilt, you can restore model and msdb system databases as well as user databases from recent backups, if such backups exist. You could also attach user databases if you had previously detached them from the server.

If the master database fails but you have a recent backup of this database you should:

  1. Start the SQL Server instance in single-user mode using -m parameter.
  2. Use sqlcmd utility and RESTORE DATABASE command to restore master database from full backup.
  3. Remove -m parameter from server configuration before restarting to ensure that server will start in multi-user mode.

Once SQL Server is started in multi-user mode you’ll have to re-deploy any changes to master database and other system databases since their corresponding full backups.

source: http://sqlserverpedia.com/wiki/Backing_Up_%26_Recovering_System_Databases

Advertisements

About alamzyah
Name : Alamsyah Nick Name : Alamzyah Place of Birth : Jakarta, 04 June 1983 sex : Male Religion : Moslem Region : Jakarta, Indonesia Specialist : IT, Computer mail : alamzyah@gmail.com

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

%d bloggers like this: