Difference between Partial Backup (SQL 2005) and File Group Backup

What is the difference between "Files aned File Groups" backup and Partial Backup?

Looks like both are same.. Please comment.

A File Group backup is a backup which only saves the data from the named file group(s).

A Partial backup is also a backup which only saves the data from some file groups, but it usually refers to the syntax:

BACKUP DATABASE foo READ_WRITE_FILEGROUPS TO....

which backs up only the primary filegroup and any read-write filegroups.

If you have a good backup of the read-only filegroups, you don't need to save them at every backup, which limits volume.

So, a Partial backup is a special case of a File Group backup.

Difference between NORECOVERY and NO_TRUNCATE

Hi
Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
with options for BACKUP LOG statement.NO_TRUNCATE
Specifies that the log not be truncated and causes the Database Engine to
attempt the backup regardless of the state of the database. Consequently, a
backup taken with NO_TRUNCATE might have incomplete metadata. This option
allows backing up the log in situations where the database is damaged.
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state.
NORECOVERY is useful when failing over to a secondary database or when
saving the tail of the log before a RESTORE operation.
For more info about BACKUP command go to the following link:
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> Hi
> Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> with options for BACKUP LOG statement.
>|||Hi Ekrem,
Can you please explain the difference with an example.
"Ekrem ?nsoy" wrote:

> NO_TRUNCATE
> Specifies that the log not be truncated and causes the Database Engine to
> attempt the backup regardless of the state of the database. Consequently,
a
> backup taken with NO_TRUNCATE might have incomplete metadata. This option
> allows backing up the log in situations where the database is damaged.
> NORECOVERY
> Backs up the tail of the log and leaves the database in the RESTORING stat
e.
> NORECOVERY is useful when failing over to a secondary database or when
> saving the tail of the log before a RESTORE operation.
> For more info about BACKUP command go to the following link:
> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
> --
> Ekrem ?nsoy
>
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
>|||There are three ways of Recovery State:
1-RECOVERY
2-NORECOVERY
3-STANDBY
If you restore your database in recovery, then your database will be online
and your users will be able to perform any operation against it.
If you restore your database in NORECOVERY then your users will not be able
to query your database.
If you restore your database in STANDBY then your users will be able to use
only SELECT against it.
Every recovery state has a purpose. You should read about this from BOL for
more information.
For example NORECOVERY and STANDBY states are being used in Log Shipping. If
you use NORECOVERY for your Log Shipping solution then your users will not
be able to query against the database on your secondary server. If you
restore your secondary database then your users will be able to query (only
SELECT) against it however your users will be disconnected each time for the
new transactions to be applied to the secondary database.
Example for no_truncate:
BACKUP LOG AdventureWorks TO DISK = N'C:\test\tiesto.bak' WITH NO_TRUNCATE
This hint is used for inaccessible database. Tibor has an article about
this, you can read it for more info:
http://www.karaszi.com/SQLServer/in...no_truncate.asp
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:AFC8BD66-6474-4837-A80A-B9D3FF0068E3@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> Can you please explain the difference with an example.
> "Ekrem ?nsoy" wrote:
>

Difference between NORECOVERY and NO_TRUNCATE

