Showing posts with label create. Show all posts
Showing posts with label create. Show all posts

Friday, March 30, 2012

Log LastChangedDate with trigger

CREATE TRIGGER logChanges
ON RepInfo
FOR UPDATE
AS
DECLARE @.RepInfoID INT
IF COLUMNS_UPDATED() > 0
UPDATE RepInfo
SET LastChangedDate = GetDate()
WHERE @.RepInfoID = RepInfoID
GO
--
Trying to update LastChangedDate in a given record when any of the columns
in the record are modified. The first time I ran this, I did not have any of
the "@.RepInfoID" clauses, and I go the whole column to update to GETDATE();
I
didn't want that, so I threw in the references to RepInfoID since it's the
identity column, trying to mimic some VB.NET code I have in another
application. No joy. This has to be a simple fix, but one that's elusive to
me. Any help is appreciated!
--
Thanks,
-AAAaron wrote:
> CREATE TRIGGER logChanges
> ON RepInfo
> FOR UPDATE
> AS
> DECLARE @.RepInfoID INT
> IF COLUMNS_UPDATED() > 0
> UPDATE RepInfo
> SET LastChangedDate = GetDate()
> WHERE @.RepInfoID = RepInfoID
> GO
> --
> Trying to update LastChangedDate in a given record when any of the columns
> in the record are modified. The first time I ran this, I did not have any
of
> the "@.RepInfoID" clauses, and I go the whole column to update to GETDATE()
; I
> didn't want that, so I threw in the references to RepInfoID since it's the
> identity column, trying to mimic some VB.NET code I have in another
> application. No joy. This has to be a simple fix, but one that's elusive t
o
> me. Any help is appreciated!
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Try this:
CREATE TRIGGER logChanges
ON RepInfo
FOR UPDATE
AS
- -- Assuming Columns_Updated() is a user-defined function
IF COLUMNS_UPDATED() > 0
UPDATE RepInfo
SET LastChangedDate = GetDate()
FROM inserted as i
WHERE i.RepInfoID = RepInfo.RepInfoID
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhqcxoechKqOuFEgEQKr4ACgvVYOXFpxFHW1
vT9osNSW/67oajAAnj9u
y8qk0gGECHy2AZQ9bWS00e6K
=Jj7k
--END PGP SIGNATURE--|||Thanks Pal, that works great!
Btw, IF (COLUMNS_UPDATED()) is not a user-defined function. Here's a clip
from SQL Server Books Online:
--<Snip>--
IF (COLUMNS_UPDATED())
Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or
columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit
pattern that indicates which columns in the table were inserted or updated.
--<Snip>--
-AA
"MGFoster" wrote:

> Aaron wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Try this:
> CREATE TRIGGER logChanges
> ON RepInfo
> FOR UPDATE
> AS
> - -- Assuming Columns_Updated() is a user-defined function
> IF COLUMNS_UPDATED() > 0
> UPDATE RepInfo
> SET LastChangedDate = GetDate()
> FROM inserted as i
> WHERE i.RepInfoID = RepInfo.RepInfoID
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQhqcxoechKqOuFEgEQKr4ACgvVYOXFpxFHW1
vT9osNSW/67oajAAnj9u
> y8qk0gGECHy2AZQ9bWS00e6K
> =Jj7k
> --END PGP SIGNATURE--
>sql

Log Ins

Hi all I was wondering how would I keep tabs on when users enter data in a database? Can I create a field or program a table to keep track of the users that enter in data so theres no mix up, to automatically keep track of users entering in dataHere is a sample script:

CREATE TABLE [Sample]
([SampleID] [uniqueidentifier] NOT NULL,
[Sample] [varchar] (50) NOT NULL,
[Modified] [datetime] NULL,
[Modifier] [varchar] (100) NULL)
ALTER TABLE [dbo].[Sample] WITH NOCHECK ADD
CONSTRAINT [PK_Sample] PRIMARY KEY CLUSTERED ([SampleID])

ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_SampleID] DEFAULT (newid()) FOR [SampleID]
ALTER TABLE [dbo].[Sample] ADD CONSTRAINT IX_Sample UNIQUE NONCLUSTERED ([Sample]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_Modified] DEFAULT (getdate()) FOR [Modified]
ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_Modifier] DEFAULT (convert(varchar(100), host_name() + ':' + suser_sname() + '(' + current_user + ') - ' + app_name())) FOR [Modifier]
GO

CREATE TRIGGER TR_Sample_U ON [dbo].[Sample]
FOR UPDATE
AS
set nocount on
update [Sample]
set Modified = getdate(),
Modifier = convert(varchar(100), host_name() + ':' + Original_Login() + '(' + current_user + ') - ' + app_name())
from [Sample]
inner join inserted on [Sample].[SampleID] = inserted.[SampleID]
GO|||How are you these days? Thank you for your reply, I give it a shot|||I'm doing fine thank you.

Try this script. Just enter your table name in the appropriate variable and indicated whether you want to use GUIDs or Identities for a surrogate key, and execute the script in text mode. It will write a script that you can copy and paste to create your new table.

set nocount on
declare @.TableName varchar(50)
declare @.UseGUIDs varchar(4)
set @.TableName = 'SampleRecords'
set @.UseGUIDs = 'Y' --'Y' for GUIDs, 'N' for Identity

declare @.EntityName varchar(50)
declare @.IDName varchar(50)
declare @.Version char(4)

