Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Posts
    6

    Question Unanswered: Copy Database Wizard Error

    Hi,

    I am using SQL Server 2008 Copy Database Wizard in SQL Server Management Studio to copy DB1 from Server1 to Server2. I keep having the error in the "Job History" like this:

    The SSIS logging provider has failed to open the log. Error code: 0x80070005. Access is denied.

    I also checked the directory in the destination server that I set to store the error log and didn't find it. Could you help me? It looks like some user permission setup. But I don't know where and how to set up it.

    Thanks,
    Jing

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to set Write permissions for the account that runs SQLAgent service on the folder where you store the log file. You do it in DOS with cacl or in Windows Explorer with...your mouse
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2010
    Posts
    6

    Question Here is the error msg

    HI rdjabarov,

    Thanks for your reply.

    Here is some info and what I did.
    1. SQL Server 2008, Windows Server 2008 SP2 on both Server 1 and Server2;
    2. Database on both servers are connected through windows Authentication with account name "jcao".

    I checked the directory Properties using Exlporer->Properties->Security. Permissions for account "jcao" has Full Control.

    I ran Copy Database Wizard through SQL server management studio on Server1 and the job failed. Through Job History, I get the following error info:

    +++++++++++++++
    Step ID 1
    Server server2
    Job Name CDW_server1_server2_22
    Step Name CDW_server1_server2_22_Step
    Duration 00:00:04
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: Domain\sqldb. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:27:08 AM Progress: 2010-07-14 11:27:09.24 Source: server1_server2_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2010-07-14 11:27:10.23 Code: 0x00000000 Source: server1_server2_Transfer Objects Task Description: The process cannot access the file 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\remote.mdf' because it is being used by another process. StackTrace: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.Delete(String path) at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.VerifyDestinationFile(String filePath, Boolean overWrite) End Error Error: 2010-07-14 11:27:10.25 Code: 0x00000000 Source: server1_server2_Transfer Objects Task Description: Object reference not set to an instance of an object. StackTrace: at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.TransferDatabasesUsingSMOTrans fer() End Error Progress: 2010-07-14 11:27:10.52 Source: server1_server2_Transfer Objects Task Database transfer failed for 1 database(s).: 0% complete End Progress Progress: 2010-07-14 11:27:10.52 Source: server1_server2_Transfer Objects Task Transfer objects finished execution.: 100% complete End Progress Warning: 2010-07-14 11:27:10.53 Code: 0x80019002 Source: CDW_server1_server2_22 Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:27:08 AM Finished: 11:27:10 AM Elapsed: 2.137 seconds. The package execution failed. The step failed.
    ++++++++++++++++++++++++++++++

    FYI, "sqldb" is another account name which I set up while I was installing SQL Server to both Server 1 and Server 2. I don't know why it is executed as user "sqldb" instead of "jcao".

    Questions:
    1. Could you tell me where to setup the "execution user"? I am puzzled why the job was "Executed as user: Domain\sqldb".

    2. The error msg said: " The process cannot access the file 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\remote.mdf' because it is being used by another process. " Where to find which process is using this file and how to teminate it?

    Thank you very much!
    Jing

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In order to truly troubleshoot the "wizard" problem, you need to understand the concept of this "wizardry", which is nothing but such. All it does is tries to detach the database, then copy MDF and LDF files to the destination, then attach both source and destination to their respective instances. When you posted the first error, - that was related to the permissions of the account running the job on the destination file system location. When you posted the second error - that relates to the usage of the database when the job attempts to detach it. You can preceed the wizard's package with the statement:

    Code:
    alterdatabase [remote] set single_user with rollback immediate;
    alterdatabase [remote] set restricted_user with rollback immediate;
    
    Last edited by rdjabarov; 07-14-10 at 16:54. Reason: formatting
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2010
    Posts
    6

    Question

    HI rdjabarov,

    Thanks for the reply.

    For the two lines of code you posted, do you mean I should run it in as SQL script first before I run the Copy Database Wizard?

    Thanks,
    Jing

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    yes...to short of an answer, but that is what needs to be done.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jul 2010
    Posts
    6

    Question

    HI rdjabarov,

    Thanks for the reply.

    I assume this script should run on the source server--Server1, right? Since the destination server -- Server2 does not have the database [remote] yet.

    Also, do you mean "alter database" instead of "alterdatabase"?

    I tried

    alter database [remote] set single_user with rollback immediate;
    alter database [remote] set restricted_user with rollback immediate;

    on Server1 and then run Copy Database Wizard again and got the same error.

    One thing that puzzled me is that the error kept showing
    "Executed as Domain/sqldb", however, I am connecting both servers with account name "jcao" instead of "sqldb", as can see from "Object Explorer" in "SQL server Management Studio".
    Where are the other places that I can check?

    Thanks,
    Jing

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I suspect you're still connected to Server1.remote database when you're trying to run the wizard. After the commands that I posted earlier, issue "use tempdb", and make sure that in Object Explorer you're not placing focus on the remote database in Server1.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jul 2010
    Posts
    6

    Question

    HI rdjabarov,

    The issue now is, I couldn't find database "remote" in the Object explorer in the SQL Server Management Studio, however, remote.mdf and remote_log exist in the "E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA" directory.

    How to delete these two files is my main problem now.

    Thanks,
    Jing

Tags for this Thread

Posting Permissions

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