SQL Server Backup and Restore strategy – Part 1

Backup and Restore strategy is always important for application that uses database intensively. A good database administrator must have known that if he/she doesn’t have good strategy then significant data loss might be occur.

DB Administrator has a task to minimizes data loss.

Your backup strategy has a type of backups, frequency and how fast it requires. Off course, it is also depends on hardware specifications. On the other hand, restore strategy has a way to restore database from backups you created before and testing it.
Frequent backup can be scheduled automatically by SQL Server schedule job. To activate SQL Server schedule job you need to start sql server agent service.

Backup and restore can’t be compared with High Available DB method such as mirroring, log shipping and replication. In High Availability (HA) scenario, you have 2 servers, primary and secondary.
Database in primary server copied to secondary at every data transaction occur or at repetitive small time frame. You can have exact copy of a primary database. If something happens to primary then the application can fail over to secondary automatically.
HA is a must for medium-large database with so many users connected to it. But bare backup also have to be concerned. You had better to have backups.

In case of ‘fat finger’ like users misuse application or programmers accidentally miss-type query i.e ‘delete’ or ‘update’ without ‘where’ condition, primary and mirror server will update the data. So, both of them has data mistakes.
If you have a backup then you can restore to the data point where close enough before accident happened.

I will explain a scenario using three types of SQL Server 2005/2008 backups which are full, differential and transaction log. These kind of backup types are suitable for databases that have small to large sizes. For very large size database, you might want to consider file group backup.

A full database backup backs up the database complete with its transaction logs. The backup file is a database representation at the time it backed up. Small to Medium size database may backup with this full type often. Since full backup on small-medium size database will not consume much time.
On the other hand, medium-large database must consider off-peak hours or ideally when system usage is very low so that backup process doesn’t disturbing day-to-day operation. Usually full backup on large database occur on night job process and it consumes more time than small-medium database.

What happen if sometime in noon the database server is crash? Data from last full backup on night to the time server crash is loss. It is not good, we should minimize data loss.

Small databases can schedule full backup more frequent. Adding more time slot on schedule, so that can minimize data loss. But for large databases, full backup will consume much more time. So, we need additional backup type which is differential backup.
Differential backup is based on the most recent full backup data. It captures only the data has changed since the last backup.

If system usage is low at noon then we can set at schedule job to do differential backup on that time as additional to full backup at night. Differential backup will consume time and size lesser than full. Test it to know how much time will consume then decide whether it will disturbing users operation or not.

Let’s say for some reason the database server crash at 4 hours after noon differential backup, data is loss between that time. On small frequent change on database it can be tolerated, but on very frequent update data loss for 4 hours is not tolerated. We need other additional backup type that is transaction log.

Transaction log records queries that writes into database. Log backup will back up transaction log between full/differential backup. When we do another log backup in next 15 minutes then transaction log between earlier and next backup will backed up sequencely.
Log backup will reduce its size and this is good for database maintenance. Log backup only takes few times and size of the backup is relatively small. We can do this backup often to minimize data loss.

A T-SQL syntax to do full, differential and log backup to disk are as below:

Full backup : Backup Database <dbname> To Disk='<path of backup>’ With Format
Differential backup : Backup Database <dbname> To Disk='<path of backup>’ With Differential
Transaction log backup : Backup Log <dbname> To Disk='<path of backup>’
To shrink log file use : dbcc shrinkfile(<logfilename>)

Replace <dbname> with database name you want to backup, <path of backup> with backup file location on your harddisk and <logfilename> with related log file name. Additionally we should use different path of backup file foreach backups taken. For this purpose we can concat timestamp with backup filename.
For example on full backup use this following syntax:

DECLARE @MyBackupName nvarchar(250)
Declare @bckfilename nvarchar(250)
set @bckfilename = 'full_backup_' + convert( varchar(16), getdate(), 112 ) + Replace(CONVERT(VARCHAR(8), getdate(), 108),':','') + '.BAK'
set @MyBackupName = '<backup directory>' + @bckfilename
backup database <dbname> to disk=@MyBackupName with format

We can set up three of those above on sql server scheduled job and make sure the sql server agent is running. In example of schedule time are full at night, differential at noon, and log backup on every 15 minutes.

When time goes on we have to check the availability of hard disk or other backup space because its process fullfill disk spaces. Usually we can remove old backup files or move them to another media.

Agung Gugiaji

One response to “SQL Server Backup and Restore strategy – Part 1

  1. Very interesting topic, regards for posting.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s