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.bak' to 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
No comments:
Post a Comment