none
copy database wizard failed

    Question

  • Hi

    I am trying to copy one of the production databases that has set up the database mirroring to the test server using copy database wizard. It is failing with the follwoing error message. I need to refresh the test database with prodcution copy weekly once and i would like to schedule the job. Could some one tell me how do i copy the database that has set up mirroring.

    Thanks!
    Neelima
    kp
    Friday, February 26, 2010 8:13 PM
    Answerer

Answers

  • You can try something of the following sort through SQL agent jobs.

     

    Have the following steps in your job,

     

    1. Backup the database from server 1

    2. Perform filecopy through network using dos commands or xp_cmdshell

    3. Set the existing database in server 2 as single user mode

    4. Restore the new backup file

    5. Use system procedure sp_change_users_login to fix the mismatch logins.


    Thanks, Leks
    Thursday, March 04, 2010 8:57 PM
    Answerer

All replies

  • Hi neelimab,

    Backup / restore will be the best way to achieve this.
    Thanks, Leks
    Monday, March 01, 2010 10:36 PM
    Answerer
  • Hi,

    Please refer to the following document to prepare a mirror database that includes backup and restore the database:
    http://technet.microsoft.com/en-us/library/ms189047.aspx

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, March 02, 2010 8:17 AM
    Moderator
  • Thanks for your reply. Can we schedule the job to run the restore on weekly basis? Right now it is only weekly and I am doing the backup and restore manually. Very soon we may need this alternate days. So I am looking for the way to schedule the restore like a scheduled job.

    Please let me know if there is any options like that. Thanks!
    kp
    Thursday, March 04, 2010 6:33 PM
    Answerer
  • You can try something of the following sort through SQL agent jobs.

     

    Have the following steps in your job,

     

    1. Backup the database from server 1

    2. Perform filecopy through network using dos commands or xp_cmdshell

    3. Set the existing database in server 2 as single user mode

    4. Restore the new backup file

    5. Use system procedure sp_change_users_login to fix the mismatch logins.


    Thanks, Leks
    Thursday, March 04, 2010 8:57 PM
    Answerer
  • Thanks. Can I put all the commands and run it as a batch job or something like that? Is there any option like that?
    kp
    Monday, March 08, 2010 8:45 PM
    Answerer
  • HI Lowell,

    I am able to access the above link.


    Thanks, Leks
    Saturday, August 14, 2010 1:19 AM
    Answerer