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.
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 2023 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 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 Fix Disk Signature Error PartMgr 58
- Copy Long Path Names and Deep Folders over 240 Characters
- Virtual Machine Backup Software for Hyper-V, VMware, VirtualBox
- How to Backup Hyper-V Virtual Machine on Windows Server 2022 or Windows 11
- Low-Cost Backup Software and Cloud Storage Solution
- How to Fix Error 1219: Multiple connections to a server or shared resource by the same user
- USB Disks and Hyper-V: How to Use, Pros and Cons
- Fix for: The shadow copies of volume X: were aborted because of an IO failure
- How to Easily Protect Your Servers Against Ransomware
- How Fix Invalid File Date Time Automatically
- Video Step-by-Step Restore VM from Hyper-V Backup on Windows Server 2022 and Windows 11
- Gmail SMTP Configuration for Backup Alerts
- P2V Converter & Backup Software for Hyper-V VMware VirtualBox
- Hyper-V Replica Pros and Cons
- How to: Bare-Metal Recovery via USB Windows Server Boot Disk
- Backup Software Without Compression Option: As-Is File Backup
- Why a Hyper-V Checkpoint Isn’t a Backup
- Pros and Cons of Deduplication Technologies, VHDX, Hyper-V
- Alternatives to Acronis, Veeam, Backup Exec, and Microsoft DPM
- Ubuntu VM Hyper-V Backup and What You Need to Know for Linux Support