Joe Johnson


Upload SQL Backup File via FTPS

Joe Johnson - 05/03/2010

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.

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

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]
/****** Object: Job [CorpBackup] Script Date: 03/05/2010 12:13:51 ******/
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)
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>DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’CorpBackup’,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@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’,
@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
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
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’,
@os_run_priority=0, @subsystem=N’CmdExec’,
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’,
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
GOTO 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!

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!

Tags: , , ,
Tags: , , ,

Path to SQLCMD

Joe Johnson - 20/01/2010

At a major client we use SQL Server 2008 Enterprise Edition on Windows Server 2008 Enterprise Edition, all 64-bit. After 20 minutes of searching, I couldn’t find the actual path to sqlcmd.exe for a script I am writing. Tired of relying on Windows Search to find the executable path, I’m making sure I do my part to ensure it is widely known to those people who don’t seem to know (despite Microsoft and most of the MVPs seeming to think it’s a given to know the path without ever using it).

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE

Integrating MOSS 2007 and SQL 2008 Reporting Services

Joe Johnson - 08/07/2009

Recently, a customer was curious about implementing Microsoft Office SharePoint Server 2007 inside of their organization. While working with one of their external clients, they were exposed to the collaboration and versioning features of the software and were captivated by the simplicity of the software. As they are TechNet subscribers and Registered Partners with Microsoft, there were no licensing costs for their internal use, so we implemented the software on a VMware ESXi virtual server running Windows 2003 R2 and SQL Server 2008.

SQL 2008 introduces a new aspect to the Reporting Services component: SharePoint Integrated mode. Curious, I read more into this feature and found that it was added to ease the past pain of getting MOSS 2007 and SQL 2005 Reporting Services to integrate properly. Because, though, you need to install SQL 2008 before you install MOSS 2007, I was in a bit of a “chicken before the egg” situation: how can I connect SRS to MOSS when MOSS cannot be installed until SQL is installed? The answer is that I just need to change the configuration once the setup is complete!

SharePoint Integrated Warning Box
When you install SQL 2008 with SRS in SharePoint Integrated Mode and MOSS is not installed, you get the following error message.

Step 1: Install Reporting Services Add-in for SharePoint on the MOSS Server

Once SQL and MOSS are installed on their respective servers correctly, download and install the Microsoft SQL Server 2008 Reporting Services Add-in for SharePoint on the MOSS application server. This add-in basically provides a Report Viewer Web Part that provides report viewing capability, export to other rendering formats, page navigation, search, print, and zoom.

Step 2: Configure Report Server in SharePoint Central Admin Tool.

Once the add-in is installed and ready to go, we need to tell SharePoint to use the reports server in the Central Administration utility. On the MOSS server, open the Programs menu, choose Microsoft Office Server, and then SharePoint 3.0 Central Administration. This will open the Central Administration utility in a browser window. You will need to login using credentials that have access to the Central Administration utility.

In Central Administration, go to Site Actions in the top corner and choose Site Settings:
Site Settings

Under Site Settings, choose “Site collection features”:
Site collection features

Find the Report Server Integration Feature on the list and ensure it is Active (click Activate if it is not marked as Active):

Report Server Integration Feature

Step 3: Set Report Services Permission to Access Server Farm

Still in the Central Administration utility, click on the Application Management tab on the top of the screen. Locate the Reporting Services section, and click the Grant database access link. Enter the appropriate database server name (in my case, it is the same as the application server), choose your instance (in my case, the Default Instance) and click OK.

Grant Database Access

You will be prompted for a user account and a password; you must choose a user account with sysadmin access to the Reporting Services server (I used my domain admin account). This is a one-time login to gather info on the instance, and it will not continue to use your credentials to access SRS.

Step 4: Configure Reporting Services Integration

To activate Reporting Services in MOSS, you need to provide the URL to the SRS install to MOSS. From the Application Management tab in the Central Administration utility, click on “Manage integration settings” under Reporting Services. Here, enter the URL to the SRS installation (in my example, the SQL server is named www01 so my URL is http://www01/ReportServer). If your reports will use Windows Authentication, you must choose Windows Authentication here. If you will be using trusted connections in the published reports, you can use Trusted Account in the Authentication Mode. Press OK when you have entered the URL and chosen an authentication method.


And now you are set! You can publish your reports from Visual Studio and then view them in the MOSS Report Viewer Web-part that was installed and made available by the Reporting Services Add-in for SharePoint. Again, if you have issues or questions please don’t hesitate to contact me or leave a comment.