In this article we will explain of how to set up automatic backup of Microsoft SQL Server to Amazon S3.
The method described in this article works well for all editions of MSSQL Server,
including Microsoft SQL Server Express Edition.
We use Microsoft SQL Server Management Studio to generate backup script.
If you do not have this tool installed, you can download and install it from Microsoft's website.
If you are familiar with T-SQL you can write backup script manually or use existing one.
Open SQL Server Management Studio, expand Databases and select the database you want to backup.
Right-click the database, point to Tasks, and then click Back Up.
Open SQL Server Management Studio, right-click the database and choose Tasks -> Back Up
The Back Up Database dialog box will appear. Here you can configure various backup
settings. Detailed description can be found here.
Back Up Database dialog allows you to configure various backup settings
Note that we changed default destination to Z: drive,
this is the virtual drive that points to the Amazon S3 Bucket.
Check out these simple instructions
to learn how to mount an S3 Bucket as a Windows Drive.
After you configure backup click Script -> Script Action to File
and save generated script to the file.
Script Action to File
The following script was generated:
BACKUP DATABASE [master] TO DISK = N'Z:\mssql-backup\full-backup.bak'
WITH DESCRIPTION = N'Automatic backup to Amazon S3 (_full_)',
NOFORMAT, NOINIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Let's see this script in action. Open Command Prompt and type the following command if you are using Windows Authentication:
sqlcmd -S .\SQLEXPRESS -i c:\scripts\full-db-backup.sql
Or type the following command if you are using SQL Authentication:
sqlcmd -U BackupUser -P password -S .\SQLEXPRESS -i c:\scripts\full-db-backup.sql
You should see output similar to this
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\Administrator>sqlcmd -S .\SQLEXPRESS -i c:\scripts\full-db-backup.sql
11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
52 percent processed.
61 percent processed.
70 percent processed.
81 percent processed.
90 percent processed.
Processed 352 pages for database 'master', file 'master' on file 5.
100 percent processed.
Processed 2 pages for database 'master', file 'mastlog' on file 5.
BACKUP DATABASE successfully processed 354 pages in 0.659 seconds (4.394 MB/sec).
C:\Users\Administrator>
Ok, now let's create the following batch file c:\scripts\run-mssql-backup.cmd
@echo off
sqlcmd -S .\SQLEXPRESS -i c:\scripts\full-db-backup.sql
And finally type the following command to add new task to Task Scheduler
schtasks /Create /TN mssql-full-backup /SC DAILY /TR c:\scripts\run-mssql-backup.cmd /ST 01:00
This command creates a scheduled task "mssql-full-backup" to run c:\scripts\run-mssql-backup.cmd
starting at 01:00 every day. You will see output similar to this:
SUCCESS: The scheduled task "mssql-full-backup" has successfully been created.
Congratulations, automated backup to Amazon S3 is configured.