set @.Version = substring(@.@.Version, 22, 4)

if right(@.TableName, 3) = 'ies' set @.EntityName = left(@.TableName, len(@.TableName)-3) + 'y'
else if right(@.TableName, 4) = 'sses' set @.EntityName = left(@.TableName, len(@.TableName)-2)
else if right(@.TableName, 6) = 'status' set @.EntityName = @.TableName
else if right(@.TableName, 8) = 'statuses' set @.EntityName = left(@.TableName, len(@.TableName)-2)
else if right(@.TableName, 1) = 's' set @.EntityName = left(@.TableName, len(@.TableName)-1)
else set @.EntityName = @.TableName

if right(@.TableName, 3) = 'ies' set @.IDName = left(@.TableName, len(@.TableName)-3) + 'yID'
else if right(@.TableName, 4) = 'sses' set @.IDName = left(@.TableName, len(@.TableName)-2) + 'ID'
else if right(@.TableName, 6) = 'status' set @.IDName = @.TableName + 'ID'
else if right(@.TableName, 8) = 'statuses' set @.IDName = left(@.TableName, len(@.TableName)-2) + 'ID'
else if right(@.TableName, 1) = 's' set @.IDName = left(@.TableName, len(@.TableName)-1) + 'ID'
else set @.IDName = @.TableName + 'ID'

declare @.SQLString varchar(8000)
set @.SQLString =
'Use ' + case when db_name() in ('master', 'msdb', 'model') then '[XXX]' else db_name() end + '
' + replace('GXO' ,'X', '') + '

CREATE TABLE [' + @.TableName + ']
([' + @.IDName + '] ' + Case when @.UseGUIDs = 'Y' then '[uniqueidentifier] NOT NULL' else '[bigint] IDENTITY(1,1) NOT NULL' end + ',
[' + @.EntityName + '] [varchar] (50) NOT NULL,
[Modified] [datetime] NULL,
[Modifier] [varchar] (100) NULL)
ALTER TABLE [dbo].[' + @.TableName + '] WITH NOCHECK ADD
CONSTRAINT [PK_' + @.TableName + '] PRIMARY KEY CLUSTERED ([' + @.IDName + '])

' + Case when @.UseGUIDs = 'Y' then 'ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_' + @.IDName + '] DEFAULT (newid()) FOR [' + @.IDName + ']' else '' end + '
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT IX_' + @.TableName + ' UNIQUE NONCLUSTERED ([' + @.EntityName + '])
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_Modified] DEFAULT (getdate()) FOR [Modified]
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_Modifier] DEFAULT (convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())) FOR [Modifier]
' + replace('GXO' ,'X', '') + '

CREATE TRIGGER TR_' + @.TableName + '_U ON [dbo].[' + @.TableName + ']
FOR UPDATE
AS
set nocount on
update [' + @.TableName + ']
set Modified = getdate(),
' + case when @.Version >= '2005'
then ' Modifier = convert(varchar(100), host_name() + '':'' + Original_Login() + ''('' + current_user + '') - '' + app_name())'
else ' Modifier = convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())'
end + '
from [' + @.TableName + ']
inner join inserted on [' + @.TableName + '].[' + @.IDName + '] = inserted.[' + @.IDName + ']
' + replace('GXO' ,'X', '') + ''

select @.SQLString

See how lazy I am?|||Hi all I was wondering how would I keep tabs on when users enter data in a database? Can I create a field or program a table to keep track of the users that enter in data so theres no mix up, to automatically keep track of users entering in data

Do you have sproc access only?|||I'm doing fine thank you.

Try this script. Just enter your table name in the appropriate variable and indicated whether you want to use GUIDs or Identities for a surrogate key, and execute the script in text mode. It will write a script that you can copy and paste to create your new table.

set nocount on
declare @.TableName varchar(50)
declare @.UseGUIDs varchar(4)
set @.TableName = 'SampleRecords'
set @.UseGUIDs = 'Y' --'Y' for GUIDs, 'N' for Identity

declare @.EntityName varchar(50)
declare @.IDName varchar(50)
declare @.Version char(4)

set @.Version = substring(@.@.Version, 22, 4)

if right(@.TableName, 3) = 'ies' set @.EntityName = left(@.TableName, len(@.TableName)-3) + 'y'
else if right(@.TableName, 4) = 'sses' set @.EntityName = left(@.TableName, len(@.TableName)-2)
else if right(@.TableName, 6) = 'status' set @.EntityName = @.TableName
else if right(@.TableName, 8) = 'statuses' set @.EntityName = left(@.TableName, len(@.TableName)-2)
else if right(@.TableName, 1) = 's' set @.EntityName = left(@.TableName, len(@.TableName)-1)
else set @.EntityName = @.TableName

if right(@.TableName, 3) = 'ies' set @.IDName = left(@.TableName, len(@.TableName)-3) + 'yID'
else if right(@.TableName, 4) = 'sses' set @.IDName = left(@.TableName, len(@.TableName)-2) + 'ID'
else if right(@.TableName, 6) = 'status' set @.IDName = @.TableName + 'ID'
else if right(@.TableName, 8) = 'statuses' set @.IDName = left(@.TableName, len(@.TableName)-2) + 'ID'
else if right(@.TableName, 1) = 's' set @.IDName = left(@.TableName, len(@.TableName)-1) + 'ID'
else set @.IDName = @.TableName + 'ID'

