Tuesday, September 8, 2015

SQL Backup then Robo Copy

1. Create sproc to backup db's with compression, copy only options.
See THIS LINK for awesomness script
Backup MyDatabase to a local folder 'E:\SQLBackUp\':
exec [master].[dbo].[sp_BackUpDatabases] 'E:\SQLBackUp\', 'MyDatabase', @backUpType = 0, @COPY_ONLY = 1, @COMPRESSION = 1

2. Create WindowsTask/AgentJob to execute a robo copy of the .bak files to a network share:
Move 'E:\SQLBackUp\MyDatabase.bakto network share  '\\Server01\sqlbackup\Prod\ '


-- copy with eta, retries, top level folder
ROBOCOPY E:\SQLBackUp\ \\Server01\sqlbackup\Prod\ *.bak /MOV /ETA /S /R:100 /W:10 /LEV:1

-- Other robo copy examples
-- copy with eta, retries
robocopy I:\Pictures\_Monkeys\ \\doylecloud\Public\BB\Pictures\_Monkeys\ /ETA /S /R:100 /W:10

-- copy with eta, retries
robocopy I:\Pictures\ \\doylecloud\Public\BB\Pictures\ /ETA /S /R:100 /W:10

-- copy with eta, retries
robocopy I:\Pictures\_Monkeys\ \\doylecloud\Public\BB\Pictures\_Monkeys\ /S /MT /Z

-- copy with eta, retries
robocopy I:\ \\doylecloud\Public\ /ETA /S /R:100 /W:10 /Z /XD "I:\BB_HD" "I:\Pictures" "I:\PassportInstalls"

3.  To make life most easy create a SQL Agent Job,
Step 1 is exec [master].[dbo].[sp_BackUpDatabases] 'E:\SQLBackUp\', 'MyDatabase', @backUpType = 0, @COPY_ONLY = 1, @COMPRESSION = 1
Step 2 is .bat file with ROBOCOPY E:\SQLBackUp\ \\Server01\sqlbackup\Prod\ *.bak /MOV /ETA /S /R:100 /W:10 /LEV:1
Step 3. Save that money u would spend on RedGate