Thursday, November 22, 2012

SQL Server Backup & Reduce Time to Accomplish a Backup

As we all are aware that backup of data is essential for reducing the change of data loss due to failure and SQL server is not an exception. SQL server backup is helpful in restore & recovery of loss data. It can be created at different level of database. In addition to this, full recovery model is required for creating a backup of transaction log file. There are so many types of backup available in SQL server like: full backup, differential backup, log backup, file backup, partial backup, copy-only backup and so on.

Restrictions during Backup: MS SQL server allows you to perform various operations on the backup but there are some restrictions too. In SQL server 2005 & later versions, you can't backup a database that is offline. Backup can occur only when database is online & being in use.

Example: Suppose you want to make a full backup of database but one of the filegroup of the database is offline, here backup fails because filegroup is the part of database.   

Most of the operations are possible during backup is in process. You will be able to perform insert, delete, or update statement on the database during backup but there are some restrictions too.
  • Following operation are not allowed during backup:
  • File management statements like ALTER database.
  • Create & Delete operations.
  • Shrink transaction log or database file.
How to Make Backup? You can make full SQL server database backup by the use of SQL server management studio (SSMS), Transact SQL (TSQL), and PowerShell. I personally prefer TSQL over SSMS & PowerShell. You can choose any one or all to make full database backup of your database. Backup of the newer versions can't be restored on the lower versions of SQL server. For Example, you can't restore SQL server 2000 backup on SQL server 2005.

How to Reduced Backup Time? You can reduce the backup time of the SQL server database by doing following tasks:
  1. Use Backup Compression: Backup time is directly proportional to the size of database & speed of backup. If database size is big & backup speed is slow then it will take long time to make backup of database. You can enhance the backup speed with the help of backup compression. In addition to this, backup compression is introduced in SQL server 2008 enterprise and later versions. It enhances the backup as well as restore speed for the database and works on 'read some & write some' concept.
  2. Choose Separate Disk for Backup: If the location of your database and backup is same so disk is trying to read & write operation simultaneously. Backing up a database on the separate disk can help in reducing the time to backup.

No comments:

Post a Comment