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.
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.
Backup Software OverviewThe Best Backup Software in 2024 Download BackupChain®
BackupChain is the all-in-one server backup software for:
Disk Image Backup
Drive Cloning and Disk Copy
File Server Backup
Virtual Machine Backup
Server Backup Solution
- Best Practices for Server Backups
- NAS Backup: Buffalo, Drobo, Synology
- How to use BackupChain for Cloud and Remote
- DriveMaker: Map FTP, SFTP, S3 Sites to a Drive Letter (Freeware)
- VM Backup
- V4 Articles
- Knowledge Base
- Archive 2024
- Archive 2022
- Archive 2021
- Archive 2020
- Archive 2019
- Archive 2017
- Archive 2016
- Archive 2015
- Archive 2014
- Archive 2013
- BackupChain (German)
- German Help Pages
- BackupChain (Greek)
- BackupChain (Spanish)
- BackupChain (French)
- BackupChain (Dutch)
- BackupChain (Italian)
- BackupChain is an all-in-one, reliable backup solution for Windows and Hyper-V that is more affordable than Veeam, Acronis, and Altaro.
Other Backup How-To Guides
- How to Delete Files in the Backup Folder
- What is Disk Backup Software?
- Fix for: The shadow copies of volume X: were aborted because of an IO failure
- Hyper-V Checkpoint Risks
- How to: Hyper-V Replication for Immediate Disaster Recovery
- Hyper-V Pass-through Disk: Pros and Cons
- Differential Backup
- How to Fix Error 1219: Multiple connections to a server or shared resource by the same user
- BackupChain v2.4.531 Released!
- Why a Hyper-V Checkpoint Isn’t a Backup
- Backup Software with VSS Support for Windows Server and Windows 11
- OneDrive Backup Software for Windows PCs and Servers
- Deduplication of Virtual Machine Backups in Hyper-V and VMware
- Low-Cost Backup Software and Cloud Storage Solution
- Live Hyper-V Backup using Hyper-V VSS Writer: How Backup Works
- What You Need to Know About Servers
- 18 Hyper-V Tips & Strategies You Need to Know
- How to Delete VSS Shadows You Can’t Delete: outside of your allowed context…
- A Deep Dive into SFTP (SSH File Transfer Protocol)
- VSS Crash and Application Consistency for Hyper-V and VMware Backups