Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

Hourly log backup (24/7/365 unless a diff or full backup is processing)

We are doing regular restores of all types of backups as part of development, testing with live data and debugging, so I know everything works and that I can restore to a point-in-time without problems and with no LSN (Log Sequence Number) issues when restoring.

My problem is this monthly full backup.

I want the monthly full to be detached from my normal cycle as much as possible.

I know it will have implications to the log backups as I cannot perform log backup while a full backup is running, but I can live with that. Using a different managementclass is of course self-explanatory, and I have no problem using a different node name if it helps (will it?) to seperate things.

I cannot use VSS as I need the data stored on the TSM server.

My main concern is that this monthly backup screws up the LSN that the diff and log backups depend on. How can I be absolutely sure that it wont change or mess up the LSN of the regular backups?

So far, the best option I've come up with involves a T-SQL (below) script that dumps the databases to flat files and then use a BA to store the files. That way I can be absolutely sure that LSN numbers are not affected as I know the backups are taken with the option COPY_ONLY.

Have you tried creating a secondary node something like NODE-SQL-MONTHLY and defining the tdpopt file for it. If you want to use the copy-only function in SQL then you need to dump the backup to a file and have the MONTHLY node act like a normal client and do a backup of the directory where the backup is stored.

My main concern is that this monthly backup screws up the LSN that the diff and log backups depend on.

Click to expand...

This is an older thread but hopefully someone can answer this.

I know LOG backups depend on the Log Seqence Number, but was unaware that DIFF backups do. Are we SURE that's the case for DIFF?

I have this same situation, where I am being required to implement a 3 Month and 1 Year backup for SQL dbs, in addition to their daily. The DBs will be in SIMPLE recovery mode, but I would like to utilize DIFF backup most days to avoid a FULL everyday for Dbs that see very little change.

Does DIFF backup absolutely break the LSN if the DB is in SIMPLE recovery mode?

DIFF backups relate to the latest full backup - and a log backup relates to the latest full, diff or log backup if a full, diff or another log backup exists.

If you do not use a "Full Recovery Model" and stick to Simple (as you say you intend to) you loose all access to diff and log backups.

All backups of Simple databases are full backups - there is no log available, and thus no way of knowing what data has changed since the last backup, so a full backup with all data is the only way of ensuring a valid backup.

But please - don't take my word for it - read up on MSDN and the online MS SQL help for truely valid answers to your questions..