difference between 'Backup' and DB maint. plan backup?

hi.
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:
> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> > hi.
> >
> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> > box.
> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> >
> > is setting up a backup using a maintenance plan the same as setting up a
> > full backup under the 'Backup' tab?
> >
> > thanks.
>
>|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>> Hi,
>> Backup tab is just creating the Logical Backup device. What you are doing is
>> exacltly the right procedure to take Full database
>> and Transaction log backup.
>> Looks like you are performing backup only for non system database backup,
>> But i recomend you to do the backup for all the system
>> databases as well. This will help you in the eve of crash.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>>
>> "mb" <mb@.discussions.microsoft.com> wrote in message
>> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>> > hi.
>> >
>> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
>> > box.
>> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
>> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
>> >
>> > is setting up a backup using a maintenance plan the same as setting up a
>> > full backup under the 'Backup' tab?
>> >
>> > thanks.
>>|||Maint plan will not add the NO_TRUNCATE option for the backup log command. This option is what
empties the log and when you uncheck in the backup dialog, this option is added. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special purpose and is only
used for disaster scenarios.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>> Hi,
>> Backup tab is just creating the Logical Backup device. What you are doing is
>> exacltly the right procedure to take Full database
>> and Transaction log backup.
>> Looks like you are performing backup only for non system database backup,
>> But i recomend you to do the backup for all the system
>> databases as well. This will help you in the eve of crash.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>>
>> "mb" <mb@.discussions.microsoft.com> wrote in message
>> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>> > hi.
>> >
>> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
>> > box.
>> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
>> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
>> >
>> > is setting up a backup using a maintenance plan the same as setting up a
>> > full backup under the 'Backup' tab?
>> >
>> > thanks.
>>

difference between 'Backup' and DB maint. plan backup?

hi.
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:

> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing
is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>
>|||Maint plan will not add the NO_TRUNCATE option for the backup log command. T
his option is what
empties the log and when you uncheck in the backup dialog, this option is ad
ded. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special
purpose and is only
used for disaster scenarios.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/in...no_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>

difference between 'Backup' and DB maint. plan backup?

hi.
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.
Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.
|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:

> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>
>
|||Maint plan will not add the NO_TRUNCATE option for the backup log command. This option is what
empties the log and when you uncheck in the backup dialog, this option is added. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special purpose and is only
used for disaster scenarios.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:

Diff Backups - Urgent pls help

I have a full Backup and a diff backup on a SQL 7 database
I have restored the full backup with NORECOVERY option sucessfully in a SQL
2000 server
When i try to restore the diff backups its gives me a error
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'Z:\MS SQL
SERVER\Backup\AVAWEB\AVAWEB_DIFF_20050111_203101.b ak' to database 'AVAWEB'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
-- Here is the command i am running
RESTORE DATABASE AVAWEB
FROM DISK = 'Z:\MS SQL SERVER\Backup\AVAWEB\AVAWEB_DIFF_20050111_203101.b ak'
WITH FILE=1, NORECOVERY,
MOVE 'AvaWeb_Data' to 'Z:\MS SQL SERVER\data\avaweb_data.mdf',
MOVE 'AvaWeb_Log' to 'Z:\MS SQL SERVER\log\avaweb_log.ldf'
Are you certain that you didn't perform a database backup between that db backup and the diff backup
you are trying to restore. I'd check in the backup history tables in msdb just to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:F22E8045-3B2D-429C-BB59-754079897F49@.microsoft.com...
>I have a full Backup and a diff backup on a SQL 7 database
> I have restored the full backup with NORECOVERY option sucessfully in a SQL
> 2000 server
> When i try to restore the diff backups its gives me a error
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'Z:\MS SQL
> SERVER\Backup\AVAWEB\AVAWEB_DIFF_20050111_203101.b ak' to database 'AVAWEB'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> -- Here is the command i am running
> RESTORE DATABASE AVAWEB
> FROM DISK = 'Z:\MS SQL SERVER\Backup\AVAWEB\AVAWEB_DIFF_20050111_203101.b ak'
> WITH FILE=1, NORECOVERY,
> MOVE 'AvaWeb_Data' to 'Z:\MS SQL SERVER\data\avaweb_data.mdf',
> MOVE 'AvaWeb_Log' to 'Z:\MS SQL SERVER\log\avaweb_log.ldf'
>
sql

Diff Backups - Urgent pls help

I have a full Backup and a diff backup on a SQL 7 database
I have restored the full backup with NORECOVERY option sucessfully in a SQL
2000 server
When i try to restore the diff backups its gives me a error
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'Z:\MS SQL
SERVER\Backup\AVAWEB\AVAWEB_DIFF_20050111_203101.bak' to database 'AVAWEB'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
-- Here is the command i am running
RESTORE DATABASE AVAWEB
FROM DISK = 'Z:\MS SQL SERVER\Backup\AVAWEB\AVAWEB_DIFF_20050111_203101.bak'
WITH FILE=1, NORECOVERY,
MOVE 'AvaWeb_Data' to 'Z:\MS SQL SERVER\data\avaweb_data.mdf',
MOVE 'AvaWeb_Log' to 'Z:\MS SQL SERVER\log\avaweb_log.ldf'Are you certain that you didn't perform a database backup between that db backup and the diff backup
you are trying to restore. I'd check in the backup history tables in msdb just to be certain.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:F22E8045-3B2D-429C-BB59-754079897F49@.microsoft.com...
>I have a full Backup and a diff backup on a SQL 7 database
> I have restored the full backup with NORECOVERY option sucessfully in a SQL
> 2000 server
> When i try to restore the diff backups its gives me a error
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'Z:\MS SQL
> SERVER\Backup\AVAWEB\AVAWEB_DIFF_20050111_203101.bak' to database 'AVAWEB'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> -- Here is the command i am running
> RESTORE DATABASE AVAWEB
> FROM DISK = 'Z:\MS SQL SERVER\Backup\AVAWEB\AVAWEB_DIFF_20050111_203101.bak'
> WITH FILE=1, NORECOVERY,
> MOVE 'AvaWeb_Data' to 'Z:\MS SQL SERVER\data\avaweb_data.mdf',
> MOVE 'AvaWeb_Log' to 'Z:\MS SQL SERVER\log\avaweb_log.ldf'
>

