Microsoft SQL Server Backup to Amazon S3. Automatically 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.

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.

For more information mysql backup to s3, please get in touch with us!