Dimension or Fact?

Hi - I've got a dimensional modelling question that's got me stuck.
I have a "Student" entity and a "Student Transaction" entity.
The Student Transaction table will be a fact table -- that's easy.
And the Student will be a dimension of the Student Transaction table.
HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
of its own -- things like State, Country, etc. It's basically what
Kimball calls a rapidly changing monster dimension!
First - isn't this snowflaking, and is that bad? Should the Student
be another fact? But then how do I relate the Student to the Student
Transaction if they're both facts.
Also, the student has attributes like test scores and GPA that seem
like measures -- but a dimension can't have a measure. So how could I
get average test scores and gpa, etc...
The other thing I was thinking of doing was treating the Student as
both a dimension AND a fact! Basically create 2 cubes:
Student Transaction cube, which has Student table as a dimension
Student cube, which actually uses the same Student table, but as a
fact.
This seems to work, but it seems awfully weird to use the same
physical table as both a fact and a dimension... even if it is in
different cubes...
IF anyone has insight, i would appreciate it very much!
thanksIn your case Stundent is a Dimension, average, etc are properties of the dim
ension not measures.
It's a better practice to design the dimension with star structure. You can
make a view to group all information an make the dimension based on that vie
w.
Also you can think several cubes that uses Student dimension and time dimens
ion, like test, etc. And then you can calcultate average and other. A way to
join these cubes is with virtual cubes.
Good Luck|||The "badness" of snowflaking is highly overrated. Conversely, the benefits
of denormalized stars are also overrated.
public @. the domain below
www.tomchester.net
"groove_sf" <dockv@.hot-NOSPAM-mail.com> wrote in message
news:cfpu70dvqh0s8c2p1etalnjdrvos9et1mf@.
4ax.com...
> Hi - I've got a dimensional modelling question that's got me stuck.
> I have a "Student" entity and a "Student Transaction" entity.
> The Student Transaction table will be a fact table -- that's easy.
> And the Student will be a dimension of the Student Transaction table.
> HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
> of its own -- things like State, Country, etc. It's basically what
> Kimball calls a rapidly changing monster dimension!
> First - isn't this snowflaking, and is that bad? Should the Student
> be another fact? But then how do I relate the Student to the Student
> Transaction if they're both facts.
> Also, the student has attributes like test scores and GPA that seem
> like measures -- but a dimension can't have a measure. So how could I
> get average test scores and gpa, etc...
> The other thing I was thinking of doing was treating the Student as
> both a dimension AND a fact! Basically create 2 cubes:
> Student Transaction cube, which has Student table as a dimension
> Student cube, which actually uses the same Student table, but as a
> fact.
> This seems to work, but it seems awfully weird to use the same
> physical table as both a fact and a dimension... even if it is in
> different cubes...
> IF anyone has insight, i would appreciate it very much!
> thanks
>|||I disagree that the GPA and test scores should be part of the Student dimens
ion. They are semi-additive facts, and should be considered for a separate
star in your constellation (assuming you are using an architected DW with co
nformed dimensions.)
Think about it this way - if you have student transactions as one "business
process", thus is one star center, then GPA and test scores would merit its
own cetricity in a separate fact table, since GPA and test scores are not tr
ansactions, but are "Grades
", though GPA is going to need to be atomized into GPA factoids so it can be
calculated on the fly if you have other dimensions that will be used to sli
ce and dice. For example, you might want GPA by students by a particular ma
jor, or students in the cla
ss of 2005, etc.
Thus you would have Student as a conforming dimension to both a Transactions
fact table and a Grades fact table, thus the need for at least two stars in
your constellation that share the conformed Student dimension.

Dimension or Fact?

Hi - I've got a dimensional modelling question that's got me stuck.
I have a "Student" entity and a "Student Transaction" entity.
The Student Transaction table will be a fact table -- that's easy.
And the Student will be a dimension of the Student Transaction table.
HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
of its own -- things like State, Country, etc. It's basically what
Kimball calls a rapidly changing monster dimension!
First - isn't this snowflaking, and is that bad? Should the Student
be another fact? But then how do I relate the Student to the Student
Transaction if they're both facts.
Also, the student has attributes like test scores and GPA that seem
like measures -- but a dimension can't have a measure. So how could I
get average test scores and gpa, etc...
The other thing I was thinking of doing was treating the Student as
both a dimension AND a fact! Basically create 2 cubes:
Student Transaction cube, which has Student table as a dimension
Student cube, which actually uses the same Student table, but as a
fact.
This seems to work, but it seems awfully weird to use the same
physical table as both a fact and a dimension... even if it is in
different cubes...
IF anyone has insight, i would appreciate it very much!
thanks
In your case Stundent is a Dimension, average, etc are properties of the dimension not measures.
It's a better practice to design the dimension with star structure. You can make a view to group all information an make the dimension based on that view.
Also you can think several cubes that uses Student dimension and time dimension, like test, etc. And then you can calcultate average and other. A way to join these cubes is with virtual cubes.
Good Luck
|||The "badness" of snowflaking is highly overrated. Conversely, the benefits
of denormalized stars are also overrated.
public @. the domain below
www.tomchester.net
"groove_sf" <dockv@.hot-NOSPAM-mail.com> wrote in message
news:cfpu70dvqh0s8c2p1etalnjdrvos9et1mf@.4ax.com...
> Hi - I've got a dimensional modelling question that's got me stuck.
> I have a "Student" entity and a "Student Transaction" entity.
> The Student Transaction table will be a fact table -- that's easy.
> And the Student will be a dimension of the Student Transaction table.
> HOWEVER, the Student dimension is HUGE and has a lot of sub-dimensions
> of its own -- things like State, Country, etc. It's basically what
> Kimball calls a rapidly changing monster dimension!
> First - isn't this snowflaking, and is that bad? Should the Student
> be another fact? But then how do I relate the Student to the Student
> Transaction if they're both facts.
> Also, the student has attributes like test scores and GPA that seem
> like measures -- but a dimension can't have a measure. So how could I
> get average test scores and gpa, etc...
> The other thing I was thinking of doing was treating the Student as
> both a dimension AND a fact! Basically create 2 cubes:
> Student Transaction cube, which has Student table as a dimension
> Student cube, which actually uses the same Student table, but as a
> fact.
> This seems to work, but it seems awfully weird to use the same
> physical table as both a fact and a dimension... even if it is in
> different cubes...
> IF anyone has insight, i would appreciate it very much!
> thanks
>
|||I disagree that the GPA and test scores should be part of the Student dimension. They are semi-additive facts, and should be considered for a separate star in your constellation (assuming you are using an architected DW with conformed dimensions.)
Think about it this way - if you have student transactions as one "business process", thus is one star center, then GPA and test scores would merit its own cetricity in a separate fact table, since GPA and test scores are not transactions, but are "Grades
", though GPA is going to need to be atomized into GPA factoids so it can be calculated on the fly if you have other dimensions that will be used to slice and dice. For example, you might want GPA by students by a particular major, or students in the cla
ss of 2005, etc.
Thus you would have Student as a conforming dimension to both a Transactions fact table and a Grades fact table, thus the need for at least two stars in your constellation that share the conformed Student dimension.
sql

