| |
| This article
details the various levels of Backup and Restore strategies
in SQL Server 2000. This paper will be useful for a SQL Server
beginner to setup backup and restore strategy. |
| |
| The below topics are included
in this article: |
| |
|
| |
| 1. Backup
and Backup medias |
| |
| • |
Backup is taking a copy
of Database to ensure no data loss during crash. |
| • |
Backup media can be either: |
| |
| 1. |
HDD |
| 2. |
Tapes |
| 3. |
A Database copy itself
in a different machine. (Hot stand by with log shipping
/ Replication…) |
|
|
| |
 |
| |
| 2. Backup
Methodologies |
| |
| • |
Full database backup --
Is a full copy of the database. |
| • |
Transaction log backup.
-- Copies only the transaction log. |
| • |
Differential backup. --
Copies only the database pages modified |
| • |
Full system Backup |
| • |
System database backup |
|
| |
|
| |
| 3. Selection
of Backup Methodology |
| |
| Non-critical databases that
can be easily re-created from some other source may have no
backups, |
| |
| 1. |
Other databases may have
simple backups (FULL database backup) that can re-create
the database to the night before a failure |
| 2. |
Critical databases require
right up to the point of failure. |
|
| |
| Based on the Criticality of
data, database administrator can choose the Recovery Model.
Backup type for a database can only be decided only based on
the recovery model of that database. |
| |
|
| |
| 4. Recovery
Model |
| |
| 1. |
Full |
| 2. |
Bulk Logged |
| 3. |
Simple |
|
| |
| Full Recovery: |
| |
| 1. |
It is possible to recover
the database to a particular point in time or required
point. |
| 2. |
Used on very critical data.
|
| 3. |
For example, if a user
accidentally deletes all accounts in a database at 1 PM,
it's possible to restore the database up to 12:59 PM,
right before the deletion of the accounts occurred.
|
|
| |
| Demerits if recovery is Full:
|
| |
| Under this mode, space in the
transaction log is only cleared when a backup of the transaction
log is made. So we need to monitor the transaction log growth.
All the changes stored in the transaction log are written to
the backup and the space is freed up. Therefore, databases in
this mode need to have enough space available for the transaction
log to store all the transactions that occur between each backup.
|
| |
| Bulk Logged Recovery: |
| |
| 1. |
Almost same as Full
Recovery model. |
| 2. |
The difference is BCP,
Select /Into, Create Index, Text operations are minimally
logged. Because of this if a failure occurs during a BCP
operation. |
| 3. |
Recovery can be given till
the last Transaction log backup. (Point in time recovery
is not possible) |
|
| |
| Simple Recovery: |
| |
| 1. |
Only full database backups
are allowed. |
| 2. |
No operations are logged;
the only advantage is Transaction log gets cleared after
each checkpoint. So minimal database administration is
only required. |
|
| |
| How to set the Recovery Model |
| |
| Syntax for setting recovery
model in Query analyzer |
| |
| ALTER
DATABASE <DBNAME> SET RECOVERY FULL |
| ALTER
DATABASE <DBNAME> SET RECOVERY BULK_LOGGED |
| ALTER
DATABASE <DBNAME> SET RECOVERY FULL |
|
| |
| Or use Enterprise manager –
database tab – properties - options --- Recovery Option. |
| |
|
| |
| 5. Backup
Implementation |
| |
| This section details the detailed
steps in configuring the backup. |
| |
| Backup database DBNAME to Disk/Tape
=’c:\folder\dbname.bak’ with INIT / NOINIT, PASSWORD,
DIFFERENTIAL, STATS=n%, |
| |
| Full Database Backup |
| |
| BACKUP DATABASE pubs TO pubsdd
WITH INIT (Pubsdd is Backup device) |
| |
INIT will overwrite the old
backup sets
STATS show the backup completion status in percentage |
| |
| Differential Backup |
| |
| BACKUP DATABASE <DBNAME>
TO <DEVICE> WITH DIFFERENTIAL |
| |
| Transaction Log backup |
| |
| BACKUP LOG <DBNAME> TO
<LOGDEVICE> (LOGDEVICE is Backup device) |
| |
| File Group |
| |
| BACKUP DATABASE <DBNAME>
FILE = <filename>, FILEGROUP = <filegroup> TO {device} |
| |
| Full system Backup |
| |
| • |
Take a backup of all MDF
and LDF files |
| • |
Include the Master.mdf
and Masterlog.ldf |
| • |
Keep an output of Sp_configure,
Sp_helpsort |
|
| |
| System Databases Backup |
| |
| Ensure the backup procedures
include all system and user databases. In the event of a complete
system failure, it will be necessary to restore the system databases
along with your user databases in order to recreate the SQL
Server environment |
| |
|
| |
| 6. Restore
Methodologies |
| |
| Restoration is the methodology
of loading the database. Restore will create the database if
it does not exist, and initializes the extents in the database.
This step is bypassed if the database exists when the RESTORE
statement is executed. |
| |
| The different approaches are
shown below: |
| |
| a. |
From device (*.Bak) |
| b. |
From Device differential. |
| c. |
From Files (*.MDF, *.LDF)
– SP_ATTACH_DB |
| d. |
Point in time Restore /
Transaction Log Restore |
|
| |
| a. From Device |
| |
| This methodology allows restoring
the database from a physical backup file or Backup device |
| |
| Using Physical File: |
| |
| RESTORE DATABASE <DBNAME>
FROM DISK = DRIVE\DBNAME.BAK’ WITH STATS=10 |
| |
| Using Backup device: |
| |
| RESTORE DATABASE <DBNAME>
FROM <BACKUPDEVICENAME> WITH STATS=10 |
| |
| b. From Device Differential |
| |
RESTORE DATABASE <DBNAME>
FROM <device> WITH NORECOVERY
RESTORE DATABASE <DBNAME> FROM <device> WITH RECOVERY
|
| |
| c. From Files (*.MDF, *.LDF) |
| |
| Using SP_ATTACH_DB, CREATE DATABASE..
WITH ATTACH options you can attach a database to SQL Server. |
| |
| SP_ATTACHDB <DBNAME>,
Filename1, Filename2 … Filename16 |
| |
| Using the above command you
will be able to attach a database, which contains a maximum
of 16 files. If the database contains more than 16 files use
CREATE DATABASE.. WITH ATTACH option. |
| |
CREATE DATABASE Archive
ON PRIMARY (FILENAME = Filename')
FOR ATTACH |
| |
| d. Transaction Log Restore
|
| |
| Transaction logs must be applied
in sequential order from the oldest backup to the most recent
backup. To apply multiple transaction logs, use the NORECOVERY
option on all restores except the last restore command where
the database recovery is needed. |
| |
RESTORE DATABASE <DBNAME>
FROM <device> WITH NORECOVERY
RESTORE LOG <DBNAME> FROM <device> WITH NORECOVERY
RESTORE LOG <DBNAME> FROM <device> WITH RECOVERY |
| |
|
| |
| 7. Some
Useful Restore commands |
| |
| a. VERFIYONLY |
| |
| • |
Verifies the validity of
the backup, but does not restore the backup. |
| • |
This process confirms that
the backup set is complete and that all volumes are readable
for SQL Server to restore the backup in the future. |
| • |
If the backup is valid,
the following message is returned: "The backup set
is valid." |
|
| |
| RESTORE VERIFYONLY FROM <device> |
| |
| b. FILELISTONLY |
| |
| Lists the database and log
files contained in the backup device. |
| |
| RESTORE FILELISTONLY FROM <device> |
| |
| c. HEADERONLY |
| |
| Retrieves the backup header
information for all backup sets on a particular backup device. |
| |
| RESTORE HEADERONLY FROM <device>
|
| |
|
| |
| Summary |
| |
| The above-mentioned strategy
is derived based on my past experience as a DBA in Production
support; I hope this will be a direction provider for Database
enthusiasts to implement successful Backup and Restore strategy.
|
| |
| |
