Transfer Database vs Restore DB

[homebrew01]

Every few weeks we need to copy our production database (call it PROD_DB ) on Server#1 and overlay our test database with current data (call it TEST_DB) on Server#2.

We've been restoring from a SQL Agent tape backup to the test database ..... works fine, but I'm dependant on the Network Admin to do the restore, which limits my timeframe. I'm curious about the "Transfer Database" option available in DTS. I tried it on a tiny test database and it worked fine. What would happen if I tried it on my 100 Gig database going across servers ? Would it take 10 hours ? The tape restore takes almost 2 hours and has to be done when tha network admin is here (9-5). If the DTS were viable, I could schedule it for 2 am.

I don't have much space on the target server ( 130 G total, database uses 100 G), so I can't restore using any of the methods requiring twice the space of the database. Does Transfer database require extra space ?

Any thoughts ?

(we're SQL 2000, sp2)

 

[JayKusch]

100GB will take a while no matter what means you use. Tape seems to be the route to go. If you do have down time around 2am, another method would be to stop sql server, copy the MDF and LDF of the DB you desire over to the test server, start sql server backup up and then use an sp_attach_db on the test server to bring the DBs on line. Of course you would have to delete the DBs off the test server before you tried to reattach the fresh copy.

Thanks

J. Kusch

 

[TitleistDBA]

You could purchase a product like SQL LiteSpeed it will back your database up faster and compress it. I have a 300 gig database that gets backed up in 1 hr 15 min and is compressed to 25gig. I'm guessing a 100 gig database would be about 10 gig. That would copy over your network much faster. It used extended procs so you can backup right from a job.

Here is a copy of the syntax we use.

master.dbo.xp_backup_database @database='FDM8PRD', @filename='F:\Programs\Mssql\litespd\FDM8PRD_data.bak'
, @backupname='FDM8PRD'
, @desc='Backup of FDM8PRD'
, @init = 1
, @threads = 1

As you can see it looks alot like a regular backup command.
You can download a trial version from

http://www.sqllitespeed.com

I am able to backup, copy over a network and restore my database in under 7 hours.
I wouldn't use the DTS. It could take about 12 hours.

http://www.acushnetcompany.com

 

[SQLBill]

What do you mean 'SQL Agent'?

I really suggest trying Jay's option. Detach the database. Copy the .mdf and .ldf files. Move them across the network. Attach them. You just have to detach and delete the .mdf and .ldf files from the target server BEFORE moving the copies over.

-SQLBill

 

[homebrew01]

I don't have any reliable downtime for the production database .... (there are night jobs that run on various schedules) so I can't detach it and copy the MDF or LDF. The test server is generally used only 9-5. I thought the Transfer DB option would allow a refresh without having to bother users from 9-5 if possible.

Thanks for all the replies.

 

[JayKusch]

Were pretty much back to square one on this. If you use the Transfer DB option, its backend, or driving program, is DTS. 100GB DB will take a long time, and if there are any issues w/ transfering any of the objects in the DB over to the test server, it get rather tedious in syncing up the objects that failed. Once again, in this instance at least, tape is a good option.

Thanks

J. Kusch

 

[SQLBill]

Curiosity....why are you dependent on the Network Admin to do the restore?

Possibility....is there a tape drive on the testbed server? You might be able to create a job in SQL Server, schedule it for a specific time, put the tape in the drive and just let it restore through the job.

-SQLBill

 

[mrdenny]

Ask the Admin to check to see if there is a way in the backup software to schedule the restore. Our tape backup system has the ability to schedule restores as well as backups.

Do you know which tape backup platform is being used?

Denny Cherry

 

[homebrew01]

The network admin does the restores because he controls the network backups & tape devices. I can work with my own backups to drives, but don't have disk space to store a backup of the production database.

We're using Veritas ........ I'm sure it has scheduling capability. We don't do these restores often, I was just curious about different methods. I may look into the scheduling option for next time.

Thanks for everyone's input.