Differential Backups 1

[Pebkac]

does anyone know how to get a diffential backup to stay as an archive? I am looking to get a diffential backup on my DB every 2 hrs. I have Logs every 20 min. The full backup is once a night.

I can see the time stamps on the logs and full DB files.

When I run the Diff backup I get either a LARGE file that continually grows (noinit) or it is overwriten every time I run it. (init) Here is the sql Statement I am using:

BACKUP DATABASE [DB1] TO DISK = N'\\UNCPATH TO BACKUP LOCATION\FILENAME.diff' WITH INIT , NOUNLOAD , RETAINDAYS = 1, DIFFERENTIAL , NAME = N'DB1 backup', NOSKIP , STATS = 10, NOFORMAT

 

[tlbroadbent]

Append the date and time to the file nmae.

BACKUP DATABASE [DB1] TO DISK = N'\\UNCPATH TO BACKUP LOCATION\FILENAME020328.diff' WITH INIT , NOUNLOAD , RETAINDAYS = 1, DIFFERENTIAL , NAME = N'DB1 backup', NOSKIP , STATS = 10, NOFORMAT

You can create and execute the backup statement dynamically. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Pebkac]

How can I to this dynamically?

I want this to be done with the schedule of every 2 hrs, not that I would have to hand append the time stamp at the end.

I know I can create a job for the SQLAgent and put in the step, but how can I get the dynamic timestamp?

 

[tlbroadbent]

You can create SQL scripts to run the backup using dynamically generated file names that include date and time. These can be executed directly from a job or can be part of a stored procedure.

--Script to do full backup all
--databases except 'tempdb'

DECLARE @dt char(12), @sql varchar(4000)

-- Get date and time for backup file name
SELECT @dt=convert(char(6), getdate(), 12) +
replace(convert(char(8),getdate(),8),':','')
SELECT @sql=''

-- Create the backup statements
SELECT @sql=@sql+
'PRINT ''' + name + '''' + char(10) +
'PRINT ''''' + char(10) +
'BACKUP DATABASE ' + name + char(10) +
' TO DISK = N''C:\SQL\Backup\'+ name +
@dt + '.bak'' ' + char(10)
FROM master..sysdatabases
WHERE name<>'tempdb'

-- Execute the backup statements
Exec(@sql)

--------------------------------------------


-- Script to do diffrential backups on
-- all DBs execpt 'master' and 'tempdb'

DECLARE @dt char(12), @sql varchar(4000)

-- Get date and time for backup file name
SELECT @dt=convert(char(6), getdate(), 12) +
replace(convert(char(8),getdate(),8),':','')
SELECT @sql=''

-- Create backup statements
SELECT @sql=@sql+
'PRINT ''' + name + '''' + char(10) +
'PRINT ''''' + char(10) +
'BACKUP DATABASE ' + name + char(10) +
' TO DISK = N''C:\SQL\Backup\'+ name + '_Diff_' +
@dt + '.diff'' ' + char(10) +
' WITH INIT, NOUNLOAD, RETAINDAYS=7, Differential,' + char(10) +
' NAME = '''+name+' DB Backup'', NOSKIP,' + char(10) +
' STATS=10, NOFORMAT' + char(10) + char(10)
FROM master..sysdatabases
WHERE name Not In ('master','tempdb')

-- Execute backup statements
Exec(@sql) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Pebkac]

Thank you very much. Works like a charm

I have one more question that maybe you can answer.

Why would each Diff backup be 90+% of the complete database file size, yet the total size of the logs for the same time period is 3 %. I used your Diff backup statement.

The DB is pretty small <50 Mg but I dont think the diff backup should be almost the same size. I thought that the Diff made smaller size files than the Complete backup.

 

[tlbroadbent]

The differential backup contains all changes since the last full backup - not since the last differential backup. It can grow larger, sometimes much larger, than the full backup. Remember that all activity is recorded. Updates, deletes, schema changes, index rebuilds, etc. Many changes will not change the database size and may even reduce it while the transaction and differential backups get larger.