Difficulties getting @@identity to work in transaction

I am having some problems wit a form where i need to insert a record into a table and then get the id of the record i just inserted and use it to insert a record in another table for a many to many relationship. The code is below (I cut out as much as i could to make it more readable).

The erro message i get is this:

Error saving file ATLPIXOFC.txt Reason: System.Data.SqlClient.SqlException: Prepared statement '(@.FileName varchar(13),@. i' expects parameter @.FileID, which was not supplied. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microvit_Document_Product_Document_Add_v2.insertDocumentToDB() in e:\inetpub\wwwroot\Microvit\Document\Product_Document_Add_v2.aspx.vb:line 127 at Microvit_Document_Product_Document_Add_v2.btnInsert_Click(Object sender, EventArgs e) in e:\inetpub\wwwroot\Microvit\Document\Product_Document_Add_v2.aspx.vb:line 37

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, ParameterDirection.Output))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute 1st Command

'****************************************************************************

myCommand.CommandText ="INSERT INTO [Files] ([FileName) VALUES (@.FileName); SELECT @.FileID = @.@.Identity;"

myCommand.ExecuteNonQuery()

'****************************************************************************

' Execute 2nd Command

'****************************************************************************

'myCommand.Parameters("@.FileID").Value = ddlProduct.SelectedValue

myCommand.CommandText ="INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, @.FileID)"

myCommand.ExecuteNonQuery()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

Has nothing to do with transactions.

You defined the @.FileID parameter as such:

myCommand.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, ParameterDirection.Output))

Note, you said output. The second command expects it as input.

You can fix this a number of ways. Use two different mycommand objects with different parameters, or set variables to the parameters, clear and rebuild the parameters, and reset the parameter values from your variables, change your first query to accept the same parameters, or just use a single command.

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute Command

'****************************************************************************

myCommand.CommandText ="INSERT INTO [Files] ([FileName) VALUES (@.FileName) INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, SCOPE_IDENTITY())"

myCommand.ExecuteNonQuery()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

|||

Or if you really need the identity back in your program:

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute Command

'****************************************************************************

myCommand.CommandText ="SET NOCOUNT ON INSERT INTO [Files] ([FileName) VALUES (@.FileName) SET NOCOUNT OFF SELECT SCOPE_IDENTITY() SET NOCOUNT ON INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, SCOPE_IDENTITY()) SET NOCOUNT OFF"

dim MyIdentity=myCommand.ExecuteScaler()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

|||Oops, I guess I should mention that you can of course, create a stored procedure to do the insert as well which takes all the things you want to insert into your various tables as parameters.sql

Differential backups and transaction logs.

Hello. it would be great if someone could help me.
I have a large database (18GB) which is being backed up on the Monday
of every week. Transactions logs are taken every 15mins during the day
and then a diff at the end of the day. My question is this, if a take
a differential at the end of the day can i still restore the
transaction logs from that day after restoring the full backup? I can
restore the full and differentials then the logs fine but when i try
and restore say the full and the transactions it complains that there
is a previous log which there isnt.
Any help would be great at this point.
Try out once again. I have gone thorugh lot of transaction log loads, it
didnt caused me any trouble. I faced the same issue when we transfer it to DR
server, sometimes FTP fail so manually copies it and go for log load which
works fine. Can you post the Error you getting, in that there will be
timestamp saying till what time load happend.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"bungy" wrote:

> Hello. it would be great if someone could help me.
> I have a large database (18GB) which is being backed up on the Monday
> of every week. Transactions logs are taken every 15mins during the day
> and then a diff at the end of the day. My question is this, if a take
> a differential at the end of the day can i still restore the
> transaction logs from that day after restoring the full backup? I can
> restore the full and differentials then the logs fine but when i try
> and restore say the full and the transactions it complains that there
> is a previous log which there isnt.
> Any help would be great at this point.
>
|||Thanks for the help but I found out what the problem was. zip extract
script that was extracting the files wasnt working correctly and the
sproc that is doing the restore wasnt right. Its hard if you didnt
write the bloody thing yourself.
Many thanks,
Sreejith G wrote:[vbcol=seagreen]
> Try out once again. I have gone thorugh lot of transaction log loads, it
> didnt caused me any trouble. I faced the same issue when we transfer it to DR
> server, sometimes FTP fail so manually copies it and go for log load which
> works fine. Can you post the Error you getting, in that there will be
> timestamp saying till what time load happend.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "bungy" wrote:

Differential backups and transaction logs.

Hello. it would be great if someone could help me.
I have a large database (18GB) which is being backed up on the Monday
of every week. Transactions logs are taken every 15mins during the day
and then a diff at the end of the day. My question is this, if a take
a differential at the end of the day can i still restore the
transaction logs from that day after restoring the full backup? I can
restore the full and differentials then the logs fine but when i try
and restore say the full and the transactions it complains that there
is a previous log which there isnt.
Any help would be great at this point.Try out once again. I have gone thorugh lot of transaction log loads, it
didnt caused me any trouble. I faced the same issue when we transfer it to D
R
server, sometimes FTP fail so manually copies it and go for log load which
works fine. Can you post the Error you getting, in that there will be
timestamp saying till what time load happend.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"bungy" wrote:

