On the subject of backup plans ...

Given the situation of a daily full backup and shrink of a database, does
anyone know of any 'gotchas' that are likely to bite one in the backside
when the recovery model for the databse is 'Full' as opposed to 'Simple'.
For example:
use master;
backup database <databasename> to disk='<filename>' with description='Full
backup of <databasename>',init,skip;
dbcc shrinkdatabase(<databasename>)When you set your database's recovery model to FULL then your passive
virtual log files will be held until you backup the database.
FULL recovery model is recommended for production environments because then
you could restore your database to the point of failure. Also you'd be able
to backup Transaction Logs.
If you set it's rec. model to SIMPLE then passive vlfs will be truncated at
every checkpoint. This recovery model is recommended for test environments
and if you use this recovery model then you'd not be able to transaction log
files. So you'd be able to restore your database only using your FULL and
DIFFERENTIAL backups.
Not using Transaction Log File backups is unacceptable for most of the
production environments.
Also, shrinking a database all the time is not a good idea for every
situation. This could be a performance problem as your database would need
to expand when it is being used. Expending and shrinking is a resource
consuming process.
Overview of the Recovery Models:
http://msdn2.microsoft.com/en-us/library/ms189275.aspx
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Stephany Young" <noone@.localhost> wrote in message
news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
> Given the situation of a daily full backup and shrink of a database, does
> anyone know of any 'gotchas' that are likely to bite one in the backside
> when the recovery model for the databse is 'Full' as opposed to 'Simple'.
> For example:
> use master;
> backup database <databasename> to disk='<filename>' with
> description='Full backup of <databasename>',init,skip;
> dbcc shrinkdatabase(<databasename>)
>|||Yes, yes ... I know all that!
So you are not aware of any 'gotchas' for the situation I described then?
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
> When you set your database's recovery model to FULL then your passive
> virtual log files will be held until you backup the database.
> FULL recovery model is recommended for production environments because
> then you could restore your database to the point of failure. Also you'd
> be able to backup Transaction Logs.
> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
> at every checkpoint. This recovery model is recommended for test
> environments and if you use this recovery model then you'd not be able to
> transaction log files. So you'd be able to restore your database only
> using your FULL and DIFFERENTIAL backups.
> Not using Transaction Log File backups is unacceptable for most of the
> production environments.
> Also, shrinking a database all the time is not a good idea for every
> situation. This could be a performance problem as your database would need
> to expand when it is being used. Expending and shrinking is a resource
> consuming process.
> Overview of the Recovery Models:
> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
> --
> Ekrem Önsoy
> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>
> "Stephany Young" <noone@.localhost> wrote in message
> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database, does
>> anyone know of any 'gotchas' that are likely to bite one in the backside
>> when the recovery model for the databse is 'Full' as opposed to 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>|||Not interested in your subject anymore.
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Stephany Young" <noone@.localhost> wrote in message
news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Yes, yes ... I know all that!
> So you are not aware of any 'gotchas' for the situation I described then?
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive
>> virtual log files will be held until you backup the database.
>> FULL recovery model is recommended for production environments because
>> then you could restore your database to the point of failure. Also you'd
>> be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
>> at every checkpoint. This recovery model is recommended for test
>> environments and if you use this recovery model then you'd not be able to
>> transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the
>> production environments.
>> Also, shrinking a database all the time is not a good idea for every
>> situation. This could be a performance problem as your database would
>> need to expand when it is being used. Expending and shrinking is a
>> resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database,
>> does anyone know of any 'gotchas' that are likely to bite one in the
>> backside when the recovery model for the databse is 'Full' as opposed to
>> 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>|||> So you are not aware of any 'gotchas' for the situation I described then?
Shrinking files every night is bad because of the performance hit that
occurs when files grow back to the needed size during the day. In the FULL
recovery model, the logs should be sized to accommodate expected activity
between log backups. The goal is to avoid file growth during normal
activity while keep file sizes manageable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephany Young" <noone@.localhost> wrote in message
news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Yes, yes ... I know all that!
> So you are not aware of any 'gotchas' for the situation I described then?
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive
>> virtual log files will be held until you backup the database.
>> FULL recovery model is recommended for production environments because
>> then you could restore your database to the point of failure. Also you'd
>> be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
>> at every checkpoint. This recovery model is recommended for test
>> environments and if you use this recovery model then you'd not be able to
>> transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the
>> production environments.
>> Also, shrinking a database all the time is not a good idea for every
>> situation. This could be a performance problem as your database would
>> need to expand when it is being used. Expending and shrinking is a
>> resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database,
>> does anyone know of any 'gotchas' that are likely to bite one in the
>> backside when the recovery model for the databse is 'Full' as opposed to
>> 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>|||Thanks for that Dan.
Maybe I phrased my original question badly.
I think the question should have been:
Is anyone aware of any behavioural differences between executing a 'dbcc
shrinkdatabase(<databasename>)' on a Full Recovery Model database compared
to executing the same statement on the same database if it were using Simple
Recovery Model?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:7AF6D479-8816-43AC-98A4-67F284D3E7D4@.microsoft.com...
>> So you are not aware of any 'gotchas' for the situation I described then?
> Shrinking files every night is bad because of the performance hit that
> occurs when files grow back to the needed size during the day. In the
> FULL recovery model, the logs should be sized to accommodate expected
> activity between log backups. The goal is to avoid file growth during
> normal activity while keep file sizes manageable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stephany Young" <noone@.localhost> wrote in message
> news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Yes, yes ... I know all that!
>> So you are not aware of any 'gotchas' for the situation I described then?
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive
>> virtual log files will be held until you backup the database.
>> FULL recovery model is recommended for production environments because
>> then you could restore your database to the point of failure. Also you'd
>> be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
>> at every checkpoint. This recovery model is recommended for test
>> environments and if you use this recovery model then you'd not be able
>> to transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the
>> production environments.
>> Also, shrinking a database all the time is not a good idea for every
>> situation. This could be a performance problem as your database would
>> need to expand when it is being used. Expending and shrinking is a
>> resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database,
>> does anyone know of any 'gotchas' that are likely to bite one in the
>> backside when the recovery model for the databse is 'Full' as opposed
>> to 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>>
>|||Ya, I got "gotcha" for ya.
When you shrink a DB (Simple, BulkLogged, or Full), watch the effect on the
filesystem in terms of drive fragmentation (outside of SQL Server).
Use the Windows defrag tool to analyze the partition your .MDF/.NDF files
are on and when it is done, look at the report for the .MDF/.NDF you shrunk.
It is likely the file will be fragmented on the disk, hindering overall
performance.
Since you specified 'shrinkdatabase', I'm assuming you're after .mdf/.ndf
files. The recovery model is dealing with the transaction log .ldf file. So
I guess the 'gotcha' is that you're operating on different areas.
I've never used 'dbcc shrinkdatabase', only 'dbcc shrinkfile' for out of
control log files (which relate more directly to the Recovery Model), so I
don't know the details of their differences.
Jay
"Stephany Young" <noone@.localhost> wrote in message
news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
> Given the situation of a daily full backup and shrink of a database, does
> anyone know of any 'gotchas' that are likely to bite one in the backside
> when the recovery model for the databse is 'Full' as opposed to 'Simple'.
> For example:
> use master;
> backup database <databasename> to disk='<filename>' with
> description='Full backup of <databasename>',init,skip;
> dbcc shrinkdatabase(<databasename>)
>|||> Is anyone aware of any behavioural differences between executing a 'dbcc
> shrinkdatabase(<databasename>)' on a Full Recovery Model database compared to executing the same
> statement on the same database if it were using Simple Recovery Model?
For data files, the pages moved are logged to the transaction log. The shrink is not one
transaction, it is a lot of smaller transaction. This means that the log can be truncated (emptied)
during the shrink if you are in simple recovery.
And, just in case you didn't read: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stephany Young" <noone@.localhost> wrote in message news:%23aN8wbwFIHA.6068@.TK2MSFTNGP02.phx.gbl...
> Thanks for that Dan.
> Maybe I phrased my original question badly.
> I think the question should have been:
> Is anyone aware of any behavioural differences between executing a 'dbcc
> shrinkdatabase(<databasename>)' on a Full Recovery Model database compared to executing the same
> statement on the same database if it were using Simple Recovery Model?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:7AF6D479-8816-43AC-98A4-67F284D3E7D4@.microsoft.com...
>> So you are not aware of any 'gotchas' for the situation I described then?
>> Shrinking files every night is bad because of the performance hit that occurs when files grow
>> back to the needed size during the day. In the FULL recovery model, the logs should be sized to
>> accommodate expected activity between log backups. The goal is to avoid file growth during
>> normal activity while keep file sizes manageable.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Yes, yes ... I know all that!
>> So you are not aware of any 'gotchas' for the situation I described then?
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive virtual log files will be
>> held until you backup the database.
>> FULL recovery model is recommended for production environments because then you could restore
>> your database to the point of failure. Also you'd be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated at every checkpoint.
>> This recovery model is recommended for test environments and if you use this recovery model
>> then you'd not be able to transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the production environments.
>> Also, shrinking a database all the time is not a good idea for every situation. This could be a
>> performance problem as your database would need to expand when it is being used. Expending and
>> shrinking is a resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database, does anyone know of any
>> 'gotchas' that are likely to bite one in the backside when the recovery model for the databse
>> is 'Full' as opposed to 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with description='Full backup of
>> <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>>
>