General discussion of SQL Topics; aimed for the novice-intermediate level Microsoft SQL Server User. Currently focuses on using SQL Server 2005.

Tuesday, June 2, 2009

Backing up directly to a network share

Over the past few weeks I’ve seen many users who’ve complained that they can’t directly backup to a network share drive. They’ve all expressed, at one point or another, how disappointing this is for a SQL Server product that boasts about best practices, high availability, and security…yet, you have to backup locally then use a separate script to get the backup file to a network share (seems a bit of a risk involved for the brief time the backup file is stored locally; as well as using up precious local resources to do all of this).

Well, I finally intervened in one of those discussions and pointed out that SQL in fact can backup directly to a network share. In fact there are multiple methods that can be performed to accomplish this simple feat! First, you must create a “backup device” that is located on a network share and backup to that device, this is done by one of two ways you can specify to backup to a “disk” (or file) and point to a network share drive/location. Or you may create a backup “media set” which is stored on the network share. These are just two of the methods to accomplish this, which are easy enough to perform; as well as documented in Books Online (BOL). I’ll briefly cover this method here; you’ll want to refer to BOL to find more detail on it, as well as other possible methods.

Some terminology to help…

NOTE: Be sure to understand the differences in terminology here. Notice that I’ve quoted “backup device” and “media set”.

A “backup device” is not necessarily a physical device (i.e. tape drive, DVD media, etc). A “backup device” is a virtual device to SQL, meaning that you tell SQL if the ‘device’ is a “DISK” or “TAPE” (understand that DVD-ROM, USB, etc are considered “TAPE” devices in this context). Depending on the ‘device’ you choose will depend on where SQL stores the backup.

A “media set” is a logical name of a collection of (or a single) “backup devices”. This means that you could have multiple locations on a “DISK” to where SQL stores the backup (this can be used for striping, to increase your speed of backups…as well as to increase your data integrity capabilities).

See the “References” section for BOL definition and details on both “Backup Devices” and “Media Sets”.

Network Share Backup Requirements…

First things first, if I’m pointing out that it’s relatively simple enough to backup to a network share, then why are there so many people claiming they can’t (or believe that SQL can’t)? Well, as with all other features in SQL…most of the problem is either permissions or lack of knowledge on how to implement (accomplish) the required task.

Now, to make this all work you need two ensure two things happen.

1) You have to know how to tell SQL to make the backup to the network share. As mentioned earlier you need to specify a “backup device” located on the network share. Here are the links in BOL describing this in detail. Note: There is a section in BOL dedicated to this subject, review it!

2) Ensure that the network share you are attempting to make the backup to can actually be seen and written to. A simple test is to open up SQL Server Management Studio (SSMS) and attempt to backup the database using the “Back up…” task wizard. You’ll want to backup to “DISK” and then browse to the network share location, or enter the Universal Naming Convention (UNC) path. If you can successfully backup to the network share through SSMS, then you know at a minimum that you have the permission with the account you logged into SSMS with to make the backup to the network share. Even if you can’t pass this test, it doesn’t necessarily mean SQL can’t backup to the shared drive; scheduled jobs and various other methods to create regular backups often times will use different credentials than your local account uses. Regardless you’ll probably want to look into the permissions you are given for the folder you are trying to backup to, or discuss this with the responsible party that sets up the shared folder permissions to ensure that the appropriate SQL services has the required credentials. Now you either will want to set up your back up to use the proper credentials, or ensure that the proper SQL Service account has the permissions to write to this folder (check BOL for service account information for SQL Server).

Performing the backup…

The following is a quick script to backup a sample database called “Backup_Testing” to a folder called “Backups” on a server called “DEV-CLUS-07”. This is a “script” created after using the task “Back up…” in SSMS:

Backup to Network Share using UNC path (T-SQL example):
BACKUP DATABASE [Backup_Testing] 
TO  DISK =
N'\\DEV-CLUS-07\Backups\NetShare01.bak' 
WITH  DESCRIPTION = N'Backing up the database to a network share drive',
 
NOFORMAT, INIT,  NAME = N'Backup_Testing_Full_DB_BkUp_to_Network_Share',
 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

GO

Notice that the strings are prefixed with “N” to designate unicode strings; this is an important part to use when specifying the UNC directly.

Now, instead of entering the UNC each time you want to create a backup you can create a “backup device” that specifies the UNC path for you; the drawback is that you must also specify the file name. This is ideal if you want to continuously use the same file over and over, and especially if you make heavy use of expirations for your backups and or want multiple choices of backups in a single file location (maybe for easier file administration).

You first create the “backup device”. This is a backup device called “Network_Share_Device_01”, located in the same location as the previous example (except the file name has been also specified, which is “NetShareDev01.bak”).

Create a “backup device” that points to a network share location (T-SQL example):

USE [master]
GO
EXEC master.dbo.sp_addumpdevice    @devtype = N'disk', 
                               
@logicalname = N'Network_Share_Device_01',
 
                               
@physicalname =
N'\\DEV-CLUS-07\Backups\NetShare01.bak'
GO

With this device in place you would use the same T-SQL as the first example; except instead of using the “TO DISK” option you’d use the “TO [device_name_here]” option…

Backup to Network Share using backup device (T-SQL example):

BACKUP DATABASE [Backup_Testing] 
TO
[Network_Share_Device_01]
WITH  DESCRIPTION = N'Backing up the database to a network share drive',
 
NOFORMAT, INIT,  NAME = N'Backup_Testing_Full_DB_BkUp_to_Network_Share',
 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

GO

Conclusion:

The next time you setup your database backup schedule, remember that you can directly backup to your network share and lower the risks of keeping the backup locally…and especially, lower the overhead of resources used on the local instance of SQL to get the backup to your final destination.

