Search This Blog & Web

Sunday, April 27, 2014

System databases, Backup procedure and restore statergies

Following are the system databases and their recommended backup procedures

·         master Database
Records all the system-level information for an instance of SQL Server. Symantec keys and Certificates information. Back up master as often as necessary to protect the data sufficiently for your business needs. Best practices recommendation are for regular backup schedule, which you can supplement with an additional backup after a substantial update.
·        msdb Database
Is used by SQL Server Agent for scheduling alerts and jobs. Back up msdb whenever it is updated.
·        model Database
Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward. Back up model only when necessary for your business needs; for example, immediately after customizing its database options. Best practice:  We recommend that you create only full database backups of model, as required.
·        Resource Database
Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database resides in the mssqlsystemresource.mdf file, which contains only code. Therefore, SQL Server cannot back up the Resource database.
·        tempdb Database
Is a workspace for holding temporary objects or intermediate result sets. You cannot back up the tempdb system database.
·        Configure Distribution
A database that exists only if the server is configured as a replication Distributor. This database stores metadata and history data for all types of replication, and transactions for transactional replication.

Database restore:
System databases
System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server 2008 SP1, you must use a database backup that was created after the server instance was upgraded to SQL Server 2008 SP1.
To restore any database, the instance of SQL Server must be running. Startup of an instance of SQL Server requires that the master database is accessible and at least partly usable. If master becomes unusable, you can return the database to a usable state in either of the following ways:
·         Restore master from a current database backup.
·         Rebuild master completely
Performing a Complete Database Restore (Simple Recovery Model)
In a complete database restore, the goal is to restore the whole database. The whole database is offline for the duration of the restore. Before any part of the database can come online, all data is recovered to a consistent point in which all parts of the database are at the same point in time and no uncommitted transactions exist. Under the simple recovery model, the database cannot be restored to a specific point in time within a specific backup.
1.       A full database restore under the simple recovery model involves only one or two RESTORE statements, depending on whether a differential database backup has to be restored.

If you are using only a full database backup, just restore the most recent backup, as shown in the following illustration.

If you are also using a differential database backup, restore the most recent full database backup without recovering the database, and then restore the most recent differential database backup and recover the database. The following illustration shows this process.


When you are completely restoring a database, one restore sequence should be used. The following example shows the critical options in a restore sequence for the complete database restore scenario. A restore sequence consists of one or more restore operations that move data through one or more of the phases of restore. Syntax and details that are not relevant to this purpose are omitted.
Performing a Complete Database Restore (Full Recovery Model)
Under the full recovery model, the database can be restored to a specific point in time. The point in time can be the most recently available backup, a specific date and time, or a marked transaction.
Typically, recovering a database to the point of failure involves the following basic steps:
1.       Back up the active transaction log (known as the tail of the log). This creates a tail-log backup. If the active transaction log is unavailable, all transactions in that part of the log are lost.
2.       Restore the most recent full database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
3.       If differential backups exist, restore the most recent one without recovering the database (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY).
4.       Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence with NORECOVERY.
5.       Recover the database (RESTORE DATABASE database_name WITH RECOVERY). Alternatively, this step can be combined with restoring the last log backup.
6.       A complete database restore can usually be recovered to a point of time or marked transaction within a log backup. However, under the bulk-logged recovery model, if the log backup contains bulk-logged changes, point-in-time recovery is not possible. For more information, see Restoring a Database to a Point Within a Backup.
7.       The following illustration shows this process. After a failure occurs (1), a tail-log backup is created (2). Next, the database is restored to the point of the failure. This involves restoring a database backup, a subsequent differential backup, and every log backup taken after the differential backup, including the tail-log backup.


1.       When you are completely restoring a database, a single restore sequence should be used. The following example shows the critical options in a restore sequence for the complete database restore scenario in which the database is restored to the point of failure. A restore sequence consists of one or more restore operations that move data through one or more of the phases of restore. Syntax and details that are not relevant to this purpose are omitted.
The database is restored and rolled forward. A database differential is used to reduce roll-forward time. This restore sequence is intended to eliminate work loss; the last backup that is restored is a tail-log backup.

All this data is reference to different sources i.e Microsoft
Post a Comment