Over a million developers have joined DZone.

How to Setup a SQL Server Backup System

DZone's Guide to

How to Setup a SQL Server Backup System

· Database Zone
Free Resource

Download the Guide to Open Source Database Selection: MySQL vs. MariaDB and see how the side-by-side comparison of must-have features will ease the journey. Brought to you in partnership with MariaDB.

Arranging a server backup system is generally more complex than setting up a PC backup.  For the PC, backup is often a matter of plugging in a flash storage drive and taking a few minutes to configure the operating system backup utility settings.  This is not the case for a full server.  A business server will have many types of storage files and information to preserve.  The importance of a complete backup is business-critical.  There are no simple utilities to allow an administrator to setup a complete backup protocol in a few minutes.  Most importantly, a server can rarely by backed up as an image.  Files must be preserved on an individual basis, and backup and restore times are often critical parameters.

Windows servers can often be backed up on a component-by-component basis.  One of the most common and important of these components is SQL Server. With NovaStor SQL server backup, all your business data is secure and adequately protected. Here are the steps an administrator can use for automating a backup system for SQL Server 2012.

Use Azure to Create Windows Storage Objects

Start by creating a storage account from the Azure Management Portal.  Access the storage account, and then choose the Manage Keys section.  Here you will enter the storage account name, and the Primary and Secondary access keys.  If you like, you can perform this entire process programmatically using the REST API.

Next, create the Blob container by choosing the Containers tab and clicking on Add Container at the bottom of the screen.  Enter the container name, and store this name separately on your backup design sheet.  You will use this designator later in the process to specify the URL backup path.  Be sure to specify the container type as private, in order to secure you files.  Note that authentication will be required, regardless of whether the container is made private or public.  Note also that this part of the process can also be automated with REST APIs.

Create Credentials

The credential will store the authentication information.  It contains both the storage account name and the access key.  After creating the credential, use it as part of the BACKUP and RESTORE statements for the WITH CREDENTIAL specification.  Create these with the CREATE CREDENTIAL statement.

Write a Backup Targeting the Azure Blob Storage Service

The backup process is written in tsql.  Start by connecting to the instance of SQL Server 2012 in the SQL Server Management Studio.  Choose New Query.  Create the tsql code by using the BACKUP DATABASE statement with the proper entries for your database and credentials (see the Help Files for an example.)  Press the execute button for a trial run, and use the object explorer browser to locate the newly created backup files.

Perform a Full Restore

Repeat the steps for creating the storage service, this time using the RESTORE DATABASE statement and settings (again found in the Help Files).  Verify the code by clicking Execute, and checking the restored file set on your server.

Backing up a business server is a significantly more complex task than backing up a simple PC.  However, Windows has made it easier to perform the backups according to various services.  The SQL Server backup can be automated with tsql code, and used to perform an off-site server backup for maximum security and file protection.

Presented by NovaStor

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.


Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}