Backup only new databases

Is there any way with SQL 2000 SP3a to backup all new databases that have
been created and have not had a full backup run yet?
CR wrote:
> Is there any way with SQL 2000 SP3a to backup all new databases that
> have been created and have not had a full backup run yet?
You could probably query the msdb.backupset table for all user databases
that do not have rows in the table (check the database_name column)
David Gugick
Quest Software
|||CR
Create a table where you store the db_id or dbnames.
insert into table (dbid,name) select dbid,name from sysdatabases
select dbid,name from sysdatabases where not exists
(select * from table where table.dbid=sysdatabases.dbid)
"CR" <a@.b.c> wrote in message news:u$MxBHhIGHA.676@.TK2MSFTNGP10.phx.gbl...
> Is there any way with SQL 2000 SP3a to backup all new databases that have
> been created and have not had a full backup run yet?
>

Backup only new databases

Is there any way with SQL 2000 SP3a to backup all new databases that have
been created and have not had a full backup run yet?CR wrote:
> Is there any way with SQL 2000 SP3a to backup all new databases that
> have been created and have not had a full backup run yet?
You could probably query the msdb.backupset table for all user databases
that do not have rows in the table (check the database_name column)
David Gugick
Quest Software|||CR
Create a table where you store the db_id or dbnames.
insert into table (dbid,name) select dbid,name from sysdatabases
select dbid,name from sysdatabases where not exists
(select * from table where table.dbid=sysdatabases.dbid)
"CR" <a@.b.c> wrote in message news:u$MxBHhIGHA.676@.TK2MSFTNGP10.phx.gbl...
> Is there any way with SQL 2000 SP3a to backup all new databases that have
> been created and have not had a full backup run yet?
>

Backup only new databases

Is there any way with SQL 2000 SP3a to backup all new databases that have
been created and have not had a full backup run yet?CR wrote:
> Is there any way with SQL 2000 SP3a to backup all new databases that
> have been created and have not had a full backup run yet?
You could probably query the msdb.backupset table for all user databases
that do not have rows in the table (check the database_name column)
--
David Gugick
Quest Software|||CR
Create a table where you store the db_id or dbnames.
insert into table (dbid,name) select dbid,name from sysdatabases
select dbid,name from sysdatabases where not exists
(select * from table where table.dbid=sysdatabases.dbid)
"CR" <a@.b.c> wrote in message news:u$MxBHhIGHA.676@.TK2MSFTNGP10.phx.gbl...
> Is there any way with SQL 2000 SP3a to backup all new databases that have
> been created and have not had a full backup run yet?
>

BACKUP LOG <database name> WITH TRUNCATE_ONLY failing