> Hello. it would be great if someone could help me.
> I have a large database (18GB) which is being backed up on the Monday
> of every week. Transactions logs are taken every 15mins during the day
> and then a diff at the end of the day. My question is this, if a take
> a differential at the end of the day can i still restore the
> transaction logs from that day after restoring the full backup? I can
> restore the full and differentials then the logs fine but when i try
> and restore say the full and the transactions it complains that there
> is a previous log which there isnt.
> Any help would be great at this point.
>|||Thanks for the help but I found out what the problem was. zip extract
script that was extracting the files wasnt working correctly and the
sproc that is doing the restore wasnt right. Its hard if you didnt
write the bloody thing yourself.
Many thanks,
Sreejith G wrote:[vbcol=seagreen]
> Try out once again. I have gone thorugh lot of transaction log loads, it
> didnt caused me any trouble. I faced the same issue when we transfer it to
DR
> server, sometimes FTP fail so manually copies it and go for log load which
> works fine. Can you post the Error you getting, in that there will be
> timestamp saying till what time load happend.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "bungy" wrote:
>

Differential backups and transaction logs.

Hello. it would be great if someone could help me.
I have a large database (18GB) which is being backed up on the Monday
of every week. Transactions logs are taken every 15mins during the day
and then a diff at the end of the day. My question is this, if a take
a differential at the end of the day can i still restore the
transaction logs from that day after restoring the full backup? I can
restore the full and differentials then the logs fine but when i try
and restore say the full and the transactions it complains that there
is a previous log which there isnt.
Any help would be great at this point.Try out once again. I have gone thorugh lot of transaction log loads, it
didnt caused me any trouble. I faced the same issue when we transfer it to DR
server, sometimes FTP fail so manually copies it and go for log load which
works fine. Can you post the Error you getting, in that there will be
timestamp saying till what time load happend.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"bungy" wrote:
> Hello. it would be great if someone could help me.
> I have a large database (18GB) which is being backed up on the Monday
> of every week. Transactions logs are taken every 15mins during the day
> and then a diff at the end of the day. My question is this, if a take
> a differential at the end of the day can i still restore the
> transaction logs from that day after restoring the full backup? I can
> restore the full and differentials then the logs fine but when i try
> and restore say the full and the transactions it complains that there
> is a previous log which there isnt.
> Any help would be great at this point.
>|||Thanks for the help but I found out what the problem was. zip extract
script that was extracting the files wasnt working correctly and the
sproc that is doing the restore wasnt right. Its hard if you didnt
write the bloody thing yourself.
Many thanks,
Sreejith G wrote:
> Try out once again. I have gone thorugh lot of transaction log loads, it
> didnt caused me any trouble. I faced the same issue when we transfer it to DR
> server, sometimes FTP fail so manually copies it and go for log load which
> works fine. Can you post the Error you getting, in that there will be
> timestamp saying till what time load happend.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "bungy" wrote:
> > Hello. it would be great if someone could help me.
> >
> > I have a large database (18GB) which is being backed up on the Monday
> > of every week. Transactions logs are taken every 15mins during the day
> > and then a diff at the end of the day. My question is this, if a take
> > a differential at the end of the day can i still restore the
> > transaction logs from that day after restoring the full backup? I can
> > restore the full and differentials then the logs fine but when i try
> > and restore say the full and the transactions it complains that there
> > is a previous log which there isnt.
> >
> > Any help would be great at this point.
> >
> >

differential backups

Hi,
Running SQL 7. We have a maintanence plan doing transaction log backups
every hour. Outside of the maintanence plan we have a job that does a
differential backup of the database. My question is, when the differential
backup occurs, is the transaction log truncated? For example if the last
transaction log backup was at 1pm, and a differential at 1:30pm, then
another transaction log backup at 2pm; will the 2pm transaction log backup
contain the transactions at 1:15pm?
Secondly, how can I modify the differential backup job below to:
a) name the filename of the diff backup file to be
<database_name>_diff_MMDDYYhhmmss.bak
b) have the script remove diff backup files older than 1 day.oops! here is the script I need to modify that I referred to below:
BACKUP DATABASE [myDB] TO DISK =N'F:\hot_backups\myDB\myDB_db_latest_diff.BAK' WITH INIT , NOUNLOAD ,
DIFFERENTIAL , NAME = N'myDB latest diff backup', SKIP , STATS = 10,
DESCRIPTION = N'every 5 hour diff', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='myDB'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='myDB')
RESTORE VERIFYONLY FROM DISK =N'F:\hot_backups\myDB\myDB_db_latest_diff.BAK' WITH FILE = @.i
"aaz" <aaz@.webcapacity.com> wrote in message
news:uSP7mY$SDHA.3188@.tk2msftngp13.phx.gbl...
> Hi,
> Running SQL 7. We have a maintanence plan doing transaction log backups
> every hour. Outside of the maintanence plan we have a job that does a
> differential backup of the database. My question is, when the differential
> backup occurs, is the transaction log truncated? For example if the last
> transaction log backup was at 1pm, and a differential at 1:30pm, then
> another transaction log backup at 2pm; will the 2pm transaction log backup
> contain the transactions at 1:15pm?
> Secondly, how can I modify the differential backup job below to:
> a) name the filename of the diff backup file to be
> <database_name>_diff_MMDDYYhhmmss.bak
> b) have the script remove diff backup files older than 1 day.
>|||Azz
Running a differential backup does not truncate the
transaction log, it just records all the changes to the
database sine the last full backup.
Bear in mind you should not just do differential backups,
you should do a full backup as well as part of you backup
strategy. How often depends on the size of your database
and how dynamic the data is.
If you do not do full backups eventually your differential
will be as large and take as long as a full backup, and
you will still need a full backup to use it.
Regards
John|||yeah we are also doing fulls once a day, diffs 2x, then transaction logs
every hour. I just wanted to make sure that running the differential did not
truncate the transaction logs.
can anyone help with the second 1/2 of my question?
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:093f01c34c3e$2afca6b0$a101280a@.phx.gbl...
> Azz
> Running a differential backup does not truncate the
> transaction log, it just records all the changes to the
> database sine the last full backup.
> Bear in mind you should not just do differential backups,
> you should do a full backup as well as part of you backup
> strategy. How often depends on the size of your database
> and how dynamic the data is.
> If you do not do full backups eventually your differential
> will be as large and take as long as a full backup, and
> you will still need a full backup to use it.
> Regards
> John|||Here is a sample to start from:
-- Separate file for each day of the week --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\MVP\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD D:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "D:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly
SQL Server MVP
"aaz" <aaz@.webcapacity.com> wrote in message
news:Owh8jYHTDHA.2460@.TK2MSFTNGP10.phx.gbl...
> yeah we are also doing fulls once a day, diffs 2x, then transaction logs
> every hour. I just wanted to make sure that running the differential did
not
> truncate the transaction logs.
> can anyone help with the second 1/2 of my question?
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:093f01c34c3e$2afca6b0$a101280a@.phx.gbl...
> > Azz
> >
> > Running a differential backup does not truncate the
> > transaction log, it just records all the changes to the
> > database sine the last full backup.
> >
> > Bear in mind you should not just do differential backups,
> > you should do a full backup as well as part of you backup
> > strategy. How often depends on the size of your database
> > and how dynamic the data is.
> >
> > If you do not do full backups eventually your differential
> > will be as large and take as long as a full backup, and
> > you will still need a full backup to use it.
> >
> > Regards
> >
> > John
>

