Following are the system databases and their
recommended backup procedures
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.
Is used by SQL Server
Agent for scheduling alerts and jobs. Back up msdb whenever it is
updated.
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.
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.
Is a workspace for
holding temporary objects or intermediate result sets. You cannot back up the tempdb system
database.
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
No comments:
Post a Comment