Execute incremental backup of SQL Server database after performing an update
I have a client who runs an update procedure to append and update data in a SQL Server from one of there legacy database systems. The updates are irregular, although the procedure I wrote allows them to kick off the process from a Windows Scheduled activity.
What I want to do is run an incremental backup of the database that gets updated after each upload process. I assume I can do this as a pass-through query (am looking for the proper syntax), but cannot figure out how to write the pass-through as it immediately asks for either a File or System DSN connection, and I want to use a DSN-less connection for this process.
Assuming I'm doing this in a command buttons click event, what should that code look like?
What I want to do is run an incremental backup of the database that gets updated after each upload process. I assume I can do this as a pass-through query (am looking for the proper syntax), but cannot figure out how to write the pass-through as it immediately asks for either a File or System DSN connection, and I want to use a DSN-less connection for this process.
Assuming I'm doing this in a command buttons click event, what should that code look like?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim P.
Actually you should see about having the client set up a regular nightly backups (and regular transaction log backups, if needed) daily.
ASKER
Jimpen,
Don't really need nightly db or translog backups as this is basically a reporting database. As originally mentioned, they only rarely (at most three times a month,usually during the last week of the month) import data from their legacy systems into this database, so my assumption was that it would make more sense to do a differential backup of the database and transaction log after each upload. Then, at the beginning of the month do a full backup.
Does that make sense?
Don't really need nightly db or translog backups as this is basically a reporting database. As originally mentioned, they only rarely (at most three times a month,usually during the last week of the month) import data from their legacy systems into this database, so my assumption was that it would make more sense to do a differential backup of the database and transaction log after each upload. Then, at the beginning of the month do a full backup.
Does that make sense?
ASKER
Scott,
So, you would recommend writing a stored procedure for the "differential" backups of the database, and run that at the end of the upload process?
When you do a transaction log backup, do you have to backup the whole thing, or will it do a "differential backup" as well?
I'm still working on my SQL Server "syntax". With "simple transaction logging", at what point will old transactions be deleted from the log? Is that done after a differential db backup, or does that only occur after a full backup? With simple transaction logging, will I need to shrink the transaction log, or will the process of doing the db backup handle some of that?
:-) Never had to worry about this with Access!
So, you would recommend writing a stored procedure for the "differential" backups of the database, and run that at the end of the upload process?
When you do a transaction log backup, do you have to backup the whole thing, or will it do a "differential backup" as well?
I'm still working on my SQL Server "syntax". With "simple transaction logging", at what point will old transactions be deleted from the log? Is that done after a differential db backup, or does that only occur after a full backup? With simple transaction logging, will I need to shrink the transaction log, or will the process of doing the db backup handle some of that?
:-) Never had to worry about this with Access!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What Jim said ^^^
:)
:)
ASKER
Jim,
This makes my head hurt. I may need to lay all this out in a table to make sense of it.
Dale
This makes my head hurt. I may need to lay all this out in a table to make sense of it.
Dale
Don't worry -- after you play with it for a couple of years, like I have, you'll have it in your head. It just pushes out all the memories of friends and family. :-D
ASKER
Thanks, guys.
I guess I need to break out the SQL books. Mary Chipman and Andy Baron wrote one (Microsoft Access Developer's Guide to SQL Server) that I really liked back in 2001, when I was first starting to play with SQL Server. But then went about 9 years in an environment where they wouldn't spring for a SQL Server, so I lost it all. Looks like I'll be doing a lot more in the near future.
I guess I need to break out the SQL books. Mary Chipman and Andy Baron wrote one (Microsoft Access Developer's Guide to SQL Server) that I really liked back in 2001, when I was first starting to play with SQL Server. But then went about 9 years in an environment where they wouldn't spring for a SQL Server, so I lost it all. Looks like I'll be doing a lot more in the near future.