Backing up SQl transaction logs (starting to panic)

[edutech4schools]

Just called the LA with all your posts in hand and they say that Capita 100% wants the database set to Full as a recovery model. If anyone can locate any info from Capita that says anything different please post, otherwise I will try and do a disaster recovery test next week.

[vikpaw]

They probably don't, they just 'recommend'.

KB78225 , KB104766 and actually looking at their new shrinkdblog tool, it doesn't change the recovery mode back to Full.

There's no point arguing with them. Just get them to check that the setup is as they want it. There's no use us saying one thing if they are adamant they want something else. Just make sure they tell you the setup is okay. Check the size of your LDF i imagine it's probably pretty big, if they aren't backing it up or truncating it.

 

Disaster recover will work to a degree up to the point of the backup, but you won't have things between backups unless the logs are taken. That's my understanding anyway, @Jinn' will confirm. So for a proper test, you want to restore a backup the way they do it, and compare it to the live system as late as possible after the last backup was taken, to see if you're catching changes made during the day.

 

I know some people, for ease, just do a series of Full, Simple backups through the day. Say, hourly, so the most info they will lose is the last hour.

[matt40k]

Just called the LA with all your posts in hand and they say that Capita 100% wants the database set to Full as a recovery model. If anyone can locate any info from Capita that says anything different please post, otherwise I will try and do a disaster recovery test next week.

 

Do you have Attix 5 \ RedStor \ RBUSS installed? That needs it to be FULL.

[edutech4schools]

Do you have Attix 5 \ RedStor \ RBUSS installed? That needs it to be FULL.

@matt40k Yes RBUSS. Thanks.

[matt40k]

Ah, then that's right. Not 100% sure how RBUSS\Attix 5 works, I just know it needs it set to FULL and it uses dbattach to backup and your LA has a dashboard to check the status of all the backups. It checks for diff at a bit level of the .bak file backup and then backups only those changes. If you set it to SIMPLE it sends the whole thing rather then just the changes, same thing if you compress (ie zip) it, so just leave it and it does it's magic. Clearly whoever made it is very clear and has some deep understanding of MS-SQL backup format - it really does just work.

 

The password problem is prob what I detailed above, might be worth reseting the sysman password on that backup copy you restored just to confirm - also undo any changes you may have done.

[edutech4schools]

Apparently, and this is odd, I can fix the login password issue by restoring the database (bak) using SQL, then use dbattach to export and then re import the database which apparently fixes the user logins. Going to test this next week.

[vikpaw]

Yep, it's described in that document that they linked you to and that i referenced. If the passwords are stored in the database as a result of detaching the database at some point, then you can restore a backup of it.

 

Alternatively you can just run a stored procedure that will copy the latest logins into the database for you, so that they will be up to date for future backups.

It's best to limit detach-attach cycles simply because it's too scary and not necessary.

 

I'm sure ages ago there was a change request to add command line options for these procedures into dbattach, but it got lost and is probably obsolete now since the cleanup of supportnet.

[SkywOrca]

The best way to put your mind at rest on this would be to check the size of the ldf files for your databases. If the LA solution is not correctly backing up or checkpointing the transaction log then these ldf files (these are the files that contain the transaction log itself) would just keep on growing. It would depend on how long the backup solution has been in use for and how much data gets changed in your database as to how big "big" would be, but as a rule of thumb if it's bigger than your database file it's probably not being dealt with correctly. In this case, as others have suggested get back to your LA and report the large transaction log file as an issue and get them to fix it.

 

Also, even with a database that is in a Full recovery model you can still restore (to the point at which the backup was taken) even without having taken a transaction log backup alongside the main backup, but you would lose differences that the transaction log would have been storing for you, so even if the setup was not correct you wouldn't be completely unprotected.

 

The problem with the Full recovery model is the potential that your transaction log files grow out of control and either fill your disk (very bad) or hit the maximum file size limit set on that file (also pretty bad). The result of this would be that the SQL server could no longer add or update data in your database, which would generally mean that your MIS system would start spitting out a whole bunch of errors - I'm not sure exactly which errors SIMS would give you at that stage, but you could probably expect to see almost anything and it would likely be related to the lack of tlog space. Full recovery is safe so long as your transaction log backups continue to happen, and so long as your on top of checking your backups you'll probably spot an issue with it pretty quick.

[matt40k]

I'm sure ages ago there was a change request to add command line options for these procedures into dbattach, but it got lost and is probably obsolete now since the cleanup of supportnet.

 

The really frustrating thing about it is the amount of time we've wasted talking about it vs the half and hour it would take to code it in, heck, waste an afternoon on dbattach and you could get it to restore SOLUS3 and Discover databases as well!

[vikpaw]

The really frustrating thing about it is the amount of time we've wasted talking about it vs the half and hour it would take to code it in, heck, waste an afternoon on dbattach and you could get it to restore SOLUS3 and Discover databases as well!

@SkywOrca the error, if it's not reason 0 would be fault code: bad-da7a-dead-feed