Post reply

restore backup versus detach / attach move

  • April 12, 2005 at 11:10 am

    #185718

    Hello all,

    I had a quick question: we need to move a db from test to production. Is there any real difference (in this scenario) between the two methods below:

    1. Back up the db on the test server, copy the backup to the prod server and restore it.

    2. Detach the db from the test servers, copy the db and tlog to the new server and attach them.

    I know option 2 means that the db will no longer exist on the test server (which is fine) but other than that, is there any difference? Lemme know if you need any more info on the scenario, etc to be able to answer.

    Thanks guys and gals

  • April 12, 2005 at 12:05 pm

    #552200

    You can then reattach your database on the test server.

    Differences:

    1. Size - The backup file may be smaller than the mdf+ldf files which will help the transfer move faster.

    2. The time it takes to detach+move+attach could be longer than the time it takes to backup+move+restore.

    3. The database is unavailable during the detach+move+attach process but is available during the backup+move+restore process on the test server.



    Michelle

  • April 12, 2005 at 12:21 pm

    #552207

    Thanks Michelle =)

  • April 12, 2005 at 12:35 pm

    #552211

    Well,

    It has been my experience that restore is slower than attach.

    I do agree though that the backup/restore (as a whole)  may be faster depending on file size  (specially whe using something like litespeed)

    And yes you get availability when the backup is happening but not when the restore process is running

     


    * Noel

  • April 13, 2005 at 2:05 am

    #552316

    Another thing to consider is that when Attaching a database you don't already need an entry in SQL Server for that database.

    In my experience I can detach a database from one server, move it to another that has never known of that database and then attach and it is there. With a backup/restore option there must be an existing database to restore into.

    Steve.

  • April 13, 2005 at 2:09 am

    #552317

    Hi,

    I think is better attach/detach instead backup/restore because is very simple and quickly.

    If the db size is very large I would choose the first method too.

    Regards

  • April 13, 2005 at 3:27 am

    #552332

    When you restore from backup you require excessive amounts of disk space, I recently copied a 23 gb database and backup and restore was unable to restore the database on a server that had the 19gb bak file and another 30 gb of available space.  The solution was to detach the database make a local copy of the mdf and ldf files, reattach the database then move the copied files to the new server.  The other advantage to detach and attach is that you get to specify the location of mdf and ldf files.

    My prefered method is now detach/attach.

  • April 13, 2005 at 3:41 am

    #552334

    Just adding my comments

    There are a few things that you need to keep in mind when detaching and attaching databases.

    If you're using full-text indexing then detaching the database will break it, meaning you have to run a full population again after you've re-attached (you may have to play around with it a little just to get it working in the first place before doing the re-population).

    You may also encounter orphaned logins in the database after attaching, these can be identified using the following SP.

    EXEC sp_change_users_login 'Report'

    You can read more about it in BOL, or if you prefer feel free to ask here.

  • April 13, 2005 at 6:52 am

    #552395

    You may encounter orphaned logins whether you are attaching or restoring.

  • April 14, 2005 at 11:51 am

    #552789

    Attach will be a time saver vs backup/restore when comes to the big DB's (hundreds of GB)

  • April 14, 2005 at 12:50 pm

    #552796

    means that the db will no longer exist on the test server (which is fine)

    Just a point, if you copy instead of move, you can just re-attach the DB to the test server after the copy is done.

    Second - you may wnat to get sp_sidmap from the MS site.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;298897

    http://support.microsoft.com/default.aspx?scid=kb;en-us;240872

    That should help resolve the login problems.

    Good luck.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • April 14, 2005 at 2:18 pm

    #552816

    I have to differ with that statement. When you have such large DB a product like litespeed will compress your backups on the fly therefore the copy and handling of those are in clear advantage. I am in no way related to that company but It has been my experience that whe the size of the DB grows above a couple hundred gigs this product becomes esencial

     


    * Noel

Viewing 12 posts - 1 through 11 (of 11 total)

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