SQL Server Backups
In August's article, we discussed the importance of backing up your databases. This month we'll examine best practices to back up most SQL Server databases.
Full backups with SQL Server can be done quite easily in two ways. The first is by creating a maintenance plan and the second is using the sqlcmd utility from a shell.
The maintenance plan is access using SQL Server Management Studio (SSMS). To create a new plan, connect to your database server, expand Management, right-click Maintenance Plans, and click Maintenance Plan Wizard. Enter a name for your plan, set the schedule (recommended to be at least daily), select the server and indicate that this is a backup task. You will then be shown a screen where you can input the destination file(s) and the full backup type. Select the appropriate options, finish the wizard, and your plan will run on the schedule you defined. Check the destination folder(s) after it runs to ensure that the data is backed up properly, and view the history periodically to ensure the backups still function correctly.
Performing the backup from the command line requires knowledge of the BACKUP Transact-SQL command. Using this command, you can perform the database backups as part of your command line processes, from a query window in SSMS, or even stored procedures. To perform a full backup of a database with compression, create an SQL file with the following contents:
use [databasename]; BACKUP DATABASE [databasename] TO DISK = "E:pathtobackupdatabasename.bak" WITH COMPRESSION, FORMAT;
Replace databasename with the name of the database to back up. The file is then utilized from the command line using the following command:
sqlcmd -Usa -Psapass -S localhost,2008 -i E:pathtosqlfile.sql
If everything worked correctly, the backup file specified in the SQL file should now exist and contain all of your data. You can confirm this by testing a restore to a new database.
This achieves the goal of getting a full database backup created, but how do we create a differential so that we can take backups even more often for larger databases?
To create a differential backup using a Maintenance Plan, follow the same steps as above, but instead of selecting the full backup type, select the differential backup type. This will result in the backup containing only the data changed since the last backup (full or differential) was taken. Note that it is still a good idea to periodically take a full backup to speed up the restoration time in case of failure. When restoring with partial backups, you will need to load the most recent available full backup and then all partials taken after the full.
Creating a differential backup using sqlcmd utilizes the following command:
use [databasename]; BACKUP LOG [databasename] TO DISK = "E:pathtobackupdatabasename.bak" WITH COMPRESSION, FORMAT;
Run the SQL file the same was as the full backup, and you should now have your partial backup containing all changes made since the previous backup (be it full or differential).