Hi
Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
with options for BACKUP LOG statement.NO_TRUNCATE
Specifies that the log not be truncated and causes the Database Engine to
attempt the backup regardless of the state of the database. Consequently, a
backup taken with NO_TRUNCATE might have incomplete metadata. This option
allows backing up the log in situations where the database is damaged.
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state.
NORECOVERY is useful when failing over to a secondary database or when
saving the tail of the log before a RESTORE operation.
For more info about BACKUP command go to the following link:
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
--
Ekrem Ã?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> Hi
> Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> with options for BACKUP LOG statement.
>|||Hi Ekrem,
Can you please explain the difference with an example.
"Ekrem Ã?nsoy" wrote:
> NO_TRUNCATE
> Specifies that the log not be truncated and causes the Database Engine to
> attempt the backup regardless of the state of the database. Consequently, a
> backup taken with NO_TRUNCATE might have incomplete metadata. This option
> allows backing up the log in situations where the database is damaged.
> NORECOVERY
> Backs up the tail of the log and leaves the database in the RESTORING state.
> NORECOVERY is useful when failing over to a secondary database or when
> saving the tail of the log before a RESTORE operation.
> For more info about BACKUP command go to the following link:
> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
> --
> Ekrem Ã?nsoy
>
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> > Hi
> >
> > Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> > with options for BACKUP LOG statement.
> >
> >
>|||There are three ways of Recovery State:
1-RECOVERY
2-NORECOVERY
3-STANDBY
If you restore your database in recovery, then your database will be online
and your users will be able to perform any operation against it.
If you restore your database in NORECOVERY then your users will not be able
to query your database.
If you restore your database in STANDBY then your users will be able to use
only SELECT against it.
Every recovery state has a purpose. You should read about this from BOL for
more information.
For example NORECOVERY and STANDBY states are being used in Log Shipping. If
you use NORECOVERY for your Log Shipping solution then your users will not
be able to query against the database on your secondary server. If you
restore your secondary database then your users will be able to query (only
SELECT) against it however your users will be disconnected each time for the
new transactions to be applied to the secondary database.
Example for no_truncate:
BACKUP LOG AdventureWorks TO DISK = N'C:\test\tiesto.bak' WITH NO_TRUNCATE
This hint is used for inaccessible database. Tibor has an article about
this, you can read it for more info:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Ekrem Ã?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:AFC8BD66-6474-4837-A80A-B9D3FF0068E3@.microsoft.com...
> Hi Ekrem,
> Can you please explain the difference with an example.
> "Ekrem Ã?nsoy" wrote:
>> NO_TRUNCATE
>> Specifies that the log not be truncated and causes the Database Engine to
>> attempt the backup regardless of the state of the database. Consequently,
>> a
>> backup taken with NO_TRUNCATE might have incomplete metadata. This option
>> allows backing up the log in situations where the database is damaged.
>> NORECOVERY
>> Backs up the tail of the log and leaves the database in the RESTORING
>> state.
>> NORECOVERY is useful when failing over to a secondary database or when
>> saving the tail of the log before a RESTORE operation.
>> For more info about BACKUP command go to the following link:
>> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
>> --
>> Ekrem Ã?nsoy
>>
>> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
>> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
>> > Hi
>> >
>> > Can some body explain me the difference between NORECOVERY and
>> > NO_TRUNCATE
>> > with options for BACKUP LOG statement.
>> >
>> >sql

Difference between NORECOVERY and NO_TRUNCATE

Hi
Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
with options for BACKUP LOG statement.
NO_TRUNCATE
Specifies that the log not be truncated and causes the Database Engine to
attempt the backup regardless of the state of the database. Consequently, a
backup taken with NO_TRUNCATE might have incomplete metadata. This option
allows backing up the log in situations where the database is damaged.
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state.
NORECOVERY is useful when failing over to a secondary database or when
saving the tail of the log before a RESTORE operation.
For more info about BACKUP command go to the following link:
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
> Hi
> Can some body explain me the difference between NORECOVERY and NO_TRUNCATE
> with options for BACKUP LOG statement.
>
|||Hi Ekrem,
Can you please explain the difference with an example.
"Ekrem ?nsoy" wrote:

> NO_TRUNCATE
> Specifies that the log not be truncated and causes the Database Engine to
> attempt the backup regardless of the state of the database. Consequently, a
> backup taken with NO_TRUNCATE might have incomplete metadata. This option
> allows backing up the log in situations where the database is damaged.
> NORECOVERY
> Backs up the tail of the log and leaves the database in the RESTORING state.
> NORECOVERY is useful when failing over to a secondary database or when
> saving the tail of the log before a RESTORE operation.
> For more info about BACKUP command go to the following link:
> http://msdn2.microsoft.com/en-us/library/ms186865.aspx
> --
> Ekrem ?nsoy
>
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:58439EBF-9080-484F-A5DD-B9856BD0913E@.microsoft.com...
>
|||There are three ways of Recovery State:
1-RECOVERY
2-NORECOVERY
3-STANDBY
If you restore your database in recovery, then your database will be online
and your users will be able to perform any operation against it.
If you restore your database in NORECOVERY then your users will not be able
to query your database.
If you restore your database in STANDBY then your users will be able to use
only SELECT against it.
Every recovery state has a purpose. You should read about this from BOL for
more information.
For example NORECOVERY and STANDBY states are being used in Log Shipping. If
you use NORECOVERY for your Log Shipping solution then your users will not
be able to query against the database on your secondary server. If you
restore your secondary database then your users will be able to query (only
SELECT) against it however your users will be disconnected each time for the
new transactions to be applied to the secondary database.
Example for no_truncate:
BACKUP LOG AdventureWorks TO DISK = N'C:\test\tiesto.bak' WITH NO_TRUNCATE
This hint is used for inaccessible database. Tibor has an article about
this, you can read it for more info:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
Ekrem ?nsoy
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:AFC8BD66-6474-4837-A80A-B9D3FF0068E3@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> Can you please explain the difference with an example.
> "Ekrem ?nsoy" wrote:

Difference between Backup/Restore & Attach/Detach

Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>

