Using Copy Database Wizard

[pthalacker]

In SQL Server 2000 I am trying to use the Copy Database Wizard to copy a database from our production server to the local server on my development machine. When I select the destination server (my local machine) and click Next, I get an error message "Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account to have the right to copy files over the network. I am using Enterprise Manager which I assume use the 'sa' login. I can't figure out what setting I need to change.

pamela

 

[ptheriault]

you need to run the SQL Service account on your local machine with an account that access to the Domain.
If you open Services, go to the SQL Server service, open it and change the log on to an account that has access to the Domain. This account should also have Admin rights to your local machine...
However, there is an easier way (and faster) to copy a database from your prod server to your dev. Just take the most recent backup of your database copy it to your local machine and restore it.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[pthalacker]

Okay, I got past that hurdle. Now, when I select next after I have chosen the database to move or copy and go to the Database File Location page, there is a red X in front of Files on the Source have the same name as the destination. But there is nothing on the destination with the name of the source database.

What does it want? Does it have something to do with the master database on my local server?

pamela

 

[Catadmin]

Is the source database on the same box as the destination? If not, then you need to verify the destination server is accessible by the same domain account you're using on the source. It could be that you can't copy because the destination is also using a local admin account or that the domain account doesn't have permissions (share or SQL) on the new box.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Pamela,
I should have been more clear about the permissions needed. the domain account you added needs to be on both servers and have admin rights. Like Catadmin described.
also to just say it again. it's much quicker and easier to copy and restore a backup.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[pthalacker]

it's much quicker and easier to copy and restore a backup.

Yeah, I am getting that. For that to work I need the database created on the target server and I need the owner login in the server instance logins? I did this once 6 months ago, but I don't remember exactly.

pamela

 

[ptheriault]

Actually Pam you don't need any of that to restore a backup. You can create the database on the "fly" with the restore database wizard from Enterprise manager. You just need to be sysadmin on the server you are restoring to. Once you have restored the database just delete any orphaned user accounts you don't need.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

As a matter of fact, SQL Server 2005 has a weird error (as in I can't reproduce it when I try, but I've seen other non-DBA types do it) that when you create a new database in SSMS Object Explorer and then try to restore a backup to that database, it fails and causes all sorts of errors.

I know you're using 2000, but I wonder if you're running into the same issue. Just do a Restore Database from EM and enter a new database name in the wizard and you should be fine.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[pthalacker]

You can create the database on the "fly" with the restore database wizard from Enterprise manager. You just need to be sysadmin on the server you are restoring to.

With both the Restore Wizard and the Copy Database Wizard I get the same error: "must be in single user mode when trying to restore the master database" What and where do I need to set to single user mode?

pamela

 

[ptheriault]

why are you trying to restore the master database from another server?
Lets start over...what is it you are trying to accomplish. Please provide as much detail as possible.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[pthalacker]

Paul,

I am trying to create a copy of a production database on my local development machine. The reason I was trying to restore over master is because it is the default in the Restore Wizard. When you said 'on the fly' I took that to mean that you didn't have to do anything other than point to the source. But it appears to me that you have to create the target database on the target server or else there is nothing to point the Restore Wizard at.

IAC, all is well. I have the copy on the local server where I want it and I will be back with more dumb questions shortly.

pamela
The only stupid question is the one that doesn't get asked.

 

[ptheriault]

Ok,
When you run the restore wizard there is a drop down to select the database you want to restore. In that drop down there is also the option to create the database you are restoring.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[pthalacker]

In that drop down there is also the option to create the database you are restoring.

I am not seeing that. How do I get to it?

pamela

 

[ptheriault]

I just noticed that you are using 2000. I am using the 2005 tools. sorry.

- Paul
- If at first you don't succeed, find out if the loser gets anything.