SQL Server Backup and Restore from Network Drives

Working with virtual machine environments has always presented a challenge in figuring out how to “easily” or “effectively” restore SQL Server databases. This is especially true during migrations and upgrades. It is easy to run out of hard drive space when copying, moving and manipulating several terabytes of content databases. Recently, I found myself working with tens of terabytes and I found myself lacking in space and time. Even with enough storage, copying and moving the databases was taking significant amounts of time. I concluded that it would be to my benefit, my client’s benefit and all of those great people reading this blog to figure out how to backup and restore a SQL database from a network drive. And, I figured it out after putting together several scripts and testing various credentials on the network.  Yes, you heard right, it exists. The following script effectively maps a network drive under the SQL Server process account which also performs the backups and restores. This is huge: its a huge storage saver, but more importantly and huge, huge time saver. Alas, here’s the script (in three steps):

Step 1: Map the drive

In this step, we issue the commands to map a drive (i.e “U:”) under the SQL Server process account with an account that has access. Replace the “DOMAIN\username” with your account if you have access to the network share to map the network drive under your credentials.  By running the command in the Query Window, the SQL Server process account will be able to see the mapped drive, thus, allowing us to backup and restore to that network drive directly.

Use Master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC XP_CMDSHELL 'net use u: /delete'
EXEC XP_CMDSHELL 'net use u: \\networkserver\ShareBackups MySecretPassword /USER:DOMAIN\username'
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
Step 2: Backup or Restore

In this step, we backup or restore the desired databases.

To perform a backup (note we use copy_only to avoid breaking the backup chain and compression):

BACKUP DATABASE WSS_CONTENT TO DISK = 'u:\WSS_CONTENT.bak' WITH COPY_ONLY,COMPRESSION;
GO

To perform a restore:

RESTORE DATABASE WSS_CONTENT FROM DISK = 'U:\WSS_CONTENT.bak' WITH MOVE 'WSS_CONTENT' TO 'D:\SQLData\WSS_CONTENT_Data.mdf', MOVE 'WSS_CONTENT_Log' TO 'D:\SQLLogs\WSS_CONTENT_Log.ldf', RECOVERY, REPLACE, STATS = 10;
Step 3: Unmap the drive

Lastly, we issue the commands to delete the mapped drive (i.e “U:”).

Use Master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC XP_CMDSHELL 'net use u: /delete'
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO