transactional and differential back up question

[FALCONSEYE]

My differential and transactional backup files are very big. (54 gig and 22 gig respectively). My current backup strategy is to do a full back up once a week, do a diff backup once a day and do 15mins tx backups. Is there a way to set the diff and tx backups so that it will start all over again from the time the full backup is executed? This is what I have:

backup database xxx to
disk = 'D:\sqlBackUp\xxxDiff_BackUp.bak'
with differential

backup log xxx to xxxTxLog

Please let me know if there is a better way of doing this. thanks

http://www.fuzzysiberians.com

 

[mrdenny]

Differential backups are based on the most recent full backup that was taken.

Transaction log backups are based on the most recent transaction log backup.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[FALCONSEYE]

That was my understanding too. However, I don't understand why my full backup is only 4gigs and diff log is 22gigs. The tx log is even worse: 54 gigs.

If the diff log looks at the last full backup, wouldn't the file size be less than that of full backup?

http://www.fuzzysiberians.com

 

[SQLSister]

Look at these options in the description of Backup in BOL
EXPIREDATE = { date | @date_var }

Specifies the date when the backup set expires and can be overwritten. If supplied as a variable (@date_var), this date is specified as either a string constant (@date_var = date), as a variable of character string data type (except for the ntext or text data types), a smalldatetime, or datetime variable, and must follow the configured system datetime format.

RETAINDAYS = { days | @days_var }

Specifies the number of days that must elapse before this backup media set can be overwritten. If supplied as a variable (@days_var), it must be specified as an integer.


I think one of these might be what you need.

"NOTHING is more important in a database than integrity." ESquared

 

[mrdenny]

It's because you don't have WITH INIT in the command. The old backups aren't ever being removed from the backup files.

Do a RESTORE HEADERONLY from the backup files and you'll see every differential backup that you've ever done in the file.

I assume that you backup these files up to tape each day? If you do put a WITH INIT on the differential file so that it clears the file each time it backs up.

The transaction log should be setup to backup to a different file each time, then you can simply delete the older files after a few days.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[FALCONSEYE]

SQLSister, I think one of those might work. MrDenny, If I do a tx/diff log with init, it will overwrite the previous backup. Since I do a tx log every 15 mins and a diff log every day, wouldn't those overwrite let's say the tx log from 30mins ago or the diff log from yesterday?

Also, I guess I can do a backup/truncate tx and diff logs in my full backup.

http://www.fuzzysiberians.com

 

[mrdenny]

Correct, if you to the backup WITH INIT it will wipe out all the data in the current file.

This is why the logs should be made to a different file for each backup, then you can delete the older files after they go to tape and you don't want to keep the backups on disk any more.

With the differential if you backup the differential file to disk each day, then wiping out the prior days backup from disk isn't a dig deal. If you don't backup the diff file to tape each day, then do the backup each day WITH INIT but to a different file (same as the logs) and delete the older files.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog