Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2012 database copy between Servers

Posted on 2013-05-10
16
Medium Priority
?
451 Views
Last Modified: 2016-02-11
Good evening,

I am trying to copy databases between two 2012 SQL  servers using the CopyDatabase Wizard.  My problem occurs when the Wizard is on the "Configure the Package" screen.  A timeout error occurs:  "While trying to find a folder on SQL...." and then the timeout message.  
I can use MMS to connect to both servers without any problems and am using the sa accounts in the Wizard.   SQL Server Agent is running on both servers. I have tripled the timeout period.

Thanks
V Whitehead
0
Comment
Question by:vaughnwhitehead
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 6

Expert Comment

by:nettek0300
ID: 39157517
I would perform a backup of the database you want to copy and then copy the backup file to the new server and restore from the backup file.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39157732
Make sure that Agent account does have sufficient read/write credentials on the folder pointed out by the error message.
0
 

Author Comment

by:vaughnwhitehead
ID: 39158164
I checked the Agent account.  The folder does not exist, so I am assuming the a temporary folder is being created.  If I ignore the error, the next screen runs through all steps (including create package) successfully.  The final step, Execute SQL Server Agent Job is where I get the log error.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39159780
I checked the Agent account.
And was it the default Local system account or a domain account with appropriate permissions?
0
 

Author Comment

by:vaughnwhitehead
ID: 39163441
Server Agent is connected through the sa account.  However,  under Agent properties the connection is Windows Authentication which I do not use.  The 'Use SQL Authentication' is grayed out .

I appreciate the help.  I know I could do a database backup and restore, but I will be doing multiple transfers between servers and this seems to be the obvious method.

V Whitehead
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39163532
Let me try this once again:
Is the Windows startup account for the SQL Server Agent service a Local System account or a domain account with appropriate permissions?

This has nothing to do with sa.  If you don't know the answer or even how to find it we can give you step by step instructions.
0
 

Author Comment

by:vaughnwhitehead
ID: 39164240
Lets try the step by step
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39164523
1. Press the Windows key.
2. Type "services.msc"
3. Press Enter.
4. Make sure the Services list is sorted by Name (by clicking on the title).
5. Scroll to find the SQL Server Agent service.
6. Double-click.
7. Select  the Log On tab.
8. Let us know if it is a "Local System Account" or "This Account".  If it is the latter, does the account used have the appropriate permissions?
0
 

Author Comment

by:vaughnwhitehead
ID: 39164801
It is set as This Account to the SQLServerAgent.   If I browse from the Account Name to 'Select User or Group' then click 'Advanced' I can find Bulit-in in security principals and users and from there do a search resulting in Administrator,  my account name, etc.  Save to assume that I just need to set it to Administrator?
0
 

Author Comment

by:vaughnwhitehead
ID: 39164866
Under logins, I checked the ServerRoles for the SQLServerAgent on both servers and all are checked.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39165561
It is set as This Account to the SQLServerAgent.
So is this a domain account or a local account?  If it is a domain account does it have the appropriate permissions for both servers?
0
 

Author Comment

by:vaughnwhitehead
ID: 39166248
It is a domain account.  I checked on both servers and all permissions are enabled.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39166446
Then I am afraid I have no idea what is going on.
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 2000 total points
ID: 39171312
Better to create SSIS package and use trasnfer database task.
0
 

Author Comment

by:vaughnwhitehead
ID: 39177769
Ok, thanks
I will try it
0
 

Author Closing Comment

by:vaughnwhitehead
ID: 39198088
The SSIS package solution worked fine.  

Thanks
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question