July 13, 2005 at 9:01 am
Hi,
Does anyone know if it is possible to tell if a database has been changed since it's last backup? I guess SQL server is capable of doing this since it can perform a differential backup. The reason I ask is that I have a 3rd party application that creates databases for fun (currently we're up to 600) and the users want to keep these attached. We use Netbackup to run our jobs and it's not able to tell if a database has changed so we have to backup all 600 databases, to tape, which takes about 14hours - lots of repositioning of the tape causing most of the delay as the data is only about 200gb. We're looking into a long term solution with a new SAN but that's not going to be ready for 3 months. I was rather hoping to be able to interrogate the master db to produce a list of databases that have been changed since the last backup and pass this list to netbackup. I reckon we'd get down to about 20 or 30 changing in a day at the most.
Any help greatly appreciated.
Jon
July 13, 2005 at 10:18 am
Jon,
That's a great question, but there's no way I know of. I'll ping a few people and see if they have an idea.
Steve
July 13, 2005 at 2:59 pm
Got this from the Red Gate folks:
You could probably look at the live log (SELECT * FROM ::fn_dblog(default, default)) to see if there are any transactions after the last backed up LSN (which you should be able to retrieve from msdb..backupset). You'd need to filter out only the log entries you care about. Alternatively, a DBCC TRACEON (3604) DBCC DBINFO (databasename) might give you the information you need.
July 17, 2005 at 5:20 pm
I use the following query to findout if my backups have worked :
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'D'
GROUP BY B.Name
ORDER BY LastBackupDate
Hope this works
July 24, 2005 at 9:44 am
Steve mentioned this to me and initially I thought it was pretty interesting since it could reduce the time needed for your backup - freeing up time in the maintenance window that could perhaps be used for something else.
It's still interesting and perhaps has its place, but after further thinking I worry that it's a bad shortcut overall, for a couple reasons:
- Eventually your data will grow anyway, putting you back to the same long backup time or longer, forcing you to find a better solution
- Assuming you have a decent tape rotation system in place, you lose the assurance that you can pull any type that is still live and pull the db from it. You could conceivably go long enough without doing a backup that it could fall out of tape rotation entirely.
- If its really changed infrequently, why not just back it up once to a special tape, and then just run log backups on it? Set it to do a full once a month whether it needs it or not.
I hear you about needing a short term fix - what about using compression on the backups?
July 26, 2005 at 3:17 am
Thanks to all the replies for this post, much appreciated. We've been trying a few things but nothing has proved satisfactory but I'll expand a little on our situation.
The system is comprised of about 600 DB's and this number is continually growing. 90% of these databases are sent to us on media that we keep and therefore they can be rebuilt fairly easily and they don't generally change much. The users don't want the hassle of detaching and attaching all the time otherwsie we would have gone down that route. We are in the process of installing a new SAN and once this is in, we will be able to take a file backup within the SAN and then backup the windows files in about 2 hours, problem solved.
What we were really hoping to do was to find out if a database had been changed since the last backup date and if so, back it up. The issue of a database potentially not being on any tape wasn't a major concern as the databases that would fall into this background could be rebuilt from the delivery media and the business accepted that they may lose some minor changes they had made to the database. We run a full monthly cycle and keep monthly tapes forever, so the chances were minimal anyway.
We had followed Steve Jones's suggestion quite far but ran into problems due to the DB's only being backed up with a simple model, so the transaction logs didn't contain any data to test on. It was suggested that we could do a simple row count on the transaction log and unless it contained more than a certain number of rows (I think there's a default of something like 8 that the system uses to initialise the log), don't back it up. HAven't looked any further into that one for a while.
Will keep you posted on progress...
Jon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply