How to Back up Microsoft SQL Server Databases

Microsoft SQL Server Database Backup

There are two ways to back up SQL Server database. One way to use a VSS-powered hot copy of the database files (MDF, LDF, etc.). Alternatively, you can use SQL Server’s BACKUP DATABASE command and then have BackupChain deduplicate the backup file.

Backup MDF and LDF Files

Create a new SQL Server backup task:

Select the Microsoft SQL Server database folders for backup. The default location of database files is under C:\Program Files\Microsoft SQL Server

Ensure the service “SQL Server VSS Writer” is activated and enabled. Ideally it should be set to start at boot time automatically.

Follow the guidelines introduced in previous chapters to complete the backup.

Restore

Follow the general restore steps outlined in previous sections. (Select Restore from main menu and select backup folder).

Then navigate to the database files (MDF and LDF file types). In our example below these are located in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

We select the latest MDF and LDF files and hit restore. Note: you need to select matching LDF and MDF pairs (matching date / time) when restoring an older version. You may use the date filter at the top of the restore screen, or select one of the older file versions at the bottom half of the screen.

After restoring to a temp folder, open SQL Server Management Studio’s option to attach databases:

Select the MDF file you just restored. In the example above, the database is called testdb.mdf.

Then, refresh the Databases node of SQL Server Management Studio and the new database will show up as a child node.

Backup Via SQL Server Script and then Deduplicate Using BackupChain

Some people prefer to use SQL Server’s backup function to create a backup file.

Run the following script to create a BAK file from SQL Server:

BACKUP DATABASE mydatabase TO DISK='C:\tmp\db.bak' WITH FORMAT

Alternatively, create a SQL Server Backup task in BackupChain and use this command as external utility. Run the utility before the backup starts:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -E -Q "BACKUP DATABASE mydatabase TO DISK='C:\tmp\db.bak' WITH FORMAT"

See example below:

Then, point BackupChain to the folder C:\tmp to back up the BAK file using deduplication.

In addition, you will want to turn on deduplication and compression for BAK files in the File Types tab (use Add button if entry does not exist):

Click Add and enter *.bak. Check “Compress Files of This Type” and check “Apply file deduplication on files of this type”:

Your backup configuration is now complete.

Restore using BAK files

Follow the general restore guidelines discussed in earlier sections to restore the BAK file. Then, follow Microsoft’s SQL Server documentation on how to use the RESTORE DATABASE command to restore the database (http://msdn.microsoft.com/en-us/library/aa238405%28SQL.80%29.aspx).

Beyond SQL Backup: BackupChain Features

As a server backup software that includes version backup, BackupChain offers various backup modules for physical and virtual machines. Windows backup in BackupChain is also offered with online backup and remote backup, allowing users to push all their data to the cloud or via FTP backup to any storage server online.
BackupChain is also a great backup solution for virtual machines: you can backup without shutting down, meaning VMs are backed up live without interruption. BackupChain offers backup for Hyper V, VirtualBox backup, backup for VMware and other platforms.
As a general purpose backup program, BackupChain also includes Exchange backup, backup for file servers and SQL backup, all powered by its deduplication backup engine.
Have you seen our list of freeware for servers? Also you may find DriveMaker useful, which is a freeware that can map FTP as a drive letter.