[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

SQL 2012 database copy between Servers

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
vaughnwhitehead
Asked:
vaughnwhitehead
1 Solution
 
nettek0300Commented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Make sure that Agent account does have sufficient read/write credentials on the folder pointed out by the error message.
0
 
vaughnwhiteheadAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
I checked the Agent account.
And was it the default Local system account or a domain account with appropriate permissions?
0
 
vaughnwhiteheadAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
vaughnwhiteheadAuthor Commented:
Lets try the step by step
0
 
Anthony PerkinsCommented:
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
 
vaughnwhiteheadAuthor Commented:
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
 
vaughnwhiteheadAuthor Commented:
Under logins, I checked the ServerRoles for the SQLServerAgent on both servers and all are checked.
0
 
Anthony PerkinsCommented:
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
 
vaughnwhiteheadAuthor Commented:
It is a domain account.  I checked on both servers and all permissions are enabled.
0
 
Anthony PerkinsCommented:
Then I am afraid I have no idea what is going on.
0
 
Alpesh PatelAssistant ConsultantCommented:
Better to create SSIS package and use trasnfer database task.
0
 
vaughnwhiteheadAuthor Commented:
Ok, thanks
I will try it
0
 
vaughnwhiteheadAuthor Commented:
The SSIS package solution worked fine.  

Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now