declare @.SQLString varchar(8000)
set @.SQLString =
'Use ' + case when db_name() in ('master', 'msdb', 'model') then '[XXX]' else db_name() end + '
' + replace('GXO' ,'X', '') + '

CREATE TABLE [' + @.TableName + ']
([' + @.IDName + '] ' + Case when @.UseGUIDs = 'Y' then '[uniqueidentifier] NOT NULL' else '[bigint] IDENTITY(1,1) NOT NULL' end + ',
[' + @.EntityName + '] [varchar] (50) NOT NULL,
[Modified] [datetime] NULL,
[Modifier] [varchar] (100) NULL)
ALTER TABLE [dbo].[' + @.TableName + '] WITH NOCHECK ADD
CONSTRAINT [PK_' + @.TableName + '] PRIMARY KEY CLUSTERED ([' + @.IDName + '])

' + Case when @.UseGUIDs = 'Y' then 'ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_' + @.IDName + '] DEFAULT (newid()) FOR [' + @.IDName + ']' else '' end + '
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT IX_' + @.TableName + ' UNIQUE NONCLUSTERED ([' + @.EntityName + '])
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_Modified] DEFAULT (getdate()) FOR [Modified]
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_Modifier] DEFAULT (convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())) FOR [Modifier]
' + replace('GXO' ,'X', '') + '

CREATE TRIGGER TR_' + @.TableName + '_U ON [dbo].[' + @.TableName + ']
FOR UPDATE
AS
set nocount on
update [' + @.TableName + ']
set Modified = getdate(),
' + case when @.Version >= '2005'
then ' Modifier = convert(varchar(100), host_name() + '':'' + Original_Login() + ''('' + current_user + '') - '' + app_name())'
else ' Modifier = convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())'
end + '
from [' + @.TableName + ']
inner join inserted on [' + @.TableName + '].[' + @.IDName + '] = inserted.[' + @.IDName + ']
' + replace('GXO' ,'X', '') + ''

select @.SQLString

See how lazy I am?

I needed to figure something out for keeping track of who is entering data and when. I was looking on BOL to see if I can find something, I'm the main DBA so I have full access to sql enterprise 2005 which I am still learning about. But I think I like 2005 better|||Hi Blindman when I run it I get error messages

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.
Msg 105, Level 15, State 1, Line 30
Unclosed quotation mark after the character string '
'.
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near '
'.|||Make sure your query tool is not truncating the results. You will need to bump up the max resultset characters option to 8000.

Wednesday, March 28, 2012

Log full alert

Hi,
I want to create a job that will raise an error case Log is getting almost
full.
Is there a sp (or any other tool) that can assist?
Thanks, Hagay.Hi all,
I've found a way to extract log precentage usage:
master db has a table msysperinfo which holds (among other detail) log
precentage use of each database.
so the following query will give you what you need:
select cntr_value
from msysperfinfo
where counter_name = 'Percent Log Used'
and instance_name = @.databaseName
"Hagay Lupesko" <hagayl@.nice.com> wrote in message
news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to create a job that will raise an error case Log is getting almost
> full.
> Is there a sp (or any other tool) that can assist?
> Thanks, Hagay.
>
>|||Maybe you have ran a script to create that table as I don't think it is a
standard table, you could do
dbcc sqlperf('logspace')
to get log information and space used/free, from there you could throw that
to a table and check for percentage used to send an alert.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Hagay Lupesko" <hagayl@.nice.com> wrote in message
news:ezOTZOOkDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I've found a way to extract log precentage usage:
> master db has a table msysperinfo which holds (among other detail) log
> precentage use of each database.
> so the following query will give you what you need:
> select cntr_value
> from msysperfinfo
> where counter_name = 'Percent Log Used'
> and instance_name = @.databaseName
> "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I want to create a job that will raise an error case Log is getting
almost
> > full.
> > Is there a sp (or any other tool) that can assist?
> >
> > Thanks, Hagay.
> >
> >
> >
>|||The counter in the sysperfinfo table and the value from DBCC
SQLPERF(LOGSPACE) are essentially the same. You can use these values to
alert you on a certain log usage threhold (e.g. 85%), if your log files are
not set to autogrow.
However, if one of your log files is set to autogrow, these values will
often give false alarms. For instance, your log might be 90% used, but if
you still have 10GB of free space of the drive on which the log file is set
to autogrow, you may not be too pleased to be paged at 3 AM.
In short, your script needs to consider the autogrow property, and it can
become rather complex if you have multiple log files on different drives.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:uQljKlOkDHA.2416@.TK2MSFTNGP10.phx.gbl...
> Maybe you have ran a script to create that table as I don't think it is a
> standard table, you could do
> dbcc sqlperf('logspace')
> to get log information and space used/free, from there you could throw
that
> to a table and check for percentage used to send an alert.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> news:ezOTZOOkDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Hi all,
> >
> > I've found a way to extract log precentage usage:
> >
> > master db has a table msysperinfo which holds (among other detail) log
> > precentage use of each database.
> >
> > so the following query will give you what you need:
> >
> > select cntr_value
> > from msysperfinfo
> > where counter_name = 'Percent Log Used'
> > and instance_name = @.databaseName
> >
> > "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> > news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I want to create a job that will raise an error case Log is getting
> almost
> > > full.
> > > Is there a sp (or any other tool) that can assist?
> > >
> > > Thanks, Hagay.
> > >
> > >
> > >
> >
> >
>|||Just let Agent do this using a performance condition alert!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hagay Lupesko" <hagayl@.nice.com> wrote in message news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to create a job that will raise an error case Log is getting almost
> full.
> Is there a sp (or any other tool) that can assist?
> Thanks, Hagay.
>
>