SQL Server2K, SP3a. Attempt to run in EM:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY
The error returned is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'WITHTRUNCATE_ONLY'.
The two keywords are concatenated in the error message, as shown above.
Database name works in other statements. Command is straight out of books
online! Have searched web & news groups (1 hit at a pay site).
Many thanks for any info/help.Have you tried running this in Query Analyzer? How are you running it in
EM?
"Mailing Lists" <ml010241@.berkea.com> wrote in message
news:erRGwuOlEHA.896@.TK2MSFTNGP12.phx.gbl...
> SQL Server2K, SP3a. Attempt to run in EM:
> BACKUP LOG <database_name> WITH TRUNCATE_ONLY
> The error returned is:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near
> 'WITHTRUNCATE_ONLY'.
> The two keywords are concatenated in the error message, as shown above.
> Database name works in other statements. Command is straight out of books
> online! Have searched web & news groups (1 hit at a pay site).
> Many thanks for any info/help.
>|||Below is a copy& paste from what I just executed in Query Analyzer:
BACKUP LOG pubs WITH TRUNCATE_ONLY
Can you copy your exact command you executed? Also, try executing it from Query Analyzer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mailing Lists" <ml010241@.berkea.com> wrote in message news:erRGwuOlEHA.896@.TK2MSFTNGP12.phx.gbl...
> SQL Server2K, SP3a. Attempt to run in EM:
> BACKUP LOG <database_name> WITH TRUNCATE_ONLY
> The error returned is:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
> 'WITHTRUNCATE_ONLY'.
> The two keywords are concatenated in the error message, as shown above.
> Database name works in other statements. Command is straight out of books
> online! Have searched web & news groups (1 hit at a pay site).
> Many thanks for any info/help.
>|||That did the trick (I'm ashamed to say). I considered this earlier but
wrote it off as identical to executing the SQL statement in EM--SQL is SQL,
darn it. :-) To answer your question, open the SQL view on any query and
type away.
Thank you very much!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%2305Xx6OlEHA.3816@.TK2MSFTNGP14.phx.gbl...
> Have you tried running this in Query Analyzer? How are you running it in
> EM?
>
> "Mailing Lists" <ml010241@.berkea.com> wrote in message
> news:erRGwuOlEHA.896@.TK2MSFTNGP12.phx.gbl...
> > SQL Server2K, SP3a. Attempt to run in EM:
> >
> > BACKUP LOG <database_name> WITH TRUNCATE_ONLY
> >
> > The error returned is:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near
> > 'WITHTRUNCATE_ONLY'.
> >
> > The two keywords are concatenated in the error message, as shown above.
> > Database name works in other statements. Command is straight out of
books
> > online! Have searched web & news groups (1 hit at a pay site).
> >
> > Many thanks for any info/help.
> >
> >
>|||"Mailing Lists" <ml010241@.berkea.com> wrote in message
news:erOtp%23OlEHA.3356@.TK2MSFTNGP15.phx.gbl...
> wrote it off as identical to executing the SQL statement in EM--SQL is
SQL,
> darn it. :-) To answer your question, open the SQL view on any query and
> type away.
SQL is SQL, but EM pre-parses the SQL according to its own rules when
you go into that SQL view mode. I recommend only using EM for
administrative tools (e.g. adding Agent jobs), and use QA for everything
else. It's a far superior tool.|||Yes, apparently this statement must be run in Query Analyzer. Thank you for
the quick reply.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lU5I9OlEHA.896@.TK2MSFTNGP12.phx.gbl...
> Below is a copy& paste from what I just executed in Query Analyzer:
> BACKUP LOG pubs WITH TRUNCATE_ONLY
> Can you copy your exact command you executed? Also, try executing it from
Query Analyzer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mailing Lists" <ml010241@.berkea.com> wrote in message
news:erRGwuOlEHA.896@.TK2MSFTNGP12.phx.gbl...
> > SQL Server2K, SP3a. Attempt to run in EM:
> >
> > BACKUP LOG <database_name> WITH TRUNCATE_ONLY
> >
> > The error returned is:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near
> > 'WITHTRUNCATE_ONLY'.
> >
> > The two keywords are concatenated in the error message, as shown above.
> > Database name works in other statements. Command is straight out of
books
> > online! Have searched web & news groups (1 hit at a pay site).
> >
> > Many thanks for any info/help.
> >
> >
>|||After this "amusing" detour I couldn't agree more--thanks again!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OHYMUAPlEHA.952@.TK2MSFTNGP14.phx.gbl...
> "Mailing Lists" <ml010241@.berkea.com> wrote in message
> news:erOtp%23OlEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > wrote it off as identical to executing the SQL statement in EM--SQL is
> SQL,
> > darn it. :-) To answer your question, open the SQL view on any query
and
> > type away.
> SQL is SQL, but EM pre-parses the SQL according to its own rules when
> you go into that SQL view mode. I recommend only using EM for
> administrative tools (e.g. adding Agent jobs), and use QA for everything
> else. It's a far superior tool.
>

Backup issue

