January 30, 2020 at 8:53 am
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.
January 30, 2020 at 9:12 am
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
January 30, 2020 at 9:22 am
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.
January 30, 2020 at 9:42 am
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 ?
January 30, 2020 at 9:56 am
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
January 30, 2020 at 10:08 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply