is this the best way to backup more than one database

I am setting up a DTS so that we can backup each of the database, is this the best way to back up each of the mdf & ldf files to another server? cheers

EXEC sp_addumpdevice 'disk', 'cc_150_chg', '\\homer\db_backups$\cc_150_chg.mdf'
EXEC sp_addumpdevice 'disk', 'cc_150_chg_admin', '\\homer\db_backups$\cc_150_chg_admin.mdf'
EXEC sp_addumpdevice 'disk', 'cc_150_chg_Log', '\\homer\db_backups$\cc_150_chg_log.ldf'
EXEC sp_addumpdevice 'disk', 'cc_150_chg_admin_log', '\\homer\db_backups$\cc_150_chg_admin.ldf'
EXEC sp_addumpdevice 'disk', 'cc_153', '\\homer\db_backups$\cc_153.mdf'
EXEC sp_addumpdevice 'disk', 'cc_153_log', '\\homer\db_backups$\cc_153.ldf'
EXEC sp_addumpdevice 'disk', 'cc_153_admin', '\\homer\db_backups$\cc_153_admin.mdf'
EXEC

This will form part of the step in 'command line'?

Cheers
EWhy don't you just backup to a file?|||backup all the database to a file? How can I do this? also do i need to specifiy all the databases individually by backuping both mdf & ldf files?|||Backup each database to a separate file. You don't need to back up the log files (ldf) unless you need point-in-time restore.|||You don't need to back up the log files (ldf)

You're fired|||alll sorted!!!!1thanx|||OK Brett, YOU explain the whole concept of log files to him! I got actual work I gotta do today.

...or just point him to Books Online...

:)|||OK Brett, YOU explain the whole concept of log files to him! I got actual work I gotta do today.

...or just point him to Books Online...

:)

Settle down...

Hey, there's a first time for everything, too

OR, I could just post Tara's blog

http://weblogs.sqlteam.com/tarad/category/95.aspx

I think I've stolen most of that...

Let me ask...when was the last time, aside from testing contingency, that you had to do a restore, and have to reapply all of the logs?

I don't remeber...had to be 6.5

Is this possible with DTS?

I would like to transfer data from one sql server to another based on
what records exist on server.
One server would be a local server (server1), the other one remote
(server2).
Is it possible to do something like this :-
select *
from server1.database1.table
where customerid not in
(select cusid from server2.database1.table)
Is this possible using DTS? If so can someone point me the right
directection.
TIA
Simon.Possible solutions:
1.Linked server ( you can use that syntax)
2.Bulk insert( just for inserting)
3. Openquery, opendatasource,openrowset (for distributed databases)(pass
through - for adohoc)
4.dts( gives you full control for permenant use)
see BOL for this
r.d
"bozzzza@.lycos.co.uk" wrote:

> I would like to transfer data from one sql server to another based on
> what records exist on server.
> One server would be a local server (server1), the other one remote
> (server2).
> Is it possible to do something like this :-
> select *
> from server1.database1.table
> where customerid not in
> (select cusid from server2.database1.table)
> Is this possible using DTS? If so can someone point me the right
> directection.
> TIA
> Simon.
>|||Hi,
This can be done if you have Linked server created from Server1 pinting to
Server2. But if you have Linked server then
you could directly use the INSERT statement rather than DTS.
Some thing like below:-
INSERT INTO TABLE select * from server1.database1.table where customerid not
in (select cusid from server2.database1.table)
Thanks
Hari
SQL Server MVP
<bozzzza@.lycos.co.uk> wrote in message
news:1123835051.556270.44930@.g14g2000cwa.googlegroups.com...
>I would like to transfer data from one sql server to another based on
> what records exist on server.
> One server would be a local server (server1), the other one remote
> (server2).
> Is it possible to do something like this :-
> select *
> from server1.database1.table
> where customerid not in
> (select cusid from server2.database1.table)
> Is this possible using DTS? If so can someone point me the right
> directection.
> TIA
> Simon.
>|||bozzza,
You can build a stored procedure which creates a link, do the transference
and at the end, delete the link.
"Hari Pra" wrote:

> Hi,
> This can be done if you have Linked server created from Server1 pinting to
> Server2. But if you have Linked server then
> you could directly use the INSERT statement rather than DTS.
> Some thing like below:-
> INSERT INTO TABLE select * from server1.database1.table where customerid n
ot
> in (select cusid from server2.database1.table)
> Thanks
> Hari
> SQL Server MVP
>
> <bozzzza@.lycos.co.uk> wrote in message
> news:1123835051.556270.44930@.g14g2000cwa.googlegroups.com...
>
>

Is this a silly way to use SSIS?

I have typically done any ETL style manipulations I needed to do to data stored in SQL Server in VB.NET. I would use the IMPORT?EXPORT DTS wizard to import flat files, or mabe something from ACCESS every now and then.

I am looking at a situation in my current contract where I will be pulling flat files from a mainframe and quasi relational stuff from a DB2 instance via an ODBC connection. I will be using this stuff to build a datawarehouse for a manufacturing client.

My question is this. Is there really enough good stuff in SSIS for what I will be doing to justify my learning it or if I'm comfortable doing the manipulations in VB.NET will that work just as well for my client? After all, I can schedule VB.NET apps to write results to log files and to run at specified times, etc. It just sort of always seemed to me that DTS was for people that needed to manipulate data without necessarily having to know a lot about programming per se.

