SQL Server: Backup/Restore to/from NAS Shares with Username/Password

Traditional Permanent NAS Share

We setup NAS shares all the time for SQL Server backups and the procedure is quite simple.

1. Find the SQL Server Engine Service account
2. Grant full-control privileges to the share for the account
3. Restart the SQL Server Engine Service for permissions to take effect (if needed)

#Get the service account name(s). Powershell command Requires dbatools
>Get-DbaService -ComputerName MySQLServerHostName | 
                 Where-Object {$_.ServiceType -eq 'Engine'} | 
                 select StartName -Unique

StartName  
---------  
MyDomain\MyServiceAccount1
MyDomain\MyServiceAccount2

How about a Temporary Share with Username/Password?

Sometimes, you have a share (like Azure Data Box via SMB as was the case for me) that you can access only with a UserName and Password. This is fine as long as you are accessing it interactively by typing it in, but how about accessing it from SQL Server for the purposes of backing up and restoring?

This is where “NET USE” command comes in handy becomes necessary

NET USE

I really like the simple and comprehensive post by Rudy Mens on how to use the NET USE command and the different options available

Basically, what you want is to persist the username and password to be accessible in the future without being prompted for. When this is done for the SQL Service account, it becomes like a traditional NAS Share that you can then backup to or restore from.

You could map the share to a drive or access it with the full path. I chose the latter.

#----------------------------
# What is in use by NET USE now?
#----------------------------
NET USE

C:\Users\>net use
New connections will be remembered.

Status       Local     Remote                    Network

-------------------------------------------------------------------------------
Unavailable  Z:        \\22.222.22.22\SomeShare
 Microsoft Windows Network
OK                     \\22.222.22.22\SomeShare
 Microsoft Windows Network
The command completed successfully.

In case you made mistakes and need to start over, just remove what is setup and start over

#----------------------------
# Delete existing and start-over
#----------------------------
Net use z: /delete
Net use \\22.222.22.22\SomeShare /delete

#Above both the mapping to Z drive as well as the share path are being deleted

To save the username and password with “Net Use”

Enable xp_cmdshell in SQL Server and run this from SSMS. Basically, the intent is that you are running “net use” as the SQL Server Service account.

--UNC type path
EXEC XP_CMDSHELL 'net use \\22.222.22.22\SomeShare SharePassword /User:22.222.22.22\UserName'

--Alternatively do this to backup to Z:\path instead of UNC path
EXEC XP_CMDSHELL 'net use Z: \\22.222.22.22\SomeShare SharePassword /User:22.222.22.22\UserName'

Check to see if it works (again from SSMS)

--Check the contents of the directory
xp_cmdshell 'dir \\22.222.22.22\SomeShare\'

If this does not work for some reason, delete and start over. Simple things can throw you off and cause issues. Check the basic things

• No extra backslash at the end of the file/share path – IMPORTANT!
• The domain\user should have a backslash and not a forward slash

Ready to do backups? Generate the TSQL commands to Backup!

Now that you are ready to backup to the share that you setup, try backing up a single small database like “msdb” or “master” to verify that it works as expected. If it does, generate the backup commands to backup all databases. Below, for my purposes, I am generating the TSQL commands to set specific set of databases to read-only and then backup to the share that I just setup

--CTRL+T on SSMS to send output to Text (for easy copying)
--In SSMS Tools -> Options -> Query Results -> SQL Server -> Results to Text 
--   Change "Maximum number of characters displayed in each column" to 8000

DECLARE @SetDBReadOnlyTemplate VARCHAR(MAX),
	@BackupTemplate VARCHAR(MAX);

SET @SetDBReadOnlyTemplate = 'USE [master] ' + CHAR(13)+CHAR(10) 
	+ 'GO' + CHAR(13)+CHAR(10) + 
	'ALTER DATABASE [<<DBNAME>>] SET  READ_ONLY WITH NO_WAIT ' + CHAR(13)+CHAR(10) 
	+ 'GO' + CHAR(13)+CHAR(10);
SET @BackupTemplate = 'BACKUP DATABASE [<<DBNAME>>] TO  DISK = N''\\22.222.22.22\MyShare\MyFolder\<<DBNAME>>.CopyOnly.' + FORMAT(getdate(), 'yyyyMMdd_HHmm') +'.bak'' WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = N''<<DBNAME>>-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10' + CHAR(13)+CHAR(10) 
	+ 'GO' + CHAR(13)+CHAR(10);

SELECT --@@SERVERNAME AS Server, 
	REPLACE(@SetDBReadOnlyTemplate, '<<DBNAME>>', name) AS SetDBReadOnlyCommand,
	REPLACE(@BackupTemplate, '<<DBNAME>>', name) AS BackupCommand
FROM sys.databases
WHERE 
	database_id > 4 --Exclude system databases	
	AND name IN (
		'Database1',
		'Database2',
		'Database3',
		'Database4'
		)
ORDER BY name;

Once the backup is complete, you can even verify that they are usable (again, we generate the commands to do so using the latest backup filename for the databases we are interested in). I know this is irrelevant to this post, but it does not hurt to have extra information 🙂 besides the fact that what else are you going to do with the share and SQL Server besides backup/restore/bulk import/export?!

 SELECT
	'RESTORE VERIFYONLY FROM DISK = ''' + physical_device_name + '''' AS VerifyCommand	
 FROM msdb.dbo.backupmediafamily
 INNER JOIN msdb.dbo.backupset
     ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
 WHERE 
	--Only care about "Database" backups
	CASE msdb..backupset.type
         WHEN 'D' THEN 'Database'
         WHEN 'L' THEN 'Log'
     END = 'Database'  
	--Just the backups in the last 1 day
	AND (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
	--....for backups done to a location like this
	AND physical_device_name LIKE '%\\22.220.22.22\%'
 ORDER BY 
	database_name,backup_finish_date desc

You could do whatever you need to do with SQL Server and the share at this point like restoring from it but the point is that the username and password are no longer referenced, and it should all work seamlessly!