SQL Magic - Making off-server backups

So if you've ever done some budget SQL server administration, you'll see the benefit of this post. When I say "budget", I mean if you've been tasked with backing up a SQL server but don't have any fancy backup software to do the work for you. The below outlines two scripts that will do the following. It makes a full backup of a SQL database and moves it to an off-box Windows share (could be any kind of SMB share). A caveat of this script is that if you want it to run on a schedule without your intervention, then it needs to run as a user that has permissions on the destination windows share. You have to be able to connect to the destination path (e.g. \\myserver\sharename) without being prompted to login and be able to write files there. Make sure to modify the scripts to match your file names, paths, database names and server names.


Here's the scripts, first comes the Batch file (.cmd):

@echo off
REM ------------- ADJUST THE FOLLOWING TO CHANGE THE DESTINATION FOLDER NAME ---------------
set DestFolder=myDatabase Backup Folder

REM Make the folder even if it already exists
mkdir e:\MSSQL\Backup

REM Run the SQL script to do the backups.  This runs against the default SQL instance on the current machine - check the docs if you need to specify an instance.
sqlcmd -S %COMPUTERNAME% -i E:\BackupDB.sql

REM Make the destination folder on the destination server
mkdir "\\destServerXYZ\Backups\%DestFolder%\"

REM Move the backups to the destination server
move e:\MSSQL\Backup\*.bak "\\destServerXYZ.fully.qualified.domain.com\Backups\%DestFolder%\"

---

Then here's the .sql file:

DECLARE @curDate AS varchar(8), @dbPath1 as varchar(53), @filepath as varchar(128)
set @filepath = 'E:\MSSQL\Backups\';
set @curDate = convert(char(4),YEAR(getdate())) + RIGHT('00' + convert(varchar,MONTH(getdate())),2) + RIGHT('00' + convert(varchar,DAY(getdate())),2);
set @dbPath1 = @filepath + 'myDatabase_' + @curDate + '.bak';
BACKUP DATABASE [myDatabase] TO  DISK = @dbPath1 WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'myDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO