Home Forums Discussions Problems Problem with Incremental Backups

Hello,
I have a problem that I hope someone can assist with. I wish to do incremental backups throughout the day in the event of a drive crash. The software I'm using is NovaBACKUP, and it's capable of backing up SQL databases and open files. When I first ran the backup, I did a full backup, which included the entire Amazing Charts directory and subdirectories. I then set it for incremental backups every hour ... and that's when things got weird. When I checked the logs, I never saw AmazingCharts.mdf & AmazingCharts.ldf get backed up. Occasionally Codes.mdf & Codes.ldf were backed up. I tried it on my computer at home, and had the same problem. I immediately blamed NovaBACKUP. But then I looked at the files in the directory... the AmazingCharts.mdf & AmazingCharts.ldf files had dates from several days ago, and the Archive attribute was cleared (I'm using Windows 7 Professional). Just this evening, I was playing around at home... I did a full backup, and all attributes were cleared. I then went in to my test version of AC and added an email address to a patient's demographics. I saved the record and quit AC. I refreshed the directory, and Codes.mdf & Codes.ldf were now showing the current date as the modified date, and the Archive attribute was set. No changes to AmazingCharts.mdf/ldf. And I verified in the .xml file that AC was using the correct mdf file, and I even searched the hard drive for any other file ...none.

So, my question...is this normal? Has anyone else encountered this problem?

Thanks!
Dean

And Happy New Year!

Hi Dean,

We need a lot more information before we start. There are four versions of NovaBACKUP. My guess is you are using NovaBACKUP Business Essentials as it can do all you need to do at a good price. The next price break is a bit steep and is for three servers and 25 workstations. What computer is the database on?

OK, so it gets a little complicated. The SQL database or instance consists of the database engine with Master, Model, MSDB and then the three databases which interact with AC. The entire instance is backed up and has to be backed up while offline. You can run your backup, but this is because your backup program is capable of using VSS which takes a snapshot or Open Files Backup, which takes a snapshot. AC makes its backup by actually stopping the SQL Service, then starting it again after the backup. While you can back it up while online, you are likely to get corrupted backups, which is why the cloud backups are not safe. When I read the website, it said it was compatible with 2005, 2010 and 2012. But, it didn't mention Express. It probably does, but I would check with support to make sure it does.

Incremental backups can't be used with SQL only full and differential and transaction. Also, in order to back up the six databases correctly, you usually need the sa and password for the instance? Does it mention that when setting it up? I suspect that this issue along with incremental backups are causing your problem.

If you run a full backup and the program is capable using open file backup, etc. Each day, you would then have a full copy of your server or AC with SQL. In order to truly make multiple backups of AC, you would want to make multiple backups of SQL, therefore, you would have all your data. Some backup programs will allow you to run JUST a SQL Backup program. With this, you make a full backup in the morning preferably with it offline, and then you make T-log backups. SQL backs up intermittently to a T-log, which is simply a log of the pages in the database. The backup program then backs up the latest T-log and produces a transaction log. This method takes less than two seconds to back up the entire database since the last T-log was made. You can run them every five minutes if you want. I think every 30 minutes is sufficient. The best way to back up SQL is to use SSMS or SQL Server Management Studio. With that you must have the sa and password as well.

Now, getting back to your plan. I see the logic behind it, but I would suggest not running incremental backups especially while people are using the server. Do you really need to ensure you don't lose that little data? I would recommend backing up daily and doing it at night. At the most, maybe twice a day doing one at lunch. I just never trust incrementals anyway as it is dependent on all of the individual backups. You would be better off doing differentials even though they would take longer.

Personally, I would rather have five full solid backups, than 15 backups with 14 incremental backups. But, that's just me. If you really want to do it correctly and know the complete scoop on SQL Server and how to back it up, I would recommend talking with my friend Raja. He has the Microsoft credentials to get you through it.

Thanks, Bert!

NP.