How to backup MS-SQL Databases to Amazon S3. Configure Automatic backup of MS-SQL Server to Amazon S3.
Bringing the Cloud Closer

Automatic backup MSSQL Server to Amazon S3

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.

SQL Management Studio -> Tasks -> 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.

SQL Management Studio -> Back Up Database dialog

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.

save sql 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

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).


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.

Fully Functional Free Trial
Powered by Amazon Web Services
Social Connection
Mount Amazon S3 Drive
People like TntDrive!
Related Products
Windows Client for Amazon Glacier - new low-cost storage for data archiving and backup.
Copyright © 2008-2024 Netsdk Software. All rights reserved.  Terms of Use.  Privacy Policy.  S3 Client.  Prevent RDP Brute-Force.