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

Updated: Search Stored Procedures for Dependencies / References

In a discussion on the MSDN Forums I had posted my original code to search a Stored Procedure (search any object within SQL really) for specific text (http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/847b421d-332d-40f8-9649-b26fc2306920). You can view my original blog post describing this code block at: http://sqln.blogspot.com/2008/10/searching-text-of-programmable-objects.html.

Adam Haines had posted a wonderful enhancement to my T-SQL code. He had returned the matching text result as an XML instead of a text, this allows the user to click on the returned text and have the resulting text appear in a new window.

The new code, with the XML returned results, is:

DECLARE @StringToSearch NVARCHAR(MAX)
SET @StringToSearch = 'ENTER_SEARCH_TEXT_HERE'

SELECT    
    [name],   
    (   
        SELECT    
            OBJECT_DEFINITION(obj2.object_id) AS [text()]   
        from sys.all_objects obj2   
        where obj2.object_id = obj1.object_id   
        FOR XML PATH(''), TYPE   
    ) AS Obj_text,   
    [type] as ObjType    
FROM sys.all_objects obj1  
WHERE OBJECT_DEFINITION(object_id([name])) LIKE '%' + @StringToSearch + '%'
--Optional to limit search results....    
--AND [is_ms_shipped] = 0 --Only search user & post SQL release objects
--Change above to 1 if want to search only Microsoft provided objects released with SQL Server
--AND [type] = 'P' --Search only Stored Procedures
--AND [type] <> 'V' --Exclude Views
--See referenced article for listing of additional object types
--http://www.sqlservercentral.com/articles/T-SQL/63471/

Thanks again for this enhancement Adam!

Until next time, Happy Coding!!!

TSQL Code: Compare Tables of Databases on Separate Servers

NOTE: This script was originally developed to compare table row counts, but with the below mentioned modifications this could be helpful if you have a database that is shipped to another server and often times needs to be ETL into the other database and you are worried there may be records that aren't getting transferred properly (or at all).

The other day I came across a question on how to compare the tables in one database to the tables in another database, this was in concerns to a migration project. This person had a database that existed on an older server running SQL 2000 and they chose to migrate their database to a new server running SQL Server 2005. They had wanted to be able to somehow be able to visually display a comparison of the two databases to prove the conversion was successful, and also to display the comparisons of the tables. Results meeting above requirements are shown in Figure 1.

 
Figure 1 (results of running sp_CompareTables_All)

In response to this I had started to develop a TSQL script that created a stored procedure that would allow you to run it from the current database and specify the location of the original database. The script would then collect the tables and count the records within each table for both databases. It will display the table names on the outside (left and right) of the results, and the record counts will be next to each other on the inside (next to their table names); which will allow for a very simple and pleasing visual comparison. I wanted, and did, avoid using cursors or other techniques that will potentially bog down the system resources (such as the stored proc 'sp_MSForEachDB' and 'sp_MSForEachTable').

Now, this is a rough draft that I had thrown together and tested over a lunch period; so, there are some issues that can still be cleaned up on the script and it lacks the ability to detect if a table exists on one database, but not the other.

At the end of the script I'll also provide a quick comment that will allow you to change the results from displaying all tables and their row counts to display only the tables with mismatched row counts (which may be useful if you want to use this script as a method to troubleshoot databases that tend to not migrate all records).

The first problem faced is how to best access the server with the original database; while there are many options I chose to use the built-in stored procedure "sp_addlinkedserver". This procedure seems to be simple to implement and allows for seamless integration into TSQL code. Testing for this script was performed on the AdventureWorks database (developed for SQL 2005) on both test systems. The 'originating' database was on a SQL 2005 instance (although testing was briefly performed on SQL 2000 and SQL 2008 to validate compatibility). The 'originating' server is called TestServer35, the database is on an instance called Dev05; the database for both instances is called AdventureWorks2005. This information becomes important when using the sp_addlinkedserver command. I used the following TSQL code:

EXEC sp_addlinkedserver   
    @server='TestServer35-5', 
    @srvproduct='',
    @catalog='AdventureWorks2005',
    @provider='SQLNCLI', 
    @datasrc='Server35\Dev05'

 

