sql backup files: model

Showing posts with label model. Show all posts

Thursday, March 29, 2012

Backup Strategies

Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.com
Have you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
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/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>
|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> 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/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> nice
>
sql

Backup Strategies

Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.comHave you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
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/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> 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/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> nice
>

Backup Strategies

Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.comHave you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
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/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> 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/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
>> Our environment is development and data warehousing with many non logged
>> trasactions. Our recover model is simple and our backups are always
>> full
>> backups.
>> My question is:
>> Is there anything in sql that is comparable to the archive flag of a
>> file?
>> We do have some databases that might not change in a week. It would be
> nice
>> not to have to back them up
>> Robert Alexander
>> Robert.Alexander@.cca-audit.com
>>
>

Tuesday, March 20, 2012

Backup Question 100234987492874

Hi all,
The database in question uses Full Recovery Model but my maintenance plan
dictates to only keep 2 days of transaction log files. This has always
worked great.
For no obvious reason the backup no longer removes transaction files older
than 2 days so I am quickly running out of disk space. Again, nothing has
changed on this server, we keep detailed change management logs. I am
assuming that if I just blast the old .TRN files I will be fine but am still
puzzled why this behavior started and how to correctly stop it.
ThanksHi,
I have noticed this if the backup/transaction backup job in the schedule
doesn't complete successfully
--
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.aspx. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:O106PHfuDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> The database in question uses Full Recovery Model but my maintenance plan
> dictates to only keep 2 days of transaction log files. This has always
> worked great.
> For no obvious reason the backup no longer removes transaction files older
> than 2 days so I am quickly running out of disk space. Again, nothing has
> changed on this server, we keep detailed change management logs. I am
> assuming that if I just blast the old .TRN files I will be fine but am
still
> puzzled why this behavior started and how to correctly stop it.
> Thanks
>|||Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message news:O106PHfuDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> The database in question uses Full Recovery Model but my maintenance plan
> dictates to only keep 2 days of transaction log files. This has always
> worked great.
> For no obvious reason the backup no longer removes transaction files older
> than 2 days so I am quickly running out of disk space. Again, nothing has
> changed on this server, we keep detailed change management logs. I am
> assuming that if I just blast the old .TRN files I will be fine but am still
> puzzled why this behavior started and how to correctly stop it.
> Thanks
>

Backup Question

