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 an attack ?

Last night we made the backup as usual, in the morning a table is
completely all nulls except for the elements of the key.
We thought there was some error or accident, so we copy that table from
the backup, 30 min later we saw another table with the same problem,
then checking another DB used for testing also had the same problem, is
this an attack, virus ?
We don't know what to do, please someone advise.
SQL Server 2005 Standard 9.0.1399
Thanks
Andres Sanchez
Monterrey MexicoYou could run Profiler to try to track down if someone is submitting such UP
DATE statement to your
database instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mancha" <andres.sanchez.rendon@.gmail.com> wrote in message
news:1159492282.552559.267590@.i3g2000cwc.googlegroups.com...
> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>|||Hi
You don't say how may non-key columns there are, but you may want to review
why they are all nullable.
John
"Mancha" wrote:

> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>sql

is this an attack ?

Last night we made the backup as usual, in the morning a table is
completely all nulls except for the elements of the key.
We thought there was some error or accident, so we copy that table from
the backup, 30 min later we saw another table with the same problem,
then checking another DB used for testing also had the same problem, is
this an attack, virus ?
We don't know what to do, please someone advise.
SQL Server 2005 Standard 9.0.1399
Thanks
Andres Sanchez
Monterrey Mexico
You could run Profiler to try to track down if someone is submitting such UPDATE statement to your
database instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mancha" <andres.sanchez.rendon@.gmail.com> wrote in message
news:1159492282.552559.267590@.i3g2000cwc.googlegro ups.com...
> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>
|||Hi
You don't say how may non-key columns there are, but you may want to review
why they are all nullable.
John
"Mancha" wrote:

> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>

is this an attack ?

Last night we made the backup as usual, in the morning a table is
completely all nulls except for the elements of the key.
We thought there was some error or accident, so we copy that table from
the backup, 30 min later we saw another table with the same problem,
then checking another DB used for testing also had the same problem, is
this an attack, virus ?
We don't know what to do, please someone advise.
SQL Server 2005 Standard 9.0.1399
Thanks
Andres Sanchez
Monterrey MexicoYou could run Profiler to try to track down if someone is submitting such UPDATE statement to your
database instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mancha" <andres.sanchez.rendon@.gmail.com> wrote in message
news:1159492282.552559.267590@.i3g2000cwc.googlegroups.com...
> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>|||Hi
You don't say how may non-key columns there are, but you may want to review
why they are all nullable.
John
"Mancha" wrote:
> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>

Is there such thing as "incremental" backup for SQL server?

I'm not looking for transaction log backups as substitute becouse it can
still be larger then just changed data.
My issue with differential backups is that they keep growing since last full
backup and I want only changed data since last full or differential backup to
be backed up and shipped to offisite location. Currently the only option is
ever growing differential backup or transaction log shipping which can be
quite large during operations which are modifiying small subset of data.
Diff and translog backups are "incremental" backups, which contain
changes since the last full backup, and last differential backup,
respectively. For a complete discussion of SQL Server backups, see
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
However, if you are looking to minimize the size of the backups
altogether, I believe you will have to look at different technolgies...
we are just now switching over to a Net App and snap manager which will
eliminate the need for regular backups, but honestly I leave that to
the other DBA here, not my specialty :-)
ChanKaiShi wrote:
> I'm not looking for transaction log backups as substitute becouse it can
> still be larger then just changed data.
> My issue with differential backups is that they keep growing since last full
> backup and I want only changed data since last full or differential backup to
> be backed up and shipped to offisite location. Currently the only option is
> ever growing differential backup or transaction log shipping which can be
> quite large during operations which are modifiying small subset of data.
|||Is is possible that you are not backing up the transaction logs enough.
For example, I have managed a 100 gig db that was highly transactional
and I had to backup the logs on a 15-30 minute basis during the day in
order to keep the log size small. If I waited 12 hours and did 1
transaction log backup, it was sometimes 15 gigs. I used differential
backups for a while,but they were almost as big as the fulls, so I
switched to purely fulls and transaction logs.
kata...@.gmail.com wrote:[vbcol=seagreen]
> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:
|||I need incremenatal backup which will hold only changes since last
incremental backup not differential one which has all changes ever done since
last full backup.
"kataoki@.gmail.com" wrote:

> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:
>
|||>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
As previously stated, transaction log backups are incremental backups.
Transaction log backups contain only changes made since the last transaction
log backup (or full backup) and are not cumulative. You can implement
transaction log backups with only full database backups; differential
database backups are optional.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...[vbcol=seagreen]
>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
> "kataoki@.gmail.com" wrote:
|||transactional log backups are not incremental becouse incremental will log
only changes since last full or incremental while transaction log will log
all the transactions made in database even if all of them were on single row.
Secondly I'm not sure I'm understanding properly but full database backups
are not supposed to reset transaction log sequence, right? Becouse I'm
planning to use log shipping for external database server and my
understanding that I can do full database log backups while log shipping
process is being run.
"Dan Guzman" wrote:

> As previously stated, transaction log backups are incremental backups.
> Transaction log backups contain only changes made since the last transaction
> log backup (or full backup) and are not cumulative. You can implement
> transaction log backups with only full database backups; differential
> database backups are optional.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
> news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...
>
|||> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
I guess it depends how often you backup your log ;-) If backup file size is
your concern, you might also look into third party tools that compress
backups during the backup process.

> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right?
Yes, full backups do not truncate the log. In the FULL or BULK_LOGGED
model, committed transactions are removed from the log only by BACKUP LOG.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:415F6F11-50E8-45BA-ACEC-7720AE88D2B4@.microsoft.com...[vbcol=seagreen]
> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right? Becouse I'm
> planning to use log shipping for external database server and my
> understanding that I can do full database log backups while log shipping
> process is being run.
> "Dan Guzman" wrote:

Is there such thing as "incremental" backup for SQL server?

I'm not looking for transaction log backups as substitute becouse it can
still be larger then just changed data.
My issue with differential backups is that they keep growing since last full
backup and I want only changed data since last full or differential backup t
o
be backed up and shipped to offisite location. Currently the only option is
ever growing differential backup or transaction log shipping which can be
quite large during operations which are modifiying small subset of data.Diff and translog backups are "incremental" backups, which contain
changes since the last full backup, and last differential backup,
respectively. For a complete discussion of SQL Server backups, see
http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
However, if you are looking to minimize the size of the backups
altogether, I believe you will have to look at different technolgies...
we are just now switching over to a Net App and snap manager which will
eliminate the need for regular backups, but honestly I leave that to
the other DBA here, not my specialty :-)
ChanKaiShi wrote:
> I'm not looking for transaction log backups as substitute becouse it can
> still be larger then just changed data.
> My issue with differential backups is that they keep growing since last fu
ll
> backup and I want only changed data since last full or differential backup
to
> be backed up and shipped to offisite location. Currently the only option i
s
> ever growing differential backup or transaction log shipping which can be
> quite large during operations which are modifiying small subset of data.|||Is is possible that you are not backing up the transaction logs enough.
For example, I have managed a 100 gig db that was highly transactional
and I had to backup the logs on a 15-30 minute basis during the day in
order to keep the log size small. If I waited 12 hours and did 1
transaction log backup, it was sometimes 15 gigs. I used differential
backups for a while,but they were almost as big as the fulls, so I
switched to purely fulls and transaction logs.
kata...@.gmail.com wrote:[vbcol=seagreen]
> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:|||I need incremenatal backup which will hold only changes since last
incremental backup not differential one which has all changes ever done sinc
e
last full backup.
"kataoki@.gmail.com" wrote:

> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:
>|||>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
As previously stated, transaction log backups are incremental backups.
Transaction log backups contain only changes made since the last transaction
log backup (or full backup) and are not cumulative. You can implement
transaction log backups with only full database backups; differential
database backups are optional.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...[vbcol=seagreen]
>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
> "kataoki@.gmail.com" wrote:
>|||transactional log backups are not incremental becouse incremental will log
only changes since last full or incremental while transaction log will log
all the transactions made in database even if all of them were on single row
.
Secondly I'm not sure I'm understanding properly but full database backups
are not supposed to reset transaction log sequence, right? Becouse I'm
planning to use log shipping for external database server and my
understanding that I can do full database log backups while log shipping
process is being run.
"Dan Guzman" wrote:

> As previously stated, transaction log backups are incremental backups.
> Transaction log backups contain only changes made since the last transacti
on
> log backup (or full backup) and are not cumulative. You can implement
> transaction log backups with only full database backups; differential
> database backups are optional.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
> news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...
>|||> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
I guess it depends how often you backup your log ;-) If backup file size is
your concern, you might also look into third party tools that compress
backups during the backup process.

> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right?
Yes, full backups do not truncate the log. In the FULL or BULK_LOGGED
model, committed transactions are removed from the log only by BACKUP LOG.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:415F6F11-50E8-45BA-ACEC-7720AE88D2B4@.microsoft.com...[vbcol=seagreen]
> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right? Becouse I'm
> planning to use log shipping for external database server and my
> understanding that I can do full database log backups while log shipping
> process is being run.
> "Dan Guzman" wrote:
>