I have been assigned the task of automating backups without the use of SQL Server Agent. To date I have been able to create and successfully call a VB app( using xp_cmdshell) which can do the backups with SQLDMO. Now I need to create some kind of trigger which will run only when more than 100 changes have been made to a database. I have no idea if I can do this from a system database or if there is a way of doing it from Master. Any help greatly appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Difficult Problem 1
- Thread starter kiwiCoder
- Start date
-
1
- #2
100 changes! Isn't that going to happen every day? What scenario is it that allows this to make sense? If the db isn't used very often, then what's so terrible about a weekly or monthly backup? To put an actual trigger in every table of the database would be insane.
Maybe you mean table instead of database.
-Karl
[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
Maybe you mean table instead of database.
-Karl
[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
- Thread starter
- #3
Yes it will hapen every day. Our clients have multiple databases all of which are mission critical. I plan to do a db backup Monday, incremental backup once daily and logfile backup after x(100) changes to the db. From what I have been able to understand the only way to schedule backups by time, ie every 15 minutes is with SQL Server Agent which we do not want to use, so I thought to execute after x number of changes to the db. I totally agree with you that a trigger in each db is stupid, open to any other suggestions.
Setup the database for replication, that should give you the layer of protection you're looking for. Check out transaction replication in BOL.
-Karl
[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
-Karl
[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
If you HAVE to do it your way....
I think I would create a table to hold update information. Have two columns: DBName and UpdateCount.
Put a FOR UPDATE trigger on each database, the trigger should check this new table and compare the UpdateCount for the database. If the UpdateCount is less than 99, it increments the UpdateCount by 1. If UpdateCount = 99 (meaning this update is #100), then it would do the backup and reset the UpdateCount to 0.
-SQLBill
I think I would create a table to hold update information. Have two columns: DBName and UpdateCount.
Put a FOR UPDATE trigger on each database, the trigger should check this new table and compare the UpdateCount for the database. If the UpdateCount is less than 99, it increments the UpdateCount by 1. If UpdateCount = 99 (meaning this update is #100), then it would do the backup and reset the UpdateCount to 0.
-SQLBill
- Thread starter
- #8
![[bandito]](/data/assets/smilies/bandito.gif "[bandito] [bandito]")
[blue]DBomrrsm[/blue] - Thread starter
- #11
Now I'm not convinced you need to make extra work. If 100 updates isn't a requirement, then just doing Transaction Log (TL) backup should work. My understanding is the TL backup is just of all changes since the last backup of any kind. So, you do a:
FULL on Monday (call it midnight)
DIFF on Tuesday
DIFF on Wednesday, etc.
and you set TL Log backups every hour. The Tuesday 0100 AM TL backup will only be of changes since the full backup was done and if there weren't any changes then nothing is backed up. And so on for each TL backup. Or at least that's my understanding.
-SQLBill
FULL on Monday (call it midnight)
DIFF on Tuesday
DIFF on Wednesday, etc.
and you set TL Log backups every hour. The Tuesday 0100 AM TL backup will only be of changes since the full backup was done and if there weren't any changes then nothing is backed up. And so on for each TL backup. Or at least that's my understanding.
-SQLBill
Poor choice of words on my part to say magic. I just wanted to know why the drive for 100 transactions.
Will you be keeping each backup in a separate location? Or will you be overwriting the previous one?
I would say there's far more sense in backing something up every 15 minutes than in writing a ton of code to figure out how to do it every 100 updates. That's creating a maintenance headache! What actual real problem are you solving by doing this?
If you use full recovery model and schedule just a log backup for every 15 minutes, you won't be wasting much. Storage space is cheap enough nowadays that there's no point in conserving some small amount.
Alternately, there has got to be some other way to determine that some update occurred in the database. Instead of doing a backup every 15 minutes, run some query every 15 minutes (or even an external procedure) which determines if a backup is necessary. Then the backup fires.
If you had to build a car which could check its own oil, would you put all sorts of gears and wheels and mechanics attached to each part of the engine, so it could check the oil level every 10000 revolutions of the engine? Or would you just add a module that verified the oil level was correct every X number of minutes, even when the car was just sitting there not running? Which would be less likely to break down? Which would be easier to modify and maintain?
Will you be keeping each backup in a separate location? Or will you be overwriting the previous one?
I would say there's far more sense in backing something up every 15 minutes than in writing a ton of code to figure out how to do it every 100 updates. That's creating a maintenance headache! What actual real problem are you solving by doing this?
If you use full recovery model and schedule just a log backup for every 15 minutes, you won't be wasting much. Storage space is cheap enough nowadays that there's no point in conserving some small amount.
Alternately, there has got to be some other way to determine that some update occurred in the database. Instead of doing a backup every 15 minutes, run some query every 15 minutes (or even an external procedure) which determines if a backup is necessary. Then the backup fires.
If you had to build a car which could check its own oil, would you put all sorts of gears and wheels and mechanics attached to each part of the engine, so it could check the oil level every 10000 revolutions of the engine? Or would you just add a module that verified the oil level was correct every X number of minutes, even when the car was just sitting there not running? Which would be less likely to break down? Which would be easier to modify and maintain?
Replication does solve the "keep another copy in case the thing blows up" problem. But it doesn't protect against evil queries, like "DELETE Customers." Now the replicated database is missing all its customers, too.
So, I'm saying that replication is a great solution, but just pointing out one of its weaknesses. In fact, I can see using BOTH methods. Replication to keep a working copy running, transaction log backups to be able to step to any point in time. Unless I don't know what I'm talking about, which is always a distinct possibility.
So, I'm saying that replication is a great solution, but just pointing out one of its weaknesses. In fact, I can see using BOTH methods. Replication to keep a working copy running, transaction log backups to be able to step to any point in time. Unless I don't know what I'm talking about, which is always a distinct possibility.
- Status
Similar threads
- Locked
- Question