Sunday, March 31, 2013

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.  

No comments:

Post a Comment