A nice a and simple one to share this week. Often when giving SQL Server consultancy I have to make backups at points before I’m doing something hazardous in a client’s database. This might be patching or running a script for example. It’s best to take your own backups just in case something doesn’t go to plan.

I occasionally see people fumbling around the GUI of SQL Server Management Studio to do backups and when there’s multiple databases to do it can really get clunky. There’s an argument to say that often it’s better to learn to script rather than work through GUIs. I certainly agree. Learning to do things in SQL Server via T-SQL commands means you can save them for later and also save a lot of time.

Your most basic command to backup a SQL database is as so:

BACKUP DATABASE [MyDatabase] TO DISK = N'Backup_Mydatabase.bak' WITH COPY_ONLY, COMPRESSION, STATS= 10

That’s really it for you GUI fans.

Let’s break this down further and look at the arguments after the BACKUP DATABASE command:

TO DISK = N’Backup_Mydatabase.bak’ – instructs SQL Server to make the backup to the disk with the name as specified. In SQL Server 2008 R2 and above the backup path can be seen in SSMS by right click the instance name > Properties > Database settings tab and note the path for Backups right at the bottom. Prior to (if I remember correctly) SQL Server 2008 R2 this backup path needed to be specified in full. You can specify a full path if you want to backup elsewhere but you need to ensure that SQL Server has permissions there.

COPY_ONLY – this option instructs SQL Server to only make a copy of the database and not record it in the backup chain. What this means is that if you have a disaster and want to recover then the backup you have just taken isn’t considered the last full backup in the chain. You should consider using this option for copying databases to another server and also for backups outside the normal backup routine.

COMPRESSION – applies compression to the backup. Leaving this off will mean SQL Server will follow the server’s default setting. I have the habit of doing this because unplanned backups take space somewhere and adding unplanned backups can tip the balance if you’re not careful. This being said you should always check space before making backups and restores.

STATS = 10 – this is a useful argument to add on as in the messages tab of SQL Server Management Studio this will give you a progress message at every interval which is a multiple of the number you have specified. So for 10 for example it will say when it’s reached 10%, 20%, 30% and so on up to 100%. This is handy to know as the backup progresses so you can estimate how far off completion you are.