I have set up my server to use the simple recovery model. I understood this
to mean that the Transaction Log is not used. So why is it that my
Transaction Log is still being used and is growing?Simple does not mean that your T log is not used.
It means that your T-Log is truncated each time a transaction is
successfully comitted (i'm not sure about the truncation criteria).
But, unless I missed a point, this does not mean your T-Log is shrunk. You
still have to do it or by a maintenance plan, of by DBCC SHRINKDATASE on a
regular basis,
Chris
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:3FEABB58-6782-4980-8A02-1287A1E8D841@.microsoft.com...
> I have set up my server to use the simple recovery model. I understood
this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?|||simple recovery does not mean that your transaction will not be used.
Transaction logs will be used and this is an integral part of databases.
please note that when simple recovery model is set this means that the
in-active portion transaction(ie. commited transactions) are truncated at
every checkpoint(this is dictated by the configuration of your recovery
interval) and over written by new active , or uncommitted transactions. this
however does not mean that the transaction logfile size will reduce,
for example if you ran a procedure which grew your transaction log to 10Gb!
the fact that you have set a recovery model of simple means that as long as
all protions of this transaction log is active the physical log file will
grow to 10Gb!
once the transaction has been completed and committed the percentage use of
the transaction log file will be close to 0% check this with dbcc
sqlperf(logspace)
the file will still remain at 10Gb until you perform a dbcc shrinkfile which
will reduce the size of the physical file (with truncateonly option returns
space acquired back to sqlserver - see BOL for more info)
there is no reason to shrink the logfiles - especially if it will only grow
to it's original size, shrinking it will just put uneccessary IO overhead in
the middle of a transaction (ie. while sql server tries to grow log file to
accomodate the transaction)
HTH
"Hoof Hearted" wrote:
> I have set up my server to use the simple recovery model. I understood this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?

Monday, March 19, 2012

Backup Question

I have set up my server to use the simple recovery model. I understood this
to mean that the Transaction Log is not used. So why is it that my
Transaction Log is still being used and is growing?Simple does not mean that your T log is not used.
It means that your T-Log is truncated each time a transaction is
successfully comitted (i'm not sure about the truncation criteria).
But, unless I missed a point, this does not mean your T-Log is shrunk. You
still have to do it or by a maintenance plan, of by DBCC SHRINKDATASE on a
regular basis,
Chris
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:3FEABB58-6782-4980-8A02-1287A1E8D841@.microsoft.com...
> I have set up my server to use the simple recovery model. I understood
this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?|||simple recovery does not mean that your transaction will not be used.
Transaction logs will be used and this is an integral part of databases.
please note that when simple recovery model is set this means that the
in-active portion transaction(ie. commited transactions) are truncated at
every checkpoint(this is dictated by the configuration of your recovery
interval) and over written by new active , or uncommitted transactions. this
however does not mean that the transaction logfile size will reduce,
for example if you ran a procedure which grew your transaction log to 10Gb!
the fact that you have set a recovery model of simple means that as long as
all protions of this transaction log is active the physical log file will
grow to 10Gb!
once the transaction has been completed and committed the percentage use of
the transaction log file will be close to 0% check this with dbcc
sqlperf(logspace)
the file will still remain at 10Gb until you perform a dbcc shrinkfile which
will reduce the size of the physical file (with truncateonly option returns
space acquired back to sqlserver - see BOL for more info)
there is no reason to shrink the logfiles - especially if it will only grow
to it's original size, shrinking it will just put uneccessary IO overhead in
the middle of a transaction (ie. while sql server tries to grow log file to
accomodate the transaction)
HTH
"Hoof Hearted" wrote:

> I have set up my server to use the simple recovery model. I understood thi
s
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?

backup question

Dear all,
i want to verify whether the following backup planning can restore to the
original environment or not
1.) backup master, msdb, model with full backup to bak
2.) backup all user database with full backup and tran log backup to bak
question
how about the DTS package, user login, replication, job schelding, is it can
restore when i backup to the master and model?Users, packages and jobs are all in the databases you are
backup up (DTS Packages - as long as they are saved to SQL
Server as the location) so backing up all of your databases
and restoring all databases, including system databases will
get things back.
Replication restore strategies depend on how you have
implemented replication. You'd want to backup the publisher,
distributor, subscriber. There are different strategies for
merge, transactional, snapshot replication. You can find
them in books online. Look up the index topic:
replication, backup and restore operations
-Sue
On Mon, 3 Oct 2005 04:17:13 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:

>Dear all,
>i want to verify whether the following backup planning can restore to the
>original environment or not
>1.) backup master, msdb, model with full backup to bak
>2.) backup all user database with full backup and tran log backup to bak
>question
>how about the DTS package, user login, replication, job schelding, is it ca
n
>restore when i backup to the master and model?

Backup Question