Difference between Backup/Restore & Attach/Detach

Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Can someone please clarify the difference between Detach / Attach of
>> MDF/LDF files versus the Backup / Restore of backup to device/file
>> feature? Which would be ideal for restores of actual databases?
>> Thank you.
>|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
>> recovery. Databases can be backed up while online. The backup file size
>> is usually smaller than the database files since only used pages are
>> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can
>> reduce potential data loss by performing transaction log backups.
>> Detaching a database removes the database from SQL Server while leaving
>> the physical database files intact. This allows you to rename or move
>> the physical files and then re-attach. Although one could perform cold
>> backups using this technique, detach/attach isn't really intended to be
>> used as a backup/recovery process.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "KP" <kp@.msn.com> wrote in message
>> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Can someone please clarify the difference between Detach / Attach of
>> MDF/LDF files versus the Backup / Restore of backup to device/file
>> feature? Which would be ideal for restores of actual databases?
>> Thank you.
>>
>

Difference between Backup/Restore & Attach/Detach

Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.
Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>
|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>
|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
sql

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:

Difference between backing up to a file and a logical backup device?

What's is the between backing up a database to a file and a logical backup device?

If I point the logical backup device to a file on the filesystem, it's same as backing up to a file? isn't?

Thanks

Yep, that is right, the backup device just allows you to change the underlying target for the backup without changing the backup jobs themselves.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Difference between Attach / Detach and Backup / Restore

I am new to SQL Server and we are using SQL Server 2005.
We recently upgrade a finance system by consultant. He detaches the
database from the old server (Running SQL Server 2000), copy the mdf file
across and attached in the new SQL Server 2005.
I would like to know is there any benefit for detaching / attaching than
make a backup and restore on the SQL Server 2005 ?
Someone has mentioned that we have to recreate SQL Server Login though the
Database Login exists on the database copied across. Is there any tools /
reference that makes it easy ?
ThanksPeter
It is just faster.
Search on interenet for 'sp_help_revlogin' for SQL Server 2005
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>|||I have explained the step to move the logins as well here:
http://sqlguy.it-craft.dk
As well as links to the stored procedures for both 2000 and 2005
Regards
Rasmus Glibstrup
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>sql

Difference between Attach / Detach and Backup / Restore

I am new to SQL Server and we are using SQL Server 2005.
We recently upgrade a finance system by consultant. He detaches the
database from the old server (Running SQL Server 2000), copy the mdf file
across and attached in the new SQL Server 2005.
I would like to know is there any benefit for detaching / attaching than
make a backup and restore on the SQL Server 2005 ?
Someone has mentioned that we have to recreate SQL Server Login though the
Database Login exists on the database copied across. Is there any tools /
reference that makes it easy ?
ThanksPeter
It is just faster.
Search on interenet for 'sp_help_revlogin' for SQL Server 2005
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>|||I have explained the step to move the logins as well here:
http://sqlguy.it-craft.dk
As well as links to the stored procedures for both 2000 and 2005
Regards
Rasmus Glibstrup
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZmOrl6wHHA.600@.TK2MSFTNGP05.phx.gbl...
>I am new to SQL Server and we are using SQL Server 2005.
> We recently upgrade a finance system by consultant. He detaches the
> database from the old server (Running SQL Server 2000), copy the mdf file
> across and attached in the new SQL Server 2005.
> I would like to know is there any benefit for detaching / attaching than
> make a backup and restore on the SQL Server 2005 ?
> Someone has mentioned that we have to recreate SQL Server Login though the
> Database Login exists on the database copied across. Is there any tools /
> reference that makes it easy ?
> Thanks
>

Diff between SQL server and SQL server Personal edition

Hi All,
I would like to install SQL server 2000 personal edition
on a workstation as a backup to my server. (disaster
recovery scenario) I run a backup to this machine already
every 30 minutes, all I would have to do is turn on SQL
services, restore the latest backup and I am back up and
running within a few minutes. The workstaion has a 2 GIG
processor and 256 Ram which I am planning on upgrading to
512.
Are there any downsides to running 6 clients hitting this
personal edition server? Is the PE the same as the server
edition? Does the personal edition have limitations that
the server edition does not?
Thanks,
GP
Hi
Just remember that a worstation edition of Windows (XP or 2000 Pro) both
have a limit of 10 concurrent client connections.
Regards
Mike
"GeorgeP" wrote:

> Hi All,
> I would like to install SQL server 2000 personal edition
> on a workstation as a backup to my server. (disaster
> recovery scenario) I run a backup to this machine already
> every 30 minutes, all I would have to do is turn on SQL
> services, restore the latest backup and I am back up and
> running within a few minutes. The workstaion has a 2 GIG
> processor and 256 Ram which I am planning on upgrading to
> 512.
> Are there any downsides to running 6 clients hitting this
> personal edition server? Is the PE the same as the server
> edition? Does the personal edition have limitations that
> the server edition does not?
> Thanks,
> GP
>
|||Thanks,
Yes I know Windows prof. has a conection limitation, but
are there any SQL limitations running on a workstation?
Thanks again for your input.
GP
|||The technical differences between editions are listed in Books Online (see the architecture
section). I suggest you call MS to make sure you aren't violating the licensing agreement:
To speak to someone regarding licensing:
You can call 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 6:00 P.M. (PST) to speak directly to a Microsoft licensing
specialist for licensing problem. Worldwide customers can use the Guide to
Worldwide Microsoft Licensing Sites
http://www.microsoft.com/licensing/index/worldwide.asp to find contact
information in their locations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"georgeP" <anonymous@.discussions.microsoft.com> wrote in message
news:493c01c49f24$18fc1830$a601280a@.phx.gbl...
> Thanks,
> Yes I know Windows prof. has a conection limitation, but
> are there any SQL limitations running on a workstation?
> Thanks again for your input.
> GP