I'm looking for opinions from people that know SSIS well. Is there enough meat to make cooking the SSIS meal worth the trouble?

Thanks in advance for any info.

I'm a bit biased but in my opinion if you're moving data about then you should use SSIS. There's no point in building an application when you have a product available to do the work for you!

-Jamie

|||

Thanks for the feedback Jamie, I've read a lot of your posts so I will take what you say very seriously. I guess about the only thing I can think of to reply is that if my client decided tomorrow to scrap SQL Server for MySQL or Oracle or DB2 (all of which they have and run at various locales in the organization) then a properly written VB app could be modified slightly and keep on ticking. But if they scrap SQL server (and hey, with the way acquisitions, etc. work these days it's not beyond the realm of possibility) then they would have to start from scratch with their ETL process. But, I guess that would really argue more for a 3rd party ETL tool than for a special purpose written app.

Thanks,

Steve

|||

Note that your client's company could choose to use other database storage engines, but continue using SQL Server Integration Services, especially in light of the favorable pricing (for SQL). We knew of companies that purchased SQL licenses in the past just to get DTS...given the increased power of SSIS, this may become even more common (although we'd love to see everyone taking advantage of the entire SQL package, of course).

|||

Absolutely, SSIS has no reliance on SQL Server.

Its also worth saying that if you were to change your RDBMS its unlikely that your SSIS packages would be too affected - assuming the metadata of the tables is the same. In certain (lucky) scenarios the only thing you might have to change is a connection string. And if you're using best practice of storing connection strings external to your package then that is incredibly easy

-Jamie

Is there any better method for DTS ?

I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
ThanksYou could have an 'execute SQL task', that checks for the existence of the
table first, and drops it if needed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks|||"Peter" wrote:
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access database).
> What is a better way for me to handle this problem ? Should I edit the DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
Destination Objects First", would this be of any use?
Cheers,
Ian|||Dear Narayana,
Thank you for your advice. However, I don't know how to create an "Execute
SQL Task" to check the existence and delete the table in the Access Table.
Can you give me some advice ?
Thanks
Peter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23KxHhN%23oFHA.3828@.TK2MSFTNGP12.phx.gbl...
> You could have an 'execute SQL task', that checks for the existence of the
> table first, and drops it if needed.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access
> database).
> What is a better way for me to handle this problem ? Should I edit the
> DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
>|||Dear Ian,
Thank you for your advice. However, I find that the options only applies to
database object and don't work for exporting to Access Database.
Peter
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:522E7384-0E4F-4EF3-8E04-45E5158C3757@.microsoft.com...
>
> "Peter" wrote:
>> I have created a package that just export a number of tables to an Access
>> Database and it works fine.
>> However, when I rerun the package, I get error message as it cannot
>> create
>> the tables (This is because they are already exists in the Access
>> database).
>> What is a better way for me to handle this problem ? Should I edit the
>> DTS
>> package to remove the "Create Table" step (It involves 23 tables) OR is
>> there any better way to create a package that can be reused ?
>> Thanks
> In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
> Destination Objects First", would this be of any use?
> Cheers,
> Ian

Is there any better method for DTS ?

I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks
You could have an 'execute SQL task', that checks for the existence of the
table first, and drops it if needed.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks
|||"Peter" wrote:

> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access database).
> What is a better way for me to handle this problem ? Should I edit the DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
Destination Objects First", would this be of any use?
Cheers,
Ian
|||Dear Narayana,
Thank you for your advice. However, I don't know how to create an "Execute
SQL Task" to check the existence and delete the table in the Access Table.
Can you give me some advice ?
Thanks
Peter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23KxHhN%23oFHA.3828@.TK2MSFTNGP12.phx.gbl...
> You could have an 'execute SQL task', that checks for the existence of the
> table first, and drops it if needed.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access
> database).
> What is a better way for me to handle this problem ? Should I edit the
> DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
>
|||Dear Ian,
Thank you for your advice. However, I find that the options only applies to
database object and don't work for exporting to Access Database.
Peter
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:522E7384-0E4F-4EF3-8E04-45E5158C3757@.microsoft.com...
>
> "Peter" wrote:
> In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
> Destination Objects First", would this be of any use?
> Cheers,
> Ian

Is there any better method for DTS ?

I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
ThanksYou could have an 'execute SQL task', that checks for the existence of the
table first, and drops it if needed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks|||"Peter" wrote:

> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access database
).
> What is a better way for me to handle this problem ? Should I edit the DT
S
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
Destination Objects First", would this be of any use?
Cheers,
Ian|||Dear Narayana,
Thank you for your advice. However, I don't know how to create an "Execute
SQL Task" to check the existence and delete the table in the Access Table.
Can you give me some advice ?
Thanks
Peter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23KxHhN%23oFHA.3828@.TK2MSFTNGP12.phx.gbl...
> You could have an 'execute SQL task', that checks for the existence of the
> table first, and drops it if needed.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access
> database).
> What is a better way for me to handle this problem ? Should I edit the
> DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
>|||Dear Ian,
Thank you for your advice. However, I find that the options only applies to
database object and don't work for exporting to Access Database.
Peter
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:522E7384-0E4F-4EF3-8E04-45E5158C3757@.microsoft.com...
>
> "Peter" wrote:
>
> In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
> Destination Objects First", would this be of any use?
> Cheers,
> Ian