Post reply

Copying Databases

  • June 23, 2005 at 4:13 am

    #87921

    Hi,

    We have developer who copies databases between Servers by detaching, deleting the ldf file, copying the mdf file to the new location and reattaching.

    The database is a small one, uses Full Recovery Model and has very few users, and using this method he claims never to have lost data. IS this method OK or is there potential for data loss?

    I would expect that using Full Recovery Model there would be a whole raft of changes still in the ldf file waiting to be written, by a checkpoint say, to the mdf file.

    Thoughts?

    Chris

  • June 23, 2005 at 4:37 am

    #567828

    I think that's a fastest way to copy database to another server, and have the minimum risk.

  • June 23, 2005 at 4:39 am

    #567829

    Isn't there a risk of losing trasactions if you delete the transaction log?

  • June 23, 2005 at 5:53 am

    #567845

    #1.  Why is he doing this?  Moving from DEV to TEST?

    #2.  Why does a DEVELOPER have this authorization?



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • June 23, 2005 at 5:56 am

    #567846

    #1: As you say he's moving from Dev to Test

    #2: he doesn't but he writes instructions for our Delivery Team to do this. They are the team with access, unfortunately they are not experts in SQL Server and just follow the instructions.

  • June 23, 2005 at 6:23 am

    #567855

    It is fast. It's not necessarily safe though. Once you've detached the files, in theory, just about anything could happen to them. What's wrong with backup & restore. I've never lost any data during those processes either and they don't require me to take a database offline, detach it from SQL Server, and re-attach it to SQL Server. Still, there's nothing inherently unsafe with the process, it just seems like a lot of unecessary work.

    "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

  • June 23, 2005 at 7:21 am

    #567890

    First.  I must agree with the question from AJ Ahrens, "Why does a DEVELOPER have this authorization?"  but feel that it should go a step futher and ask "Why does anyone but a trained DBA have the access to perform a task of this type?"  You are playing Russian Roulette with an UZI.  Had to get that out of my system.

    There is nothing wrong the detach/attach method.  In fact I do it all the time in very large production databases.  I have never had a problem.  I just keep close watch and am very careful in what I am doing.  You can mess anything up.  That is why people should be trained for the job they are doing.  It is not necessary to drop the ldf file.  In fact not doing so allows better control on the locations of the files than does the sp_attach_single_file_db procedure.

    Anyway, You should be OK doing what you have been doing.

     

  • June 23, 2005 at 7:39 am

    #567904

    I agree with an earlier post that backup/restore is the easiest way to peform this database copy.  The great part about backup/restore is you don't have to take the source database down. 

    Detaching is fine if your not worried about taking the database down and killing all open connections into the database.

    I also agree with the post that having non trained SQL people performing actions as critical as detatching and attaching databases is very dangerous.  Backup and restore us a much safer operation.

  • June 23, 2005 at 9:43 am

    #567975

    If I had my way then they wouldn't have access at all.

    The problem has occurred because we work in a large department with dozens of servers - they don't want us developers (although I'm trying to mold myself into a DBA role as I've been on the courses) having access to live servers. Understandable.

    However, having this operation in the hands of the untrained is a recipe for disaster. I now have to wrtie step by step instructions for them to follow - no matter how detailed they are they still make mistakes.

     

    Don't get me started!

  • June 23, 2005 at 5:55 pm

    #568161

    The way we move objects is:

    #1  Create SQL scripts for ALL objects that are being promoted through the system



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • June 24, 2005 at 3:59 am

    #568214

    Why do a detach if you can do a backup? If you can afford the downtime, then it's OK.

    I think (haven't tested it though) that you can make a VSS copy of the datadisk in the server and get a offline copy of the MDF and LDF files. As long as you create the snapshot on both MDF and LDF it should work.

    But in general, I prefer the Backup command!

    JP

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

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