Looking for a way to perform remote backups for your SQL Server database(s) without relying on expensive 3rd party software? It's easier than you think, T-SQL to the rescue. Part I of II in remote database backups (MySQL backups over Rsync covered in part II).

Let's get started. We're operating under the assumption that your client would like 30 day rotating complete backups, not differential (fairly generous, but in a pinch, it's always nice to revert back to X day in the previous month). Obviously, if client database(s) are relatively large and disk space is at a premium, adjust the backup rotation period and type (full vs. differential) accordingly. If not already available on the client server, go ahead and download Microsoft's free and highly useful Sql Server Management Studio Express

First of all, we need an SQL statement that will perform the backup -- here's a basic backup example:

DECLARE @BackupName nvarchar(250)
SET @BackupName= 'C:\path\to\client\backups\dailyBak' + convert( varchar(10), getdate(), 112 ) + '.BAK'
BACKUP DATABASE [Name_Of_Client_DB_Here] TO DISK = @BackupName
WITH NOFORMAT,
NOINIT,
NAME = N'Name_Of_Client_DB_Here-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

Open up studio express and login (windows authentication is fine for now) -- click the query browser tab and paste the above SQL snippet into the query window pane. Click the F5 key to execute the query. If all goes well, you'll have created a complete backup of your client's database. Go ahead and browse to the backup location to confirm the backups database was created. If the backup is there (should display as "dailyBak" + timestamp + ".BAK"), proceed to the next step, creating the .BAT files to remove backups older than 30 days, and to copy production server backups over to a backup/development server via network share (optional, obviously, but completes the solution, IMO, as client's data is secured in multiple locations).

The .BATs are simple, but powerful.

Open up Notepad and copy-paste the following:

forfiles -p C:\path\to\client\backups\ -s -m *.BAK -d -30 -C "cmd /c del /q @PATH/@FILE"
forfiles -p \\Backup-Server-Network-Name\share-folder-name-on-backup-server\ -s -m *.BAK -d -30 -C "cmd /c del /q @PATH/@FILE"

Close the file and rename via Explorer to "DeleteBackupsOlderThanOneMonth.bat", or file name of your choosing (.bat file extension required).

Next, open Notepad again and copy-paste the following:

forfiles -p C:\path\to\client\backups\ -s -m *.BAK -d -0 -C "cmd /c copy @FILE \\Backup-Server-Network-Name\share-folder-name-on-backup-server\@FILE"

Close the file and rename via Explorer to "CopyMostRecentBackupToNetworkShare.bat", or file name of your choosing (.bat file extension required).

Finally, take the SQL snippet above and copy-paste into a new Notepad file. and save it as "SQLServerBackupScript.sql" (file type All Files)

Now we're ready to automate the process, let's create a few scheduled tasks. Start >> Control Panel >> Scheduled Taks

First, we'll create the backup task itself. Create a new scheduled task and enter "C:\Program Files\Microsoft SQL Server\90\Tools\Binn" into the "Start In" field. Click the browse button and find your SQL backup script. Set desired time (ex. 2AM); Click OK and enter adminstrator password when prompted. Next, create schedule tasks for both the network backup and delete files > 30 days .BAT operations. Browse to applicable .BAT file and enter the path to the .BAT in the "Start In" field. Set a time later than the backup task above; Click OK and enter adminstrator password.

That's it, full rotating 30-day backups with optional network backup solution, all completely automated