I have to SQL2000 servers setup and for a while I was backing one to the
other using a DTS package. Now it fails. I cannot figure out why. The
latest error is a "DMO Bulk Copy Execution Failed" error.

Perhaps I am doing this wrong. What should I be doing to totally backup a
database from one server to the other?

Thanks!
Jon

You could simply use

BACKUP DATABASE

sending the fiole to the other server and then on the other server issue

RESTORE DATABASE



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jon Glazer" <jglazer.deleteme (AT) adconn (DOT) com> wrote

I need to do this once a day. How do I automate it?

Thanks!

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You use SQL Server Agent and a Job (two jobs actually).

You can have the BACKUP job fire the RESTORE job after completion


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

hmm.... can you expand on this?

Again, I have 2 servers say server1 and server2

How do I specifically setup an operation such that it periodically (once a
day) does a backup (on server1) then copies the file to server2 and does a
restore on server2.

Thanks!
Jon
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I really would prefer to use the DTS to do a copy/backup of my database from
one server to the other. Can someone help me here? I don't understand why
it fails on the copy data part of the package.

Thanks!

"Jon Glazer" <jglazer.deleteme (AT) adconn (DOT) com> wrote

On Server 1 you create a linked server of Server 2
On server 1 you create a Job

Step 1

BACKUP DATABASE XXX To DISK = 'UNC PATH'

Step 2

You execute

EXEC ServerName.masdb.dbo.sp_start_job 'RESTORE JOB NAME'



On Server 2 you have a job called 'RESTORE JOB NAME'

Step 1

RESTORE DATABASE XXX FROM DISK = 'UNC PATH' WITH REPLACE



You may need to add an additional step in the job on server 2 to clear
out anybody using the database as you cannot restore with people in it.
You may also need to modify the RESTORE command if you need to move the
files on the server 2.



Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

You mention creating a linked server. You mean a VPN or something?

Thanks again!

Jon
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

No.

Look at these topics in BOL

Backup

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Book
s\tsqlref.chm::/ts_ba-bz_35ww.htm

Restore

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Book
s\tsqlref.chm::/ts_ra-rz_25rm.htm

sp_detach_db

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Book
s\tsqlref.chm::/ts_sp_da-di_83fm.htm

sp_attach_db

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Book
s\tsqlref.chm::/ts_sp_ae-az_52oy.htm

sp_start_job

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Book
s\tsqlref.chm::/ts_sp_sa-sz_11uq.htm

Linked servers

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Book
s\adminsql.chm::/ad_1_server_4uuq.htm


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Excuse my ignorance, but what is BOL?

Jon
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote