Failing To Copy DB From One Machine To Another

Question

Failing To Copy DB From One Machine To Another

czeluff

SSC Rookie

Points: 42
More actions
February 25, 2008 at 2:32 pm

#182556

Hello to all,
http://www.developmentnow.com/g/103_2004_11_0_0_398012/Copy-Database-Wizard-Fails-to-Copy-Files.htm
I'm having the same error this person was having. It fails on the same line.
Everyone says this is a permission issue. I've been struggling to make this work for hours now. On each computer i'm trying to use Windows authentication. Maybe this is where im wrong, but even logging in as 'sa' on the source machine doesn't seem to work.
One of the responses says:
"I had the exact same problem until I executed the package under an account
that was explicitly defined in the sysadmin role, and not inheriting it."
Would someone please describe specifically how to go about doing this? I'm a real n00b when it comes to sql stuff.
Thanks so much,
Chad Z

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You should not use sa account to copy a file from one server to another. You should use a domain account.

To look whether or not an account has authority to access other server, you may use mapping drive in your window on one server, and do a test copy.

Another issue in copying one file from one server to another is instability in network. It will be noticed when you have a huge file to copy.

Lowell SSC Guru Points: 323518 More actions · Answer 2

SQL ORACLE is right...since "sa" never logs into the domain, it has no access to the computers and shares that exist on the network.

you'll have a similar issue if you ever map excel files to be a linked server...although 'sa' can execute sp_addlinkedserver without errors, when you try to select from the linked server, chances are "sa" doesn't have read access to the folder the spreadsheet resides in, resulting in an error.

Use a domain account to fix both issues above.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

CDBA Hall of Fame Points: 3266 More actions · Answer 3

You may take any one of the below approach:

===================================

Using NT:

1. Make sure the NT login has correct permission on both box

2. Start the copy

SQL Login (Not suggested):

Create a new SQL Account with same name and password on both box

However, file copy can not work with this credential. You must use domain pwd to get the file copy done

CDBA

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

As mentioned by other users file copy can be done only with windows account with network permissions. So you need to have a daomin user to copy the file to the other machine. also you need yourr sql services to be running on an account that can access network resources.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com