Remote database backups and restores using SQLCMD

One of the clients I work for has a lot of servers in countries like Somalia, Kenya, Afghanistan with what we call 'field databases' on them. The users often request for a . Though these servers are on the same vpn, it is often very painful to RDP to the remote server, take a backup, compress and upload to FTP (or copy to a network share). On a busy day, we usually have about 10 such database copy requests involving ten different servers. Automation is the way to go right? So we came up with the below script which uses linked server and OPENQUERY to make database copying process a bit easier.

begin
declare @dbname nvarchar(30)
set @dbname = 'DATABASENAME'
declare @servername nvarchar(50)
set @servername = 'FIELDSERVER'
declare @SrcBackupLocaction nvarchar(100)
set @SrcBackupLocaction = '\\' + @servername+ '\backups\'
declare @DestBackupLocaction nvarchar(100)
set @DestBackupLocaction = '\\HOMESERVER\BackupsFromField\'
print @DestBackupLocaction 
declare @backupFileName nvarchar(60)
declare @backupStmt nvarchar(500)
declare @compactStmt nvarchar(500)
declare @UncompactStmt nvarchar(500)
declare @copyStmt nvarchar(500)
--declare @destFile nvarchar(100)
declare @backupNameFullPath nvarchar(500)
declare @mdf nvarchar(500)
declare @ldf nvarchar(500)
declare @dblogName nvarchar(60)

--Declare @RestoreDBName nvarchar(30)
select @backupFileName = @dbname + '_'+ (Select replace(CONVERT(varchar(10),t,10),'-','') as t from openquery(FIELDSERVER, 'select getdate() as t'))+'_'+ @servername
set @backupStmt = 'BACKUP DATABASE ' + @dbname+ ' to disk=N''' + @SrcBackupLocaction + @backupFileName + '.bak'''
print @backupStmt 
print 'Backing up database to ' + @backupFileName

print @SrcBackupLocaction
print @DestBackupLocaction

set @backupNameFullPath = N'E:\Backups\BackupsFromField\'+ @backupFileName +'.bak'
set @mdf = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\' + @backupFileName + '.mdf'
set @ldf = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\' + @backupFileName + '_1.LDF'
set @dblogName = @dbname + '_log'

/*
print @backupNameFullPath 
print @mdf
print @ldf
print @dblogName
end
*/

--Backup database
 exec FIELDSERVER.master.dbo.sp_executesql @backupStmt
print 'database BackUp completed '
print '--------------------------'

--Compact the backed up database
--set @compactStmt = N'Compact /C '+ @SrcBackupLocaction + @backupfilename + '.bak'
set @compactStmt = N'"E:\Program Files\7-Zip\7z.exe" a -tzip '+ @SrcBackupLocaction + @backupfilename + '.zip ' + @SrcBackupLocaction + @backupfilename + '.bak -mx5'
print @compactStmt 
 Exec FIELDSERVER.master.dbo.xp_cmdshell @compactStmt

print 'database compressed successfully'
print '--------------------------'

--Copy compacted backup file
set @copyStmt = N'Copy '+ @SrcBackupLocaction + @backupfilename + '.zip ' + @DestBackupLocaction + ' /V /Y /Z'
print @copyStmt 
 Exec FIELDSERVER.master.dbo.xp_cmdshell @copyStmt

print 'Copied Compressed file copied successfully'
print '--------------------------'


--UnCompact the copied zip file
--set @compactStmt = N'Compact /C '+ @SrcBackupLocaction + @backupfilename + '.bak'
set @UncompactStmt = N'"E:\Program Files\7-Zip\7z.exe" e -y -o' + @DestBackupLocaction + ' '+ @DestBackupLocaction + @backupfilename + '.zip'
print @UncompactStmt 
 Exec dbo.xp_cmdshell @UncompactStmt

print 'Unzip the file successfully'
print '--------------------------'


RESTORE DATABASE @backupfilename FROM  DISK = @backupNameFullPath
WITH  FILE = 1,  
MOVE 'DataFile' TO @mdf,  
MOVE 'LogFile' TO @ldf,  NOUNLOAD,  STATS = 10


print 'Database ' + @dbname + ' created successfully'
print '-------End of Procedure-------------------'

End

Nice isn't it? But there is a catch, you need to add the field servers as linked servers on HomeServer, RDP to the field server, open management studio, connect to the HomeServer and then execute this script to achieve the desired result. If for some reason, the network connection times out while the compressed backup is being copied, we would have to go back to the manual mode to complete the rest of the steps(some of these servers are on 256 kbps internet connections and are often fickle). What if a user needs a database urgently? What if you can't remote into the field server at all? Then I found SQLCMD. Here is what I do these days - Log on to the HomeServer (which is a decent server with better resources and which is located in US) Open command prompt and connect to the remote server using SQLCMD

You can get the help about SQLCMD commands by using sqlcmd /? in command prompt. Since we use Windows authentication and I am a domain admin, I just specify the server name to connect to and I am in. Then issue the backup command. All of our field servers are alike in drive and folder structure, so all I need is the name of the database to copy. If there are long and funky names you can choose from:

SELECT name FROM sysdatabases
GO

Then, issue the backups command:

BACKUP DATABASE [My_Field_Database] TO  DISK = N'E:\MyFieldServer\BackupPath\MyFiledDB_082712.bak' 
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

We use standard edition on field servers so you won't see COMPRESSION in the backup query :)

 I know that 7zip is installed on all the field servers and I know the path. So I will now use sqlcmd and xp_cmdshell to compress the backup so that it is easier for me to copy to HomeServer: I know xp_cmdshell is a security threat etc - so lets enable it, use it and then disable it. No harm done.

exec sp_configure 'show advanced options', 1
reconfigure
GO
exec sp_configure 'xp_cmdshell', 1
reconfigure
GO

--Lets now compress that backup using xp_cmdshell
exec xp_cmdshell '"E:\Program Files\7-Zip\7z.exe" a -tzip E:\YourFieldBackups\FileName.7z E:\YourFieldBackups\BackupName.bak -mx5'

-- Okay, we are done. Lets disable the cmdshell dude

exec sp_configure 'show advanced options', 1
reconfigure
GO
exec sp_configure 'xp_cmdshell', 0
reconfigure
GO

The rest is easy. You know where the compressed backup file is. You can now use xcopy to move the file to HomeServer

xcopy \\remoteserver\bacups\compressedfile.7z e:\backups\

I like this method a lot! I just need to put these steps together in a script and add some error handling etc. Thats a task for the near future!