Disaster Recovery Plan

Hello,
We have to put in place a disaster recovery plan for our production enviroment. Our goal is to have the data available on a separate machine.
Here are the steps we came up with. My question is: Are these steps correct?
We have created 4 jobs:
1- Data Full Backup Every Night:
-Step 1: Truncate Log
Backup Log DBName with truncate_only
USE DBName
DBCC SHRINKFILE (DBName _Log, 5)
-Step 2: Do the Backup
BACKUP DATABASE [DBName] TO DISK = N'Z:\DBFullBackup' WITH INIT , NOUNLOAD , NAME = N'Production Full Backup', NOSKIP , STATS = 10, NOFORMAT
2- Transaction Log Backup Every Night:
BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH INIT , NOUNLOAD , NAME = N'Transaction log backup with overwrite', NOSKIP , STATS = 10, NOFORMAT
3- Data Differential Backup Every Hour:
BACKUP DATABASE [DBName] TO DISK = N'Z:\DBDiffBackup' WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Production Differential Backup 1', NOSKIP , STATS = 10, NOFORMAT
4-Transaction log Backup Every Ten Minutes (during working hours.):
BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH NOINIT , NOUNLOAD , NAME = N'Transaction Log Backup', NOSKIP , STATS = 10, NOFORMAT
Will it work?
I suppose step 2 is the first log backup after the full backup? Otherwise,
the INIT option will cause you to lose all your old logs. Plan seems to be
fine.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:A3AE9ADD-8676-4FAB-85AC-7FB9DDA00532@.microsoft.com...
> Hello,
> We have to put in place a disaster recovery plan for our production
enviroment. Our goal is to have the data available on a separate machine.
> Here are the steps we came up with. My question is: Are these steps
correct?
> We have created 4 jobs:
> 1- Data Full Backup Every Night:
> -Step 1: Truncate Log
> Backup Log DBName with truncate_only
> USE DBName
> DBCC SHRINKFILE (DBName _Log, 5)
> -Step 2: Do the Backup
> BACKUP DATABASE [DBName] TO DISK = N'Z:\DBFullBackup' WITH INIT ,
NOUNLOAD , NAME = N'Production Full Backup', NOSKIP , STATS = 10,
NOFORMAT
> 2- Transaction Log Backup Every Night:
> BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH INIT , NOUNLOAD ,
NAME = N'Transaction log backup with overwrite', NOSKIP , STATS = 10,
NOFORMAT
> 3- Data Differential Backup Every Hour:
> BACKUP DATABASE [DBName] TO DISK = N'Z:\DBDiffBackup' WITH INIT ,
NOUNLOAD , DIFFERENTIAL , NAME = N'Production Differential Backup 1',
NOSKIP , STATS = 10, NOFORMAT
> 4-Transaction log Backup Every Ten Minutes (during working hours.):
> BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH NOINIT , NOUNLOAD
, NAME = N'Transaction Log Backup', NOSKIP , STATS = 10, NOFORMAT
> Will it work?
|||Hi
As a novice in backup, I'm wondering, what's the pupose of the first step
where the log is being truncated before the full backup is being done?
Couldn't you just do the full backup and then the log backup (which then
will be the first log in the log backup sequence.)
Regards
Steen
"Peter Yeoh" <nospam@.nospam.com> skrev i en meddelelse
news:uxxcEbvUEHA.384@.TK2MSFTNGP10.phx.gbl...
> I suppose step 2 is the first log backup after the full backup?
Otherwise,
> the INIT option will cause you to lose all your old logs. Plan seems to
be[vbcol=seagreen]
> fine.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Try MiniSQLBackup
> "Alexis" <Alexis@.discussions.microsoft.com> wrote in message
> news:A3AE9ADD-8676-4FAB-85AC-7FB9DDA00532@.microsoft.com...
> enviroment. Our goal is to have the data available on a separate machine.
> correct?
> NOUNLOAD , NAME = N'Production Full Backup', NOSKIP , STATS = 10,
> NOFORMAT
,[vbcol=seagreen]
> NAME = N'Transaction log backup with overwrite', NOSKIP , STATS = 10,
> NOFORMAT
> NOUNLOAD , DIFFERENTIAL , NAME = N'Production Differential Backup 1',
> NOSKIP , STATS = 10, NOFORMAT
NOUNLOAD
> , NAME = N'Transaction Log Backup', NOSKIP , STATS = 10, NOFORMAT
>
|||Assume the following:
11 p.m - last trx log backup
12 a.m - truncate log and full backup performed
1 a.m - first new trx log backup
If we did not truncate the log at 12 a.m. before the full backup, the log
backup at 1 a.m. will include all trxs from 11 p.m. to 1 a.m. The trxs from
11 p.m. to 12 a.m are redundant, since you already have a full backup at 12
a.m. Not really a big issue if there are very few trxs at that hour.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uukvHJ3UEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi
> As a novice in backup, I'm wondering, what's the pupose of the first step
> where the log is being truncated before the full backup is being done?
> Couldn't you just do the full backup and then the log backup (which then
> will be the first log in the log backup sequence.)
> Regards
> Steen
>
> "Peter Yeoh" <nospam@.nospam.com> skrev i en meddelelse
> news:uxxcEbvUEHA.384@.TK2MSFTNGP10.phx.gbl...
> Otherwise,
> be
machine.[vbcol=seagreen]
NOUNLOAD
> ,
> NOUNLOAD
>
|||Thanks Peter -
I just wanted to be sure that there wasn't any functional issues to it.
Regards
Steen
"Peter Yeoh" <nospam@.nospam.com> skrev i en meddelelse
news:OfGI0Q3UEHA.484@.TK2MSFTNGP10.phx.gbl...
> Assume the following:
> 11 p.m - last trx log backup
> 12 a.m - truncate log and full backup performed
> 1 a.m - first new trx log backup
> If we did not truncate the log at 12 a.m. before the full backup, the log
> backup at 1 a.m. will include all trxs from 11 p.m. to 1 a.m. The trxs
from
> 11 p.m. to 12 a.m are redundant, since you already have a full backup at
12[vbcol=seagreen]
> a.m. Not really a big issue if there are very few trxs at that hour.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Try MiniSQLBackup
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uukvHJ3UEHA.1656@.TK2MSFTNGP09.phx.gbl...
step[vbcol=seagreen]
to[vbcol=seagreen]
> machine.
> NOUNLOAD
10,[vbcol=seagreen]
1',
>
|||A couple of observations:
Why do you do BBACKUP LOG with TRUNCATE_ONLY?
Why do you shrink the log file? (http://www.karaszi.com/SQLServer/info_dont_shrink.asp)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:A3AE9ADD-8676-4FAB-85AC-7FB9DDA00532@.microsoft.com...
> Hello,
> We have to put in place a disaster recovery plan for our production enviroment. Our goal is to have the data
available on a separate machine.
> Here are the steps we came up with. My question is: Are these steps correct?
> We have created 4 jobs:
> 1- Data Full Backup Every Night:
> -Step 1: Truncate Log
> Backup Log DBName with truncate_only
> USE DBName
> DBCC SHRINKFILE (DBName _Log, 5)
> -Step 2: Do the Backup
> BACKUP DATABASE [DBName] TO DISK = N'Z:\DBFullBackup' WITH INIT , NOUNLOAD , NAME = N'Production Full
Backup', NOSKIP , STATS = 10, NOFORMAT
> 2- Transaction Log Backup Every Night:
> BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH INIT , NOUNLOAD , NAME = N'Transaction log backup
with overwrite', NOSKIP , STATS = 10, NOFORMAT
> 3- Data Differential Backup Every Hour:
> BACKUP DATABASE [DBName] TO DISK = N'Z:\DBDiffBackup' WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME =
N'Production Differential Backup 1', NOSKIP , STATS = 10, NOFORMAT
> 4-Transaction log Backup Every Ten Minutes (during working hours.):
> BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH NOINIT , NOUNLOAD , NAME = N'Transaction Log
Backup', NOSKIP , STATS = 10, NOFORMAT
> Will it work?
|||But truncating the log prohibits skipping a database backup during restore! Assume following:backups:
(1) DB
(2) LOG
(3) LOG
(4) LOG
(5) DB
(6) LOG
(7) LOG
And we now want to do restore. However, (5) is damaged. Assuming that we did *not* do the truncate, we can
restore:
(1) DB
(2) LOG
(3) LOG
(4) LOG
(6) LOG
(7) LOG
However, if the truncate was performed, we can only get to (4)!!!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:OfGI0Q3UEHA.484@.TK2MSFTNGP10.phx.gbl...
> Assume the following:
> 11 p.m - last trx log backup
> 12 a.m - truncate log and full backup performed
> 1 a.m - first new trx log backup
> If we did not truncate the log at 12 a.m. before the full backup, the log
> backup at 1 a.m. will include all trxs from 11 p.m. to 1 a.m. The trxs from
> 11 p.m. to 12 a.m are redundant, since you already have a full backup at 12
> a.m. Not really a big issue if there are very few trxs at that hour.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Try MiniSQLBackup
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uukvHJ3UEHA.1656@.TK2MSFTNGP09.phx.gbl...
> machine.
> NOUNLOAD
>
|||Good point. I was under the assumption that your backups are reliable. I
guess that's why Yukon (SQL2K5) has mirrored backups, and so does
MiniSQLBackup
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Onw0Qt3UEHA.3692@.TK2MSFTNGP09.phx.gbl...
> But truncating the log prohibits skipping a database backup during
restore! Assume following:backups:
> (1) DB
> (2) LOG
> (3) LOG
> (4) LOG
> (5) DB
> (6) LOG
> (7) LOG
> And we now want to do restore. However, (5) is damaged. Assuming that we
did *not* do the truncate, we can
> restore:
> (1) DB
> (2) LOG
> (3) LOG
> (4) LOG
> (6) LOG
> (7) LOG
> However, if the truncate was performed, we can only get to (4)!!!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
news:OfGI0Q3UEHA.484@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
log[vbcol=seagreen]
from[vbcol=seagreen]
12[vbcol=seagreen]
step[vbcol=seagreen]
then[vbcol=seagreen]
seems to[vbcol=seagreen]
production[vbcol=seagreen]
steps[vbcol=seagreen]
,[vbcol=seagreen]
10,[vbcol=seagreen]
,[vbcol=seagreen]
1',[vbcol=seagreen]
hours.):[vbcol=seagreen]
NOFORMAT
>
|||I noticed the mirror feature of MiniSQLBackup. Good thinking :-)
Personally, I prefer to not truncate the log, as I find that it buys me very little (and in the unlikely case
that something went wrong with my db backup, I definitely would regret this).
(We added this to db maint in v4, although DbMaint uses native backups and *after* backup it does zipping,
copying etc... ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:%23QlUY83UEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Good point. I was under the assumption that your backups are reliable. I
> guess that's why Yukon (SQL2K5) has mirrored backups, and so does
> MiniSQLBackup
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Try MiniSQLBackup
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Onw0Qt3UEHA.3692@.TK2MSFTNGP09.phx.gbl...
> restore! Assume following:backups:
> did *not* do the truncate, we can
> news:OfGI0Q3UEHA.484@.TK2MSFTNGP10.phx.gbl...
> log
> from
> 12
> step
> then
> seems to
> production
> steps
> ,
> 10,
> ,
> 1',
> hours.):
> NOFORMAT
>
|||Hello and Thank to you all for responding to my post.
While I was wating for responses I continue my research and I found something else..."Log shipping" I am still reading about it.
Have any of you have looked at it?
So far I can see It will save me a lot of time since it keeps both databases "almost" synch wich is very good. I'm still trying to evaluate if there is any performance issue with this method.
"Alexis" wrote:

