Menu & Search

Backing Up a SQL Server Database

Backing Up a SQL Server Database

Backing up a database is something I’ve always found easier done within a query window. There’s not much to it;

	TO DISK = N'e:\directory\databasename_backupdate.bak' 

That’s what I’m used to writing. I’ve no need to worry about the copy_only option these days, or check SQL Server Editions to see if I can use compression. Good times that was. Measuring available disk space against estimated backup sizes, and sometimes even adding temporary virtual disks to the server to accommodate the backup file size. The SQL statement for backups is simple… but there’s a lot to consider when performing a one. It just depends on the restrictions of the servers you are working with really.

Below is a simple run-through for backing up a database using the Back Up Database Wizard. As always, have a look at the manual first.

How to Backup a SQL Server Database

1. Right click your database, select Tasks and click Back Up…

SQL Server Backup SSMS

2. In the General tab of the prompted window you’ll see it’s already set-up for a Full Backup on AdventureWorks which is using the Simple Recovery model.

SQL Server Backup Wizard

3. I’ll remove the destination file and move it somewhere else.

SQL Server Backup Destination

The directory has been picked up by my chosen Database Settings. Type the new backup file name here, remembering the .bak at the end.

SQL Server Backup Destination

4. Over to the Media Options tab, there’s a few options for consideration. Such as verifying the backup or performing checksums. You might think that verifying backups with automatic offsite restores works for you. Or, maybe you’re happy relying on the VM backups? As the importance of your database backups grows, the deeper it goes. I’m leaving those options unchecked as I don’t need them, and all other options are set as default.

SQL Server Backup Media Options

5. In the Backup Options tab, I’m leaving all options set to their defaults. No backup expiry or encryption, and I’m going to trust I’m using compression as default on my SQL Server.

SQL Server Backup Options

6. Click the Script button at the top of the window to see the generated SQL code behind this backup operation.

SQL Server Backup Generate Script

7. Some of the code contained within the statement is unnecessary, but it’s doing the same thing anyway. We can run this command or click OK on the Wizard.

SQL Server Backup Script

8. And when you’re done…

SQL Server Backup Successful

You have a backup of your database!