Differential Backup Size and Transaction Log Backup Size !

Hello All,
Sometime back we had scheduled Differential database backups on our OLTP
system. The backup is scheduled to run once at 6AM, once at 12Noon and
7PM. This backup is apart from the half hourly transaction log backup
and the daily full database backup.
Normally, for a day, the Transaction log backup does not exceed 300MB and
the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
exceed 500MB and the 7PM Diff backup does not exceed 600MB.
However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
but the Transaction log backup still 300MB.
I would expect the Transaction Log backup also to show around 1500MB.
Does anyone know why the differential database backup is so large when
compared to the Transaction Log.
Thanks,
RgnOne explanation would be that you move to bulk logged recovery mode, and
bcp'd in a lot of data ( which is minimally logged).
Another explanation would be that you dropped/re-created a bunch of indexes,
only the drop/create statement would be in the log, but the entire index
would be copied in the differential backup.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rgn" <anonymous@.discussions.microsoft.com> wrote in message
news:9B20C782-042B-4D0C-857C-F68787C5C45C@.microsoft.com...
> Hello All,
> Sometime back we had scheduled Differential database backups on our OLTP
> system. The backup is scheduled to run once at 6AM, once at 12Noon and
> 7PM. This backup is apart from the half hourly transaction log backup
> and the daily full database backup.
> Normally, for a day, the Transaction log backup does not exceed 300MB and
> the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
> exceed 500MB and the 7PM Diff backup does not exceed 600MB.
> However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
> the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
> but the Transaction log backup still 300MB.
> I would expect the Transaction Log backup also to show around 1500MB.
> Does anyone know why the differential database backup is so large when
> compared to the Transaction Log.
> Thanks,
> Rgn|||Wayne
You are very true in saying that the bulk-logged operation generates a large transaction log backup and/or differential DB backup. However, my question is that, if the differential Db backup is 1.5GB then even the transaction log backup shoul
be of simillar size, right
In my case, the transaction log seems to be only 300MB where as the differential db backup is 1.4GB
rgn

Differential Backup Restore Problem

