Downgrading an SQL Server Database Using Scripts

One of my biggest annoyances about SQL Server is the inability to load a database from a later version into a previous one. Database files and backups don’t have any indicators of their version (unless you are OCD enough to enforce naming conventions) so whenever I get a backup file from a client (or from my archive) it’s a crapshoot which server it belongs to.

Worst of all, restoring it to a LATER version IS supported, so if I have an SQL Server 2005 backup and restore it to my local SQL Server 2008, it works fine. Then when I attempt to deploy it to the server it’s supposed to go, I get the dreaded error:

The database ‘DatabaseName’ cannot be opened because it is version XXX. This server supports version XXX and earlier. A downgrade path is not supported.

Which is even more infuriating, because God forbid it says something helpful like “version SQL Server 2005” or “version SQL Server 2008” instead of the cryptic and useless “661” and “655”. 2005 was seven years ago and we still don’t have a better error message for this?

So, to make sure I don’t change the version of the database, I have to first try SQL Server 2005, to ensure that it doesn’t attempt to update it if it’s the wrong version. Then I have to go up the chain through 2008 and 2008 R2 because of course I’m not going to remember which number goes with which version…

Of course, the worst part is when the database was created on a later version of SQL Server, but needs to be deployed to a prior version. It’s a rare situation these days, especially since these days I insist needing help with their database be very clear about their environment.

But alas, sometimes it happens, and of course when it does, it’s a pain in the ass to fix. In some cases you can just use the Copy Database or Import Export tools of SQL Server, but these often fail if you’re trying to sync between SQL Server and SQL Express.

The most sure-fire way I’ve found to do the downgrade is to script the entire database (along with all its data) and run that script on the desired instance.

Unfortunately (again!), this is also prone to fail, espcially if your scripts end up being extremely large, resulting in the error:

Exception of type ‘System.OutOfMemoryException’ was thrown.

Sigh.

So long story short (too late!), the solution is to use the SQLCMD utility which is a command line interface for executing SQL commands. This utitliy will execute scripts one line at a time, ensuring that they complete without throwing a memory error.

So once your scripts are generated, launch the sqlcmd utility and execute the following command:

sqlcmd.exe -S [SERVER] -U [USERNAME] -P [PASSWORD] -i “[c:\FULLPATHTOSCRIPTFILE]”

This of course expects that you have the username and password to the server and can connect to it from that machine. For other connection scenarios, refer to the SQLCMD Utility Reference.

Once the database is loaded, you can finally back that up and deploy it to the desired server.

Missing Sitefinity Blog Default Page

When you create a blog in Sitefinity, you must also assign it a Default Page, which is the page that contains the BlogPosts widget for displaying the full Details View of a post.

If you delete this page, move the BlogPosts widget to another page, or modify this relationship in any other way, you must make sure to update the settings for your blog so that it always points to the correct page. Otherwise, Sitefinity will have no way of knowing where your blog posts are located.

Sitefinity 4: Null Reference When Editing Templates

I experienced a "duh" moment today while trying to tackle an error I was receiving while playing with the Sitefinity 4 RC (due out later this week!). Whenever I created a template, I’d always get the following error when I tried to edit it:

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
   Telerik.Sitefinity.Modules.Pages.DraftProxyBase.CreateChildControls(Page page) +1804
   Telerik.Sitefinity.Modules.Pages.TemplateEditorRouteHandler.ApplyLayoutsAndControls(Page page, RequestContext requestContext) +196
   Telerik.Sitefinity.Web.RouteHandler.InitializeContent(Page handler, RequestContext requestContext) +475
   System.Web.UI.Control.LoadRecursive() +94
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2759

I kept thinking it was a bug in the RC, or perhaps because I needed to build my project or because it was a Web Applicaiton Project and not a Web Site, as I’m used to working with…

Turns out this was once again another example of my tendency to be a complete bonehead.

Since I was using a free css template (courtesy of STUDIO7DESIGNS) I simply copied and pasted the HTML into my master page. Unfortunately I neglected to replace the <form runat=”server” > tag that is required by ASP.NET master pages to function properly!

Talk about duh. Sorry about that. I’m posting this in case I ever make this mistake again (and God knows, I will), when I search for the answer online, I’ll no doubt find the answer on my own blog!

Sitefinity Toolkit 1.1 (with support for Sitefinity 3.7 SP3)

At long last I finally found time to publish the latest version of the Sitefinity Toolkit, as of now version 1.1 with support for Sitefinity 3.7 SP1 through SP3 (it will not work with any other versions than these) on both the Standard and Community versions.

See the main page for the Sitefinity Toolkit for the download links and changelog, as the latest information for the toolkit will always be posted there.

Although I’ve tried to be thorough on this release, I honestly haven’t had the time to test it as much as I’d like, so if you encounter any issues, PLEASE email me and let me know so I can fix and release an update if necessary.

I hope you find the toolkit useful, and if you have any suggestions or comments please send them my way. The toolkit is forever free, but if you enjoy my work and wish to help support it, consider leaving a donation to my music studio fund, details on are on the sidebar.

Thanks for your patience, I’m looking forward to continuously improving the project!

IIS 7 Adobe PDF Bug Fix

This is just a quick note to document a fix for a horrendous bug in Adobe Reader that causes downloads to fail if they are being served from a Windows Server 2008 machine. It has been plaguing me for weeks, before I even knew it was a bug! The last time I needed help with something I ended up finding the solution on my own blog, so hopefully if I encounter it again, I’ll end up here. Hello future self!

Anyway, the details of the bug are discussed in depth on the iis.net forum, but the basic summary is that a bug in Adobe Reader is causes the file download from IIS to halt, and the user is shown a blank screen. This doesn’t seem to happen if the user SAVES the file then opens it; only opening the file directly seems to cause the error (at least that was my experience).

Anyway, long story short, the fix that worked for me was provided by merk on page 5 of the discussion, which came in the form of an HttpHandler. Basically you map pdf files to run through this handler instead of the standard pipeline, and it does its magic voodoo to properly serve the file to the user.

Merk uploaded his solution to mediafire, you can download it here: http://www.mediafire.com/file/twrd2mnzm3y/PDFHandler2.zip.

I simply dropped it in the bin folder, mapped the handler to intercept *.pdf requests and now all is right with the world.

That’s all for today, with many thanks to Merk wherever you are!