?
Solved

Execute incremental backup of SQL Server database after performing an update

Posted on 2012-09-14
10
Medium Priority
?
690 Views
Last Modified: 2012-09-23
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?
0
Comment
Question by:Dale Fye
  • 4
  • 3
  • 3
10 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 600 total points
ID: 38401454
There's no such beast as an "Incremental" backup in SQL Server. You can do Differential or Transaction Log backup, which are similar, but the term "Incremental" is meaningless in SQL Server.

Since you know the connection info, you could build an ADO connection and issue your statement without worrying with a DSN:

Dim con As ADODB.Connection
Set con = New ADODB.Connection

con.Open "Your connection info here"
con.Execute "BACKUP DATABASE MyDatabase TO DISK='C:\somefolder\myfile.bak' WITH DIFFERENTIAL"

The above creates a Differential backup, which may be sufficient for your needs. If you need to do a Transaction Log backup, see item B, towards the bottom of the link below.

Backup information:
http://msdn.microsoft.com/en-us/library/ms186865.aspx

Note that you may also find a Stored Procedure easier to deal with in this case. To use that, just build the STored Proc, and then execute it like this:

con.Execute "EXEC YourStoredProcName"
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38403392
Actually you should see about having the client set up a regular nightly backups (and regular transaction log backups, if needed) daily.
0
 
LVL 49

Author Comment

by:Dale Fye
ID: 38403489
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?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 49

Author Comment

by:Dale Fye
ID: 38403499
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!
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 600 total points
ID: 38411814
Depends on what you're trying to do - what is your goal in providing the backup mechanism? If you just want to insure that nothing goes wrong with the upload, and have the ability to "rollback", then a differential backup should work - heck, even a full backup would work, although there might be some speed issues with larger databases.

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 no SQL Server DBA, but my understanding of a log backup is that it'll backup the LOG file, based on the last LOG backup (assuming you have Full Recovery on)

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?

Again, I'm no DBA, but my understanding is that the "Recovery Model" determines where/how the log is truncated.

See this link:

http://www.sqlmag.com/article/internals-and-architecture/what-happens-to-your-transaction-log-in-simple-recovery-model-

You might post another question in the SQL Zones for these sorts of things. I'd hate to tell you something that is wrong, based on my limited understanding of these sorts of SQL Server mechanisms.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 1400 total points
ID: 38411901
If your database is in Simple recovery as soon as the transaction is committed, it is marked as deleted in the transaction log.

If you are in Bulk-Logged, most transactions are in the log except for those that were don with the BCP. The transaction from BCP is noted, but the data is not stored.

If you are in Full recovery everything is stored in the transaction log including BCP loads.

There are three types of backups for SQL server Full, Differential and Log.

The transaction Log backup is only available if the DB is Full or Bulk-Logged recovery model. This will backup the transaction logs from the last Log, Differential, or Full backup depending on which occurred most recently.

The Differential will back up the changes since the last Full. And of course the Full backs it all up.

Now the reason why you should just do a full backup anyway is that if you had to restore and get the data, you first have to do a restore of the full db, then apply any differential backups, then any transaction log backups.

Where if you had a nightly full, you can just grab the night, before or after load, depending if you want the data there or not.

There is always a decision on the recovery model and how much data you can afford to lose.

I've had a DB that had about several GBs of data was loaded every single day, but we had it in simple recovery because the data was volatile on daily basis, and all we needed was about 75MB of that data. Then I had another DB that they processed all day on and losing more than an hour could really hurt the user's.
0
 
LVL 85
ID: 38412122
What Jim said ^^^

:)
0
 
LVL 49

Author Comment

by:Dale Fye
ID: 38415225
Jim,

This makes my head hurt.  I may need to lay all this out in a table to make sense of it.  

Dale
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38415470
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
0
 
LVL 49

Author Closing Comment

by:Dale Fye
ID: 38426924
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question