I have a maintenance plan for a database Where I have a
full database backups on every sunday and a differential
backup every evening and transaction log backups every
hour (7:00 Am - 7:00 Pm).
I have a backup server where I restore my backups. After I
restore my full backup, I restore the differential backup
then the transaction log backups. I created a job to
restore my backups but it fails after the first
differential backup (Tuesday's differential backup). It
restore the first differential backup after the full
backup but not the second one or the others after the
first one. I only have transaction log backups in between.
When I restore the second differential backup manually, I
see that the first differential backup is selected in
the 'view contents' box, anotherwords, it is trying to
restore the first differential backup with the second diff
backup file.
Is there a way to restore diff. backups in a sequence '
Thanks for any info........Here's some info from BOL's, you need to specify 'with
file' and the number. If you have three, you would restore
increase the file number for each differential.
Hope that helps... here's the examples from BOL
--
This example restores a database, differential database,
and transaction log backup of the MyNwind database.
-- Assume the database is lost at this point. Now restore
the full
-- database. Specify the original full backup and
NORECOVERY.
-- NORECOVERY allows subsequent restore operations to
proceed.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY
GO
-- Now restore the differential database backup, the
second backup on
-- the MyNwind_1 backup device.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH FILE = 2,
NORECOVERY
GO
-- Now restore each transaction log backup created after
-- the differential database backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
>--Original Message--
>I have a maintenance plan for a database Where I have a
>full database backups on every sunday and a differential
>backup every evening and transaction log backups every
>hour (7:00 Am - 7:00 Pm).
>I have a backup server where I restore my backups. After
I
>restore my full backup, I restore the differential backup
>then the transaction log backups. I created a job to
>restore my backups but it fails after the first
>differential backup (Tuesday's differential backup). It
>restore the first differential backup after the full
>backup but not the second one or the others after the
>first one. I only have transaction log backups in between.
>When I restore the second differential backup manually, I
>see that the first differential backup is selected in
>the 'view contents' box, anotherwords, it is trying to
>restore the first differential backup with the second
diff
>backup file.
>Is there a way to restore diff. backups in a sequence '
>Thanks for any info........
>.
>|||Then, How can this be an automated process? I am using a
disk backup............
>--Original Message--
>Here's some info from BOL's, you need to specify 'with
>file' and the number. If you have three, you would restore
>increase the file number for each differential.
>Hope that helps... here's the examples from BOL
>--
>This example restores a database, differential database,
>and transaction log backup of the MyNwind database.
>-- Assume the database is lost at this point. Now restore
>the full
>-- database. Specify the original full backup and
>NORECOVERY.
>-- NORECOVERY allows subsequent restore operations to
>proceed.
>RESTORE DATABASE MyNwind
> FROM MyNwind_1
> WITH NORECOVERY
>GO
>-- Now restore the differential database backup, the
>second backup on
>-- the MyNwind_1 backup device.
>RESTORE DATABASE MyNwind
> FROM MyNwind_1
> WITH FILE = 2,
> NORECOVERY
>GO
>-- Now restore each transaction log backup created after
>-- the differential database backup.
>RESTORE LOG MyNwind
> FROM MyNwind_log1
> WITH NORECOVERY
>GO
>RESTORE LOG MyNwind
> FROM MyNwind_log2
> WITH RECOVERY
>GO
>
>>--Original Message--
>>I have a maintenance plan for a database Where I have a
>>full database backups on every sunday and a differential
>>backup every evening and transaction log backups every
>>hour (7:00 Am - 7:00 Pm).
>>I have a backup server where I restore my backups. After
>I
>>restore my full backup, I restore the differential
backup
>>then the transaction log backups. I created a job to
>>restore my backups but it fails after the first
>>differential backup (Tuesday's differential backup). It
>>restore the first differential backup after the full
>>backup but not the second one or the others after the
>>first one. I only have transaction log backups in
between.
>>When I restore the second differential backup manually,
I
>>see that the first differential backup is selected in
>>the 'view contents' box, anotherwords, it is trying to
>>restore the first differential backup with the second
>diff
>>backup file.
>>Is there a way to restore diff. backups in a sequence '
>>Thanks for any info........
>>.
>.
>|||Why would you want to restore diff backups in sequence? The only one needed is the last diff backup
and then all subsequent log backups. Or did I misunderstand your situation?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:03c501c3b918$2c88c3a0$a301280a@.phx.gbl...
> I have a maintenance plan for a database Where I have a
> full database backups on every sunday and a differential
> backup every evening and transaction log backups every
> hour (7:00 Am - 7:00 Pm).
> I have a backup server where I restore my backups. After I
> restore my full backup, I restore the differential backup
> then the transaction log backups. I created a job to
> restore my backups but it fails after the first
> differential backup (Tuesday's differential backup). It
> restore the first differential backup after the full
> backup but not the second one or the others after the
> first one. I only have transaction log backups in between.
> When I restore the second differential backup manually, I
> see that the first differential backup is selected in
> the 'view contents' box, anotherwords, it is trying to
> restore the first differential backup with the second diff
> backup file.
> Is there a way to restore diff. backups in a sequence '
> Thanks for any info........|||That is exacly what I want but not manually. I want to
automate this process..............
>--Original Message--
>Why would you want to restore diff backups in sequence?
The only one needed is the last diff backup
>and then all subsequent log backups. Or did I
misunderstand your situation?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:03c501c3b918$2c88c3a0$a301280a@.phx.gbl...
>> I have a maintenance plan for a database Where I have a
>> full database backups on every sunday and a differential
>> backup every evening and transaction log backups every
>> hour (7:00 Am - 7:00 Pm).
>> I have a backup server where I restore my backups.
After I
>> restore my full backup, I restore the differential
backup
>> then the transaction log backups. I created a job to
>> restore my backups but it fails after the first
>> differential backup (Tuesday's differential backup). It
>> restore the first differential backup after the full
>> backup but not the second one or the others after the
>> first one. I only have transaction log backups in
between.
>> When I restore the second differential backup manually,
I
>> see that the first differential backup is selected in
>> the 'view contents' box, anotherwords, it is trying to
>> restore the first differential backup with the second
diff
>> backup file.
>> Is there a way to restore diff. backups in a sequence '
>> Thanks for any info........
>
>.
>|||You can read the output from the RESTORE HEADERONLY command if you need to find out this information
from the backup device and if you have several backups on the same backup device. Or do as EM does,
read the info from the backup history tables in the MSDB database.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:050001c3b9a6$0d5611c0$a001280a@.phx.gbl...
> That is exacly what I want but not manually. I want to
> automate this process..............
>
>
> >--Original Message--
> >Why would you want to restore diff backups in sequence?
> The only one needed is the last diff backup
> >and then all subsequent log backups. Or did I
> misunderstand your situation?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:03c501c3b918$2c88c3a0$a301280a@.phx.gbl...
> >> I have a maintenance plan for a database Where I have a
> >> full database backups on every sunday and a differential
> >> backup every evening and transaction log backups every
> >> hour (7:00 Am - 7:00 Pm).
> >>
> >> I have a backup server where I restore my backups.
> After I
> >> restore my full backup, I restore the differential
> backup
> >> then the transaction log backups. I created a job to
> >> restore my backups but it fails after the first
> >> differential backup (Tuesday's differential backup). It
> >> restore the first differential backup after the full
> >> backup but not the second one or the others after the
> >> first one. I only have transaction log backups in
> between.
> >>
> >> When I restore the second differential backup manually,
> I
> >> see that the first differential backup is selected in
> >> the 'view contents' box, anotherwords, it is trying to
> >> restore the first differential backup with the second
> diff
> >> backup file.
> >>
> >> Is there a way to restore diff. backups in a sequence '
> >>
> >> Thanks for any info........
> >
> >
> >.
> >

DIFFERENTIAL Backup Problem

I have a full recovery model backup scenario in place where I do full db
backups every night, incremental transaction log backups every 15 minutes and
differential backups of the database every hour. O/S is Windows 2000 Advanced
Server, SQL 2000 sp3a, latest patches, hot fixes etc on everything…
This has been working flawlessly for over a year; now for some reason one of
my differential backup jobs is backing up the entire database every hour
instead of what has changed since the last full.
This happened for the first time about a week ago and after checking the
database for errors etc and finding nothing; all I could narrow it down to
was that for some
reason the job did not like the applicable record in msdb..backupset; so the
interim fix was to do a manual full backup forcing a new record to be created
and it started working fine again for a few days...
It started doing it again on the same database yesterday; same same; no
errors found; the differential just started doing full backups instead of
differentials...
Here is my script for my incremental backup;
declare @.strFileNamePath varchar(100)
set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbTransLog' +
convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
cast(datepart(mi,getdate())as varchar) + '.bak'
BACKUP LOG [TheDb]
TO DISK = @.strFileNamePath
WITH NOINIT, NOUNLOAD, RETAINDAYS = 5,
NAME = N'TheDb backup',
NOSKIP,
STATS = 10,
NOFORMAT
DECLARE @.i INT
select @.i = position
from msdb..backupset
where database_name='TheDb'
and type!='F'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset
where database_name='TheDb')
RESTORE VERIFYONLY
FROM DISK = @.strFileNamePath
WITH FILE = @.i
Here is my script for my differential backup:
declare
@.strFileNamePath varchar(100)
set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbDaily' +
convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
'.bak'
BACKUP DATABASE [TheDb]
TO DISK = @.strFileNamePath
WITH NOINIT, NOUNLOAD, DIFFERENTIAL, RETAINDAYS = 5,
NAME = N'TheDb backup',
NOSKIP ,
STATS = 10,
NOFORMAT
DECLARE @.i INT
select @.i = position
from msdb..backupset
where database_name='TheDb'and type!='F'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset
where database_name='TheDb')
RESTORE VERIFYONLY FROM DISK = @.strFileNamePath
WITH FILE = @.i
The full (a db maintenance plan job):
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
65514236-64B7-493A-841A-88F695626EB1 -Rpt "\\BackupServer\SQL Backups\TheDb
DB Maintenance Plan14.txt" -DelTxtRpt 5DAYS -WriteHistory -VrfyBackup
-BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB "\\BackupServer\SQL
Backups\TheDb" -DelBkUps 5DAYS -BkExt "BAK"'
As I said all these have been working flawlessly for over a year...
Thanks in advance.
I'm not sure what you are saying. Does it really produce a full backup (judging by file size)? If
you look at this backup using RESTORE HEADERONLY, is it a db backup or a diff backup? I don't see
how backup history msdb could affect the backup command you execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LvBohemian" <LvBohemian@.discussions.microsoft.com> wrote in message
news:5F24EC8D-A6B0-496B-B1F1-7D8B3687F059@.microsoft.com...
>I have a full recovery model backup scenario in place where I do full db
> backups every night, incremental transaction log backups every 15 minutes and
> differential backups of the database every hour. O/S is Windows 2000 Advanced
> Server, SQL 2000 sp3a, latest patches, hot fixes etc on everything…
> This has been working flawlessly for over a year; now for some reason one of
> my differential backup jobs is backing up the entire database every hour
> instead of what has changed since the last full.
> This happened for the first time about a week ago and after checking the
> database for errors etc and finding nothing; all I could narrow it down to
> was that for some
> reason the job did not like the applicable record in msdb..backupset; so the
> interim fix was to do a manual full backup forcing a new record to be created
> and it started working fine again for a few days...
> It started doing it again on the same database yesterday; same same; no
> errors found; the differential just started doing full backups instead of
> differentials...
> Here is my script for my incremental backup;
> declare @.strFileNamePath varchar(100)
> set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbTransLog' +
> convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
> cast(datepart(mi,getdate())as varchar) + '.bak'
> BACKUP LOG [TheDb]
> TO DISK = @.strFileNamePath
> WITH NOINIT, NOUNLOAD, RETAINDAYS = 5,
> NAME = N'TheDb backup',
> NOSKIP,
> STATS = 10,
> NOFORMAT
> DECLARE @.i INT
> select @.i = position
> from msdb..backupset
> where database_name='TheDb'
> and type!='F'
> and backup_set_id=(select max(backup_set_id)
> from msdb..backupset
> where database_name='TheDb')
> RESTORE VERIFYONLY
> FROM DISK = @.strFileNamePath
> WITH FILE = @.i
> Here is my script for my differential backup:
> declare
> @.strFileNamePath varchar(100)
> set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbDaily' +
> convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
> '.bak'
> BACKUP DATABASE [TheDb]
> TO DISK = @.strFileNamePath
> WITH NOINIT, NOUNLOAD, DIFFERENTIAL, RETAINDAYS = 5,
> NAME = N'TheDb backup',
> NOSKIP ,
> STATS = 10,
> NOFORMAT
> DECLARE @.i INT
> select @.i = position
> from msdb..backupset
> where database_name='TheDb'and type!='F'
> and backup_set_id=(select max(backup_set_id)
> from msdb..backupset
> where database_name='TheDb')
> RESTORE VERIFYONLY FROM DISK = @.strFileNamePath
> WITH FILE = @.i
> The full (a db maintenance plan job):
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 65514236-64B7-493A-841A-88F695626EB1 -Rpt "\\BackupServer\SQL Backups\TheDb
> DB Maintenance Plan14.txt" -DelTxtRpt 5DAYS -WriteHistory -VrfyBackup
> -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB "\\BackupServer\SQL
> Backups\TheDb" -DelBkUps 5DAYS -BkExt "BAK"'
> As I said all these have been working flawlessly for over a year...
> Thanks in advance.
>
|||I don't understand it either...
running RESTORED HEADERONLY tells me that it is in fact a differential
backup with a backuptype of 5 in the result set...
I am ascertaining that it is the entire database based on the physical size
of the backup, its just too big to be the differential...
Am I missing somthing obvious?
The scripts in my previous post have been working as is for over a year, now
this headache...
Any suggestions would be most welcome.
Thanks in advance.
|||and the differential backup size is almost exactly the same size as the full
backups
|||Perhaps you did some mass-modification of data? Like rebuilding indexes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LvBohemian" <LvBohemian@.discussions.microsoft.com> wrote in message
news:15B2AB34-D6B7-4E87-9828-A7ACABD731F7@.microsoft.com...
>I don't understand it either...
> running RESTORED HEADERONLY tells me that it is in fact a differential
> backup with a backuptype of 5 in the result set...
> I am ascertaining that it is the entire database based on the physical size
> of the backup, its just too big to be the differential...
> Am I missing somthing obvious?
> The scripts in my previous post have been working as is for over a year, now
> this headache...
> Any suggestions would be most welcome.
> Thanks in advance.
|||Indexes are rebuilt as needed by another job, that also has not changed in
over a year...
How would some mass-modification of data like rebuilding an index or other?
cause something like this?
I'd really like to figure out the cause and solution...
Thanks in advance.
RE:
"Tibor Karaszi" wrote:
Perhaps you did some mass-modification of data? Like rebuilding indexes...
|||A diff backup contains all pages that has been modified since the last database backup. If you
rebuild an index, then all pages that the index uses are modified (both pages for the old index and
for the new index).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LvBohemian" <LvBohemian@.discussions.microsoft.com> wrote in message
news:7E5552A0-77C5-40B0-9262-7B01E8646B8D@.microsoft.com...
> Indexes are rebuilt as needed by another job, that also has not changed in
> over a year...
> How would some mass-modification of data like rebuilding an index or other?
> cause something like this?
> I'd really like to figure out the cause and solution...
> Thanks in advance.
> RE:
> "Tibor Karaszi" wrote:
> Perhaps you did some mass-modification of data? Like rebuilding indexes...
>

Differential Backup

I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>|||>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
>AK
>
>.
>|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK
>--Original Message--
>I wouldn't figure the differential should ever be larger
than the full DB
>backup. Is the differential backup writing to the same
file each time? If so
>then you're probably adding to the file each time, look
up the WITH FORMAT
>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
>> I have the following scheduled for backup:
>> a) Full back up (daily) which creates a *.bak of 1.5GB
in
>> size.
>> b) Transaction log (every 2 hours) which creates a *.TRN
>> of 20 MB
>> c) Differential back up which creates a *.bak of 9GB and
>> continues to grow.
>>
>> Couple of questions:
>> 1) Is the size normal for Differential? What am I doing
>> wrong? Why is it so big?
>> 2) Is there only one file that gets created for
>> differential?
>>
>> AK
>>
>
>.
>|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
>--Original Message--
>>--Original Message--
>>I have the following scheduled for backup:
>>a) Full back up (daily) which creates a *.bak of 1.5GB
in
>>size.
>>b) Transaction log (every 2 hours) which creates a *.TRN
>>of 20 MB
>>c) Differential back up which creates a *.bak of 9GB and
>>continues to grow.
>>
>>Couple of questions:
>>1) Is the size normal for Differential? What am I doing
>>wrong? Why is it so big?
>>2) Is there only one file that gets created for
>>differential?
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>>AK
>>
>>.
>.
>|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
> >--Original Message--
> >I wouldn't figure the differential should ever be larger
> than the full DB
> >backup. Is the differential backup writing to the same
> file each time? If so
> >then you're probably adding to the file each time, look
> up the WITH FORMAT
> >option of the BACKUP command.
> >
> >Mike Kruchten
> >
> >
> >"AK" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> >> I have the following scheduled for backup:
> >>
> >> a) Full back up (daily) which creates a *.bak of 1.5GB
> in
> >> size.
> >>
> >> b) Transaction log (every 2 hours) which creates a *.TRN
> >> of 20 MB
> >>
> >> c) Differential back up which creates a *.bak of 9GB and
> >> continues to grow.
> >>
> >>
> >> Couple of questions:
> >>
> >> 1) Is the size normal for Differential? What am I doing
> >> wrong? Why is it so big?
> >>
> >> 2) Is there only one file that gets created for
> >> differential?
> >>
> >>
> >> AK
> >>
> >>
> >
> >
> >.
> >

