DR

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:

[sql]
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 &lt;&gt; 0 OR @ReturnCode &lt;&gt; 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 &lt;&gt; 0 OR @ReturnCode &lt;&gt; 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 &lt;&gt; 0 OR @ReturnCode &lt;&gt; 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 &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 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 &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:
[/sql]

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):

[code]
curl -T K:\Corp\remote.bak –ftp-ssl -k -u remotebackup:SomeP@ssword! ftp://ftps.corpdomain.com/
[/code]

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!

Last week I had a client with a crashed hard drive on their Hyper-V server. Coming from a physical server world, they had performed file-level backups of all the virtual machines and virtual machine files on the system. Expecting to just recover the files to the new RAID array and use the “Import virtual machine” feature of Hyper-V Manager, I was shocked to learn that unless you perform a specific “Export virtual machine” step, you cannot import a virtual machine file.

When you only have a simple VM that consists of a single VHD file and no snapshots, things are quite easy to restore: create a new virtual machine with the same physical configuration and attach the existing VHD. Open and shut, done in 5 minutes or less. However, if the machine has had snapshots, even if you have since removed the snapshots, chances are the individual snapshot files are still floating on the hard drive and are required for full recovery.

What I ended up doing was following the very helpful instructions located here for Option 2 and recovered both machines to the server in about 45 minutes. Very easy, very straightforward, but also very unsupported. Lacking a Hyper-V-aware backup, though, this was the best option for me.

Going forward, we have implemented Hyper-V-aware backups using the Microsoft Data Protection Manager 2007 with SP1 software and iSCSI mounted storage. Alternatively, BackupExec 12.5 has an option available that makes BE aware of the Hyper-V machines and can independently backup their configurations.

Good luck!

I checked and checked and couldn’t find a good answer to this question: how do I recover my MySQL databases for Plesk after a system crash if I don’t have MySQL-aware backups? BackupExec captures the .frm files and the InnoDB logs, but it can’t restore specific databases or tables, just the raw files. And if all you have is the raw files, you cannot restore just one DB. So, I figured what the hell, I’ll restore it all!

First, I stopped mysqld-nt on the server. Then, I restored all of my database folders and the InnoDB logs (for me, the contents of C:\Program Files\Parallels\Plesk\Databases\MySQL\Data). Finally, I started mysqld-nt and viola! All of my databases and users were restored to the system.

In theory, I could restore these files to another machine running MySQL for Windows of the same build as my live server, then run a MySQL backup (mysqldump, for example) and restore with more granularity, but I did not test this: YMMV. In this case, the whole shebang was gone, so I needed it all back.

Good luck!