Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012

    Unanswered: restore tables from master backup

    Hi there!

    Version: 10.00.1600 (I believe the first 2008 release)

    I'm new, with the forum and not too experienced with mssql eighter.
    I made a backup from the master, model and msdb system tables. Now I travelled to a different country, and I only have the backups and would like to access them. I was able to restore the master db to a new installation of mssql but, it wont start. I do know why: as I read after I quickly realised that master backups take the accounts, serverID, groups, directory structure, and lots of other things.
    Basically I have no chance of doing a master restore.
    The question here: Is there any way of getting my data out from the master backup file (thats where my stuff is)? I'm thinking of "restoring" it to a separate non-master db, table, anything...
    I know now that I did the most stupidest thing ever, but believe me I don't want the last half years work to just disappear.
    Any advices highly apprichiated!


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    OK. First thing to do is to fix the existing installation. Pop in the install disk, and there should be an option for rebuilding the system databases. This will put your instance back to its default state, so if anything else is on there (other databases, logins, etc.), you will have to re-attach the databases, and add back the logins, and various other tasks.

    I just did a quick test and yes, you can restore a backup of the master database to another name, BUT (you just knew that word was coming, didn't you?) any of the global system tables (sys.databases, sys.logins, etc.) will be unavailable, due to the way SQL Server works. when you query an object in the sys schema, the system will check the master database first, then the local sys schema. So, if you need something out of some of those tables, you may be in for a bit more work. If all you need is the local sys.sql_source table, you will likely get that back.

  3. #3
    Join Date
    Nov 2012


    Thank You for You're quick reply MCrowley!

    I were right: I needed the same version of SQL and then I had to restore the master db as a user db. I kept on restoring it as the master db.

    The restore command I used in sqlcmd:

    RESTORE DATABASE mastercopy FROM DISK = N'C:\DBback\master'
    WITH MOVE N'master' TO N'C:\DBback\mastercopy.mdf',
    MOVE N'mastlog' TO N'C:\DBback\mastercopylog.ldf';

    That did the job. Now I have a mastercopy DB and I can finally move my tables, records to the new development db.

    Thank you for your help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts