Post reply

What is the Safest and Fastest Way to Move Data Between Two MSSQL Installations?

  • November 6, 2012 at 10:11 am

    #266590

    I work for a small software vendor that does occasional server moves and MSSQL Upgrades for our clients. We have occasionally run into issues when moving our databases between two servers and we are looking to find out what more experienced users suggest.

    We have dealt with orphaned users with both Unattach - Reattach and Backup - Restore, but have also tried Import - Export using the wizard and have had issues with security settings being wrong on the new databases we try to import the data into.

    We only know enough about MSSQL to be dangerous so we are trying to educate ourselves as best as we can. If you need any further information please let me know.

    I greatly appreciate any input you have in regards to this topic.

    Thanks,

    Aaron

  • November 6, 2012 at 10:21 am

    #1556539

    Backup restore is usually the preferred method.

    For larger DB's I use logshipping which takes a small amount of preparation but can be setup well in advance of any DB moves

    Orphaned users are a thing of the past if you use sp_help_revlogin which you can find here.

    http://support.microsoft.com/kb/918992

  • November 6, 2012 at 11:18 am

    #1556545

    when we moved servers we had a scheduled down time and did a backup and restore on to the new server. main benefit we found is that if for some reason something goes boom in the backup or restore you can just bring the old server back with out much trouble. other shops may do things different but we found that to be the easiest.

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • November 6, 2012 at 12:05 pm

    #1556559

    Backup and restore mostly but some detaching. revlogin works pretty good for orphans but I've had times when some SQL logins passwords didn't come over properly.

    ---------------------------------------------------------------
    Mike Hahn - MCSomething someday:-)
    Right way to ask for help!!
    http://www.sqlservercentral.com/articles/Best+Practices/61537/
    I post so I can see my avatar :hehe:
    I want a personal webpage 😎
    I want to win the lotto 😀
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

  • November 9, 2012 at 9:27 am

    #1557679

    Thanks for the input everyone. Please keep the votes coming. It seems that import/export is avoided by most of you. is there a reason for this?

    We also have preferred backup-restore in the past, but I am not sure how they dealt with the orphaned users issue since I was not involved in those server moves.

    We have a lot more research to do before we know SQL well enough, but this give us a good direction to go in for future server moves.

  • November 9, 2012 at 9:36 am

    #1557686

    aaronjameswalters (11/9/2012)

    Thanks for the input everyone. Please keep the votes coming. It seems that import/export is avoided by most of you. is there a reason for this?

    Import/Export are problematic when you have a number of foreign key constraints to negotiate. And I don't know a single DBA who would use the Copy Database Wizard.

    Backup and restore is the preferred method, particularly for a large database because you can perform the restore early and leave the database in a non-recovered state. When it comes time to do the deployment, you can either replay all logs since the full backup, or grab a differential and logs to bring the database to current.

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

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