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?
Showing posts with label planning. Show all posts
Showing posts with label planning. Show all posts
Monday, March 19, 2012
Backup query ?
Im currently planning to move a Sql Server Database from one Server to another due to SAN errors on the current server.
What I need to know is...
Im currently restoring from last nights backup.. I then plan to apply all the transaction logs from today and tomorrow.
(Up until downtime)
If the database back's up between now and tomorrow will I loose data at the points between the back and the next transaction log backup ??
Im not truncating the transaction log at backup but I just wanted to make sure that ive got this straight before I start the work.
Thanks.You will not loose data between the full backup and the next transactional log backup. That is, of course, everything was done correctly.
What I need to know is...
Im currently restoring from last nights backup.. I then plan to apply all the transaction logs from today and tomorrow.
(Up until downtime)
If the database back's up between now and tomorrow will I loose data at the points between the back and the next transaction log backup ??
Im not truncating the transaction log at backup but I just wanted to make sure that ive got this straight before I start the work.
Thanks.You will not loose data between the full backup and the next transactional log backup. That is, of course, everything was done correctly.
Sunday, March 11, 2012
Backup Planning SQL2000
Hi i have implemented this backup strategy :
1 - 00:00 AM Backup FULL "myDB"
2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
from Transaction Log
Shrink T-LOG
My Rocovery model for "MyDB"is "Full"
If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
time) and restore a backup of T-LOg (at 12:00) and recover all information
from 00:00 AM to 12:00 PM ?!'!
In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
entries from Transaction Log" enable and in the same job (after backup) i
shrink t-log ro recover a space ?
Thanks in advance.I would go for the following option
00:00 Full backup
06:00 Log backup - The log is backed up from the last successfully
executed log backup or full backup to the current end of the log. Once the
log is backed up the space may be truncated when no longer required by a
transaction or replication.
12:00 Log backup
If @. 14:00 the system crashes I will restore the full backup followed by
6:00 and 12:00 logs. (This way the transaction log remais small and do not
have to shrink)
Other option could be
00:00 Full backup
06:00 Differential
12:00 Differential
If @. 14:00 the system crashes, I would restore Full backup and 12:00 copies.
I am not sure as to why you would like to specifically want to keep inactive
transactions.
HTH.
Bhanu.
.
<io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>|||Thanks for your reply !
Sorry i have a last question :
If i backup only a datafile and the db crash may i restore a db backup and
restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
Thanks.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> I would go for the following option
> 00:00 Full backup
> 06:00 Log backup - The log is backed up from the last successfully
> executed log backup or full backup to the current end of the log. Once
the
> log is backed up the space may be truncated when no longer required by a
> transaction or replication.
> 12:00 Log backup
>
> If @. 14:00 the system crashes I will restore the full backup followed by
> 6:00 and 12:00 logs. (This way the transaction log remais small and do
not
> have to shrink)
> Other option could be
> 00:00 Full backup
> 06:00 Differential
> 12:00 Differential
> If @. 14:00 the system crashes, I would restore Full backup and 12:00
copies.
>
> I am not sure as to why you would like to specifically want to keep
inactive
> transactions.
> HTH.
> Bhanu.
>
> .
>
> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
entries[vbcol=seagreen]
00:00[vbcol=seagreen]
> information
i[vbcol=seagreen]
>|||No. Make certain you do frequent enough transaction log backups. If the data
base crashes, what you
do, if possible, is to do a *backup* of the log after the database crash. Re
ad about the NO_TRUNCATE
option to the BACKUP LOG command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com> wrote in message news:e71Ua$UtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply !
> Sorry i have a last question :
> If i backup only a datafile and the db crash may i restore a db backup and
> restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
> Thanks.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> the
> not
> copies.
> inactive
> entries
> 00:00
> i
>|||<io.com> wrote in message news:%23x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
Besides other comments, I'd skip the "Shrink T-LOG" step. Only thing it
really can do is lead to fragmentation of the disk.
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>
1 - 00:00 AM Backup FULL "myDB"
2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
from Transaction Log
Shrink T-LOG
My Rocovery model for "MyDB"is "Full"
If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
time) and restore a backup of T-LOg (at 12:00) and recover all information
from 00:00 AM to 12:00 PM ?!'!
In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
entries from Transaction Log" enable and in the same job (after backup) i
shrink t-log ro recover a space ?
Thanks in advance.I would go for the following option
00:00 Full backup
06:00 Log backup - The log is backed up from the last successfully
executed log backup or full backup to the current end of the log. Once the
log is backed up the space may be truncated when no longer required by a
transaction or replication.
12:00 Log backup
If @. 14:00 the system crashes I will restore the full backup followed by
6:00 and 12:00 logs. (This way the transaction log remais small and do not
have to shrink)
Other option could be
00:00 Full backup
06:00 Differential
12:00 Differential
If @. 14:00 the system crashes, I would restore Full backup and 12:00 copies.
I am not sure as to why you would like to specifically want to keep inactive
transactions.
HTH.
Bhanu.
.
<io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>|||Thanks for your reply !
Sorry i have a last question :
If i backup only a datafile and the db crash may i restore a db backup and
restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
Thanks.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> I would go for the following option
> 00:00 Full backup
> 06:00 Log backup - The log is backed up from the last successfully
> executed log backup or full backup to the current end of the log. Once
the
> log is backed up the space may be truncated when no longer required by a
> transaction or replication.
> 12:00 Log backup
>
> If @. 14:00 the system crashes I will restore the full backup followed by
> 6:00 and 12:00 logs. (This way the transaction log remais small and do
not
> have to shrink)
> Other option could be
> 00:00 Full backup
> 06:00 Differential
> 12:00 Differential
> If @. 14:00 the system crashes, I would restore Full backup and 12:00
copies.
>
> I am not sure as to why you would like to specifically want to keep
inactive
> transactions.
> HTH.
> Bhanu.
>
> .
>
> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
entries[vbcol=seagreen]
00:00[vbcol=seagreen]
> information
i[vbcol=seagreen]
>|||No. Make certain you do frequent enough transaction log backups. If the data
base crashes, what you
do, if possible, is to do a *backup* of the log after the database crash. Re
ad about the NO_TRUNCATE
option to the BACKUP LOG command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com> wrote in message news:e71Ua$UtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply !
> Sorry i have a last question :
> If i backup only a datafile and the db crash may i restore a db backup and
> restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
> Thanks.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> the
> not
> copies.
> inactive
> entries
> 00:00
> i
>|||<io.com> wrote in message news:%23x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
Besides other comments, I'd skip the "Shrink T-LOG" step. Only thing it
really can do is lead to fragmentation of the disk.
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>
Backup Planning SQL2000
Hi i have implemented this backup strategy :
1 - 00:00 AM Backup FULL "myDB"
2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
from Transaction Log
Shrink T-LOG
My Rocovery model for "MyDB"is "Full"
If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
time) and restore a backup of T-LOg (at 12:00) and recover all information
from 00:00 AM to 12:00 PM ?!'!
In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
entries from Transaction Log" enable and in the same job (after backup) i
shrink t-log ro recover a space ?
Thanks in advance.I would go for the following option
00:00 Full backup
06:00 Log backup - The log is backed up from the last successfully
executed log backup or full backup to the current end of the log. Once the
log is backed up the space may be truncated when no longer required by a
transaction or replication.
12:00 Log backup
If @. 14:00 the system crashes I will restore the full backup followed by
6:00 and 12:00 logs. (This way the transaction log remais small and do not
have to shrink)
Other option could be
00:00 Full backup
06:00 Differential
12:00 Differential
If @. 14:00 the system crashes, I would restore Full backup and 12:00 copies.
I am not sure as to why you would like to specifically want to keep inactive
transactions.
HTH.
Bhanu.
.
<io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>|||Thanks for your reply !
Sorry i have a last question :
If i backup only a datafile and the db crash may i restore a db backup and
restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
Thanks.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> I would go for the following option
> 00:00 Full backup
> 06:00 Log backup - The log is backed up from the last successfully
> executed log backup or full backup to the current end of the log. Once
the
> log is backed up the space may be truncated when no longer required by a
> transaction or replication.
> 12:00 Log backup
>
> If @. 14:00 the system crashes I will restore the full backup followed by
> 6:00 and 12:00 logs. (This way the transaction log remais small and do
not
> have to shrink)
> Other option could be
> 00:00 Full backup
> 06:00 Differential
> 12:00 Differential
> If @. 14:00 the system crashes, I would restore Full backup and 12:00
copies.
>
> I am not sure as to why you would like to specifically want to keep
inactive
> transactions.
> HTH.
> Bhanu.
>
> .
>
> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> > Hi i have implemented this backup strategy :
> >
> > 1 - 00:00 AM Backup FULL "myDB"
> > 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> > from Transaction Log
> > 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> > from Transaction Log
> > 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive
entries
> > from Transaction Log
> > Shrink T-LOG
> >
> >
> > My Rocovery model for "MyDB"is "Full"
> >
> > If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at
00:00
> > time) and restore a backup of T-LOg (at 12:00) and recover all
> information
> > from 00:00 AM to 12:00 PM ?!'!
> >
> > In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> > entries from Transaction Log" enable and in the same job (after backup)
i
> > shrink t-log ro recover a space ?
> >
> > Thanks in advance.
> >
> >
>|||No. Make certain you do frequent enough transaction log backups. If the database crashes, what you
do, if possible, is to do a *backup* of the log after the database crash. Read about the NO_TRUNCATE
option to the BACKUP LOG command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com> wrote in message news:e71Ua$UtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply !
> Sorry i have a last question :
> If i backup only a datafile and the db crash may i restore a db backup and
> restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
> Thanks.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
>> I would go for the following option
>> 00:00 Full backup
>> 06:00 Log backup - The log is backed up from the last successfully
>> executed log backup or full backup to the current end of the log. Once
> the
>> log is backed up the space may be truncated when no longer required by a
>> transaction or replication.
>> 12:00 Log backup
>>
>> If @. 14:00 the system crashes I will restore the full backup followed by
>> 6:00 and 12:00 logs. (This way the transaction log remais small and do
> not
>> have to shrink)
>> Other option could be
>> 00:00 Full backup
>> 06:00 Differential
>> 12:00 Differential
>> If @. 14:00 the system crashes, I would restore Full backup and 12:00
> copies.
>>
>> I am not sure as to why you would like to specifically want to keep
> inactive
>> transactions.
>> HTH.
>> Bhanu.
>>
>> .
>>
>> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
>> > Hi i have implemented this backup strategy :
>> >
>> > 1 - 00:00 AM Backup FULL "myDB"
>> > 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
>> > from Transaction Log
>> > 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
>> > from Transaction Log
>> > 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive
> entries
>> > from Transaction Log
>> > Shrink T-LOG
>> >
>> >
>> > My Rocovery model for "MyDB"is "Full"
>> >
>> > If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at
> 00:00
>> > time) and restore a backup of T-LOg (at 12:00) and recover all
>> information
>> > from 00:00 AM to 12:00 PM ?!'!
>> >
>> > In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
>> > entries from Transaction Log" enable and in the same job (after backup)
> i
>> > shrink t-log ro recover a space ?
>> >
>> > Thanks in advance.
>> >
>> >
>>
>|||<io.com> wrote in message news:%23x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
Besides other comments, I'd skip the "Shrink T-LOG" step. Only thing it
really can do is lead to fragmentation of the disk.
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>
1 - 00:00 AM Backup FULL "myDB"
2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
from Transaction Log
Shrink T-LOG
My Rocovery model for "MyDB"is "Full"
If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
time) and restore a backup of T-LOg (at 12:00) and recover all information
from 00:00 AM to 12:00 PM ?!'!
In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
entries from Transaction Log" enable and in the same job (after backup) i
shrink t-log ro recover a space ?
Thanks in advance.I would go for the following option
00:00 Full backup
06:00 Log backup - The log is backed up from the last successfully
executed log backup or full backup to the current end of the log. Once the
log is backed up the space may be truncated when no longer required by a
transaction or replication.
12:00 Log backup
If @. 14:00 the system crashes I will restore the full backup followed by
6:00 and 12:00 logs. (This way the transaction log remais small and do not
have to shrink)
Other option could be
00:00 Full backup
06:00 Differential
12:00 Differential
If @. 14:00 the system crashes, I would restore Full backup and 12:00 copies.
I am not sure as to why you would like to specifically want to keep inactive
transactions.
HTH.
Bhanu.
.
<io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>|||Thanks for your reply !
Sorry i have a last question :
If i backup only a datafile and the db crash may i restore a db backup and
restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
Thanks.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> I would go for the following option
> 00:00 Full backup
> 06:00 Log backup - The log is backed up from the last successfully
> executed log backup or full backup to the current end of the log. Once
the
> log is backed up the space may be truncated when no longer required by a
> transaction or replication.
> 12:00 Log backup
>
> If @. 14:00 the system crashes I will restore the full backup followed by
> 6:00 and 12:00 logs. (This way the transaction log remais small and do
not
> have to shrink)
> Other option could be
> 00:00 Full backup
> 06:00 Differential
> 12:00 Differential
> If @. 14:00 the system crashes, I would restore Full backup and 12:00
copies.
>
> I am not sure as to why you would like to specifically want to keep
inactive
> transactions.
> HTH.
> Bhanu.
>
> .
>
> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> > Hi i have implemented this backup strategy :
> >
> > 1 - 00:00 AM Backup FULL "myDB"
> > 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> > from Transaction Log
> > 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> > from Transaction Log
> > 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive
entries
> > from Transaction Log
> > Shrink T-LOG
> >
> >
> > My Rocovery model for "MyDB"is "Full"
> >
> > If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at
00:00
> > time) and restore a backup of T-LOg (at 12:00) and recover all
> information
> > from 00:00 AM to 12:00 PM ?!'!
> >
> > In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> > entries from Transaction Log" enable and in the same job (after backup)
i
> > shrink t-log ro recover a space ?
> >
> > Thanks in advance.
> >
> >
>|||No. Make certain you do frequent enough transaction log backups. If the database crashes, what you
do, if possible, is to do a *backup* of the log after the database crash. Read about the NO_TRUNCATE
option to the BACKUP LOG command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com> wrote in message news:e71Ua$UtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply !
> Sorry i have a last question :
> If i backup only a datafile and the db crash may i restore a db backup and
> restore a copy a .ldf file (no backup of logfile but only .ldf file) !'!?
> Thanks.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
>> I would go for the following option
>> 00:00 Full backup
>> 06:00 Log backup - The log is backed up from the last successfully
>> executed log backup or full backup to the current end of the log. Once
> the
>> log is backed up the space may be truncated when no longer required by a
>> transaction or replication.
>> 12:00 Log backup
>>
>> If @. 14:00 the system crashes I will restore the full backup followed by
>> 6:00 and 12:00 logs. (This way the transaction log remais small and do
> not
>> have to shrink)
>> Other option could be
>> 00:00 Full backup
>> 06:00 Differential
>> 12:00 Differential
>> If @. 14:00 the system crashes, I would restore Full backup and 12:00
> copies.
>>
>> I am not sure as to why you would like to specifically want to keep
> inactive
>> transactions.
>> HTH.
>> Bhanu.
>>
>> .
>>
>> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
>> > Hi i have implemented this backup strategy :
>> >
>> > 1 - 00:00 AM Backup FULL "myDB"
>> > 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
>> > from Transaction Log
>> > 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
>> > from Transaction Log
>> > 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive
> entries
>> > from Transaction Log
>> > Shrink T-LOG
>> >
>> >
>> > My Rocovery model for "MyDB"is "Full"
>> >
>> > If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at
> 00:00
>> > time) and restore a backup of T-LOg (at 12:00) and recover all
>> information
>> > from 00:00 AM to 12:00 PM ?!'!
>> >
>> > In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
>> > entries from Transaction Log" enable and in the same job (after backup)
> i
>> > shrink t-log ro recover a space ?
>> >
>> > Thanks in advance.
>> >
>> >
>>
>|||<io.com> wrote in message news:%23x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
Besides other comments, I'd skip the "Shrink T-LOG" step. Only thing it
really can do is lead to fragmentation of the disk.
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!'!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>
Backup Planning SQL2000
Hi i have implemented this backup strategy :
1 - 00:00 AM Backup FULL "myDB"
2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
from Transaction Log
Shrink T-LOG
My Rocovery model for "MyDB"is "Full"
If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
time) and restore a backup of T-LOg (at 12:00) and recover all information
from 00:00 AM to 12:00 PM ?!?!
In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
entries from Transaction Log" enable and in the same job (after backup) i
shrink t-log ro recover a space ?
Thanks in advance.
I would go for the following option
00:00 Full backup
06:00 Log backup - The log is backed up from the last successfully
executed log backup or full backup to the current end of the log. Once the
log is backed up the space may be truncated when no longer required by a
transaction or replication.
12:00 Log backup
If @. 14:00 the system crashes I will restore the full backup followed by
6:00 and 12:00 logs. (This way the transaction log remais small and do not
have to shrink)
Other option could be
00:00 Full backup
06:00 Differential
12:00 Differential
If @. 14:00 the system crashes, I would restore Full backup and 12:00 copies.
I am not sure as to why you would like to specifically want to keep inactive
transactions.
HTH.
Bhanu.
..
<io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!?!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>
|||Thanks for your reply !
Sorry i have a last question :
If i backup only a datafile and the db crash may i restore a db backup and
restore a copy a .ldf file (no backup of logfile but only .ldf file) !?!?
Thanks.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> I would go for the following option
> 00:00 Full backup
> 06:00 Log backup - The log is backed up from the last successfully
> executed log backup or full backup to the current end of the log. Once
the
> log is backed up the space may be truncated when no longer required by a
> transaction or replication.
> 12:00 Log backup
>
> If @. 14:00 the system crashes I will restore the full backup followed by
> 6:00 and 12:00 logs. (This way the transaction log remais small and do
not
> have to shrink)
> Other option could be
> 00:00 Full backup
> 06:00 Differential
> 12:00 Differential
> If @. 14:00 the system crashes, I would restore Full backup and 12:00
copies.
>
> I am not sure as to why you would like to specifically want to keep
inactive[vbcol=seagreen]
> transactions.
> HTH.
> Bhanu.
>
> .
>
> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
entries[vbcol=seagreen]
00:00[vbcol=seagreen]
> information
i
>
|||No. Make certain you do frequent enough transaction log backups. If the database crashes, what you
do, if possible, is to do a *backup* of the log after the database crash. Read about the NO_TRUNCATE
option to the BACKUP LOG command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com> wrote in message news:e71Ua$UtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply !
> Sorry i have a last question :
> If i backup only a datafile and the db crash may i restore a db backup and
> restore a copy a .ldf file (no backup of logfile but only .ldf file) !?!?
> Thanks.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> the
> not
> copies.
> inactive
> entries
> 00:00
> i
>
|||<io.com> wrote in message news:%23x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
Besides other comments, I'd skip the "Shrink T-LOG" step. Only thing it
really can do is lead to fragmentation of the disk.
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!?!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>
1 - 00:00 AM Backup FULL "myDB"
2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
from Transaction Log
4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
from Transaction Log
Shrink T-LOG
My Rocovery model for "MyDB"is "Full"
If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
time) and restore a backup of T-LOg (at 12:00) and recover all information
from 00:00 AM to 12:00 PM ?!?!
In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
entries from Transaction Log" enable and in the same job (after backup) i
shrink t-log ro recover a space ?
Thanks in advance.
I would go for the following option
00:00 Full backup
06:00 Log backup - The log is backed up from the last successfully
executed log backup or full backup to the current end of the log. Once the
log is backed up the space may be truncated when no longer required by a
transaction or replication.
12:00 Log backup
If @. 14:00 the system crashes I will restore the full backup followed by
6:00 and 12:00 logs. (This way the transaction log remais small and do not
have to shrink)
Other option could be
00:00 Full backup
06:00 Differential
12:00 Differential
If @. 14:00 the system crashes, I would restore Full backup and 12:00 copies.
I am not sure as to why you would like to specifically want to keep inactive
transactions.
HTH.
Bhanu.
..
<io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!?!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>
|||Thanks for your reply !
Sorry i have a last question :
If i backup only a datafile and the db crash may i restore a db backup and
restore a copy a .ldf file (no backup of logfile but only .ldf file) !?!?
Thanks.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> I would go for the following option
> 00:00 Full backup
> 06:00 Log backup - The log is backed up from the last successfully
> executed log backup or full backup to the current end of the log. Once
the
> log is backed up the space may be truncated when no longer required by a
> transaction or replication.
> 12:00 Log backup
>
> If @. 14:00 the system crashes I will restore the full backup followed by
> 6:00 and 12:00 logs. (This way the transaction log remais small and do
not
> have to shrink)
> Other option could be
> 00:00 Full backup
> 06:00 Differential
> 12:00 Differential
> If @. 14:00 the system crashes, I would restore Full backup and 12:00
copies.
>
> I am not sure as to why you would like to specifically want to keep
inactive[vbcol=seagreen]
> transactions.
> HTH.
> Bhanu.
>
> .
>
> <io.com> wrote in message news:#x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
entries[vbcol=seagreen]
00:00[vbcol=seagreen]
> information
i
>
|||No. Make certain you do frequent enough transaction log backups. If the database crashes, what you
do, if possible, is to do a *backup* of the log after the database crash. Read about the NO_TRUNCATE
option to the BACKUP LOG command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com> wrote in message news:e71Ua$UtEHA.904@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply !
> Sorry i have a last question :
> If i backup only a datafile and the db crash may i restore a db backup and
> restore a copy a .ldf file (no backup of logfile but only .ldf file) !?!?
> Thanks.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:eAdlBGUtEHA.3216@.TK2MSFTNGP15.phx.gbl...
> the
> not
> copies.
> inactive
> entries
> 00:00
> i
>
|||<io.com> wrote in message news:%23x3hNATtEHA.2516@.TK2MSFTNGP11.phx.gbl...
> Hi i have implemented this backup strategy :
> 1 - 00:00 AM Backup FULL "myDB"
> 2 - 06:00 AM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 3 - 12:00 PM Backup T-LOG Overwrite and "NO" to Remove inactive entries
> from Transaction Log
> 4- 18:00 PM Backup T-LOG Overwrite and "Yes" to Remove inactive entries
> from Transaction Log
> Shrink T-LOG
Besides other comments, I'd skip the "Shrink T-LOG" step. Only thing it
really can do is lead to fragmentation of the disk.
>
> My Rocovery model for "MyDB"is "Full"
> If my DB crash at 14:00 PM may i restore a FULL Backup of my DB (at 00:00
> time) and restore a backup of T-LOg (at 12:00) and recover all
information
> from 00:00 AM to 12:00 PM ?!?!
> In to Job 4 is corrected that i backup my T-LOG with "Remove inactive
> entries from Transaction Log" enable and in the same job (after backup) i
> shrink t-log ro recover a space ?
> Thanks in advance.
>
Backup Planning assistance
Hi all,
New to database admin and I am attempting to work out a god backup schedule.
The database is a failry critical system so minimal downtime and dataloss from failure is the key to this.
Assuming storage is not an issue for me can anyone add some help.
What I was planning was a full backup an Sunday night and Wednesday nights at 6pm using
'backup database helpdesk to BK1 with noformat, init, stats'
Differential backups each night at 6pm using
'backup database helpdesk to BK1 with differential, noformat, noinit, stats'
Then a transaction log backup every 4 hours during the day with ??
'backup log helpdesk to Bk1 with ??
This is the part I am stuck on...
Firstly should all backups be going to the same logical backup device? in this case BK1 which points to a network share or should each go to a different location?
And what switches should I be using with the TL backup?
If anyone has a good backup T-SQL script they use I would really appreciate some help.
Cheers
MarkMark
When you say minimal downtime - do you mean when the backup is being taken or on recovery of the database?
I always take the method of keeping the backup strategy as simple as possible. I personally would take a complete backup every night (if you have enoght time - ie not a 24 hour operation) and trans log backups as you see fit. This can all be set up via Enterpise Manager very easily.
As for where to store - depends on what type of crash you wish to recover from - at the extreme level thay need to be stored on external media (eg tape) so that they may be restored on another server.|||Mark
When you say minimal downtime - do you mean when the backup is being taken or on recovery of the database?
I always take the method of keeping the backup strategy as simple as possible. I personally would take a complete backup every night (if you have enoght time - ie not a 24 hour operation) and trans log backups as you see fit. This can all be set up via Enterpise Manager very easily.
As for where to store - depends on what type of crash you wish to recover from - at the extreme level thay need to be stored on external media (eg tape) so that they may be restored on another server.|||Thanks for the reply,
When I said down time I did mean time to recover, so you are correct.
I think I will take your advise and do a fullbackup each night. Cheers
New to database admin and I am attempting to work out a god backup schedule.
The database is a failry critical system so minimal downtime and dataloss from failure is the key to this.
Assuming storage is not an issue for me can anyone add some help.
What I was planning was a full backup an Sunday night and Wednesday nights at 6pm using
'backup database helpdesk to BK1 with noformat, init, stats'
Differential backups each night at 6pm using
'backup database helpdesk to BK1 with differential, noformat, noinit, stats'
Then a transaction log backup every 4 hours during the day with ??
'backup log helpdesk to Bk1 with ??
This is the part I am stuck on...
Firstly should all backups be going to the same logical backup device? in this case BK1 which points to a network share or should each go to a different location?
And what switches should I be using with the TL backup?
If anyone has a good backup T-SQL script they use I would really appreciate some help.
Cheers
MarkMark
When you say minimal downtime - do you mean when the backup is being taken or on recovery of the database?
I always take the method of keeping the backup strategy as simple as possible. I personally would take a complete backup every night (if you have enoght time - ie not a 24 hour operation) and trans log backups as you see fit. This can all be set up via Enterpise Manager very easily.
As for where to store - depends on what type of crash you wish to recover from - at the extreme level thay need to be stored on external media (eg tape) so that they may be restored on another server.|||Mark
When you say minimal downtime - do you mean when the backup is being taken or on recovery of the database?
I always take the method of keeping the backup strategy as simple as possible. I personally would take a complete backup every night (if you have enoght time - ie not a 24 hour operation) and trans log backups as you see fit. This can all be set up via Enterpise Manager very easily.
As for where to store - depends on what type of crash you wish to recover from - at the extreme level thay need to be stored on external media (eg tape) so that they may be restored on another server.|||Thanks for the reply,
When I said down time I did mean time to recover, so you are correct.
I think I will take your advise and do a fullbackup each night. Cheers
backup planning
following the installation of Ms SQL server 2000, the backup planning is not saved. Have hou got an idea?If your backups are scheduled as SQLServer Agent Jobs, they can be scripted out and then run against your new database.
I don't think you can script the maintenance plans, but a little-known fact is that they aren't anything more than glorified Wizards that create the SQLServer Agent Jobs. After this, they serve only to define what databases the job should apply to.
See Books Online for the xp_sqlmaint procedure for more details. This is the procedure that the jobs use, and you can use it yourself without going through the maintenance plan wizard.
blindman|||Thank you,
I'm going to search with this
I don't think you can script the maintenance plans, but a little-known fact is that they aren't anything more than glorified Wizards that create the SQLServer Agent Jobs. After this, they serve only to define what databases the job should apply to.
See Books Online for the xp_sqlmaint procedure for more details. This is the procedure that the jobs use, and you can use it yourself without going through the maintenance plan wizard.
blindman|||Thank you,
I'm going to search with this
Subscribe to:
Posts (Atom)