Restore SQL 2005 dbs to 2008

  • XP2600

    Valued Member

    Points: 65

    Hello all,

    I had a system running SQL server 2005, after a crash i needed to build the server from scratch and i got MS SQL 2008 installed, i restored all databases from a backup all attached well to SQL 2008 except, master db, i need to restore it to restore the users logins and db settings, when i tried to do so from sqlcmd i got this error:

    "The media family on device ... is incorrectly formed"

    any advise ? thanks.

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    You can't restore a backup of master to a different version of SQL Server.  I would advise you to build a temporary SQL Server 2005 server, restore master on that, and script out all the things you want to keep.

    John

  • anthony.green

    SSC Guru

    Points: 112474

    Not done it in a while, but it's possible to restore master as a user database.  So you could restore it as "master_restore" script out and reply what you need into the real master database.  As I say, not tried it in years so may/may not be a viable option and cant remember if I did that on a 2005 to 2005 restore or a different version restore.

    Further to that, is the choice of 2008.  Remember that 2005/2008/2012/2014 are all out of mainstream support, so if you don't have an Enterprise Agreement or enhanced support rights with Microsoft you really want to be looking at installing 2016/2017/2019.  If you do have an EA or Support then 2012 or above.

    2005/2008/2008R2 shouldn't be used any more as they are now fully unsupported versions.

  • XP2600

    Valued Member

    Points: 65

    Thank you John and Anthony,

    Unfortunately, i don't have a global license for SQL i have a separate retail license for 2008  , but how about SQL2005 express? can i use it ?

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    You don't need a licence if you're just going to fire it up, script some stuff out and tear it down without ever using it for production.  Finding the installation media, if you don't already have it, may be more of a challenge, though!

    I don't think restoring master as a user database will work, since logins won't be logins, server objects won't be server objects and so on if they're not in the actual master database.

    John

  • anthony.green

    SSC Guru

    Points: 112474

    John Mitchell-245523 wrote:

    I don't think restoring master as a user database will work, since logins won't be logins, server objects won't be server objects and so on if they're not in the actual master database.

    Should of made it a bit clearer on my "script it up" part, you would have to know the structure internally to master and generate the scripts by building up things, you couldn't use the GUI.  Things like running sp_help_revlogin inside the restored database etc would script out all the logins.  It's not for the faint at heart mind, but possible.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply