locked
Backup and Restore or Detach and Attach RRS feed

  • Question

  • Hi Everyone,

    I'm running a test environment which takes SQL 2008 Express and upgrading it to SQL 2012 standard. I was able to do this without any data base successfully so the next thing is try it with a database. So what I want to do is take the live SQL 2008 Express data and put into my "test" SQl Express 2008 (a different computer) and then try the upgrade to SQl 2012. So here's my questions:

    1.) If I detach the data from my live SQL Express 2008 instance can I simply Attach the instance right afterwards once I copy the files to another location? I don't want to disturb the working database and want to put it in production right after I get a copy of the files. (I know detaching stops everyone from using the database and that's okay. I just want to make sure I can attach it right away without any issues)

    I then will take the copied files to my test machine and attach in that environment for further work

    2.) If I ran a backup of the live database/instance would I be able to restore it on another computer (my trial computer) if I made the installation the same as the live one

    Thanks

    Steve

     


    Steve

    Thursday, February 14, 2013 1:42 AM

Answers

  • Steve,

    I would ALWAYS do the backup restore method unless it was a VLDB (though I'd make sure I had known good backups, known good!). Why? With backup/restore if the restore fails or something happens during the database upgrade (databases are automatically upgraded when attached to a higher versioned instance) with the detach/attach method then you won't have a good copy to try and attach again - hopefully you had backups. Which brings me to why deal with that and have to go to backups anyway - just use them from the start and not have to worry about issues. Something happens with a restore? Use the backup file again - no need to do anything else.

    Obviously I trivialized the scenario but it holds true IMHO.

    -Sean


    Sean Gallardy | Blog | Twitter

    Thursday, February 14, 2013 2:58 AM
    Answerer

All replies

  • Steve,

    I would ALWAYS do the backup restore method unless it was a VLDB (though I'd make sure I had known good backups, known good!). Why? With backup/restore if the restore fails or something happens during the database upgrade (databases are automatically upgraded when attached to a higher versioned instance) with the detach/attach method then you won't have a good copy to try and attach again - hopefully you had backups. Which brings me to why deal with that and have to go to backups anyway - just use them from the start and not have to worry about issues. Something happens with a restore? Use the backup file again - no need to do anything else.

    Obviously I trivialized the scenario but it holds true IMHO.

    -Sean


    Sean Gallardy | Blog | Twitter

    Thursday, February 14, 2013 2:58 AM
    Answerer
  • i would prefer to take backup from the live database and restore to the new server.

    "SQL 2008 Express and upgrading it to SQL 2012 standard" so you shouldn't have nay issues.

    Thursday, February 14, 2013 5:01 AM
  • Hi

    I will recommend you to be on safer side when you do sql server upgrade So make sure you have all options available. follw these steps

    1. Take backup of database and to verify its in good condition (restore same on same server with different name) ---> verfies your backup is in good condition

    2. Detach your database  and copy (don't use cut) your data and log file in some different location . may be you need same to attach after upgrade if some thing goes wrong And then reattach original copy

    3. Migrate same with existing datbase so if upgrade is successfull your database will automaticall upgraded.

    Note : Dont forget to take script backup of logins and user permission on database. you might need after upgrade.

    Thanks

    Saurabh Sinha

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thursday, February 14, 2013 1:14 PM
  • Hello,

    Just as others post above, if you attach a database, you have no rollback unless you kept a copy of the database files.You can refer to online article to compare the two methods:
    Database Detach and Attach (SQL Server)
    Back Up and Restore of SQL Server Databases

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Friday, February 15, 2013 7:21 AM
  • Hi Steve,

    Like suggested by others, I'd prefer to do a backup/restore rather than a detach. If you do a detach, you have basically "lost" your source database. Eventhough you don't mess with the original files, there is always a risk that  the following attach fails - and in that case you have nothing. When you do a backup, you will still have your source database operational so eventhough the restore on the new instance fails you are still ok.

    When you do the backup, you can furthermore do it with the "COPY ONLY" oiption. By adding this, you won't break your log backup sequence so in case you have a backup scheme with differential and/or log backup, this sequence will still be intact. 


    Steen Schlüter Persson (DK)

    Friday, February 15, 2013 1:32 PM
  • Thanks Sean for your reply. You present a good idea but using your idea present another issue. I'm not exactly sure what to back up because the software company that I'm working with isn't consistent with their answers. So for now I'm ghosting the wholes drive and using a remote backup service that backups SQL databases. Until I hear from theI ma software company with assurance I want to be careful.

    I am going to take the Ghost images and put it on another computer and try the upgrade on taht one but I"m in the process of doing , so more later.


    Steve

    Friday, February 15, 2013 5:31 PM
  • Sounds good once I find out exactly what to back up. Can I restore the backup on another computer even if the other computer already has another instance?

    Also read my reply to Sean


    Steve

    Friday, February 15, 2013 5:33 PM
  • Thanks Saurabh,

    I will try your idea along with the others that have suggested things. PLease see my other replies.


    Steve

    Friday, February 15, 2013 5:34 PM
  • Thanks Steen, I was concerned about re-attaching as you mentioned. It looks like backup/restore is the way to go once I figure out what I said in Sean's reply. 

    Steve

    Friday, February 15, 2013 5:36 PM
  • Thanks Fanny. Most likly I will go with the backup/restore option.

    More later


    Steve

    Friday, February 15, 2013 5:37 PM
  • Hi Everyone,

    I'm still working on this issue. here's what I would like to do. Please give me your input.

    It looks like I'm running into many different issues trying to upgrade from SQl 2008 Express to SQL 2012. So, I would like to try this.

    Install a clean copy of SQL 2012 (not express), side by side with SQL 2008 Express

    BAckup my databases from the SQL 2008 Express installation

    Restore the databases to SQl 2012

    Do you think that will work? Also, I read about migrating my data. Is that an option when I'm on the same machine or is backup/restore as I mentioned a beter idea?

    Thanks

    Steve


    Steve

    Wednesday, February 27, 2013 2:16 PM
  • Steve,

    That is a normal scenario that you just described. Honestly you're making this too involved with all of the ideas thrown around - a simple backup and restore will take care of everything you need.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, February 27, 2013 2:22 PM
    Answerer
  • Hi Steve,

    To me this sounds like a good way to do it. I don't know if you have done it already, but you could try to run the upgrade advisor before you actually try to restore/upgrade the database - http://msdn.microsoft.com/en-us/library/ms144256.aspx

    This will tell you if there are any problems upgrading the database to SQL 2012. Then you can get this sorted out before you do the upgrade.

    If the upgrade advisor doesn't show any issues, I'd expect it to work with the process you describe.


    Steen Schlüter Persson (DK)

    Wednesday, February 27, 2013 2:28 PM
  • Hi Steen,

    Thanks for the input. I have tried what you propose but it got to a point where I couldn't choose any features and then it said it wasn't an approved upgrade path even though there is an article by Microsoft saying the path is upgradable. I kind of at my wits end so hopefully this will work.

    Thanks

    Steve


    Steve

    Wednesday, February 27, 2013 2:34 PM
  • Hi Sean,

    You're probably right, I'll know in a few hours.

    Thanks

    Steve


    Steve

    Wednesday, February 27, 2013 2:46 PM
  • Hi Everyone,

    I wasn't successful restoring the master database (haven't tried others yet) because the error message said that the database was a different version than this server.

    Does this mean I cannot backup a database from SQL 2008 Express and restore into 2012? From my previous responses it sounded like I could.

    Any help would be appreciated

    Thanks

    Steve


    Steve


    • Edited by adelxt Wednesday, February 27, 2013 4:04 PM
    Wednesday, February 27, 2013 4:03 PM
  • Steve,

    Master is a special system database that can't be restored to another instance of differing versions. The backup/restore method is for user databases only. To move logins, there is an SSIS task that can be setup to do this or you could use the revlogin scrip posted by Microsoft.

    It seems you're having difficulty in the process of how to do this so let me give you a very brief outline.

    1. Install SQL Server 2012

    2. Install any Service packs or CUs that are deemed necessary

    3. Setup any maintenance scripts (could be done after but I like doing it before restoring)

    4. Backup the user databases from the old 2008 instance

    5. Restore the user database to the 2012 instance

    6. Script/SSIS the logins or manually re-create them, repair any orphaned users

    7. Test connection string changes against the new instance

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, February 27, 2013 4:09 PM
    Answerer
  • thank you, so much better. I will try this and get back to you.

    Steve

    Wednesday, February 27, 2013 4:19 PM
  • HI Sean,

    It looks like my last post didn't make it up there, so I'll repost.

    I'll do this in a few posts or edits.

    I'm still experiencing problems on the restore. I receive an access denied error during the restore process. I will include pictures describing my setup.

    Here's the first one. Also, I have only tried one database. AR for now.

    Here's what the SQL Express 2008 setup. Notice all the databases under the system databases,


    Steve

    The next picture is what the SQL 2012 Looks like:

    • Edited by adelxt Wednesday, February 27, 2013 5:56 PM
    Wednesday, February 27, 2013 5:56 PM
  • I'm limited to only 2 images per message.

    This picture is the error message


    Steve

    This is a directory structure of the C drive.

    Some additional information:

    The instance created in SQL 2012 is a different name than created in 2008 E

    Notice the directory, MSSQL 10_50 Frameworks. This is where the AR etc.. databases are and I want them restored under the MSSQL !!.HHPharmacy directory

    Any reason why I'm getting the error message?

    • Edited by adelxt Wednesday, February 27, 2013 6:06 PM
    Wednesday, February 27, 2013 5:58 PM
  • Hi Steve,

    You'll have to ensure that the account running your SQL2012 instance has access to the backup file.

    When you restore a database, there are no SQL rights involved at this stage so you jus have to check the file permissions. 


    Steen Schlüter Persson (DK)

    Wednesday, February 27, 2013 6:02 PM
  • Hi Steen,

    I wanted to get back to everyone. I have been able to restore the file sbut what I had to do is click on the files optin and check off the "relocate files to folder: option. I also went to the options tab and checked off "overright the existing database" even though it was a first restore and the database wasn't there yet.

    So it looks like I have it, I hope.

    Thanks to everyone !!


    Steve

    Wednesday, February 27, 2013 6:39 PM