Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: sharing violation while using copy database wizard

    Hi,

    I'm trying to move databases to another sql server but I always get sharing violation error message while the wizard tries to copy the .mdf file from the source-server. Any suggestions (besides rebooting to solve this issue?

    I have also tried to copy the file within the source server, but I still get the same message.

  2. #2
    Join Date
    Sep 2003
    Posts
    2

    Re: sharing violation while using copy database wizard

    Anyone?

  3. #3
    Join Date
    Jun 2003
    Posts
    31
    You can also use SP_DETACH & SP_ATTACH (see BOL) to copy and move your databases.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Stop your SQLAgent before doing copy wizard.

    SVT: copy db wizard is using sp_detach & sp_attach.

  5. #5
    Join Date
    Jun 2003
    Posts
    31
    With SP_DETACH & SP_ATTACH , you don't have to stop the SQL Agent.
    I wouldn't use or trust the Wizard, it's for novice DBA and you don't know or control of what happened behind the scene.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If SQLAgent is using the database in question, then your SP_DETACH will not work either. In this case you either have to investigate what process controlled by SQLAgent is interfering with your command, or stop SQLAgent all together and be done with it.

  7. #7
    Join Date
    Jun 2003
    Posts
    31
    Trust me, try it rdjabarov. you can detach one of the databases
    and the rest - along with SQL Agent - still working fine.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I have, but in epo-x's case, apparently, there is a process that has this database connected to, so was the reason for my recommendation. Also, if you look at the log that copy database wizard produces, then you'll see that it does SP_DETACH, OS-based copy, SP_ATTACH on the source, and SP_ATTACH on the target. In a sense, what you suggest is absolutely right, because this is the sequence of events that the wizard follows as well

    There is also a shorter way to see if the wizard (or your suggestion) will work or not:

    use master
    go
    sp_dboption 'database_name', 'off', true
    go

    If the last command does not result in error, then you can either fire the wizard, or perform the copy of the files manually, because the database is set OFF-LINE which is the same as detach except sysdatabases entry is still there with a different status.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •