Database BackUp using DTS

Question

Database BackUp using DTS

fasttrack

SSCertifiable

Points: 6071
More actions
February 15, 2007 at 3:53 am

#69515

Hello,
I need to create a DTS package that run a Database BackUp on SQL server 2000.
Sql server, Database name and back name must be variables (DTS should be installed on diffrent SQL server and used for different Dbs).

Do you how to build or where to find useful information.

Thank

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

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

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

That shouldn't be too hard.

You need one SQL Server connection and two tasks: a dynamic properties task and an executesql task. Link the two with a workflow.

In the dynamic properties task, assign the DataSource to the value in the SQLServer global variable.

In the executesql task type the following (the parse will throw an error):

BACKUP DATABASE ? to disk = ?

Next, go to Disconnected edit (Package menu). Find your executesql task. Find the InputGlobalVariableNames property under that task. Enter the name of the database name global variable followed by semi-colon followed by the name of the database file path global variable (see http://doc.ddart.net/mssql/sql2000/html/dtsprog/dtsppropfl_7ri1.htm for information on formatting this).

Test it out. And you know that you can change the value of the global variable in the command line of the dtsrun program.

Russel Loski, MCSE Business Intelligence, Data Platform

fasttrack SSCertifiable Points: 6071 More actions · Answer 2

fasttrack

SSCertifiable

Points: 6071

February 15, 2007 at 7:33 am

#689938

Thank,

I'll follow your directions.

Let me try

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

I've used a slightly different approach, using DTS to drive some T-SQL procedures to perform backups. However the advice above would work great as well.

Be sure you connect with local as the server or 127.0.0.1 to avoid server dependencies. Also watch pathing and be sure that can be a variable or you use environmental variables.

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

To each his own, but I don't get why you'd use a DTS package instead of a scheduled job with the backup commands in job steps. Can someone tell me the advantage of using the methods described above?

Thanks!

Greg

fasttrack SSCertifiable Points: 6071 More actions · Answer 5

Yes you'll be right...scheduling a backup job is very easy.

But (there is always a reason):

1) I am not in charge to create backup on several sql servers (on site and remote)

2) I have to explain how to create a backup job (people that deas know anything about SQL)

3) Backup run manually and not scheduled (when need arises)

4) This job will be installed on current and new machines (planned and to be planned).

So, I am creating something with the help of vb.net to create an easy app to launch the backup when people need it, without my action (and why not teach my own!!! may will be very useful).

Thank for any your contribution and suggestion.

fasttrack SSCertifiable Points: 6071 More actions · Answer 6

Sorry...

there is a mistake.

Point 2, says:

2) I have to explain how to create a backup job (people that does not know anything about SQL)

Also, I add

There are tools like osql and isql to this, but people do not like to edit (then mistakes during digit...you know....), so a small .net interface where to select SQL server instance, DB, backup name and destination is the best, otherwise I will be called everytime for silly problems.

Thank