Hello,
Windows 2000 SP4 Fully Patched
SQL Server 2000 SP3a + MS03-031
When I create a new database and try to make a differential backup without
ever having made a (full) database backup it fails. This is OK and is what
I expect and want. However if I delete the backups en use sp_delete_backup
to remove the entries from msdb it allows the creation of differential
backups even if there is no record of there being a full backup in the msdb
database. Probably in the database itself their is a "bit" that gets set
but I find it dangerous and need to make sure that in the latter case a full
backup is requierd. Any hints?
Thank you.Your differential backup is useless without a full backup.
The differential backup contains only the changes (the changed extents)
since the last full backup.
A full backup is always your starting point. from there you can use
differential, file/filegroup or transaction log backups to capture the
changes to the DB.
You should regularly make full backups so that if you need to restore the
DB, the restore process will be shorter.
HTH
"John Koswalski" <john.koswalski@.shell.nl> wrote in message
news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Windows 2000 SP4 Fully Patched
> SQL Server 2000 SP3a + MS03-031
> When I create a new database and try to make a differential backup without
> ever having made a (full) database backup it fails. This is OK and is
what
> I expect and want. However if I delete the backups en use
sp_delete_backup
> to remove the entries from msdb it allows the creation of differential
> backups even if there is no record of there being a full backup in the
msdb
> database. Probably in the database itself their is a "bit" that gets set
> but I find it dangerous and need to make sure that in the latter case a
full
> backup is requierd. Any hints?
> Thank you.
>|||Indeed Amy,
That's why I'm surprised that when deleting all information using
sp_delete_backuphistory it still allows me to make a differential backup. I
wonder why that is, since it is indeed useless!
"Amy" <l.a@.usa.com> wrote in message
news:ONxmud1YDHA.440@.tk2msftngp13.phx.gbl...
> Your differential backup is useless without a full backup.
> The differential backup contains only the changes (the changed extents)
> since the last full backup.
> A full backup is always your starting point. from there you can use
> differential, file/filegroup or transaction log backups to capture the
> changes to the DB.
> You should regularly make full backups so that if you need to restore the
> DB, the restore process will be shorter.
> HTH
> "John Koswalski" <john.koswalski@.shell.nl> wrote in message
> news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > Windows 2000 SP4 Fully Patched
> > SQL Server 2000 SP3a + MS03-031
> >
> > When I create a new database and try to make a differential backup
without
> > ever having made a (full) database backup it fails. This is OK and is
> what
> > I expect and want. However if I delete the backups en use
> sp_delete_backup
> > to remove the entries from msdb it allows the creation of differential
> > backups even if there is no record of there being a full backup in the
> msdb
> > database. Probably in the database itself their is a "bit" that gets
set
> > but I find it dangerous and need to make sure that in the latter case a
> full
> > backup is requierd. Any hints?
> >
> > Thank you.
> >
> >
>|||Deleting the backup history does not necessarily delete
the actual full database backup itself. A sequence
number is maintanied that shows whether a data page has
been modified since the last full backup was performed,
and if the page has been modified, it (and it's entire
extent) is included in the following differential backup.
This mechanism is separate from the backup history, which
is used primarily to make it easier to restore specific
backup sessions in the proper order.
A situation just as problematic is suppose that you do
not delete the backup history, but you do delete or
discard the full backup itself. You still can't perform
a restore with just the differential backup, although
clearly you can perform the backup.
I realize this doesn't help much, but it is smoething to
bear in mind. The system will allow the differential
backup, and it is up to the dba to ensure that the
precedign full database backup will be available in the
event that a restore is needed. Look at it this way:
If you lose the preceding full database backup, it
doesn't matter whether system allows you to perform the
differential backup or not, because you have no restore
capability either way.
Just some thoughts,
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Indeed Amy,
>That's why I'm surprised that when deleting all
information using
>sp_delete_backuphistory it still allows me to make a
differential backup. I
>wonder why that is, since it is indeed useless!
>"Amy" <l.a@.usa.com> wrote in message
>news:ONxmud1YDHA.440@.tk2msftngp13.phx.gbl...
>> Your differential backup is useless without a full
backup.
>> The differential backup contains only the changes (the
changed extents)
>> since the last full backup.
>> A full backup is always your starting point. from
there you can use
>> differential, file/filegroup or transaction log
backups to capture the
>> changes to the DB.
>> You should regularly make full backups so that if you
need to restore the
>> DB, the restore process will be shorter.
>> HTH
>> "John Koswalski" <john.koswalski@.shell.nl> wrote in
message
>> news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
>> > Hello,
>> >
>> > Windows 2000 SP4 Fully Patched
>> > SQL Server 2000 SP3a + MS03-031
>> >
>> > When I create a new database and try to make a
differential backup
>without
>> > ever having made a (full) database backup it fails.
This is OK and is
>> what
>> > I expect and want. However if I delete the backups
en use
>> sp_delete_backup
>> > to remove the entries from msdb it allows the
creation of differential
>> > backups even if there is no record of there being a
full backup in the
>> msdb
>> > database. Probably in the database itself their is
a "bit" that gets
>set
>> > but I find it dangerous and need to make sure that
in the latter case a
>> full
>> > backup is requierd. Any hints?
>> >
>> > Thank you.
>> >
>> >
>>
>
>.
>|||Hello Matthew,
You're right. But there is, in my opinion a little difference, I choose to
delete the history ... I control it or I like to believe I control it. In
the end, when you can't restore a backup it doesn't make a difference ...
practically ...
Thanks for your thoughts
"Matthew Bando" <BandoM@.CSCTechnologies.com> wrote in message
news:03f801c3635e$7b0051c0$a101280a@.phx.gbl...
> Deleting the backup history does not necessarily delete
> the actual full database backup itself. A sequence
> number is maintanied that shows whether a data page has
> been modified since the last full backup was performed,
> and if the page has been modified, it (and it's entire
> extent) is included in the following differential backup.
> This mechanism is separate from the backup history, which
> is used primarily to make it easier to restore specific
> backup sessions in the proper order.
> A situation just as problematic is suppose that you do
> not delete the backup history, but you do delete or
> discard the full backup itself. You still can't perform
> a restore with just the differential backup, although
> clearly you can perform the backup.
> I realize this doesn't help much, but it is smoething to
> bear in mind. The system will allow the differential
> backup, and it is up to the dba to ensure that the
> precedign full database backup will be available in the
> event that a restore is needed. Look at it this way:
> If you lose the preceding full database backup, it
> doesn't matter whether system allows you to perform the
> differential backup or not, because you have no restore
> capability either way.
> Just some thoughts,
> Matthew Bando
> BandoM@.CSCTechnologies.com
>
> >--Original Message--
> >Indeed Amy,
> >
> >That's why I'm surprised that when deleting all
> information using
> >sp_delete_backuphistory it still allows me to make a
> differential backup. I
> >wonder why that is, since it is indeed useless!
> >
> >"Amy" <l.a@.usa.com> wrote in message
> >news:ONxmud1YDHA.440@.tk2msftngp13.phx.gbl...
> >> Your differential backup is useless without a full
> backup.
> >> The differential backup contains only the changes (the
> changed extents)
> >> since the last full backup.
> >> A full backup is always your starting point. from
> there you can use
> >> differential, file/filegroup or transaction log
> backups to capture the
> >> changes to the DB.
> >> You should regularly make full backups so that if you
> need to restore the
> >> DB, the restore process will be shorter.
> >>
> >> HTH
> >>
> >> "John Koswalski" <john.koswalski@.shell.nl> wrote in
> message
> >> news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
> >> > Hello,
> >> >
> >> > Windows 2000 SP4 Fully Patched
> >> > SQL Server 2000 SP3a + MS03-031
> >> >
> >> > When I create a new database and try to make a
> differential backup
> >without
> >> > ever having made a (full) database backup it fails.
> This is OK and is
> >> what
> >> > I expect and want. However if I delete the backups
> en use
> >> sp_delete_backup
> >> > to remove the entries from msdb it allows the
> creation of differential
> >> > backups even if there is no record of there being a
> full backup in the
> >> msdb
> >> > database. Probably in the database itself their is
> a "bit" that gets
> >set
> >> > but I find it dangerous and need to make sure that
> in the latter case a
> >> full
> >> > backup is requierd. Any hints?
> >> >
> >> > Thank you.
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >|||I understand the control issue entirely. I typically
perform restores from devices rather than using the
sessions that the backup history maintains. I guess
that's my way of maintaining the illusion of control! :-)
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello Matthew,
>You're right. But there is, in my opinion a little
difference, I choose to
>delete the history ... I control it or I like to believe
I control it. In
>the end, when you can't restore a backup it doesn't make
a difference ...
>practically ...
>Thanks for your thoughts
>
>"Matthew Bando" <BandoM@.CSCTechnologies.com> wrote in
message
>news:03f801c3635e$7b0051c0$a101280a@.phx.gbl...
>> Deleting the backup history does not necessarily delete
>> the actual full database backup itself. A sequence
>> number is maintanied that shows whether a data page has
>> been modified since the last full backup was performed,
>> and if the page has been modified, it (and it's entire
>> extent) is included in the following differential
backup.
>> This mechanism is separate from the backup history,
which
>> is used primarily to make it easier to restore specific
>> backup sessions in the proper order.
>> A situation just as problematic is suppose that you do
>> not delete the backup history, but you do delete or
>> discard the full backup itself. You still can't
perform
>> a restore with just the differential backup, although
>> clearly you can perform the backup.
>> I realize this doesn't help much, but it is smoething
to
>> bear in mind. The system will allow the differential
>> backup, and it is up to the dba to ensure that the
>> precedign full database backup will be available in the
>> event that a restore is needed. Look at it this way:
>> If you lose the preceding full database backup, it
>> doesn't matter whether system allows you to perform the
>> differential backup or not, because you have no restore
>> capability either way.
>> Just some thoughts,
>> Matthew Bando
>> BandoM@.CSCTechnologies.com
>>
>> >--Original Message--
>> >Indeed Amy,
>> >
>> >That's why I'm surprised that when deleting all
>> information using
>> >sp_delete_backuphistory it still allows me to make a
>> differential backup. I
>> >wonder why that is, since it is indeed useless!
>> >
>> >"Amy" <l.a@.usa.com> wrote in message
>> >news:ONxmud1YDHA.440@.tk2msftngp13.phx.gbl...
>> >> Your differential backup is useless without a full
>> backup.
>> >> The differential backup contains only the changes
(the
>> changed extents)
>> >> since the last full backup.
>> >> A full backup is always your starting point. from
>> there you can use
>> >> differential, file/filegroup or transaction log
>> backups to capture the
>> >> changes to the DB.
>> >> You should regularly make full backups so that if
you
>> need to restore the
>> >> DB, the restore process will be shorter.
>> >>
>> >> HTH
>> >>
>> >> "John Koswalski" <john.koswalski@.shell.nl> wrote in
>> message
>> >> news:%23sUHAp0YDHA.1872@.TK2MSFTNGP12.phx.gbl...
>> >> > Hello,
>> >> >
>> >> > Windows 2000 SP4 Fully Patched
>> >> > SQL Server 2000 SP3a + MS03-031
>> >> >
>> >> > When I create a new database and try to make a
>> differential backup
>> >without
>> >> > ever having made a (full) database backup it
fails.
>> This is OK and is
>> >> what
>> >> > I expect and want. However if I delete the
backups
>> en use
>> >> sp_delete_backup
>> >> > to remove the entries from msdb it allows the
>> creation of differential
>> >> > backups even if there is no record of there being
a
>> full backup in the
>> >> msdb
>> >> > database. Probably in the database itself their
is
>> a "bit" that gets
>> >set
>> >> > but I find it dangerous and need to make sure that
>> in the latter case a
>> >> full
>> >> > backup is requierd. Any hints?
>> >> >
>> >> > Thank you.
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>