Monday, March 19, 2012

Log file in T-sql

Hi Folks,
I really need help on this...it very urgent for me.
How can I create a log file from a stored proc in T-SQL. I mean everytime there is an error, I need to put it into a log file. This stored proc is not being called by a DTS package..please suggest some method

Regards
Manmeet1. use xp_cmdshell and echo ...
2. write into a table and bulk copy that out in the end

I prefer approach 2 for my procedures.|||Originally posted by Enigma
1. use xp_cmdshell and echo ...
2. write into a table and bulk copy that out in the end

I prefer approach 2 for my procedures.

thanks a lot..I know this sounds stupid...can you just send me a sample code ....I myself am looking into it also|||Originally posted by Enigma
1. use xp_cmdshell and echo ...
2. write into a table and bulk copy that out in the end

I prefer approach 2 for my procedures.

hey dude...

Ignore my last msg (about sending a code)..I got it..thanks a ton man...u have been a real life saviour!!!!!!!!

but just 1 question...why do you prefer option 2..any drawback with option 1?

regards
Manmeet|||That way .. i can maintain a archive of the errors for future reference.|||Originally posted by Enigma
That way .. i can maintain a archive of the errors for future reference.

but then we can still use the echo command with >> option to keep appending the errors to the file right...that way you will still have the archive right??

--IS there a way you can get the description of the error messages. We get the error code and then query the master table for error descriptions. But the description has place holder and hence we cannot get the complete message...any sugestions dude??

Regards
Manmeet|||IS there a way you can get the description of the error messages. We get the error code and then query the master table for error descriptions. But the description has place holder and hence we cannot get the complete message...any sugestions dude??

Am a little confused .. could you elaborate|||Originally posted by Enigma
Am a little confused .. could you elaborate

I am just running a stored proc from a batch file. I need to capture any error and log it into a txt file. So If I use @.@.ERROR, I get only the error code. SO to get the description of the error, I hit the sysmessages table in master database and get the description.

so say I have a sql statement 'drop table MyTab'...then if the table does not exist...the error should be 'cannot drop the table MyTab as it does not exist in the system catalog' (Error No 3701)

But if I use @.@.ERROR and query the table sysmessages...I get the description 'Cannot %S_MSG the %S_MSG '%.*ls', because it does not exist in the system catalog'...bcos thats what the table stores for Error no 3701...

So how can I get the complete description pf the error message??

Regards
Manmeet|||I'm also curious about this...and all I'm doing is trying to save the errors to a trace log table...

/*================================================= ====================================
| Name: ErrorHandler
| Description: Just a basic function to raise an error when necessary. Also writes to tracelog table.
|
| Created: MM/DD/YYYY by JoMama Angiedaddy
| Modified:
| Inputs: errorNum - The error number that occurred.
| app - The application description in which the error occurred.
| function - the function in which the error occurred.
|================================================= =====================================*/
CREATE PROC dbo.ErrorHandler (
@.errorNum int =0 ,
@.app varchar(50) ='' ,
@.function varchar(255) ='' )
AS
BEGIN

DECLARE @.msg varchar(100)

IF @.errorNum <> 0
BEGIN
IF Exists(SELECT error FROM master..sysmessages WHERE error = @.errornum)
SELECT @.msg = 'Error Number: ' +
Convert(varchar(5), error) + ' ' + description
FROM master..sysmessages
WHERE error = @.errornum
ELSE
SELECT @.msg = 'Error Number: ' + Convert(varchar(5), @.errornum)

EXECUTE tracelog 0, @.app, @.function, @.msg

RAISERROR ('ErrorHandler %s %s %s', 16, 1, @.app, @.function, @.msg)
RETURN 1
END
ELSE
RETURN 0
END
GO

And the results are:
Server: Msg 515, Level 16, State 2, Procedure sp_Rebuild_IBD8585_CurrentList, Line 79
Cannot insert the value NULL into column 'StockOSID', table 'IBDIndex.dbo.CurrentList'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(1 row(s) affected)

Here's what gets written to the tracelog and appears on the screen as a result of the RaiseError call:

Server: Msg 50000, Level 16, State 1, Procedure ErrorHandler, Line 33
ErrorHandler IBDIndex sp sp_Rebuild_IBD8585_CurrentList Error Number: 515 Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not a

