Windows: How do I backup and restore MS SQL Server with CrashPlan?
Our (external) system administrator indicates that CrashPlan PRO does not support Microsoft SQL Server, which impact can this have on us?
Indeed, the CrashPlan PRO software does not have an agent for Microsoft SQL Server on board. That does not mean that CrashPlan PRO is unable to make backups of Microsoft SQL Server databases. Learn how you can backup your SQL Server databases using CrashPlan.
Several files
Microsoft SQL Server 7.0, 2000, 2005 and 2008 databases consist of multiple files. In the simplest form is a data file (ending in .mdf) and transaction log file (extension .ldf).
Several backup times
CrashPlan PRO software makes backups of files, even if these files are in use (thanks to Windows Volume Shadow Copy Service). CrashPlan PRO can directly and easily backup the SQL Server database files. In the case of MS SQL Server this means that the individual files that constitute a MSSQL database server, are backed up at different times.
The screenshot below shows how a folder of SQL Server database files looks like when you want to restore using CrashPlan: 
Restore = manually select
Backing up is no problem. Restoring is a different matter. When restoring Microsoft SQL server databases from the CrashPlan PRO application itself, you need to manually select all SQL Server database file. That means each log (.ldf) and each data (.mdf) file that:
- fit together
example Database_name_Data.MDF and Database_name_Log.LDF - is backed up at approximately the same time
choosing the "most recent" version is certainly no guarantee for success
Moment <-> backup versions
Because CrashPlan PRO only backups if data is changed, in practice it is sometimes difficult to select log and database files of aproximate matching date / time. See below a CrashPlan PRO screenshot, where all versions of MS SQL Server data and the associated transaction log are shown in expanded state:
| Name | Size | Last backed up | |
|---|---|---|---|
![[ ]](../../../images/crashplan-pro-client/win/selectie-ongemarkeerd-windows.png){kind=small} |
 SQL_server_data_file.MDF |
265.7 MB | 10/25/09 3:05 AM |
|
![[LO]](../../../images/crashplan-pro-client/file-version.png){kind=small} 10/25/09 2:53 AM |
265.7 MB | 10/25/09 3:05 AM |
|
10/25/09 1:53 AM |
265.9 MB | 10/25/09 2:29 AM |
|
10/22/09 1:35 AM |
265.5 MB | 10/22/09 4:56 AM |
|
10/18/09 4:38 AM |
265.5 MB | 10/18/09 8:22 AM |
|
10/18/09 2:37 AM |
265.8 MB | 10/18/09 4:08 AM |
|
10/11/09 4:10 AM |
265.6 MB | 10/11/09 4:58 AM |
|
10/4/09 3:43 AM |
265.6 MB | 10/4/09 4:57 AM |
|
9/27/09 3:18 AM |
265.6 MB | 9/27/09 4:07 AM |
|
9/20/09 4:50 AM |
265.6 MB | 9/20/09 7:00 AM |
|
9/20/09 2:50 AM |
265.9 MB | 9/20/09 3:09 AM |
|
9/13/09 4:26 AM |
265.5 MB | 9/13/09 8:01 AM |
|
9/13/09 2:25 AM |
265.8 MB | 9/13/09 4:17 AM |
|
9/6/09 3:55 AM |
265.2 MB | 9/6/09 6:44 AM |
|
9/6/09 1:55 AM |
265.5 MB | 9/6/09 3:04 AM |
|
8/30/09 3:28 AM |
265.6 MB | 8/30/09 4:35 AM |
|
8/23/09 5:00 AM |
265.9 MB | 8/23/09 7:44 AM |
|
8/23/09 2:54 AM |
265.5 MB | 8/23/09 4:04 AM |
|
8/16/09 3:05 AM |
265.4 MB | 8/16/09 4:48 AM |
|
8/9/09 4:37 AM |
265.5 MB | 8/9/09 6:04 AM |
|
8/2/09 6:10 AM |
265.6 MB | 8/2/09 8:25 AM |
|
8/2/09 4:09 AM |
265.6 MB | 8/2/09 4:53 AM |
|
8/1/09 10:09 AM |
265.8 MB | 8/1/09 4:26 PM |
|
7/27/09 6:25 PM |
265.8 MB | 7/28/09 3:13 AM |
|
7/26/09 3:44 AM |
265.8 MB | 7/26/09 7:57 AM |
|
7/26/09 1:44 AM |
266.0 MB | 7/26/09 3:44 AM |
|
7/19/09 3:17 AM |
265.9 MB | 7/19/09 4:05 AM |
|
7/12/09 4:50 AM |
265.7 MB | 7/12/09 7:38 AM |
|
7/12/09 2:50 AM |
265.9 MB | 7/12/09 3:51 AM |
|
7/5/09 4:25 AM |
265.4 MB | 7/11/09 2:36 AM |
|
SQL_server_transaction_log.LDF |
2.0 MB | 10/22/09 4:55 AM |
|
10/22/09 1:35 AM |
2.0 MB | 10/22/09 4:55 AM |
|
8/23/09 2:54 AM |
2.0 MB | 8/23/09 4:04 AM |
|
8/2/09 12:03 AM |
2.0 MB | 8/2/09 6:24 AM |
|
8/1/09 10:09 AM |
2.0 MB | 8/1/09 4:25 PM |
|
7/27/09 6:25 PM |
2.0 MB | 7/28/09 3:13 AM |
|
6/26/09 1:41 AM |
2.0 MB | 7/11/09 8:52 AM |
Inconsistent database
If the time where database and logs were backed up, is dispersed, this can result in refusal of Microsoft SQL Server to reattach this set of database files (.ldf & .mdf). SQL server reports in such cases that the database is inconsistent, like:
Error: 9003, Severity: 20, State: 6
The LSN (61797:870:1) passed to log scan in database 'xyz' is invalid
Easy & guaranteed restore
To be sure that you can restore your SQL Server database, create the backup of the SQL Server database with a tool that has SQL server support onboard. Microsoft provides two tools standard suitable for backing up and restoring SQL Server databases:
1. SQL Server Enterprise Manager
With Microsoft SQL Server Enterprise Manager you could schedule "Database maintenance" to run this periodically. One option here is to include a backup of the database(s).
2. NTBackup
SQL Server Enterprise Manager - Database maintenance
Creating and Restoring the generated backup (.bak) files can then seamlessly happen with CrashPlan PRO. Restoring the databases is consecutively done from the Microsoft SQL Server Enterprise Manager. Other (online) backup solutions that support MS SQL server, have this is all integrated into one application.
Conclusion: lacking ease
The lack of MSSQL support in CrashPlan PRO means that additional work should be done once, to perceive greater certainty that proper backups are made. Also there are additional actions to take, when restoring from CrashPlan Pro Backups.
Wie kann ich eine MS SQL Server-Datenbank mit CrashPlan PRO sichern und wiederherstellen? Hoe backup en herstel ik MS SQL server met CrashPlan?