Introduction

Creating a good backup and recovery plan for SQL database is a very important task of a database administrator. A Database administrator must have the knowledge about the troubles of SQL Server and also a quick strategy, which is suitable for the trouble. Many organizations hire accidental database administrators instead of database backup. Sometimes a bad configured backup plan can be the cause of a data loss. In this article, we will discuss the backup and recovery plans for SQL database.

Why we need a backup and recovery plan?

Sometimes, while working on a SQL Server you may face so many difficulties. One of which could be data loss. For example Network disruptions, low disk space or disk failure so on. Such type of difficulties can arise at any time in our machine or database. Although, If you have already created a good recovery plan or a strategy like a database mirroring, In which another server keeps a copy of your database and keeps it up to date. Afterall you may need to have a backup of your database. Because a disk failure on mirror server can stop the transactional process between primary and mirror database and that hiccup can corrupt the primary database and can put it in suspect status. In such situation, we cannot bring our database back.

Recovery techniques for SQL database

(1) Restoring a database from another SQL Server

The SQL Server provides the feature to restore your database from another SQL Server database where you have already created a backup copy of your existing database. SQL Server performs such tasks by using some T-SQL commands. for example: RESTORE HEADERONLY

The RESTORE HEADERONLY command is used to check what is in the backup file. See how to implement this command:

RESTORE HEADERONLY FROM DISK = 'C:\SQL\Backup\bkp.bak'

(2) Restoring a database on the same server

You can use enterprise manager to restore the database from the same server. The enterprise manager allows you to backup and restore your database.

To allow this service, Go to Enterprise Manager, then right click on the database (that you want to backup and restore) and select All Tasks. You will have a database Backup and Restore options.

(3) Restore using T-SQL commands

Apart from restoring the database from enterprise manager, another handy option is to restore the database from T-SQL command, by which you can create a script to your backup and also you can restore it using T-SQL script.

You can perform operation by using the following T-SQL commands.

RESTORE DATABASE databaseName

RESTORE LOG databaseName

(4) Database mirroring

One of the evolving feature, which came into SQL Server 2005 is Database mirroring. Which allows you to create a mirror image of your database on another server and automatically transfer it from the primary Server to secondary Server and also provides the feature to failover capabilities.

The failover processing of database mirroring depends on your choice that how you have configured it in your database. These are:

1. High availability
2. High protection
3. High performance

(5) Recover lost SQL Server data

All the above methods render you to have an unnecessary dataset, means that data which you can only use when your primary database may have been lost. But these methods may not useful when someone by mistakenly deletes a record from a table and you only have to recover that specific record. SQL Server provides some failover methods to recover lost data. i.e point-in-time restore, Log shipping, Clustering, Transaction Replication. However, each method has some of its pros and cons, which I will discuss in my next blog.

Conclusion

The article describes simple backup and recovery plans for SQL Database, in which we have discussed why a backup plan is important at the time of disaster and also discussed some recovery methods which helps we can recover our lost database. Creating a backup plan for the database is a good idea to prevent data loss.