Restore and Backup Databases using DTS

Question

Restore and Backup Databases using DTS

shree dhavale

SSChasing Mays

Points: 609
More actions
March 15, 2006 at 11:18 am

#67398

Hello,
I need to drop an existing db on a server.Then I need to backup another db existing on another server and backup that db as the one I dorpped on the first server. Can I do all this using a dts?Please help!
Thanks in advance!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

You can't create or restore a database with DTS. You can only copy objects and data, not the database itself. You should use SQL Server BACKUP and RESTORE for replacing a database that you've dropped.

Greg

shree dhavale SSChasing Mays Points: 609 More actions · Answer 2

Greg,

Thanks for your help. I am trying to automate this process and was wondering how to do that.

Thanks,

Shree

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 3

You can use DTS to do this by using the execute sql task and running sql for whatever connection you need to use.

shree dhavale SSChasing Mays Points: 609 More actions · Answer 4

The Execute SQL task gives an error saying I need at least one connection. What does that mean?

Thanks

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 5

on the left of the design page is the connection objects, choose a sql connection object and update the details with the server you want to connect to.

shree dhavale SSChasing Mays Points: 609 More actions · Answer 6

Thanks! Now I have divided my task into two "Execute sql steps"

1. Backup a db

USE rmsReal

GO

BACKUP DATABASE rmsReal

TO DISK='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\BU_remReal.bak'

Go

2. Restore it as a different db

RESTORE DATABASE rmsPlay

FROM DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\BU_rmsReal.bak'

WITH MOVE 'rmsReal' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\rmsPlay.mdf',

MOVE 'rmsReal_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\rmsPlay.ldf'

GO

The backup works but the restore gives me the following error : Cannot open backup device ":\program files\microsoft sql server\mssql\backup\bu_rmsReal.bak. Cevice error or device offline. What am I doing wrong?

Please help.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 7

If the file you are restoring from is on a different machine use the unc path:

\\servername\foldername\filename.extension

You can test to see if the server you are restoring to can see the file by logging into the machine and running this code through query analyser:

--Find out what the file structure is of the backup file

RESTORE

FILELISTONLY

FROM

DISK = '\\servername\foldername\filename.extension'