Diff between SQL server and SQL server Personal edition

Hi All,
I would like to install SQL server 2000 personal edition
on a workstation as a backup to my server. (disaster
recovery scenario) I run a backup to this machine already
every 30 minutes, all I would have to do is turn on SQL
services, restore the latest backup and I am back up and
running within a few minutes. The workstaion has a 2 GIG
processor and 256 Ram which I am planning on upgrading to
512.
Are there any downsides to running 6 clients hitting this
personal edition server? Is the PE the same as the server
edition? Does the personal edition have limitations that
the server edition does not?
Thanks,
GPThanks,
Yes I know Windows prof. has a conection limitation, but
are there any SQL limitations running on a workstation?
Thanks again for your input.
GP|||The technical differences between editions are listed in Books Online (see the architecture
section). I suggest you call MS to make sure you aren't violating the licensing agreement:
To speak to someone regarding licensing:
You can call 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 6:00 P.M. (PST) to speak directly to a Microsoft licensing
specialist for licensing problem. Worldwide customers can use the Guide to
Worldwide Microsoft Licensing Sites
http://www.microsoft.com/licensing/index/worldwide.asp to find contact
information in their locations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"georgeP" <anonymous@.discussions.microsoft.com> wrote in message
news:493c01c49f24$18fc1830$a601280a@.phx.gbl...
> Thanks,
> Yes I know Windows prof. has a conection limitation, but
> are there any SQL limitations running on a workstation?
> Thanks again for your input.
> GP|||Hi
Just remember that a worstation edition of Windows (XP or 2000 Pro) both
have a limit of 10 concurrent client connections.
Regards
Mike
"GeorgeP" wrote:
> Hi All,
> I would like to install SQL server 2000 personal edition
> on a workstation as a backup to my server. (disaster
> recovery scenario) I run a backup to this machine already
> every 30 minutes, all I would have to do is turn on SQL
> services, restore the latest backup and I am back up and
> running within a few minutes. The workstaion has a 2 GIG
> processor and 256 Ram which I am planning on upgrading to
> 512.
> Are there any downsides to running 6 clients hitting this
> personal edition server? Is the PE the same as the server
> edition? Does the personal edition have limitations that
> the server edition does not?
> Thanks,
> GP
>sql

Diff between Logshipping , Transaction Log backup & Transaction Replication

Can any one explain in detail difference betwen Logshipping , Transaction Log backup & Transaction Replication and which is
better over other on what circumstances?By all means you can get more explanation with code examples from books online for these topics.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.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'
>

Diff backup Problems

Hi,
I am taking a diff backup of a read only database over a
network link but the size of the diff backup is the same
as the normal backup.
Is there any thing that I need to take in account as the
database is sometimes put to read write and then to read
only mode so need a differential
Thanks
Anup
Hi
Eventually. enough pages would have changed in the DB that the differential
is the same as a normal backup.
e.g.
If every index in clustered, and every index gets re-built, in effect the
whole DB's pages will have changed.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ap" <anonymous@.discussions.microsoft.com> wrote in message
news:00f401c5185f$39b32a50$a601280a@.phx.gbl...
> Hi,
> I am taking a diff backup of a read only database over a
> network link but the size of the diff backup is the same
> as the normal backup.
> Is there any thing that I need to take in account as the
> database is sometimes put to read write and then to read
> only mode so need a differential
> Thanks
> Anup
|||Checked all but no such thing something has changed so
any ideas what to do ?

>--Original Message--
>Hi
>Eventually. enough pages would have changed in the DB
that the differential
>is the same as a normal backup.
>e.g.
>If every index in clustered, and every index gets re-
built, in effect the
>whole DB's pages will have changed.
>Regards
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"ap" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:00f401c5185f$39b32a50$a601280a@.phx.gbl...
a[vbcol=seagreen]
same[vbcol=seagreen]
the[vbcol=seagreen]
read
>
>.
>
|||Hi,
When was the last full backup taken?
I suggest that you take a latest full backup and then the differential
backup. If you still have the same question, please let us know the size of
the original full backup file vs differential backup file size.
Thanks
Yogish