Citrix: XenApp and MS SQL Express

Citrix: XenApp and MS SQL Express

Abstract:

Citrix XenApp Platinum with SmartAuditor comes shipped with an embedded Microsoft SQL Database. Microsoft removed the backup agent from the MS SQL Express 2005, even though it was bundled in former Express releases. There are alternatives to the stock backup capability that Microsoft stripped from their MS SQL Express.

Documentation:

Microsoft provides some best practies backup and restoral of their database. Microsoft published such notes on the Microsoft Deveoper Network, or MSDN for short.

[MSDN Managing Database Files (SQL Server Express)] - Microsoft published a note about managing database files under Microsoft SQL Server Express 2008R2 release. Similar notes for 2005 and 2008 editions were also included.

[MSDN Deploying SQL Server Express] - This technican note deployment of MS SQL Server Express from 2008 and on, including a tool called "xcopy". Versions 2008, 2008R2, and the next release (i.e. Denali) are all noted.

[MSDN How To: Attach a Database File to SQL Server Express ] - This note discusses attaching a database file, from a backup, to a Microsoft SQL Server Express installation. Versions include: 2005, 2008, 2008R2. and Denali.

[MSDN SQL Server 2008R2 Backup Overview] - This note includes a link to Recovery Model Overview. An additional note points to Creating Full and Differential Backups of an SQL Database. An additional note on Working with Transaction Logs is also referenced.

[MS Developer Network Backups] - In May of 2008, Jonathan Kehayias posted an article on Microsoft Developers Network with a stored procedure and basic usage of the procedure in order to perform backups and restorals. The article is not clear on performing restorals.

[MSDN Backup Transact-SQL] - Microsoft published a technical note on how to conduct various backups of Microsoft SQL Server. The article is not clear on performing restorals. After doing some searches, there was a similar Restore Transact-SQL note.

[MSDN How To Restore a Database Backup] - This note discussed how to conduct a restore using Microsoft SQL Server Management Studio.

[MSDN Backing Up and Restoring Databases] - This note is explicitly for SQL Server.

[MSDN Copying Databases to Other Servers] - This note is explictly for SQL Server.

Alternatives:

Over the internet, many common people lamented the problem with Microsoft yanking the agent involved in scheduling database backups from SQL Server Express starting from 2005 version. Various people took different approaches.

[DB-Save SQL Server Backup] - A robust German solution which will not only backup and restore, but allow for restoral onto other systems. DBSave says it is optimised for "SQL Server newbies". It is available in freeware and commercial editions. Encryption and compression are both available. One databases supported via freeware. Backup to FTP Server supported. Scheduled jobs are not available in freeware edition. See Feature Matrix.

[SQL Backup and FTP] - A simple solution which will backup and restore up SQL databases. It is available in freeware and commercal editions. Compression is available. Two databases are supported via freeware. Backup to FTP Server is supported in freeware edition. See Features Matrix.

[SQL Agent: A .NET Framework] - Danilo Corallo published a piece of code on May 2008 that provide the yanked Job Scheduling feature and runs as a Microsoft Service.

[SQL Server Nation SQL Server Express Automation ] - Tim Chapman published a suggestion on how to backup SQL Server Express databases using a script and the Microsoft Windows 2008R2 Scheduler.

[BrianMadden Automate Backup of SQL Server 2005 Express] - Katie Koepke in May 2007 published an article on backing up the embedded database in Citrix.

Conclusion:

Microsoft has really put the screws to new users of their embedded database technology by removing the most basic of features and not clearly documenting a simple path for backup and restore.