As everyone familiar with Murphy’s Law knows, the question isn’t whether something will go wrong, but rather when it will go wrong. Databases are certainly no exception to this law: At some point, you'll lose some data. To protect yourself from data loss, you need to make regular backups of your important databases.
You can back up your databases using either SQL Server Management Studio Express (SSMSE) or a T-SQL script. To use SSMSE, navigate to the database you want to back up in Object Explorer, then right-click the database and select Tasks, Backup. Although any backup is better than no backup, SSMSE requires you to manually select all the necessary backup options and settings and is best only for ad-hoc on-demand backup operations or for databases that are used just for development purposes. In a production environment, you need to make sure your data is backed up regularly, and the best way to perform repetitive tasks is by using T-SQL scripts.
To enable you to restore your database in the event of a disaster or data corruption, you should back up production databases in full every night at the very minimum. The code below shows how to use T-SQL's Backup command to make a full backup of the Northwind database. This sample code assumes that you've already created a directory called C:\backups.
BACKUP DATABASE Northwind
TO DISK = 'c:\BACKUPS\Northwind_Full.bak'
WITH INIT, NAME = 'Northwind Full Backup',
DESCRIPTION = 'Northwind Full Backup'
When you do a backup every night, you risk losing no more than one day of data. But what if you don’t want to risk an entire day’s work? That’s where transaction-log backups come into play.
Backing up the transaction log periodically during the day saves all the transactions that have been committed to the database since the last full backup and significantly narrows your data exposure window. You can use the transaction-log backups to restore all those saved transactions. You can run multiple transaction-log backups per day--how many you run depends on how active your database is and how much data loss you're willing and able to tolerate.
Before you use transaction-log backups, make sure that your database’s recovery model is not set to Simple. To change a database’s recovery model, open SSMSE, right-click the database, and select Properties from the context menu. Using the Options page, change the recovery model to either Full or Bulk-logged. The Full recovery model specifies that all transactions are recorded in the transaction log; the Bulk-logged model logs records all transactions except for bulk insert operations.
The following example shows the T-SQL code you'd use to create a transaction-log backup of the Northwind database:
BACKUP LOG Northwind TO DISK = 'c:\BACKUPS\Northwind_Log.bak' WITH NOINIT, NAME = 'Northwind Log Backup', DESCRIPTION = 'Northwind Log Backup', NOFORMAT
A backup a day makes for a happy SQL Server Express DBA.
