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
Posted by at

4 comments:

  1. Chris Taylor6 January 2012 at 13:57

    I know you can restore a 2005 DB in compatibility 80 to a 2008R2 instance - not sure if that helps in the slightest :)

    ReplyDelete
  2. @DBA_ANDY6 January 2012 at 15:06

    2008 R2 is still the same version as 2008 (version 10) so you can go from 2000-->2008R2....but if this holds true into 2012 you won't be able to 2000-->2012 (version 8-->11)

    ReplyDelete
  3. Unknown6 January 2012 at 15:09

    @DBA_ANDY, but is it?! Strictly speaking 2008 R2 is a different version of SQL Server to SQL Server 2008 I'm sure? either way I will get round to checking at some point.

    ReplyDelete
  4. Unknown6 January 2012 at 15:14

    Chrissy T - might confirm @DBA_ANDY's thoughts. I will see if I can get round to having a look once I've got these NAT rules sorted out.

    ReplyDelete

Subscribe to: Post Comments (Atom)