Differential Backup

I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...

quote:

---

> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>

---

|||

quote:

---

>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>

---

Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.

quote:

---

>AK
>
>.
>

---

|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK

quote:

---

>--Original Message--
>I wouldn't figure the differential should ever be larger

---

than the full DB

quote:

---

>backup. Is the differential backup writing to the same

---

file each time? If so

quote:

---

>then you're probably adding to the file each time, look

---

up the WITH FORMAT

quote:

---

>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
in[QUOTE]
>
>.
>

---

|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK

quote:

---

>--Original Message--
>
in[QUOTE]
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>.
>

---

|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...[QUOTE]
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
>
> than the full DB
> file each time? If so
> up the WITH FORMAT
> message
> in

Differential and Transaction Log BU Maintenance Plan SP2

I had a maintenance plan which does a full backup weekly, differential backup daily, and transaction log every hour. After installing SP2, the maintenance plan designer will not allow me to set up my differential. I select the database to backup in the designer, then when I go back, it didn't take and it tells me I need to specify which database. I found the following in the "Whats New" section of SP2.

The Backup Database maintenance plan task prohibits the ability to mistakenly set the option to create differential and transaction log backups for system databases.

So, have I been wrong all this time for doing Full, Differential, and Transaction log backups(even though that plan was recommended in a Sql Server 2005 book). Is it now an either or? (you either do differential or transaction log, not both). Because transaction logs take longer to restore, and you "HAVE" to do a transaction log backup or the log will grow to rediculous size, meaning you can only do differential on "Simple Mode" databases? Thanks,