As you can see, the T-SQL is straight forward; the only complication is in the setting up SQL properly to allow you to directly backup to a network share location, and really the only complicated part for the DBA is ensuring the proper permissions (at the OS level) are in place. This only becomes complicated in the scenarios where the DBA is not in control of permissions for the location of the database files and backup files. With some cooperation between database administrators and network administrators this can easily, and painlessly, be overcome.

This is a win-win situation for everyone; it streamlines the process while improving the performance, security, and data integrity of the database!

References:

Microsoft: Backup Devices (SQL 2008) - http://msdn.microsoft.com/en-us/library/ms179313.aspx
Microsoft: Media Sets, Media Families, and Backup Sets - http://msdn.microsoft.com/en-us/library/ms178062.aspx

2 comments:

Alocyte said...

I have a bit of a bother, and it's OS / SQL related. I backup to a UNC device, and frequently get a network related error - but not always. And the error is also OS related...

The scene: 64 bit Windows Server 2008 hosts 64 bit SQL 2008 Standard, and devices are created to point to 64 Server 2008 Server B (\\ServerB\SQL_backups\dvc_DB.bak). The database is backed up daily, on a job, and when run the following error is returned:
Date 2009/08/08 06:43:30 AM
Log Job History (Backup_AssetData_2009-08-08)

Step ID 3
Server SQL01
Job Name Backup_AssetData_2009-08-08
Step Name Backup Database - Second Attempt
Duration 00:05:33
Sql Severity 16
Sql Message ID 3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: COMPANY\SERVICE_USER. Processed 94096 pages for database 'AssetData', file 'AssetData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AssetData', file 'AssetData_log' on file 1. [SQLSTATE 01000] (Message 4035) The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on 'dvc_AssetData(\\riscsrv-dcm04\RISCSRV-SQL01_Backups\dvc_AssetData.bak)'. [SQLSTATE 42000] (Error 3634) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.


The file is generated, the same size as a successful backup, but the job terminates unsuccessful.

I have tried to delete the devices' file, and then the backup usually succeeds. This may point to a permissions' type error, but the user the job is run under is a domain admin. The destination server is not unavailable during this time, and the network shares also remain active throughout the excersize - although I haven't got a way to "prove" this.

I am hoping someone can help me identify the cause of the problem, or has experience with a similar problem which has been resolved.

I have a few databases that backup to the UNC described above (different files) that don't fail... some bigger, some smaller.

My SQL2000 server backs up it's databases to the SQL2008 server, and those backups don't fail with this error, also using UNC device files btw.

My research has led me to understand that the 64bit OS tries to buffer the files it receives from another server, and I was wondering if this could be influencing the backups as the destination server is also 64 bit Windows Server 2008.

You mention 'N is important, can you elaborate why, and what occurs with out it? Also, your device defined above didn't have a FileName, just a folder name... is this important too?

Thanks,
Habeeb

James said...

Hello Habeeb,

Thank you for your comments; I can't quite pinpoint a cause of your problem, but I've also not done any research on this either.

To answer your questions:

Q1) "You mention 'N is important, can you elaborate why, and what occurs with out it?"

A1) Prefixing unicode data with N is actually an ISO standard for databases. In other words, good practice. The reasoning for this is that this tells the Database Engine that your string is a Unicode string, as opposed to non-Unicode string. The primary ill-effect you may observe, and this is a hit and miss type of problem, is that SQL may not recognize particular characters...or worse yet, it may interpret the characters to mean something else. In context of the blog post, not specifying the prefix N can (and often times will) result in SQL mishandling the UNC path string you are passing in. In particulare, SQL often times has difficulty translating the "\\" (server pointer designation) when not properly translated (i.e. prefixing the path with N). You can read more about this at: http://msdn.microsoft.com/en-us/library/ms191313.aspx

Q2) "Also, your device defined above didn't have a FileName, just a folder name... is this important too?"

A2) Actually, the device declaration "sp_addumpdevice" contains a parameter called "@physicalname", which you'll notice I pass the string "N'\\DEV-CLUS-07\Backups\NetShare01.bak'" as the full path & file name. This is passed at this point because when creating the backup device you are typically creating a single file, in a single location, and will continously add/remove your backups to this single device (or multiple devices, should you choose). The first "BACKUP DATABASE" T-SQL command does not contain a file name, this is simply a typo (which I have now fixed to avoid confusion to others). The proper syntax, specifying the same location and file name as the device uses (for simplicity to follow) is:

BACKUP DATABASE [Backup_Testing]
TO DISK = N'\\DEV-CLUS-07\Backups\NetShare01.bak'
WITH DESCRIPTION = N'Backing up the database to a network share drive',
NOFORMAT, INIT, NAME = N'Backup_Testing_Full_DB_BkUp_to_Network_Share',
SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO

It's important to distinguish that the BACKUP DATABASE command and device are two separate entities in SQL Server context. Meaning, that if you run the above BACKUP DATABASE command it will create the backup file called "NetShare01.bak" at the location of "\\DEV-CLUS-07\Backups\".

The backup device does the exact same thing; except it's just a device pointing to the location and file name...the device, itself, doesn't make the backup.

You still have to execute the BACKUP DATABASE command that I provided after the T-SQL code to make the device. Notice the "TO" parameter specifies the device, instead of the network location and file name. You can read more about the BACKUP DATABASE command at: http://msdn.microsoft.com/en-us/library/ms186865.aspx. Be sure to also visit the links in the original blog post to learn more about devices and media sets.



I hope the above answers both your questions thoroughly enough. If I happen to come across anything in regards to your problem stated above I will be sure to post another comment.

Please feel free to ask any additional questions you may have.

Thanks,
James