A Script A Day - Day 22 - Database Backup

Today’s script follows on from Day 16’s script which was about database restores.  In a backwards way the script is a simple backup database script with two backup commands.  For all my production servers I have custom maintenance routines which take care of transaction log and full backups along with a whole host of other maintenance tasks.  But for a one off backup lets say before decomissioning an old server or before running some test data scripts in development then the syntax to backup a database doesn’t get much easier!  This is yet another example of T-SQL being better than using the GUI as it is far quicker ;)

Now as with my script on Day 16 not everyone knows T-SQL and thus wouldn’t be able to backup a database using it.

So I’ve included two backup database commands one will backup the SQLServer365 database to disk and one will backup the SQLServer365 database to disk with compression.  Backup compression was introduced in SQL Server 2008, details can be found on Technet.  Basically backup compression will generally save you disk space, in the example here on my instance the backup without compression was 146MB the backup with compression was 10MB!

/*

      -----------------------------------------------------------------

      Backup Database

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE master;

GO

-- Backup database

BACKUP DATABASE SQLServer365

TO DISK = 'D:\SQL\Backup\SQLServer365_20120224.bak';

GO

-- Backup database with compression

BACKUP DATABASE SQLServer365

TO DISK = 'D:\SQL\Backup\SQLServer365_20120224_Compressed.bak'

WITH COMPRESSION;

GO

Enjoy!

Chris