Backing up a database is something I’ve always found easier done within a query window. There’s not much to it;
BACKUP DATABASE [DATABASENAME] TO DISK = N'e:\directory\databasename_backupdate.bak' WITH COMPRESSION, STATS
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…
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.
3. I’ll remove the destination file and move it somewhere else.
The directory has been picked up by my chosen Database Settings. Type the new backup file name here, remembering the .bak at the end.
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.
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.
6. Click the Script button at the top of the window to see the generated SQL code behind this backup operation.
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.
8. And when you’re done…
You have a backup of your database!
Leave a Reply