As you can see, the linked server is referenced as TestServer35-5. We will use this reference, in a four-part identifier (Server.Catalog.Schema.Table). The next obstacle is to obtain a listing of tables and their row counts. I used a script I had modified last year to perform this since this script will run both on SQL 2005 and SQL 2000 (you can view my script on SQLServerCentral.com's Script section at: http://www.sqlservercentral.com/scripts/Administration/61766/). I then take the results of this and store them into a temporary table; I also do this for the new database (on local server where this stored proc is running at).

Then comes the simple part of joining the two temp tables into a final temp table. I chose this route because I wanted to have the two database in separate temp tables in the event I want to work with that data, which I will be working with the data in my update to determine if a table is missing from one of the databases.

Here is the TSQL code I used (remember if you want to use this you will need to change the linked server information to the correct information; as well as to create this stored proc in the appropriate database):

--Change the database name to the appropriate databse
USE [AdventureWorks2005];
GO

CREATE PROCEDURE sp_CompareTables_all
 AS
 
 CREATE TABLE #tblNew
( tblName varchar(50), CountRows int )

INSERT INTO #tblNew
( tblName, CountRows)
SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"
FROM sysobjects o, sysindexes i
WHERE i.id = o.id
AND indid IN(0,1)
AND xtype = 'u'
AND o.name <> 'sysdiagrams'
ORDER BY o.name

CREATE TABLE #tblOLD
( tblName varchar(50), CountRows int )

INSERT INTO #tblOLD
( tblName, CountRows)
SELECT lo.name AS "Table Name", li.rowcnt AS "Row Count"
--********
--Replace TestServer35-5 and AdventureWorks2005 below with your appropriate values
--********
FROM [TestServer35-5].[AdventureWorks2005].[dbo].[sysobjects] lo, 
    [TestServer35-5].[AdventureWorks2005].[dbo].[sysindexes] li
WHERE li.id = lo.id
AND indid IN(0,1)
AND xtype = 'u'
AND lo.name <> 'sysdiagrams'
ORDER BY lo.name

CREATE TABLE #tblDiff
( OldTable varchar(50), OldRowCount int, NewRowCount int, NewTableName varchar(50))

INSERT INTO #tblDiff
( OldTable, OldRowCount, NewRowCount, NewTableName )
SELECT ol.tblName, ol.CountRows, nw.CountRows, nw.tblName
From    #tblNew nw
JOIN #tblOLD ol
ON (ol.tblName = nw.tblName AND ol.CountRows = nw.CountRows)
        
SELECT * FROM #tblDiff
        
DROP TABLE #tblNEW
DROP TABLE #tblOLD
DROP TABLE #tblDiff

 

You simply execute the code with the following TSQL:

EXECUTE sp_CompareTables_All

 

The results of this script are shown in Figure 1 (above).

Now, this is great if you want to have a list that you can go through yourself to verify each table matches in row counts. But, what if that database has 1000 or more tables? What if you are just, simply put, lazy? Why not utilize SQL Server to process this information for you?

Well, I sure enough did just that. With a very small modification to this script you can easily have it only display any tables that don't match up in record counts.

All you have to do is change the INSERT INTO #tblDiff block's "ON" statement to join if the CountRows are NOT equal. The following is the modified block of code; the remaining stored procedure remains the same:

ON (ol.tblName = nw.tblName AND ol.CountRows <> nw.CountRows)

 

I did also rename the stored procedure from "sp_CompareTables_All" to "sp_CompareTables_Diff", but this is optional for your own ability to clarify which stored proc is being used.

To get some results I had made a few modifications to the AdventureWorks2005 database. I had added a couple of rows to a table, and removed some rows from two tables. The results of the stored proc showing only the different tables are shown in Figure 2.

Figure 2 (results of running sp_CompareTables_Diff)

As you can see the ability to change this script to show all tables or only different tables is very simple. Even setting up this script is simple, where the hardest part of the whole thing is adding a linked server (which is fairly simple also).

In a future post I'll revisit this script and include the ability to display tables that exist on one database, but not in the other. Be sure to check back for this update.

Until next time...Happy Coding!!

SQL Server 2008 Officially RTM!

On August 11, 2008 Microsoft announced through their MSDN Flash newsletter that SQL Server 2008 has been RTM (Released To Manufacturing). This has been a long awaited release for many of us in the SQL community! Congratulations to Buck Woody and the many, many others on the SQL development team on this release!

While August 11, 2008 is probably the official RTM date, those of you that have an MSDN subscription may have been enjoying the RTM a week earlier (August 6th, 2008 through the MSDN Subscriptions website) as announced by Buck Woody's blog (Carpe Datum). You can view the post at: http://blogs.msdn.com/buckwoody/archive/2008/08/06/news-flass-sql-server-2008-is-here.aspx.

Undoubtedly, you can find many blogs and posts on the new features of SQL Server 2008. This looks to have a great improvement on SQL Server 2005; in just about every area. There is improvement on security, developing, and administering; to name a few. There seems to be a little bit of something for everyone to get excited about, ranging from Business Intelligence for the enterprise users to Intellisense in TSQL syntax for the programming DBA to Resource Governing for the IT Administrators.

I look forward to using SQL Server 2008, and to enjoying a little more easily administrated database with tons of new features, flexibility, and control!

Until next time...Happy Coding!