Diff Backups - Urgent pls help

I have a full Backup and a diff backup on a SQL 7 database
I have restored the full backup with NORECOVERY option sucessfully in a SQL
2000 server
When i try to restore the diff backups its gives me a error
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'Z:\MS SQL
SERVER\Backup\AVAWEB\AVAWEB_DIFF_2005011
1_203101.bak' to database 'AVAWEB'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
-- Here is the command i am running
RESTORE DATABASE AVAWEB
FROM DISK = 'Z:\MS SQL SERVER\Backup\AVAWEB\AVAWEB_DIFF_2005011
1_203101.bak'
WITH FILE=1, NORECOVERY,
MOVE 'AvaWeb_Data' to 'Z:\MS SQL SERVER\data\avaweb_data.mdf',
MOVE 'AvaWeb_Log' to 'Z:\MS SQL SERVER\log\avaweb_log.ldf'Are you certain that you didn't perform a database backup between that db ba
ckup and the diff backup
you are trying to restore. I'd check in the backup history tables in msdb ju
st to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:F22E8045-3B2D-429C-BB59-754079897F49@.microsoft.com...
>I have a full Backup and a diff backup on a SQL 7 database
> I have restored the full backup with NORECOVERY option sucessfully in a SQ
L
> 2000 server
> When i try to restore the diff backups its gives me a error
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'Z:\MS SQL
> SERVER\Backup\AVAWEB\AVAWEB_DIFF_2005011
1_203101.bak' to database 'AVAWEB'
.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> -- Here is the command i am running
> RESTORE DATABASE AVAWEB
> FROM DISK = 'Z:\MS SQL SERVER\Backup\AVAWEB\AVAWEB_DIFF_2005011
1_203101.ba
k'
> WITH FILE=1, NORECOVERY,
> MOVE 'AvaWeb_Data' to 'Z:\MS SQL SERVER\data\avaweb_data.mdf',
> MOVE 'AvaWeb_Log' to 'Z:\MS SQL SERVER\log\avaweb_log.ldf'
>

Developer Database Snapshots

I'm developing tool for developers to create snapshots (i.e. backups) from
different states of SQL Server databases. Later when required for unit and/or
stress testing or code profiling developer could quickly restore database
snapshots.
This tool is to be very simple and at first only to support SQL Server 2005
and later to older versions. Also propably first version would be rather
limited (as my time) including only one database and hopefully later
expanding to support more databases and more fine-tuning of backup/restore
options - since there are some. Also the snapshot tool would work easy "oops"
-tool if user's routine accidentally deletes all rows in a database it could
be easily backed up before catastrophy happens.
I'm thinking of doing this as a Visual Studio.NET 2005 Add-In but including
external API for unit testers and others to easily connect to this tool from
external programs.
Tell me what do you think - is this kind of tool worth doing ?
"Tommi" <Tommi@.discussions.microsoft.com> wrote in message
news:2ECEE6F8-AD03-4245-A0A5-E35F425E6810@.microsoft.com...
> I'm developing tool for developers to create snapshots (i.e. backups) from
> different states of SQL Server databases. Later when required for unit
> and/or
> stress testing or code profiling developer could quickly restore database
> snapshots.
> Tell me what do you think - is this kind of tool worth doing ?
Not if you use the word "snapshot". There are already at least three
things in SQL Server 2005 that use that name: Database snapshots (i.e. not
backups, but snapshots -- very different), snapshot isolation level, and
snapshot replication.
Plus, there are other types of snapshots available, such as SAN
snapshots. Adding yet another snapshot to an already overused pool is going
to be extremely confusing for your potential users.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||You are right - actually the work I'm using is DevShot, which refers to kind
of developer snapshot as a backup which contains the database state at
certain point of time. I already created a prebeta of the software in
CodeProject (http://www.codeproject.com/useritems/DevShot.asp). Check it out.
This would be probably easier to achieve if normal SQL Server 2005 would
contain the database snapshots that they employ only in enterprise versions,
but even normal fullbackup to a file does the trick quite nicely.
"Adam Machanic" wrote:

> "Tommi" <Tommi@.discussions.microsoft.com> wrote in message
> news:2ECEE6F8-AD03-4245-A0A5-E35F425E6810@.microsoft.com...
> Not if you use the word "snapshot". There are already at least three
> things in SQL Server 2005 that use that name: Database snapshots (i.e. not
> backups, but snapshots -- very different), snapshot isolation level, and
> snapshot replication.
> Plus, there are other types of snapshots available, such as SAN
> snapshots. Adding yet another snapshot to an already overused pool is going
> to be extremely confusing for your potential users.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>