Friday, 6 January 2012

Database uplevel Restores

Today I've been reading Paul Randal's collection of Blogs from a series called A SQL Server DBA myth a day. If you haven't yet read it I recommend it there are a couple of misconceptions in there that I have fallen foul of in the past.  You can also find a link to the SQL Skills site in the SQL Resources section of my Blog.

Perhaps the most obvious misconception (obvious looking back and thinking about it) is listed by Paul as the below;

24x) you can restore a backup to any uplevel version of SQL Server

No. You can only restore a database from two versions back (i.e. you cannot directly restore
a SQL Server 7.0 database to SQL Server 2008).

This isn't something that I knew, I mean sure I've restored / upgraded plenty of databases in my time but never really given the number of uplevel versions a second thought. I imagine this is more a matter of luck rather than judgement on my part.

I don't have any SQL Server 7 databases to support any more but still have the odd SQL Server 2000 database. Going off this I shouldn't be able to restore a SQL Server 2000 database to a SQL Server 2008 R2 server right? I will give this a try and let you all know.

Chris