I have set up my server to use the simple recovery model. I understood this
to mean that the Transaction Log is not used. So why is it that my
Transaction Log is still being used and is growing?
Simple does not mean that your T log is not used.
It means that your T-Log is truncated each time a transaction is
successfully comitted (i'm not sure about the truncation criteria).
But, unless I missed a point, this does not mean your T-Log is shrunk. You
still have to do it or by a maintenance plan, of by DBCC SHRINKDATASE on a
regular basis,
Chris
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:3FEABB58-6782-4980-8A02-1287A1E8D841@.microsoft.com...
> I have set up my server to use the simple recovery model. I understood
this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?
|||simple recovery does not mean that your transaction will not be used.
Transaction logs will be used and this is an integral part of databases.
please note that when simple recovery model is set this means that the
in-active portion transaction(ie. commited transactions) are truncated at
every checkpoint(this is dictated by the configuration of your recovery
interval) and over written by new active , or uncommitted transactions. this
however does not mean that the transaction logfile size will reduce,
for example if you ran a procedure which grew your transaction log to 10Gb!
the fact that you have set a recovery model of simple means that as long as
all protions of this transaction log is active the physical log file will
grow to 10Gb!
once the transaction has been completed and committed the percentage use of
the transaction log file will be close to 0% check this with dbcc
sqlperf(logspace)
the file will still remain at 10Gb until you perform a dbcc shrinkfile which
will reduce the size of the physical file (with truncateonly option returns
space acquired back to sqlserver - see BOL for more info)
there is no reason to shrink the logfiles - especially if it will only grow
to it's original size, shrinking it will just put uneccessary IO overhead in
the middle of a transaction (ie. while sql server tries to grow log file to
accomodate the transaction)
HTH
"Hoof Hearted" wrote:

> I have set up my server to use the simple recovery model. I understood this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?

backup question

Does point-in-time restore only for full recovery model?
and if i use a full - backup then can i restore in any time or just the
backup time?
or if use transaction log , it can only to restore to the time of
transaction log, can i restore one minutes before the transaction logHi
Yes, Point in time only works when a database is in full recovery mode, the
database is backed up, and the transaction log is backed up on a regular
basis.
You can do a point in time restore anywhere between the time the backup is
completed and the last transaction log backup is completed.
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/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F0B43886-5D63-4F9B-A3E3-223CA20A831D@.microsoft.com...
> Does point-in-time restore only for full recovery model?
> and if i use a full - backup then can i restore in any time or just the
> backup time?
> or if use transaction log , it can only to restore to the time of
> transaction log, can i restore one minutes before the transaction log

Thursday, March 8, 2012

Backup Plan advice / suggestion

Hello guys,
I would like to know if someone can advice me on that.
I have A MyDB more system DB (master, msdb, model, tempdb)
I would like to set up a Backup plan.
I would like to:
* Backup daily MyDB and system DB
* Backup LOG MyDB every one hours
* Backup LOG System DB (every 4 hours).
Any suggestion how to plan the better schedule time? 22h? 23h for
backup?
InaSee responses in-line...
ina wrote:
> Hello guys,
> I would like to know if someone can advice me on that.
> I have A MyDB more system DB (master, msdb, model, tempdb)
> I would like to set up a Backup plan.
> I would like to:
> * Backup daily MyDB and system DB
No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> * Backup LOG MyDB every one hours
This depends on your needs and tolerance for risk. You could
*potentially* lose an hour's worth of data, is that an acceptable risk
for you?
> * Backup LOG System DB (every 4 hours).
The only system DB that you can perform log backups on is MSDB, and this
is probably unnecessary.
> Any suggestion how to plan the better schedule time? 22h? 23h for
> backup?
> Ina
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
> No need to backup TEMPDB or MODEL, unless you have modified MODEL.
What is model crashes? If you have a backup, you just restore that backup. If not, you have to
rebuild the system databases (or do something unsupported like grabbing the files from another
system and worry about collations, if it works etc). This is why I always include database backup of
model in my backups schedules.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:45264804.8030302@.realsqlguy.com...
> See responses in-line...
>
> ina wrote:
>> Hello guys,
>> I would like to know if someone can advice me on that.
>> I have A MyDB more system DB (master, msdb, model, tempdb)
>> I would like to set up a Backup plan.
>> I would like to:
>> * Backup daily MyDB and system DB
> No need to backup TEMPDB or MODEL, unless you have modified MODEL.
>> * Backup LOG MyDB every one hours
> This depends on your needs and tolerance for risk. You could *potentially* lose an hour's worth
> of data, is that an acceptable risk for you?
>> * Backup LOG System DB (every 4 hours).
> The only system DB that you can perform log backups on is MSDB, and this is probably unnecessary.
>> Any suggestion how to plan the better schedule time? 22h? 23h for
>> backup?
>> Ina
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thank you.
what I am doing now it is the full backup for MyDB at 11 PM and log
files this MyDB every hours between 2 AM until 10 PM
Is it fine?
Ina
Tibor Karaszi wrote:
> Hi Tracy,
> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> What is model crashes? If you have a backup, you just restore that backup. If not, you have to
> rebuild the system databases (or do something unsupported like grabbing the files from another
> system and worry about collations, if it works etc). This is why I always include database backup of
> model in my backups schedules.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:45264804.8030302@.realsqlguy.com...
> > See responses in-line...
> >
> >
> > ina wrote:
> >> Hello guys,
> >>
> >> I would like to know if someone can advice me on that.
> >>
> >> I have A MyDB more system DB (master, msdb, model, tempdb)
> >> I would like to set up a Backup plan.
> >> I would like to:
> >> * Backup daily MyDB and system DB
> >
> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> >
> >> * Backup LOG MyDB every one hours
> >
> > This depends on your needs and tolerance for risk. You could *potentially* lose an hour's worth
> > of data, is that an acceptable risk for you?
> >
> >> * Backup LOG System DB (every 4 hours).
> >
> > The only system DB that you can perform log backups on is MSDB, and this is probably unnecessary.
> >
> >>
> >> Any suggestion how to plan the better schedule time? 22h? 23h for
> >> backup?
> >>
> >> Ina
> >>
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||> what I am doing now it is the full backup for MyDB at 11 PM and log
> files this MyDB every hours between 2 AM until 10 PM
I assume you mean "transaction log backup every hour between 2AB and 10PM".
> Is it fine?
We cannot answer that question. You have to determine that max amount of data loss you accept in
case of some catastrophe and based on that determine what types of backup and frequency. Also, you
didn't mention what backup you do if the system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1160222216.686213.118770@.e3g2000cwe.googlegroups.com...
> Thank you.
> what I am doing now it is the full backup for MyDB at 11 PM and log
> files this MyDB every hours between 2 AM until 10 PM
> Is it fine?
> Ina
> Tibor Karaszi wrote:
>> Hi Tracy,
>> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
>> What is model crashes? If you have a backup, you just restore that backup. If not, you have to
>> rebuild the system databases (or do something unsupported like grabbing the files from another
>> system and worry about collations, if it works etc). This is why I always include database backup
>> of
>> model in my backups schedules.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:45264804.8030302@.realsqlguy.com...
>> > See responses in-line...
>> >
>> >
>> > ina wrote:
>> >> Hello guys,
>> >>
>> >> I would like to know if someone can advice me on that.
>> >>
>> >> I have A MyDB more system DB (master, msdb, model, tempdb)
>> >> I would like to set up a Backup plan.
>> >> I would like to:
>> >> * Backup daily MyDB and system DB
>> >
>> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
>> >
>> >> * Backup LOG MyDB every one hours
>> >
>> > This depends on your needs and tolerance for risk. You could *potentially* lose an hour's
>> > worth
>> > of data, is that an acceptable risk for you?
>> >
>> >> * Backup LOG System DB (every 4 hours).
>> >
>> > The only system DB that you can perform log backups on is MSDB, and this is probably
>> > unnecessary.
>> >
>> >>
>> >> Any suggestion how to plan the better schedule time? 22h? 23h for
>> >> backup?
>> >>
>> >> Ina
>> >>
>> >
>> >
>> > --
>> > Tracy McKibben
>> > MCDBA
>> > http://www.realsqlguy.com
>|||Thanks Tibor,
My DB is organize like this everyday I backup Master, Model and Msdb
and every hours a back up the log of Mdsb
For my MyDB is the same backup everyday and backup log every hours.
One question when I change the schedule of the backup (i.e MyDB log
backup) do you thing is better to change backup file or device or it
is enough to change the schedule?
Ina
Tibor Karaszi wrote:
> > what I am doing now it is the full backup for MyDB at 11 PM and log
> > files this MyDB every hours between 2 AM until 10 PM
> I assume you mean "transaction log backup every hour between 2AB and 10PM".
>
> > Is it fine?
> We cannot answer that question. You have to determine that max amount of data loss you accept in
> case of some catastrophe and based on that determine what types of backup and frequency. Also, you
> didn't mention what backup you do if the system databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ina" <roberta.inalbon@.gmail.com> wrote in message
> news:1160222216.686213.118770@.e3g2000cwe.googlegroups.com...
> > Thank you.
> >
> > what I am doing now it is the full backup for MyDB at 11 PM and log
> > files this MyDB every hours between 2 AM until 10 PM
> >
> > Is it fine?
> >
> > Ina
> > Tibor Karaszi wrote:
> >> Hi Tracy,
> >>
> >> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> >>
> >> What is model crashes? If you have a backup, you just restore that backup. If not, you have to
> >> rebuild the system databases (or do something unsupported like grabbing the files from another
> >> system and worry about collations, if it works etc). This is why I always include database backup
> >> of
> >> model in my backups schedules.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:45264804.8030302@.realsqlguy.com...
> >> > See responses in-line...
> >> >
> >> >
> >> > ina wrote:
> >> >> Hello guys,
> >> >>
> >> >> I would like to know if someone can advice me on that.
> >> >>
> >> >> I have A MyDB more system DB (master, msdb, model, tempdb)
> >> >> I would like to set up a Backup plan.
> >> >> I would like to:
> >> >> * Backup daily MyDB and system DB
> >> >
> >> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> >> >
> >> >> * Backup LOG MyDB every one hours
> >> >
> >> > This depends on your needs and tolerance for risk. You could *potentially* lose an hour's
> >> > worth
> >> > of data, is that an acceptable risk for you?
> >> >
> >> >> * Backup LOG System DB (every 4 hours).
> >> >
> >> > The only system DB that you can perform log backups on is MSDB, and this is probably
> >> > unnecessary.
> >> >
> >> >>
> >> >> Any suggestion how to plan the better schedule time? 22h? 23h for
> >> >> backup?
> >> >>
> >> >> Ina
> >> >>
> >> >
> >> >
> >> > --
> >> > Tracy McKibben
> >> > MCDBA
> >> > http://www.realsqlguy.com
> >

Saturday, February 25, 2012

backup of database

Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
MangeshDaily backups should suffice.
--
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh

Sunday, February 19, 2012

BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.

I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
log file:
Message
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
recovery model should be used to automatically truncate the transaction log.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?If the full only takes "a second" to run, just run it hourly in Simple
Recovery if you want near-time recovery and no worries about the t-log. Set
your retention appropriately
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>I have choosen the Full model over the simple model in a development
> environment because if the database was to go suspect using the simple
> model,
> I would have to choose how much work I was willing to lo lose. For example
> if
> the database was backed up at 10:00am and the next backup was due at
> 4:00pm
> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
> While using the Full model I should be able to recover up to 3:00pm. Now
> because this is a very small database, only takes a second to do a Full
> backup, it really doesn't pay to keep backups of the log, but in order to
> keep the log file from growing and growing, I clean it out so that it will
> shrink by backing up with troncate_only. I get the following message in
> the
> log file:
> Message
> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
> recovery model should be used to automatically truncate the transaction
> log.
> Now I have to say in the situtation that I have described, isn't using a
> Full model better than a simple?
>|||What you describe is not a very common scenario (i.e. running in full but not do log backups, and
reason for running in full is so you can backup log when db goes suspect). However, you can
accomplish the same thing as BACKUP LOG ... WITH TRUNCATE ONLY by setting the db in simple recovery
model and then back to full recovery model again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>I have choosen the Full model over the simple model in a development
> environment because if the database was to go suspect using the simple model,
> I would have to choose how much work I was willing to lo lose. For example if
> the database was backed up at 10:00am and the next backup was due at 4:00pm
> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
> While using the Full model I should be able to recover up to 3:00pm. Now
> because this is a very small database, only takes a second to do a Full
> backup, it really doesn't pay to keep backups of the log, but in order to
> keep the log file from growing and growing, I clean it out so that it will
> shrink by backing up with troncate_only. I get the following message in the
> log file:
> Message
> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
> recovery model should be used to automatically truncate the transaction log.
> Now I have to say in the situtation that I have described, isn't using a
> Full model better than a simple?
>|||> What you describe is not a very common scenario (i.e. running in full but
> not do log backups
Actually I have to take exception to that one Tibor. This forum and others
have numerous examples of users who complain "my data file is NNN MB and my
tlog is MM GB, what is going on!?!?". Default settings lead many users who
aren't DBAs (99.438% of them) to have FULL recovery mode doing FULL (or
often even no) backups without doing tlog backups. I guess you probably
consult at larger companies that have more significant problems than things
like this. :-))
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B057ED66-437A-4D44-8F31-DECF8EE07867@.microsoft.com...
> What you describe is not a very common scenario (i.e. running in full but
> not do log backups, and reason for running in full is so you can backup
> log when db goes suspect). However, you can accomplish the same thing as
> BACKUP LOG ... WITH TRUNCATE ONLY by setting the db in simple recovery
> model and then back to full recovery model again.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
> news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>>I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple
>> model,
>> I would have to choose how much work I was willing to lo lose. For
>> example if
>> the database was backed up at 10:00am and the next backup was due at
>> 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it
>> will
>> shrink by backing up with troncate_only. I get the following message in
>> the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction
>> log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?
>|||> Actually I have to take exception to that one Tibor. This forum and others have numerous examples
> of users who complain "my data file is NNN MB and my tlog is MM GB, what is going on!?!?".
Oh, I now see that I should have qualified my statement. It isn't a very common scenario *when the
dba understand recovery models and transaction logging*. I.e., to do this deliberately.
I definitely agree that it is unfortunately common to have this setup undeliberately. I've always
thought that full recovery model as default is a good thing. But lately, I've been having some
doubts. Perhaps SQL Server should default recovery model for the model database to simple. Would
reduce a lot of the cases we see in this newsgroup, for instance.
> I guess you probably consult at larger companies that have more significant problems than things
> like this. :-))
Nah, I do both. And I see this in the most surprising situations... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13ofcn7dvasj99c@.corp.supernews.com...
>> What you describe is not a very common scenario (i.e. running in full but not do log backups
> Actually I have to take exception to that one Tibor. This forum and others have numerous examples
> of users who complain "my data file is NNN MB and my tlog is MM GB, what is going on!?!?".
> Default settings lead many users who aren't DBAs (99.438% of them) to have FULL recovery mode
> doing FULL (or often even no) backups without doing tlog backups. I guess you probably consult at
> larger companies that have more significant problems than things like this. :-))
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:B057ED66-437A-4D44-8F31-DECF8EE07867@.microsoft.com...
>> What you describe is not a very common scenario (i.e. running in full but not do log backups, and
>> reason for running in full is so you can backup log when db goes suspect). However, you can
>> accomplish the same thing as BACKUP LOG ... WITH TRUNCATE ONLY by setting the db in simple
>> recovery model and then back to full recovery model again.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
>> news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>>I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple model,
>> I would have to choose how much work I was willing to lo lose. For example if
>> the database was backed up at 10:00am and the next backup was due at 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it will
>> shrink by backing up with troncate_only. I get the following message in the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?
>>
>|||One reason you see the message that you are is that it is being removed in
SQL2008. See http://www.mssqltips.com/tip.asp?tip=1352 and
http://www.mssqltips.com/tip.asp?tip=1370
Chris
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>I have choosen the Full model over the simple model in a development
> environment because if the database was to go suspect using the simple
> model,
> I would have to choose how much work I was willing to lo lose. For example
> if
> the database was backed up at 10:00am and the next backup was due at
> 4:00pm
> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
> While using the Full model I should be able to recover up to 3:00pm. Now
> because this is a very small database, only takes a second to do a Full
> backup, it really doesn't pay to keep backups of the log, but in order to
> keep the log file from growing and growing, I clean it out so that it will
> shrink by backing up with troncate_only. I get the following message in
> the
> log file:
> Message
> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
> recovery model should be used to automatically truncate the transaction
> log.
> Now I have to say in the situtation that I have described, isn't using a
> Full model better than a simple?
>|||I think from the replies, my point has been lost, which is that doing a full
back up followed immediately by a log backup using truncate only, is better
than using a simple model because it protects against data loss between
backup time periods. If this feature is going to be removed in 2008 than a
dba with a situtation like mine will have to either choose between allowing a
certain amount of data loss (simple) or using (full) and creating tran log
backups that he really doesn't want and has to clean up.
"Doctor Who" wrote:
> I have choosen the Full model over the simple model in a development
> environment because if the database was to go suspect using the simple model,
> I would have to choose how much work I was willing to lo lose. For example if
> the database was backed up at 10:00am and the next backup was due at 4:00pm
> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
> While using the Full model I should be able to recover up to 3:00pm. Now
> because this is a very small database, only takes a second to do a Full
> backup, it really doesn't pay to keep backups of the log, but in order to
> keep the log file from growing and growing, I clean it out so that it will
> shrink by backing up with troncate_only. I get the following message in the
> log file:
> Message
> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
> recovery model should be used to automatically truncate the transaction log.
> Now I have to say in the situtation that I have described, isn't using a
> Full model better than a simple?
>|||"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>I think from the replies, my point has been lost, which is that doing a
>full
> back up followed immediately by a log backup using truncate only, is
> better
> than using a simple model because it protects against data loss between
> backup time periods.
How do you figure? If you truncate the log you can't later recover it.
Do you mean the other way around?
> If this feature is going to be removed in 2008 than a
> dba with a situtation like mine will have to either choose between
> allowing a
> certain amount of data loss (simple) or using (full) and creating tran log
> backups that he really doesn't want and has to clean up.
> "Doctor Who" wrote:
>> I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple
>> model,
>> I would have to choose how much work I was willing to lo lose. For
>> example if
>> the database was backed up at 10:00am and the next backup was due at
>> 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it
>> will
>> shrink by backing up with troncate_only. I get the following message in
>> the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction
>> log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>I think from the replies, my point has been lost, which is that doing a full
> back up followed immediately by a log backup using truncate only
That doesn't give you anything compared to simple recovery model. What you later have in the log
isn't usable for recovery purposes since you truncated the log directly after your database backup.
It would be better to have simple recovery model.
If you do it the other way around (BACKUP LOG WITH TRUNCATE ONLY immediately before the database
backup), then you have a possible advantage compared to simple recovery model. If the database
becomes suspect, you can do a log backup. But this is a pretty extreme case, and I would suggest
that you do regular log backups instead.
> If this feature is going to be removed in 2008 than a
> dba with a situtation like mine will have to either choose between allowing a
> certain amount of data loss (simple) or using (full) and creating tran log
> backups that he really doesn't want and has to clean up.
As I replied earlier, it is only the command which will be removed. The *functionality* is still
there. Put the db in simple recovery then immediately to full again. This gives you the same effect
as BACKUP LOG WITH TRUNCATE ONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>I think from the replies, my point has been lost, which is that doing a full
> back up followed immediately by a log backup using truncate only, is better
> than using a simple model because it protects against data loss between
> backup time periods. If this feature is going to be removed in 2008 than a
> dba with a situtation like mine will have to either choose between allowing a
> certain amount of data loss (simple) or using (full) and creating tran log
> backups that he really doesn't want and has to clean up.
> "Doctor Who" wrote:
>> I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple model,
>> I would have to choose how much work I was willing to lo lose. For example if
>> the database was backed up at 10:00am and the next backup was due at 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it will
>> shrink by backing up with troncate_only. I get the following message in the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?|||You can always make the log backup be deleted after one hour, the shortest
time currently allowed.
Chris
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>I think from the replies, my point has been lost, which is that doing a
>full
> back up followed immediately by a log backup using truncate only, is
> better
> than using a simple model because it protects against data loss between
> backup time periods. If this feature is going to be removed in 2008 than
> a
> dba with a situtation like mine will have to either choose between
> allowing a
> certain amount of data loss (simple) or using (full) and creating tran log
> backups that he really doesn't want and has to clean up.
> "Doctor Who" wrote:
>> I have choosen the Full model over the simple model in a development
>> environment because if the database was to go suspect using the simple
>> model,
>> I would have to choose how much work I was willing to lo lose. For
>> example if
>> the database was backed up at 10:00am and the next backup was due at
>> 4:00pm
>> if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
>> While using the Full model I should be able to recover up to 3:00pm. Now
>> because this is a very small database, only takes a second to do a Full
>> backup, it really doesn't pay to keep backups of the log, but in order to
>> keep the log file from growing and growing, I clean it out so that it
>> will
>> shrink by backing up with troncate_only. I get the following message in
>> the
>> log file:
>> Message
>> BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple
>> recovery model should be used to automatically truncate the transaction
>> log.
>> Now I have to say in the situtation that I have described, isn't using a
>> Full model better than a simple?

