Is Full Backup is Sufficient to Recover SQL Server Database?

Data is very important for everyone, ranging from individual to big organizations. For me, data is my life. I can’t think, what will be my life without data. So I regularly manage and update data. If you think that your data is also important for you as of mine then you should also manage and update your data.

What is Data?
Data can be anything like list of records, phonebook, measurements, survey records, employee records or even a description of any things. In the other words, you can say that data is a set of records.

How to Manage your Data?
Creating copy or copies of your data is the primary method for data management. Just create copy of your important data and store it on external hard drive, pen drive, or any other removable media so that you can recover any lost or deleted data from backup copy. Fortunately, Microsoft SQL server provides backup & restore facilities for data management.

SQL Server Backup
Backing up of your SQL server database is very important for protecting your database from data loss. SQL server facilitates for backing up transaction log, whole database, partial database or sets of files & filegroups. For these, SQL server offers following backup techniques:

• Tranaction Log Backup: It records all the changes performed in the database that were not available in the previous log backup. To create a transaction log backup, you need to create at least one full backup of your database before. I recommend you to take a transaction log backup of your database frequently that will minimize data loss exposure and truncate the old transaction log.
• Full Backup: It is also known as database backup because it backs up whole database including transaction log. It can be performed on the basis of your business need. Full backup contains the data at the time of full backup finished.
• Differential Backup: It is faster in comparison of full backup because it contains the data since last full backup.
• File Backup: It backs up one or more SQL server database files or filegroups. You can backup whole filegroup instead of individual files.

Data Loss, it can happen to anyone
You should always prepare of it. It can happen with anyone at anytime. So it is very important of everybody to prepare of it and make a suitable disaster recovery plan to avoid data loss and minimize the business downtime when data loss event will occur.

So now you may need to make a full backup of your SQL server database. You can create full backup of your SQL server database by using SQL server management studio, transact-SQL, or powershell.

Is Full Backup is Sufficient Enough?
If you have a read-only SQL server database or data in your SQL server database is very static then full backup is sufficient for you. Possibly you may recover your whole database with the help of full database backup.  But think differently, your database experiences frequent modification regularly then full backup is not sufficient for you because it does not contain data after full backup. To recover every bit of your data, you need to create other backups such as differential or transaction log backup. I have already discussed about these backups in the above paragraphs.

What is the role of recovery model in transaction log backup?
SQL server facilitates database administrators to manage their transaction logs through recovery model. There are three types of recovery model in SQL server: Simple, Full & Bulk-Logged. A database administrator can take backup of transaction log when his/her database in Full or Bulk Logged recovery models but this is not possible in simple recovery model.

Points to Ponder:

• Make backup of database
• Data is key to success
• Regularly update database backup
• Different types of backup required to recover database
• Simple recovery model does not allow for transaction log backup

I hope that you have enjoyed to reading my article. As always, share your thoughts through comment.

»»  Read More...

How to take Full Database Backup in SQL Server

Full Database backup backup the whole SQL Server database. We can use Full Database backup to recover the full database as it includes part of the transaction log. You can find the size of full database backup by using the sp_spaceused system stored procedure. If you are taking long size full database backup then it will take  more time to finish and require more storage space. In this case, you should supplement a full database backup with a series of differential database backups. You can create full database backup by using three options: SQL Server Management Studio, Transact-SQL, PowerShell.

Before taking full backup, first check out its Limitations and Restrictions: 

• The BACKUP statement is not allowed in an explicit or implicit transaction.
• Backups that are created in SQL Server recent version that cannot be restored in SQL Server earlier versions.

Taking full backup by using SQL Server Management Studio:

• In Object Explorer, first connect to appropriate instance of the Microsoft SQL Server Database Engine,
• Click server name to expand the server tree.
• Expand Databases 
• Right-click the database, point to Tasks, and then click Back Up. 

The Back Up Database dialog box appears: 

• You can see all available options on the window. Update it:
• In the Database list box, verify the database name.  
• Database backup can be perform for any recovery model (FULL, BULK_LOGGED, or SIMPLE).
• In the Backup type list box, select Full.
• Backup component, click Database.
• Accept the suggested default backup set name or enter a different name.
• Optionally, in the Description text box, enter a description of the backup set.
• Specify when the backup set will expire. 
• Choose the backup destination by clicking Disk or Tape.  

To view or select the advanced options, click Options in the Select a page pane.

• Select an Overwrite Media options available on the picture: 

1. Back up to the existing media set
2. Back up to a new media set, and erase all existing backup sets

• In the Reliability section, optionally check:

1. Verify backup when finished.
2. Perform checksum before writing to media. 
3. Continue on checksum error. This option is optional. 

• If you are using  tape drive to backing up the database then 'the Unload the tape after backup, option is active. Clicking this option activates the Rewind the tape before unloading option.

• The next option is backup compression. The SQL Server 2008 Enterprise and later supports this feature. You can compress a backup by using this option, and you can prevent it by checking 'Do not compress backup'.

You can restore your corrupted whole SQL Server database by restoring the database from a full database backup to any location.  

»»  Read More...