> Hello,
> We have to put in place a disaster recovery plan for our production enviroment. Our goal is to have the data available on a separate machine.
> Here are the steps we came up with. My question is: Are these steps correct?
> We have created 4 jobs:
> 1- Data Full Backup Every Night:
> -Step 1: Truncate Log
> Backup Log DBName with truncate_only
> USE DBName
> DBCC SHRINKFILE (DBName _Log, 5)
> -Step 2: Do the Backup
> BACKUP DATABASE [DBName] TO DISK = N'Z:\DBFullBackup' WITH INIT , NOUNLOAD , NAME = N'Production Full Backup', NOSKIP , STATS = 10, NOFORMAT
> 2- Transaction Log Backup Every Night:
> BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH INIT , NOUNLOAD , NAME = N'Transaction log backup with overwrite', NOSKIP , STATS = 10, NOFORMAT
> 3- Data Differential Backup Every Hour:
> BACKUP DATABASE [DBName] TO DISK = N'Z:\DBDiffBackup' WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Production Differential Backup 1', NOSKIP , STATS = 10, NOFORMAT
> 4-Transaction log Backup Every Ten Minutes (during working hours.):
> BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH NOINIT , NOUNLOAD , NAME = N'Transaction Log Backup', NOSKIP , STATS = 10, NOFORMAT
> Will it work?