Disk Alert (please help!)

Hi,
I want to setup an alert to let me know when my disk reaches 80% capacity
(20% space available).
I did the following :
1) Set the diskperf-ym switch and restarted the server.
2) Added the logical disk - disk free space counter in the perfmon
3) Set the alert value to over 80%
4) Sample data every 15 minutes.
My question is, how can i setup sql server to generate an email and send it
to the operators i create. I know that i can create custom alerts, but will
they read it from the windows event log for the above-mentioned
process..Kinda confused...please helpppp!!
Regards,
AndyHi Vishal,
Thank you for the link, however, my question still is , whether the
procedure, as per my previous mail is something that can be done ? will a
sql alert be able to read the windows application log and send email...if
that is the case then, i can do it the way i initially wanted to.
Thanks,
Andy
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:udXP2EKRDHA.1556@.TK2MSFTNGP10.phx.gbl...
> Refer to this url
> http://www.databasejournal.com/scripts/article.php/1470811
> --
> -Vishal
> "Andy" <andy_rob108@.hotmail.com> wrote in message
> news:eACZ0$JRDHA.3768@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I want to setup an alert to let me know when my disk reaches 80%
capacity
> > (20% space available).
> >
> > I did the following :
> >
> > 1) Set the diskperf-ym switch and restarted the server.
> > 2) Added the logical disk - disk free space counter in the perfmon
> > 3) Set the alert value to over 80%
> > 4) Sample data every 15 minutes.
> >
> > My question is, how can i setup sql server to generate an email and send
> it
> > to the operators i create. I know that i can create custom alerts, but
> will
> > they read it from the windows event log for the above-mentioned
> > process..Kinda confused...please helpppp!!
> >
> > Regards,
> > Andy
> >
> >
>|||Hi Andy,
I think your intended idea is not difficult to implement. Try this:
create a batch file (c:\spacealert.bat) that looks like:
osql -Sservername -Ppassword -Usa -Q"exec master..xp_sendmail @.recipients =operator, @.subject = 'disk space has exceeded limit'"
In the alert configuration window, on Action tab, click on "Run this
program" and browse to c:\spacealert.bat. This tells the alert when firing,
will trigger the batch file to execute xp_sendmail to email message to the
operators.
Richard
"Andy" <andy_rob108@.hotmail.com> wrote in message
news:eACZ0$JRDHA.3768@.tk2msftngp13.phx.gbl...
> Hi,
> I want to setup an alert to let me know when my disk reaches 80% capacity
> (20% space available).
> I did the following :
> 1) Set the diskperf-ym switch and restarted the server.
> 2) Added the logical disk - disk free space counter in the perfmon
> 3) Set the alert value to over 80%
> 4) Sample data every 15 minutes.
> My question is, how can i setup sql server to generate an email and send
it
> to the operators i create. I know that i can create custom alerts, but
will
> they read it from the windows event log for the above-mentioned
> process..Kinda confused...please helpppp!!
> Regards,
> Andy
>

Discontinued Collations in SQL 2000

The collation LATIN1_General_BIN existed in SQL7 but is
not available in SQL2000. Which collation in SQL2000
would be the most appropriate replacement? We have
existing applications using this collation, and are stuck
at how to implement an upgrade path from 7 to 2000.
Thank you,
BillIn 2be501c37976$056ae820$a601280a@.phx.gbl, Bill Kenworthy typed:
> The collation LATIN1_General_BIN existed in SQL7 but is
> not available in SQL2000.
Sorry this is wrong, please see BOL:
"Windows Collation Name"
"SQL Collation Name"
--
Olaf|||Bill,
> The collation LATIN1_General_BIN existed in SQL7 but is
> not available in SQL2000
Not true.Try this query...
SELECT *
FROM ::fn_helpcollations()
WHERE [name] ='LATIN1_General_BIN'
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Bill Kenworthy" <jakesfather@.microsoft.com> wrote in message
news:2be501c37976$056ae820$a601280a@.phx.gbl...
> The collation LATIN1_General_BIN existed in SQL7 but is
> not available in SQL2000. Which collation in SQL2000
> would be the most appropriate replacement? We have
> existing applications using this collation, and are stuck
> at how to implement an upgrade path from 7 to 2000.
> Thank you,
> Bill

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?

Disaster Recovery Plan

Hello,
We have to put in place a disaster recovery plan for our production envirome
nt. 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 , NOU
NLOAD , 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 , NOU
NLOAD , 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 , NOUNLOA
D , 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 ,[/vbc
ol]
NAME = N'Transaction log backup with overwrite', NOSKIP , STATS = 10,
NOFORMAT[vbcol=seagreen]
> 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[/vbc
ol]
, NAME = N'Transaction Log Backup', NOSKIP , STATS = 10, NOFORMAT[vbcol=seagreen]
> 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
> 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[vbcol=seagreen]
> , 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...
> 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[vbcol=seagreen]
> ,
> 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
> 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',[vbcol=seagreen]
>|||A couple of observations:
Why do you do BBACKUP LOG with TRUNCATE_ONLY?
Why do you shrink the log file? (.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ink
.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. Ou
r 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 correc
t?
> 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 , NAM
E = 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 , N
AME = 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 fr
om
> 11 p.m. to 12 a.m are redundant, since you already have a full backup at 1
2
> 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...
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[vbcol=seagreen]
>|||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.g
bl...
> 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 i
n
> 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 somethin
g 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 enviro
ment. 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 correc
t?
> 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 , N
OUNLOAD , NAME = N'Production Full Backup', NOSKIP , STATS = 10, NOFORMA
T
> 2- Transaction Log Backup Every Night:
> BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH INIT , NOUNLOA
D , 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 , N
OUNLOAD , DIFFERENTIAL , NAME = N'Production Differential Backup 1', NOSK
IP , STATS = 10, NOFORMAT
> 4-Transaction log Backup Every Ten Minutes (during working hours.):
> BACKUP LOG [DBName] TO DISK = N'Z:\DBLogBackup' WITH NOINIT , NOUNL
OAD , NAME = N'Transaction Log Backup', NOSKIP , STATS = 10, NOFORMAT
> Will it work?