Discovering SQL Server: Backups

It is very important to pay special attention to SQL backups.  SQL is not your average server and a little extra care is in order to make sure you are doing it correctly.  The database files have constant activity, so you can’t just ask Windows make a copy.
Here is a quick SQL command to get you started:

BACKUP DATABASE MyDatabase
                TO DISK '\\server\share\MyDatabase.bak'
                WITH BUFFERCOUNT=35

BACKUP LOG MyDatabase
                TODISK '\\server\share\MyDatabase.bak'
                WITH BUFFERCOUNT=35

This takes a fresh full backup of your database and a tail backup of your log file.  Make sure you are backing up your logs. If this is a production database, you should backup the log frequently.  I used a network path in my example because I want those backups off the server.

Now that you have your backups in a file on another server, use your favorite backup method to back them up.  Every environment is different, but I do full backups nightly and keep 7 most recent backups on the network share.  My transaction logs run every 15 minutes on databases that need backed up more often than daily.

There are several options you can use when running your backups.  I also add COMPRESSION and CHECKSUM along with the BUFFERCOUNT=35 option. The buffer count one is kind of a magic number that speeds up your backups.  It allows the backup process to stream more data from disk into ram as you save it to the network.