So...I am trying (as are the previous poster and several other frustrated and head-scratching individuals I have found around the 'net on this subject) to discover a way to make the data that appears on the first (system-generated) error message make its way into the final trace error message string...instead of the placeholders from the sysmessages table.

Any insights and/or, well...just plain ole' magical code snippets or how-to's?

Thankyouverymuch

Friday, March 9, 2012

Log file

What is the use of the log file ?
Can I create a databate without a log file even if I want to use
transations in my program ?Do yuo mean the transaciton log? No every SQL database has a tran log, you can set the recovery mode to simple and it will truncate the log upon checkpoint to keep it small. Not sure if that is what your question is...

HTH

Wednesday, March 7, 2012

Log Data wrongly "redirected" To Master.dbo.sysdtslog90

I have been using the SSIS Log Provider for SQL Server quite a lot this year with little to no problems. Recently however as I edit or create packages the logging seems to keep going to the master.dbo.sysdtslog90 table as opposed to the [db].dbo.sysdtslog90 as setup in the package. I tried totally deleting the connection from the package and recreating and still had the problem.

Is there some setting on the SQL server itself that can cause this? Our "dba" likes to make changes without letting anyone know about them.

Any ideas?

I've been doing some testing and I went ahead and tried to send the logging to a different db (used existing connection from another project) and lo and behold it works fine - went to existing db from another project. So my question is why would logging go to the master db on a connection that performs everything fine (on the correct db) for all tasks in the package? Smells like a bug to me but I can't put my finger on exactly why it's behaving that way.

Log Backups vs. Differential Backups

I was recently educated (thanks Dan) that you cannot create a backup of
a database if you lose your log file (due to drive failure of
corruption). Once I learned this, I began wondering about the merits of
even using a log file with the Full recovery model. Why not just use
the Simple recovery model and use frequent differential backups? Why go
through the overhead of log files if you can get almost the same level
of safety using frequent differntial backups?
Is doing differential backups every 15 minutes close enough to the
level of protection afforded by 15 minute log backups?
The way I see it is that the only thing I lose is
up-to-the-point-of-failure restoration. But, what I gain is not having
to worry about a corrupt log file or losing the drive that has the log
file on it. The processing time seems similar.
Am I wildly wrong anout this?
Thanks in advance for your opinions.
- SeanHi Sean,
Are you say that you would only lose you log file drive? What happens if
it's the data drive that goes? If you lose your log file of the database
and then detach and reattach the drive the systm will create a new log file.
Then applying the log backups will get you back to the point of failure -
the time to the last log file backup.
Backups are also used for non disaster situations and log file give you more
options there. I have always found log file backups quicker that diff
backups when it
comes to large databases
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Greg - thanks for your reply. I was not able to rebuild a log file. I
took a sample database with a log file, stopped SQL server, deleted the
log file, and restarted SQL server. The DB was "Suspect". I was unable
to get a new log to build with any consistency - I played with setting
the DB to "Emergency Mode" and messed with detaching/reattaching and
would get it to rebuild a log file 50% of the time - still, the problem
is by losing your log file, the data becomes suspect, and you can't
trust it even if you rebuild the log.
I understand how log file backups can get you to the point of failure
if the data drive fails - but I was concerned with the log drive
failing. This led me to post on the topic: "Log Backups - Why bother
with every ten minutes?" There I learned from Dan that if you lose a
log file, your database is hosed, and you can only restore to your last
backup.
This was a disappointment to me - I really like the idea that we can
restore to the point of failure using logs if the data drive fails -
but if the log drive fails, we are right back to only getting back what
we have as a "backup".
So - I am thinking that differentials in "Simple" recovery are pretty
close to as good as log file backups in "Full" Recovery mode. Granted,
you can't restore to the point of failure EVER - but you aren't that
much worse off if the backups are frequent enough.
You mention that log file backups are quicker than differentials. A
little quicker? or significantly quicker? The database sizes I am
concerned with are ~35 Gig. The full backup process to disk takes less
than 10 minutes. Diffs and log backups are much faster, naturally.
Are there other problems with diffs that I am unaware of - locking or
something?|||Hi Sean,
In the situation you have describe to create a suspect database I was not
able to reproduct the issue. Having said that it doesn't really matter.
Any backup system that you employ you should follow the MS recommendation .
They are that transaction logs and Data should be protected while on disk.
That is with a form of RAID. While it is unlikely that a disk goes bad is
is very unlikely that two or more will go bad at the same time. So your
first line or protection is always a RAID system.
Next a diff backup is slower than a log backup. This is the bit from BOL
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft® SQL ServerT 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information,
see Using File Backups.
So to do a diff backup it needs to read the backup an check the pages that
have been backed up. Where as a log backup backups the entire log file
(pages). Somethime the LOG backup can be larger than the database bacup
because of high transactions but you can always reduce the time between
backups.
In addition having log backups and transaction logs (Full Recovery Mode)
always the use of log reader type productions to see and recover cahnges to
the data without restoring. This might not be an issue now but at sometime
we have all been asked to get back some details that were changed wrongly.
Backup don't tend to cause locking problems for long periods as they backup
pages and do so quickly.
My recommendation is to continue with log backups , ensure that the data and
log files are on RAID systems (RAID 1 for log, RAID 5 for data is typically
good)
Send your backups and log backups to another server via a script See this
link http://www.sql-scripts.com/members/ScriptDetails.aspx?S_ID=4
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124760340.712510.321430@.g47g2000cwa.googlegroups.com...
> Greg - thanks for your reply. I was not able to rebuild a log file. I
> took a sample database with a log file, stopped SQL server, deleted the
> log file, and restarted SQL server. The DB was "Suspect". I was unable
> to get a new log to build with any consistency - I played with setting
> the DB to "Emergency Mode" and messed with detaching/reattaching and
> would get it to rebuild a log file 50% of the time - still, the problem
> is by losing your log file, the data becomes suspect, and you can't
> trust it even if you rebuild the log.
> I understand how log file backups can get you to the point of failure
> if the data drive fails - but I was concerned with the log drive
> failing. This led me to post on the topic: "Log Backups - Why bother
> with every ten minutes?" There I learned from Dan that if you lose a
> log file, your database is hosed, and you can only restore to your last
> backup.
> This was a disappointment to me - I really like the idea that we can
> restore to the point of failure using logs if the data drive fails -
> but if the log drive fails, we are right back to only getting back what
> we have as a "backup".
> So - I am thinking that differentials in "Simple" recovery are pretty
> close to as good as log file backups in "Full" Recovery mode. Granted,
> you can't restore to the point of failure EVER - but you aren't that
> much worse off if the backups are frequent enough.
> You mention that log file backups are quicker than differentials. A
> little quicker? or significantly quicker? The database sizes I am
> concerned with are ~35 Gig. The full backup process to disk takes less
> than 10 minutes. Diffs and log backups are much faster, naturally.
> Are there other problems with diffs that I am unaware of - locking or
> something?
>|||Point in time restore can be vary valuable. Another thing that log backups allow you to do is to
backup the log after the data file is lost (or corrupted). Read about the NO_TRUNCATE option to the
BACKUP LOG command. I think you should ask yourself: "What advantages does db and diff backups have
in contrast to db and log backups?". I always go for log backups and only *complement* with diff
backups when needed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||I think Tibor's approach is a good one - use a combination of differential
database backups as well as log backups. This will simplify your recovery
procedure and still allow you to recover to the point of failure if you lose
data files.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Thanks to all.
Greg - knowing that the differential backups have to read the full
backup was a key piece of information in understanding why it is slower
than Log Backups. I'll look into this more thoroughly.
Tibor/Dan - I understand that using all three (full backups,
differentials and Log backups) is the recommended practice - and I
understand how it helps you recover to the point of failure.
What still disappoints me is the fact that losing the log drive makes
you lose the database. It undermines my desire to go through the setup
hassle and expense of keeping and maintaing a transaction log. I have
to setup clients with our products database, and train them to do
backups. none of them are DBAs - many are at the level of novice. The
log backups invariably cause problems, and the drawbacks seem to
outweigh the benefits in these cases.
For myself, in a large installation - I would certainly keep a log and
do log backups - but I'm not sure I can gleefully recommend the same
practice to my clients.
Thanks again for the discussion - I appreciate it.
- Sean

Log Backups vs. Differential Backups

I was recently educated (thanks Dan) that you cannot create a backup of
a database if you lose your log file (due to drive failure of
corruption). Once I learned this, I began wondering about the merits of
even using a log file with the Full recovery model. Why not just use
the Simple recovery model and use frequent differential backups? Why go
through the overhead of log files if you can get almost the same level
of safety using frequent differntial backups?
Is doing differential backups every 15 minutes close enough to the
level of protection afforded by 15 minute log backups?
The way I see it is that the only thing I lose is
up-to-the-point-of-failure restoration. But, what I gain is not having
to worry about a corrupt log file or losing the drive that has the log
file on it. The processing time seems similar.
Am I wildly wrong anout this?
Thanks in advance for your opinions.
- Sean
Hi Sean,
Are you say that you would only lose you log file drive? What happens if
it's the data drive that goes? If you lose your log file of the database
and then detach and reattach the drive the systm will create a new log file.
Then applying the log backups will get you back to the point of failure -
the time to the last log file backup.
Backups are also used for non disaster situations and log file give you more
options there. I have always found log file backups quicker that diff
backups when it
comes to large databases
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||Greg - thanks for your reply. I was not able to rebuild a log file. I
took a sample database with a log file, stopped SQL server, deleted the
log file, and restarted SQL server. The DB was "Suspect". I was unable
to get a new log to build with any consistency - I played with setting
the DB to "Emergency Mode" and messed with detaching/reattaching and
would get it to rebuild a log file 50% of the time - still, the problem
is by losing your log file, the data becomes suspect, and you can't
trust it even if you rebuild the log.
I understand how log file backups can get you to the point of failure
if the data drive fails - but I was concerned with the log drive
failing. This led me to post on the topic: "Log Backups - Why bother
with every ten minutes?" There I learned from Dan that if you lose a
log file, your database is hosed, and you can only restore to your last
backup.
This was a disappointment to me - I really like the idea that we can
restore to the point of failure using logs if the data drive fails -
but if the log drive fails, we are right back to only getting back what
we have as a "backup".
So - I am thinking that differentials in "Simple" recovery are pretty
close to as good as log file backups in "Full" Recovery mode. Granted,
you can't restore to the point of failure EVER - but you aren't that
much worse off if the backups are frequent enough.
You mention that log file backups are quicker than differentials. A
little quicker? or significantly quicker? The database sizes I am
concerned with are ~35 Gig. The full backup process to disk takes less
than 10 minutes. Diffs and log backups are much faster, naturally.
Are there other problems with diffs that I am unaware of - locking or
something?
|||Hi Sean,
In the situation you have describe to create a suspect database I was not
able to reproduct the issue. Having said that it doesn't really matter.
Any backup system that you employ you should follow the MS recommendation .
They are that transaction logs and Data should be protected while on disk.
That is with a form of RAID. While it is unlikely that a disk goes bad is
is very unlikely that two or more will go bad at the same time. So your
first line or protection is always a RAID system.
Next a diff backup is slower than a log backup. This is the bit from BOL
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft SQL ServerT 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information,
see Using File Backups.
So to do a diff backup it needs to read the backup an check the pages that
have been backed up. Where as a log backup backups the entire log file
(pages). Somethime the LOG backup can be larger than the database bacup
because of high transactions but you can always reduce the time between
backups.
In addition having log backups and transaction logs (Full Recovery Mode)
always the use of log reader type productions to see and recover cahnges to
the data without restoring. This might not be an issue now but at sometime
we have all been asked to get back some details that were changed wrongly.
Backup don't tend to cause locking problems for long periods as they backup
pages and do so quickly.
My recommendation is to continue with log backups , ensure that the data and
log files are on RAID systems (RAID 1 for log, RAID 5 for data is typically
good)
Send your backups and log backups to another server via a script See this
link http://www.sql-scripts.com/members/S...ls.aspx?S_ID=4
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124760340.712510.321430@.g47g2000cwa.googlegr oups.com...
> Greg - thanks for your reply. I was not able to rebuild a log file. I
> took a sample database with a log file, stopped SQL server, deleted the
> log file, and restarted SQL server. The DB was "Suspect". I was unable
> to get a new log to build with any consistency - I played with setting
> the DB to "Emergency Mode" and messed with detaching/reattaching and
> would get it to rebuild a log file 50% of the time - still, the problem
> is by losing your log file, the data becomes suspect, and you can't
> trust it even if you rebuild the log.
> I understand how log file backups can get you to the point of failure
> if the data drive fails - but I was concerned with the log drive
> failing. This led me to post on the topic: "Log Backups - Why bother
> with every ten minutes?" There I learned from Dan that if you lose a
> log file, your database is hosed, and you can only restore to your last
> backup.
> This was a disappointment to me - I really like the idea that we can
> restore to the point of failure using logs if the data drive fails -
> but if the log drive fails, we are right back to only getting back what
> we have as a "backup".
> So - I am thinking that differentials in "Simple" recovery are pretty
> close to as good as log file backups in "Full" Recovery mode. Granted,
> you can't restore to the point of failure EVER - but you aren't that
> much worse off if the backups are frequent enough.
> You mention that log file backups are quicker than differentials. A
> little quicker? or significantly quicker? The database sizes I am
> concerned with are ~35 Gig. The full backup process to disk takes less
> than 10 minutes. Diffs and log backups are much faster, naturally.
> Are there other problems with diffs that I am unaware of - locking or
> something?
>
|||Point in time restore can be vary valuable. Another thing that log backups allow you to do is to
backup the log after the data file is lost (or corrupted). Read about the NO_TRUNCATE option to the
BACKUP LOG command. I think you should ask yourself: "What advantages does db and diff backups have
in contrast to db and log backups?". I always go for log backups and only *complement* with diff
backups when needed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||I think Tibor's approach is a good one - use a combination of differential
database backups as well as log backups. This will simplify your recovery
procedure and still allow you to recover to the point of failure if you lose
data files.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||Thanks to all.
Greg - knowing that the differential backups have to read the full
backup was a key piece of information in understanding why it is slower
than Log Backups. I'll look into this more thoroughly.
Tibor/Dan - I understand that using all three (full backups,
differentials and Log backups) is the recommended practice - and I
understand how it helps you recover to the point of failure.
What still disappoints me is the fact that losing the log drive makes
you lose the database. It undermines my desire to go through the setup
hassle and expense of keeping and maintaing a transaction log. I have
to setup clients with our products database, and train them to do
backups. none of them are DBAs - many are at the level of novice. The
log backups invariably cause problems, and the drawbacks seem to
outweigh the benefits in these cases.
For myself, in a large installation - I would certainly keep a log and
do log backups - but I'm not sure I can gleefully recommend the same
practice to my clients.
Thanks again for the discussion - I appreciate it.
- Sean

Log Backups vs. Differential Backups

I was recently educated (thanks Dan) that you cannot create a backup of
a database if you lose your log file (due to drive failure of
corruption). Once I learned this, I began wondering about the merits of
even using a log file with the Full recovery model. Why not just use
the Simple recovery model and use frequent differential backups? Why go
through the overhead of log files if you can get almost the same level
of safety using frequent differntial backups?
Is doing differential backups every 15 minutes close enough to the
level of protection afforded by 15 minute log backups?
The way I see it is that the only thing I lose is
up-to-the-point-of-failure restoration. But, what I gain is not having
to worry about a corrupt log file or losing the drive that has the log
file on it. The processing time seems similar.
Am I wildly wrong anout this?
Thanks in advance for your opinions.
- SeanHi Sean,
Are you say that you would only lose you log file drive? What happens if
it's the data drive that goes? If you lose your log file of the database
and then detach and reattach the drive the systm will create a new log file.
Then applying the log backups will get you back to the point of failure -
the time to the last log file backup.
Backups are also used for non disaster situations and log file give you more
options there. I have always found log file backups quicker that diff
backups when it
comes to large databases
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Greg - thanks for your reply. I was not able to rebuild a log file. I
took a sample database with a log file, stopped SQL server, deleted the
log file, and restarted SQL server. The DB was "Suspect". I was unable
to get a new log to build with any consistency - I played with setting
the DB to "Emergency Mode" and messed with detaching/reattaching and
would get it to rebuild a log file 50% of the time - still, the problem
is by losing your log file, the data becomes suspect, and you can't
trust it even if you rebuild the log.
I understand how log file backups can get you to the point of failure
if the data drive fails - but I was concerned with the log drive
failing. This led me to post on the topic: "Log Backups - Why bother
with every ten minutes?" There I learned from Dan that if you lose a
log file, your database is hosed, and you can only restore to your last
backup.
This was a disappointment to me - I really like the idea that we can
restore to the point of failure using logs if the data drive fails -
but if the log drive fails, we are right back to only getting back what
we have as a "backup".
So - I am thinking that differentials in "Simple" recovery are pretty
close to as good as log file backups in "Full" Recovery mode. Granted,
you can't restore to the point of failure EVER - but you aren't that
much worse off if the backups are frequent enough.
You mention that log file backups are quicker than differentials. A
little quicker? or significantly quicker? The database sizes I am
concerned with are ~35 Gig. The full backup process to disk takes less
than 10 minutes. Diffs and log backups are much faster, naturally.
Are there other problems with diffs that I am unaware of - locking or
something?|||Hi Sean,
In the situation you have describe to create a suspect database I was not
able to reproduct the issue. Having said that it doesn't really matter.
Any backup system that you employ you should follow the MS recommendation .
They are that transaction logs and Data should be protected while on disk.
That is with a form of RAID. While it is unlikely that a disk goes bad is
is very unlikely that two or more will go bad at the same time. So your
first line or protection is always a RAID system.
Next a diff backup is slower than a log backup. This is the bit from BOL
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft SQL ServerT 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information,
see Using File Backups.
So to do a diff backup it needs to read the backup an check the pages that
have been backed up. Where as a log backup backups the entire log file
(pages). Somethime the LOG backup can be larger than the database bacup
because of high transactions but you can always reduce the time between
backups.
In addition having log backups and transaction logs (Full Recovery Mode)
always the use of log reader type productions to see and recover cahnges to
the data without restoring. This might not be an issue now but at sometime
we have all been asked to get back some details that were changed wrongly.
Backup don't tend to cause locking problems for long periods as they backup
pages and do so quickly.
My recommendation is to continue with log backups , ensure that the data and
log files are on RAID systems (RAID 1 for log, RAID 5 for data is typically
good)
Send your backups and log backups to another server via a script See this
link http://www.sql-scripts.com/members/...ils.aspx?S_ID=4
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124760340.712510.321430@.g47g2000cwa.googlegroups.com...
> Greg - thanks for your reply. I was not able to rebuild a log file. I
> took a sample database with a log file, stopped SQL server, deleted the
> log file, and restarted SQL server. The DB was "Suspect". I was unable
> to get a new log to build with any consistency - I played with setting
> the DB to "Emergency Mode" and messed with detaching/reattaching and
> would get it to rebuild a log file 50% of the time - still, the problem
> is by losing your log file, the data becomes suspect, and you can't
> trust it even if you rebuild the log.
> I understand how log file backups can get you to the point of failure
> if the data drive fails - but I was concerned with the log drive
> failing. This led me to post on the topic: "Log Backups - Why bother
> with every ten minutes?" There I learned from Dan that if you lose a
> log file, your database is hosed, and you can only restore to your last
> backup.
> This was a disappointment to me - I really like the idea that we can
> restore to the point of failure using logs if the data drive fails -
> but if the log drive fails, we are right back to only getting back what
> we have as a "backup".
> So - I am thinking that differentials in "Simple" recovery are pretty
> close to as good as log file backups in "Full" Recovery mode. Granted,
> you can't restore to the point of failure EVER - but you aren't that
> much worse off if the backups are frequent enough.
> You mention that log file backups are quicker than differentials. A
> little quicker? or significantly quicker? The database sizes I am
> concerned with are ~35 Gig. The full backup process to disk takes less
> than 10 minutes. Diffs and log backups are much faster, naturally.
> Are there other problems with diffs that I am unaware of - locking or
> something?
>|||Point in time restore can be vary valuable. Another thing that log backups a
llow you to do is to
backup the log after the data file is lost (or corrupted). Read about the NO
_TRUNCATE option to the
BACKUP LOG command. I think you should ask yourself: "What advantages does d
b and diff backups have
in contrast to db and log backups?". I always go for log backups and only *c
omplement* with diff
backups when needed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||I think Tibor's approach is a good one - use a combination of differential
database backups as well as log backups. This will simplify your recovery
procedure and still allow you to recover to the point of failure if you lose
data files.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegroups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>|||Thanks to all.
Greg - knowing that the differential backups have to read the full
backup was a key piece of information in understanding why it is slower
than Log Backups. I'll look into this more thoroughly.
Tibor/Dan - I understand that using all three (full backups,
differentials and Log backups) is the recommended practice - and I
understand how it helps you recover to the point of failure.
What still disappoints me is the fact that losing the log drive makes
you lose the database. It undermines my desire to go through the setup
hassle and expense of keeping and maintaing a transaction log. I have
to setup clients with our products database, and train them to do
backups. none of them are DBAs - many are at the level of novice. The
log backups invariably cause problems, and the drawbacks seem to
outweigh the benefits in these cases.
For myself, in a large installation - I would certainly keep a log and
do log backups - but I'm not sure I can gleefully recommend the same
practice to my clients.
Thanks again for the discussion - I appreciate it.
- Sean