Jason

Transaction log backup should control the virtual size of transaction log and that should give you more time for point-in-time recovery than the differential backups, in this case the SP2 readme is right on the subject.

Also you might try to keep the transaction log backup schedule frequently to take care of such slowness of restoring the tlog.

|||Alright, I'll take your word for it. No more Full, Differential, and Transaction Log backups. Just Full and Transaction Log. Thanks.

Differential and Transaction backup restore.

Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
Steve
Why are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>
|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent[vbcol=seagreen]
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
restore
>
|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transaction
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> you
> kicked
> it
> recent
> restore
>
|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>
|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
am[vbcol=seagreen]
Are[vbcol=seagreen]
last
>
|||Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
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/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> 8am,
> in
> am
> Are
> last
>

Differential and Transaction backup restore.

Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
SteveWhy are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
restore[vbcol=seagreen]
>|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WIT
H NO_LOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transactio
n
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> you
> kicked
> it
> recent
> restore
>|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
am[vbcol=seagreen]
Are[vbcol=seagreen]
last[vbcol=seagreen]
>|||Shrink doesn't break the log backup sequence. This assumes, of course that E
M doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
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/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> 8am,
> in
> am
> Are
> last
>

Differential and Transaction backup restore.

Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
SteveWhy are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
--
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> > Differential and Transaction backup restore.
> >
> >
> >
> > I have scheduled differential backup to run at 3PM and the transactional
> > backup to run at 3pm. In the event of data restore work, should I
restore
> > the data from differential as well as transactional backups.
> >
> > My questions, is it required to run data restore from the 3Pm
> > transactional
> > backup?
> >
> > Thanks,
> >
> > Steve
> >
> >
>|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transaction
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
> you
>> only issuing one Log backup during the entire day? Depending on which
> kicked
>> in first or finished first and if there was any activity during that time
> it
>> could be either one or both. Transaction logs keep a running chain of all
>> transactions since the first FULL backup assuming you never truncated the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to restore
>> the log and it is out of sequence it will warn you. I suggest you stagger
>> your backups and ensure you have a proper schedule of Log backups to cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the transactional
>> > backup to run at 3pm. In the event of data restore work, should I
> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>>I will rethink bout my backup scheduling. Thanks for the suggestions.
>>
>> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
>> truncated the log file. SQL Server crashes at 10am, then all my
>> transaction
>> log files are not restorable since 9.00 onwards. Is it correct?
>> Steve
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
>> you
>> only issuing one Log backup during the entire day? Depending on which
>> kicked
>> in first or finished first and if there was any activity during that
>> time
>> it
>> could be either one or both. Transaction logs keep a running chain of
>> all
>> transactions since the first FULL backup assuming you never truncated
>> the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
>> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to
>> restore
>> the log and it is out of sequence it will warn you. I suggest you
>> stagger
>> your backups and ensure you have a proper schedule of Log backups to
>> cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the
>> > transactional
>> > backup to run at 3pm. In the event of data restore work, should I
>> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>>
>|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> > WITH NO_LOG?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
> >>
> >>
> >>
> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
am
> >> truncated the log file. SQL Server crashes at 10am, then all my
> >> transaction
> >> log files are not restorable since 9.00 onwards. Is it correct?
> >>
> >> Steve
> >>
> >>
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> >> Why are you doing a Differential and a Log backup at the same time?
Are
> >> you
> >> only issuing one Log backup during the entire day? Depending on which
> >> kicked
> >> in first or finished first and if there was any activity during that
> >> time
> >> it
> >> could be either one or both. Transaction logs keep a running chain of
> >> all
> >> transactions since the first FULL backup assuming you never truncated
> >> the
> >> log outside of a log backup. The Diff has all the changes since the
last
> >> FULL backup. You always apply the last FULL backup and then the most
> >> recent
> >> Diff if you have one. Then any logs since the last Diff. Since it is
> >> imposable to tell the real order from this post it is hard to say if
> >> restoring the log would actually do anything or not in regards to
> >> transactions that the Diff may not have caught. If you attempt to
> >> restore
> >> the log and it is out of sequence it will warn you. I suggest you
> >> stagger
> >> your backups and ensure you have a proper schedule of Log backups to
> >> cover
> >> your needs.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> >> > Differential and Transaction backup restore.
> >> >
> >> >
> >> >
> >> > I have scheduled differential backup to run at 3PM and the
> >> > transactional
> >> > backup to run at 3pm. In the event of data restore work, should I
> >> restore
> >> > the data from differential as well as transactional backups.
> >> >
> >> > My questions, is it required to run data restore from the 3Pm
> >> > transactional
> >> > backup?
> >> >
> >> > Thanks,
> >> >
> >> > Steve
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>|||Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
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/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
>> If someone actually truncates the log at 9am, you can only restore til
> 8am,
>> as you suspect.
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
>> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
>> > WITH NO_LOG?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
>> >>
>> >>
>> >>
>> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
> am
>> >> truncated the log file. SQL Server crashes at 10am, then all my
>> >> transaction
>> >> log files are not restorable since 9.00 onwards. Is it correct?
>> >>
>> >> Steve
>> >>
>> >>
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> >> Why are you doing a Differential and a Log backup at the same time?
> Are
>> >> you
>> >> only issuing one Log backup during the entire day? Depending on which
>> >> kicked
>> >> in first or finished first and if there was any activity during that
>> >> time
>> >> it
>> >> could be either one or both. Transaction logs keep a running chain of
>> >> all
>> >> transactions since the first FULL backup assuming you never truncated
>> >> the
>> >> log outside of a log backup. The Diff has all the changes since the
> last
>> >> FULL backup. You always apply the last FULL backup and then the most
>> >> recent
>> >> Diff if you have one. Then any logs since the last Diff. Since it is
>> >> imposable to tell the real order from this post it is hard to say if
>> >> restoring the log would actually do anything or not in regards to
>> >> transactions that the Diff may not have caught. If you attempt to
>> >> restore
>> >> the log and it is out of sequence it will warn you. I suggest you
>> >> stagger
>> >> your backups and ensure you have a proper schedule of Log backups to
>> >> cover
>> >> your needs.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> >> > Differential and Transaction backup restore.
>> >> >
>> >> >
>> >> >
>> >> > I have scheduled differential backup to run at 3PM and the
>> >> > transactional
>> >> > backup to run at 3pm. In the event of data restore work, should I
>> >> restore
>> >> > the data from differential as well as transactional backups.
>> >> >
>> >> > My questions, is it required to run data restore from the 3Pm
>> >> > transactional
>> >> > backup?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Steve
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>