Backup log WITH NO_LOG - change recovery model to SIMPLE?

Hi,
I use FULL recovery model, SQL 2005. Is it possible this type of
backup ""change"" my recovery model to SIMPLE. I noticed when I
executed this:
BACKUP DATABSE db_name
TO DISK = 'path'
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
Now transact log grow very, very, very slow (this is symptom simple
model). But when executed this (different order):
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
BACKUP DATABSE db_name
TO DISK = 'path'
transact log grow normally
Would somebody explain me this, and tell me first statement change
(theoretically) my model to SIMPLE?
--
RegardsHi
First , read this article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
<anxcomp@.gmail.com> wrote in message
news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||This is expected. If you are in full mode and empty the log without actually doing a backup (which
is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
Server know this and in this situation, the database acts as if it is simple recovery (log is
auto-truncated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||On Aug 21, 2:52 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <anxc...@.gmail.com> wrote in messagenews:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> > Hi,
> > I use FULL recovery model, SQL 2005. Is it possible this type of
> > backup ""change"" my recovery model to SIMPLE. I noticed when I
> > executed this:
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > Now transact log grow very, very, very slow (this is symptom simple
> > model). But when executed this (different order):
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > transact log grow normally
> > Would somebody explain me this, and tell me first statement change
> > (theoretically) my model to SIMPLE?
> > --
> > Regards- Hide quoted text -
> - Show quoted text -
to change recovery model from full to simple use alter database
command for that database
alter database dbname set recovery=simple
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
setting to simple recovery helps to minimize log file growth ,but note
you will not be able to do point in time recovery incase of failures
Thanks
VS|||On 21 Sie, 11:52, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
So it is realy true, it change to SIMPLE mode, thanks.
Tibor I read your article ' Why you want to be restrictive with
shrink of database files', I understand I shouldn't use both
SHRINKFILE and SHRINKDATABASE command.
So, would you help me create good backup plan, which ENSURE me that I
shouldn't USE SHRINK* commands, please.
Main principles:
1.LOG can't grow so match (most important)
2 I can lost information max fifteen minutes back
3. I'd like use only full recovery model not SIMPLE
If you like it can be for example graphical plan - "Maintenance Plans"
on SQL 2005
Thank you
--
Regards|||> So it is realy true, it change to SIMPLE mode, thanks.
No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
same as in simple model. Important distinction.
> 1.LOG can't grow so match (most important)
OK. You handle this by doing frequent log backups since a lot backup will empty the ldf file(s).
> 2 I can lost information max fifteen minutes back
So you should do log backups at least in 15 minutes intervals. Perhaps every 10 minutes...
> 3. I'd like use only full recovery model not SIMPLE
So just keep the database in full recovery.
Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
minutes. This is easy thing to setup with the maint wizard.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187727440.811711.299920@.a39g2000hsc.googlegroups.com...
> On 21 Sie, 11:52, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> This is expected. If you are in full mode and empty the log without actually doing a backup
>> (which
>> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
>> Server know this and in this situation, the database acts as if it is simple recovery (log is
>> auto-truncated).
> So it is realy true, it change to SIMPLE mode, thanks.
> Tibor I read your article ' Why you want to be restrictive with
> shrink of database files', I understand I shouldn't use both
> SHRINKFILE and SHRINKDATABASE command.
> So, would you help me create good backup plan, which ENSURE me that I
> shouldn't USE SHRINK* commands, please.
> Main principles:
> 1.LOG can't grow so match (most important)
> 2 I can lost information max fifteen minutes back
> 3. I'd like use only full recovery model not SIMPLE
> If you like it can be for example graphical plan - "Maintenance Plans"
> on SQL 2005
> Thank you
> --
> Regards
>|||> No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
> same as in simple model. Important distinction.
OK, I understand now.
> Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
> minutes. This is easy thing to setup with the maint wizard.
I've done this use Wizard and now I'm watching what happen with log :)
Thank you Tibor for all advices and other people too :)
--
Regards

Thursday, March 29, 2012

Backup Strategies

Backup Strategies

Backup Strategies

Tuesday, March 20, 2012

Backup Question 100234987492874

Backup Question

Monday, March 19, 2012

Backup Question

backup question

Backup Question

backup question

Thursday, March 8, 2012

Backup Plan advice / suggestion

Backup Plan advice / suggestion

Backup Plan advice / suggestion

Saturday, February 25, 2012

backup of database

Sunday, February 19, 2012

BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.

BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.

Backup log WITH NO_LOG - change recovery model to SIMPLE?

sql backup files

Blog Archive

About Me