Copying a database between servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copying a database between servers

Author

Topic

DBA_nupe
Starting Member

11 Posts

Posted - 2008-04-10 : 14:21:38

I am attempting to move a User Database from the Production Server to a Training Server. What is the best/most simplistic way for me to accomplish this task and place this copying action on a schedule of say "every saturday morning @ 7am"

I tried the "Copy Database Wizard" within Enterprise Manager and it successfully copies the database, however when I try to schedule it to happen at a different time...it does not copy the database. It seems to only work when I tell it to perform this action now.

Please help.

Thanks,

JC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-10 : 14:26:13

Use BACKUP/RESTORE. To copy the file, use xcopy DOS command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

DBA_nupe
Starting Member

11 Posts

Posted - 2008-04-10 : 14:28:19

I am fairly new to being a DBA...however could you please give me a idea of how to utilize the Xcopy feature...to accomplish this task...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-10 : 14:31:26

Go to Start..Run, type in cmd and hit enter. This is where you can type in DOS commands. Type xcopy /? to check out its switches.

When you schedule it to run as a job, you can wrap the whole in a batch file, call xcopy via xp_cmdshell, or use the CmdExec job step type to execute a DOS command.

Is there anyone there with more DBA experience that can help you? This would be fairly easy for an experienced DBA to handle but probably very hard for a junior type DBA.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

DBA_nupe
Starting Member

11 Posts

Posted - 2008-04-10 : 14:34:41

Unfortunately there isnt. The only other DBA teleworks from Seattle Washington and I am in VA. You're right...for a not so experienced DBA...this is a difficult task.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-10 : 14:44:33

Why are you scheduling to move 1 database ? How big is database? What are you trying to achieve? Do they need fresh copy or sync copy?

Answer this then we will help you?

DBA_nupe
Starting Member

11 Posts

Posted - 2008-04-10 : 14:53:01

Why are you scheduling to move 1 database ? How big is database? What are you trying to achieve? Do they need fresh copy or sync copy?

I am only scheduling to move one database because the customer is not interested in any of the other db's on the server. This database is essentially the backend to our application. The database is approximately 1gb in size and the result that we are seeking is to take the database from the Production Server and move it to a Training/Demo Server so that a demo and training can be performed using data that is as close to production as possible. (Personally I dont agree with doing this, but I am but a pawn). They want this copy done every Saturday Morning at 730a when there will be no users making any changes/adjustments.

Does that clear things up a little bit better for you?

Thanks,

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-10 : 14:54:22

You didn't answer how big is your database?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-10 : 14:56:23

Yes he did.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

DBA_nupe
Starting Member

11 Posts

Posted - 2008-04-10 : 14:56:55

1 gb

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-10 : 14:59:16

Here's what needs to be done:

1. Backup the database to a file.
2. Xcopy the file to the test server.

Steps 1 and 2 should be scheduled on the prod server.

3. Restore the database from the file.
4. Unorphan the logins if needed

Steps 3 and 4 should be scheduled on the test server.

Test how long steps 1 and 2 will take and then have steps 3 and 4 kick off after the maximum time steps 1 and 2 take. I typically give an hour buffer just in case there is excessive network latency.

I have this entire scenario working for one of our systems. I can't provide the code but I can help when you run into problems.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-10 : 14:59:28

Your schedule fails becoz your database already exists in Destination server? I guess why can't you do replication as i don't see any high transaction involved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources