Over a million developers have joined DZone.

How to Setup a SQL Server Backup System

· Database Zone

To stay on top of the changing nature of the data connectivity world and to help enterprises navigate these changes, download this whitepaper from Progress Data Direct that explores the results of the 2016 Data Connectivity Outlook survey.

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

Turn Data Into a Powerful Asset, Not an Obstacle with Democratize Your Data, a Progress Data Direct whitepaper that explains how to provide data access for your users anywhere, anytime and from any source.

Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

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

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

{{ parent.tldr }}

{{ parent.urlSource.name }}