SQL Zone is brought to you in partnership with:

Paul is a DZone MVB and is not an employee of DZone and has posted 135 posts at DZone. View Full User Profile

How to Setup a SQL Server Backup System

12.15.2013
| 1033 views |
  • submit to reddit

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

Published at DZone with permission of Paul Andrews, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)