a.
What is a Full Database Backup
b.
When to take a full backup
c.
How to take a full backup
d.
How to restore a full backup
2. Differential Backup
a.
What is a Differential Backup
i. Differential backups are also called as incremental
backups. In SQL Server terms, a differential backup will back up only the
extents that have changed since the last full backup. This will make these
types of backups substantially smaller than full backups and is really useful
with large backups.
b.
When to take a differential backup
i. Typically differential backup is taken once or
twice in a day depending on the kind of application. Before you create the
first differential backup you must create a full backup.
c.
How to take a differential backup
i. Below is the script to take a differential
backup. Change the value of the variable @Pathname
and provide the Path where the backups should be stored and replace MyDatabase with your database name.
DECLARE @Pathname NVARCHAR(512)
SET @Pathname =
N'D:\DatabaseBackups\MyDatabase_DifferentialBackup_'
Convert(VARCHAR(8), SYSDATETIME(), 112) '.bak'
BACKUP DATABASE
[MyDatabase] TO
DISK =
@Pathname
WITH
DIFFERENTIAL ,
NOFORMAT, INIT, NAME = N'MyDatabase-Differential Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
d.
How to restore a differential backup
i. Differential backup restores are performed by
first restoring the latest complete full back up with the NORECOVERY option
then applying the latest differential backup over it.
ii. We don’t have to apply each differential backup.
Because differential backups are incremental backups. They contain everything
from the last full backup. Applying each differential backup is a redundant
task.
iii. Script to restore the differential backup:
RESTORE DATABASE
[MyDatabase] FROM
DISK = N'D:\DatabaseBackups\MyDatabase_FullBackup_20101015.bak'
WITH
FILE = 1,
MOVE N'MyDatabase'
TO N'C:\Program
Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabase.mdf',
MOVE N'MyDatabase_log'
TO N'C:\Program
Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabase_log.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
3. Transactional log backup
a.
What is a Transactional Log Backup
a. Transactional log backup helps
in backing up transactional log from the last successful log backup.
b.
When to take a transactional log backup
a. Typically a transactional log backup is taken
for every 4 to 8hrs. Before you create the first differential backup you must
create a full backup.
c.
How to take a transactional log backup
a. Below is the script to take a transactional log
backup. Change the value of the variable @Pathname
and provide the Path where the backups should be stored and replace MyDatabase with your database name.
DECLARE @Pathname NVARCHAR(512)
SET @Pathname =
N'D:\DatabaseBackups\MyDatabase_TransactionLogBackup_'
Convert(VARCHAR(8), SYSDATETIME(), 112) '.bak'
BACKUP LOG
[MyDatabase] TO
DISK =
@Pathname
WITH NOFORMAT, INIT, NAME = N'MyDatabase-Transaction
Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
d.
How to restore a transactional log backup
a. Once we restore the full backup we only need to
apply the latest differential backup. And then we need to apply the transaction
log backup.
b. Restore the latest full backup, Restore the latest
differential backup and Restore the transactional log backup.
c. Script to restore the transactional log backup:
DECLARE @Pathname NVARCHAR(512)
SET @Pathname =
N'D:\DatabaseBackups\MyDatabase_TrasactionBackup_'
Convert(VARCHAR(8), SYSDATETIME(), 112) '.bak'
RESTORE LOG
[MyDatabase] FROM
DISK =
@PathName
WITH FILE
= 1, NORECOVERY, NOUNLOAD, STATS = 10
Suggested procedure
for backup and recovery:
- Can you continue to work or run your business if your
database server dies? It is always important to back up your database
regularly. Know how best you can protect your data and recover it in case of
any disaster. Define a suitable disaster recovery plan.
- For a typical database with data being loaded as a nightly
process or for any data driven applications suggested to follow the below plan.
1)
Full Backup – once every week
2)
Differential Backup – once every day
3)
Transactional Log Backup - for
every 4 or 8hrs
Automating backup process using SQL Jobs:
To automate database backup process, create and schedule a
job in SQL Server. Follow the below steps to create and schedule a new job in
SQL Server.
1) Go to Object Explorer and right click on SQL
Server Agent and select New >> Job. If SQL Server Agent is disable, go to
Services and enable “SQL Server Agent(MSSQLSERVER)"
2) Enter Name and Move to Steps
3) Add a new Step by copying the script for taking
a backup (above scripts can be used directly)
4) Go to schedules and add schedule according to
your need.
5) Don’t forget to add Notifications as to be
informed about every successful or failure backup
Please post your comments...
Related tags
Database Backup, Database Recovery, Disaster Recovery