Depending on frequency of transaction log backups and the activity on the database, transaction log backups can be larger than the ful backup. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Pebkac]

I thought that the Diff backups were basicly the Transaction logs combined. The largest log I have is 190 KB, the average is 100 KB.
The Diff bkup that is run 30 min after the Backup is only 800KB smaller than the one 20 min before the Next nights backup. It just seems that if there is only 800KB difference between the begining Diff and the last after 15 hrs then something either bloated at the getgo or I am missing somthing

(only 2-3 users with maybe 10 small transactions)

 

[tlbroadbent]

How many differentials do you do each day? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Pebkac]

OK here is my problem. Maybe I am misunderstanding the purpose of the Diff Backup.
If you looked at the size of the Diff backups and compared diff before and after Backup you would find its 24 MB and 24.8 MB respectivly in size.
The one just before the backup is only 800 KB larger.
Now that is just 2.5 MB smaller than the Full Backup size!

I am currently doing a diff backup every 2 hrs, and Tran logs every 20 min.
Let me rephrase the comment about the user and transactions.
There are normally 20-25 users on the DB during the day doing many transactions,
but between the time that the nightly backup and the next diff there is a max of
2-3 users and only minor changes at that time.

When I do a backup with the TSql statement then run a Diff 30 seconds later the .diff file size is only 768 KB.
BUT when the Backup is done via the Enterprise MGR using XP_SQLMAINT and then I run the Diff TSQL 30 seconds later it is 24 MB in size and this is what happens even when there is NO ACTIVITY!

Can you tell me what the difference is?
here are the Statements that are running.

Enterprise MGR SQL Job Statements

Backup
EXECUTE master.dbo.xp_sqlmaint N'-PlanID plan 1-Rpt &quot;\\Support\Error Logs\Nightly Backup4.txt&quot; -DelTxtRpt 2DAYS -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia DISK -BkUpDB &quot;\\Support\Databases&quot; -DelBkUps 4DAYS -CrBkSubDir -BkExt &quot;BAK&quot;'

Integrity Check:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID plan 1-Rpt &quot;\\Support\Error Logs\Nightly Backup2.txt&quot; -DelTxtRpt 2DAYS -WriteHistory -CkDBRepair '

Optimization:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID Plan 1-Rpt &quot;\\Support\Error Logs\Nightly Backup0.txt&quot; -DelTxtRpt 2DAYS -WriteHistory -RebldIdx 20 '


TSQL STATEMENTS:
Diff statement
DECLARE @dt char(12), @sql varchar(4000)
-- Get date and time for backup file name
SELECT @dt=replace(convert(char(10), getdate(), 20),'-','') + replace(convert(char(8),getdate(),8),':','')
SELECT @sql=''
-- Create backup statements
SELECT @sql=@sql+ 'PRINT ''' + name + '''' + char(10) + 'PRINT ''''' + char(10) + 'BACKUP DATABASE ' + name + char(10) + '
TO DISK = N''\\Support\DATABASES\'+ name + '_Diff_' + @dt + '.diff'' ' + char(10) + ' WITH INIT, NOUNLOAD, RETAINDAYS=7, Differential,' + char(10) + ' NAME = '''+name+' DB Backup'', NOSKIP,' + char(10) + ' STATS=10, NOFORMAT' + char(10) + char(10)
FROM master..sysdatabases WHERE name = 'DB1'
-- Execute backup statements
Exec(@sql)

Backup Statement:
DECLARE @dt char(12), @sql varchar(4000)

SELECT @dt=replace(convert(char(10), getdate(), 20),'-','') + replace(convert(char(8),getdate(),8),':','')
SELECT @sql=''

-- Create the backup statements
SELECT @sql=@sql+ 'PRINT ''' + name + '''' + char(10) + 'PRINT ''''' + char(10) + 'BACKUP DATABASE '
+ name + char(10) + ' TO DISK =N''\\Support\DATABASES\'
+ name +'_db_'+ @dt + '.BAK'' ' + char(10)+char(10)
FROM master..sysdatabases WHERE name= 'DB1'

-- Execute the backup statements
Exec(@sql)

Would it be easier for you if you contacted me directly via my email?
Pebkac777@hotmail.com

 

[SQLBill]

I recently completed Microsoft's 'Administering a Microsoft SQL Server 2000 Database' class and learned the following:

A transaction log (TL) backup contains the transactions since the last backup of any kind.

A differential (DIFF) backup contains the transactions since the last FULL backup.

A full backup is just that, it contains the full database as it is when backed up.

So, let's say I am doing three TL backups a day, one DIFF backup every night, and a full backup at the end of the week:

Sunday - FULL Backup 2300
Monday - TL #1 covers Sunday 2300- Monday 0800
TL #2 covers Monday 0800-1600
TL #3 covers Monday 1600-2400
(if I have to restore now, I need the full backup
and TL #1, TL#2, TL#3)
DIFF #1 covers Sunday 2300 to Monday 2400
(a restore now needs the full backup and DIFF#1
only)
(no longer need TL backups since the DIFF backup
covers those times)
Tuesday - (can write over the TL backup tapes so...)
TL #4 covers Tuesday 0000-0800
TL #5 covers 0800-1600
TL #6 covers 1600-2400
(restore now needs full backup, DIFF#1 and TL#4,
TL#5, TL#6)
DIFF #2 covers Sunday 2300 to Tuesday 2400
(restore now needs the full backup and DIFF#2 only)
(no longer need DIFF #1 or the TL's since DIFF #2
has everything needed for a restore)
this keeps on going until Sunday night when I do my Full Backup.

-SQLBIll

 

[tlbroadbent]

Pebkac,

The rebuild index maintenance run is the culprit in this scenario. Rebuilding indexes creates entries in the transaction log because index pages are moved during the rebuild. The differential backup will contain all of those transactions.

This is an issue that we need to deal with on our servers. We have had several problems with the index rebuilds failing because the transaction log becomes large. If someone has an resolution to the problem, I'd be glad to hear it. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Pebkac]

tlbroadbent

I am looking for some help. Can you tell me how to get Retaindays to work in the statement? I tried it but the files never get deleted this is on both the Full and Dif Backups.

 

[Pebkac]

Sorry here is the DIFF Statement:


DECLARE @dt char(12), @sql varchar(4000)
-- Get date and time for backup file name
SELECT @dt=convert(char(6), getdate(), 12) +
replace(convert(char(8),getdate(),8),':','')
SELECT @sql=''

SELECT @sql=@sql+ 'PRINT ''' + name + '''' +
char(10) + 'PRINT ''''' + char(10) + 'BACKUP DATABASE ' +
name + char(10) + ' TO DISK = N''NETWORK\DRIVE\LOCATION\'+ name +
'_Diff_' + @dt + '.diff'' ' + char(10) + '
WITH INIT, NOUNLOAD, RETAINDAYS=2, Differential,' + char(10) +
' NAME = '''+name+'DBNAME Diff Backup'', NOSKIP,' + char(10) + ' STATS=10, NOFORMAT'
+ char(10) + char(10)FROM master..sysdatabases WHERE name = 'DBNAME'

Exec(@sql)

 

[tlbroadbent]

RETAINDAYS doesn't control how long a file is retained on the disk. It controls how long before a particular backup can be overwritten. You'll need to create your own delete procedure. You can do this in T-SQL but I would recomend creating a job or adding a step to the backup job. Use the Scripting filesystemobject to identify and delete files.

Example: Deleting Files Older Than 7 Days
'Assumes all files in directory are backup files.

Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot
Set fol = fso.GetFolder(&quot;E:\MSSQL\Backup&quot
Set fs = fol.Files

For Each f in fs

If f.DateCreated < date() - 7 Then
f.Delete
End If

Next
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.