Upload SQL Backup File via FTPS
Recently, a client was looking to backup the primary database for one of their acquisitions to their home office. Because of security concerns, they can’t combine the users on the office network with their corporate network until their team can fully integrate the systems, but a full, regular off-site backup is needed ASAP. Since the local staff doesn’t include any technical people, instead we chose to utilize FTPS to push the file offsite after the backup.
Requirements:
- cURL (I used the 32-bit version of 7.20)
- OpenSSL DLLs (libeay32.dll and ssleay32.dll)
- An SSL-enabled FTP server (we use IIS 7 with a commercial certificate)
Steps:
To setup this job, I installed the cURL executable into the c:\windows\system32 folder of the remote SQL machine. I then copied the two DLLs above into the c:\windows folder. It took a bit of trial and error to figure out the DLL path, but this one worked like a charm.
Once the install was completed, I went in and added a job to the system that backs up the database to local media, overwriting any existing backups and verifying the media when complete. Then, it will launch a batch file (code below) to FTP the file to our corporate office. Here’s the job:
USE [msdb]
GO
/****** Object: Job [CorpBackup] Script Date: 03/05/2010 12:13:51 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/05/2010 12:13:51 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback</p>
<p>END</p>
<p>DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'CorpBackup',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SUBSIDIARY\jjohnson',
@notify_email_operator_name=N'Corp IT', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Backup The Database] Script Date: 03/05/2010 12:13:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup The Database',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'BACKUP DATABASE [Subsidiary_Engine_SQL] TO DISK = N''K:\Corp\remote.bak'' WITH NOFORMAT, INIT, NAME = N''Subsidiary_Engine_SQL-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''Subsidiary_Engine_SQL'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''Subsidiary_Engine_SQL'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''Subsidiary_Engine_SQL'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''K:\Corp\remote.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [FTP To Corp] Script Date: 03/05/2010 12:13:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'FTP To Corp',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'c:\scripts\backup.bat',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly',
@enabled=1,
@freq_type=8,
@freq_interval=33,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20100305,
@active_end_date=99991231,
@active_start_time=234500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
This job, if you look closer, will email our operator account and alert us to the success or failure of the job. We only need the backup about once a week, so we schedule the job for Friday night. When the job is complete, we can move the backup file off to a better location manually, just as a way to confirm we have it.
The batch script, c:\scripts\backup.bat, contains the command to upload the file to our FTPS server. One of the reasons I put curl.exe in the c:\windows\system32 folder is because it’s a cheap and easy way to circumvent the need for a full path. As an old colleague used to say, “It’s not that I don’t mind work, I’m just efficiently lazy.” Here’s the code for the script (munged, obviously, since it has our account password and FTPS site):
curl -T K:\Corp\remote.bak --ftp-ssl -k -u remotebackup:SomeP@ssword! ftp://ftps.corpdomain.com/
Comclustion:
And that’s it, the file is uploaded automatically per the schedule we set in the SQL job. I get an email if it works or if it fails, and we have managed to get a backup regularly that doesn’t rely on non-technical users handling backup media or controlling off-site rotations. Use the script, the job, and hopefully it will help!
