Post reply

Copy Database vs Backup/Restore Database

  • December 30, 2014 at 5:06 am

    #293266

    Hi All,

    Whenever required to take Backup of Database . I used to do Backup and Restore option. I never use Copy Db to migrating DB from one server to other Server.

    can anyone please help me to find the difference between Copy Database Wizard and Backup/Restore option. and which one is better to use for production Db.

    Thanks,

    Arooj Ahmad

  • December 30, 2014 at 5:08 am

    #1766968

    Backup/restore - backup the database to a file. Restore from file.

    Copy database wizard - run object creation scripts on destination and copy data one table at a time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 30, 2014 at 5:10 am

    #1766969

    Thanks Gail for immediate response 🙂

  • January 2, 2015 at 4:18 am

    #1767365

    For a production database, your best bet to protect the database is backups.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 2, 2015 at 10:06 am

    #1767440

    I've personally never had much luck with the copy wizard. Backup/restore is always going to be more reliable, faster, and more repeatable. It also keeps that extra I/O away from your source box cause it doesn't have to read all those tables in realtime.

    You can tune your backups as well so you can really speed it up as your DBs get bigger.

    I've got a video that shows you how to tune your backups if you're interested.

    http://midnightdba.itbookworm.com/EventVids/SQLSAT90BackupTuning/SQLSAT90BackupTuning.wmv

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

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

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