Backing up SQl transaction logs (starting to panic)

[edutech4schools]

Hi, Our LA installed Sims/fms with sql2008r2, they also configured the database backups using the sql maintenance planer built in SQl manager. The LA also control all the offsite backups and restores etc.

 

I have been doing some reading up and it would appear that along with the full sims or fms sql backup they should also be creating a separate transaction log. See the following info from MS

 

Under the full or bulk-logged recovery model, before you can restore a database in SQL Server Management Studio, you must back up the active transaction log (known as the tail of the log). For more information, see How to: Back Up a Transaction Log (SQL Server Management Studio).

 

So, should they have created a transaction backup maintenance plan?

 

Thanks

Edited June 24, 2014 by edutech4schools

[jinnantonnixx]

You're correct, there should be a transaction log backup scheduled to take full advantage of the 'full' recovery model. You don't have to, but it doesn't make sense otherwise. If it's not being done, you might as well use the 'simple' recovery model.

 

If you have access to the SQL server (and you're allowed to do so), this script will spill the beans on the backup situation for any database.

Put your database name in the @mydatabase variable in the script.

Usual disclaimers, etc.

 

 

DECLARE @mydatabase as varchar(100)

set @mydatabase = 'your database name goes here'

SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576) as 'Size MB' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Size GB',
server_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days
AND sysdb.name = @mydatabase
ORDER BY sysdb.name, bkup.backup_finish_date desc

Edited June 24, 2014 by jinnantonnixx

[edutech4schools]

Dumb question, but want to double check what do I do to run this script.

[jinnantonnixx]

It'll tell you everything about the backups on the database.

 

If all is well, you'll see some '***FULL***' entries against your database, and in between you should see several 'LOG' entries.

 

If all you see is 'FULL', then you know for a fact that it's not set up correctly.

Edited June 24, 2014 by jinnantonnixx

[edutech4schools]

I do have full access to sql manager but not sure how I run the script? Do I run it from sql manager etc?

 

Also when I check the maintenance backup tasks from within sql manager it lists, all databases, full backup but MS say a full backup does not backup the transaction logs.

[jinnantonnixx]

Run the script from SQL Manager - right-click a database and select New Query.

 

A full backup is a recoverable backup at the time the backup was made. At this point the transaction logs are marked as re-usable, and are recycled. Otherwise, the transaction logs will grow indefinitely. A similar process happens when the transaction logs are backed up.

 

http://technet.microsoft.com/en-us/library/ms190440%28v=sql.105%29.aspx

Edited June 24, 2014 by jinnantonnixx

[edutech4schools]

Thanks for the help so far. I am only getting **FULL**, nothing else.

 

,Started to create a new maintenance plan and selected transaction log for the backup type but not sure if I should be selecting 'Backup the tail log, and leave database in restore state' which is listed at the bottom of the page, as MS seem to imply that might be needed.

 

Under the full or bulk-logged recovery model, before you can restore a database in SQL Server Management Studio, you must back up the active transaction log (known as the tail of the log). For more information, see How to: Back Up a Transaction Log (SQL Server Management Studio).

[jinnantonnixx]

No, that option is for 'brown alert' when you're trying to recover all you can from a failure. Keep to the default.

"For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries." This re-uses the physical files used by the transaction logs.

 

It looks like whoever set up your backups didn't finish the job.

 

You really should get somebody on the case as it's really important that this is done right.

Edited June 24, 2014 by jinnantonnixx

[matt40k]

What is it they say about a little knowledge?

 

I would suggest you don't touch it if the LA set it up and are controlling the backups. Capita recommends the SIMPLE recovery model because it's simple, every backup is a full so you just need 1 file to restore and you don't have to worry about the log file (sims.ldf) growing. If your LA has set it to full then they have either - made a mistake and need to change it to simple, or they have another program backing up and your about to mess up their backups.

 

[Warning mode off]

[jinnantonnixx]

Even if there was a third party backup solution operating, this would still be shown by that script. So, it's safe to say that the server is misconfigured. Tell the LA of the problem you've spotted - shared ownership is a blamefest at the dung/fan interface.

 

Also ask them when the last SIMS test restore was done and what was the result. You don't want to wait until a disaster before you see if your plan works or not.

Edited June 24, 2014 by jinnantonnixx

[SkywOrca]

I'd agree with advising caution here. You can pretty easily check if the databases are in full or simple recovery mode by checking the Properties of the databases, Options tab and the Recovery model setting. If this is set to anything other than Simple you might need to contact your LA and get them to double check the configuration of your backups as you'd be likely to run up against a transaction log that either fills your disk or hits its maximum file size, both of which would result in your system no longer recording new data. Given that it was your LA that set up your backups they should be responsible if there is an error in the config, so get them to do the donkey work of fixing it.

[edutech4schools]

What is it they say about a little knowledge?

 

I would suggest you don't touch it if the LA set it up and are controlling the backups. Capita recommends the SIMPLE recovery model because it's simple, every backup is a full so you just need 1 file to restore and you don't have to worry about the log file (sims.ldf) growing. If your LA has set it to full then they have either - made a mistake and need to change it to simple, or they have another program backing up and your about to mess up their backups.

 

[Warning mode off]

 

The school paid them for this one off sql install / set up just before I started a few years back and they do not have any other sql backup systems in place. The school have an offsite backup contract with the LA but we list what directories etc they backup.

 

So it looks like they have not set this up correctly a few years back and I very much doubt they will get involved now.

 

What is this simple backup you mention?

[edutech4schools]

OK, testing the backup myself. Just executed the maintenance plan and copied the Sims 'FULL' sql backup onto a virtual server that I have just installed Sims and SQL2008r2. This virtual server is disconnected from school network.

 

Imported the Sims 'FULL' database in to SQL manager.

Run Sims but after entering login details I get 'Invalid user / password'.

 

Would the transaction log have fixed the login error?

[matt40k]

OK, testing the backup myself. Just executed the maintenance plan and copied the Sims 'FULL' sql backup onto a virtual server that I have just installed Sims and SQL2008r2. This virtual server is disconnected from school network.

 

Imported the Sims 'FULL' database in to SQL manager.

Run Sims but after entering login details I get 'Invalid user / password'.

 

Would the transaction log have fixed the login error?

 

Prob not, its prob this old thing that the user\pass are stored in the MASTER database and you need to run a bit of SQL to get them backed up into the SIMS database so it's included in the SIMS backup. Interestly you can, from SQL 2012 store them in the (SIMS) database, but Capita need to do this - but they can't do it until everyone is on SQL 2012!! Something I've ranted about before - I’m sorry, but SIMS8 can’t come fast enough – Matt Smith

 

Getting back on topic, is it Attix 5? Can you logon as another user? Can you get logged on a sysman?

 

 

 

Even if there was a third party backup solution operating, this would still be shown by that script.

 

Your making assumptions, on paper you might be correct, but in pratice...

 

You don't want to wait until a disaster before you see if your plan works or not.

 

Agreed. Always worth checking, just don't start changing things we don't fully understand.

Edited June 24, 2014 by matt40k

[jinnantonnixx]

The school paid them for this one off sql install / set up just before I started a few years back and they do not have any other sql backup systems in place. The school have an offsite backup contract with the LA but we list what directories etc they backup.

 

So it looks like they have not set this up correctly a few years back and I very much doubt they will get involved now.

 

What is this simple backup you mention?

 

'Simple' is a recovery model. The backup is done at a particular time, and that's that - the transaction log files play no further part in the restoration process. This means that they cannot be used to recover data after the last simple backup. You restore the simple backup, and all data committed after the last backup is lost. The Simple model works for some users, but if you need a point-in-time recovery, Full+logs is the model to choose. It all depends on your requirements and the loss of data you can accept.

[matt40k]

Personally, I prefer to adding a single SATA drive to the server in secondary school that is dedicated for doing backups. Means you can do full backups in mins, also remember SQL backups can run with users still using the system.

 

Don't get me wrong, FULL+logs have their place, the guys behind me swear by them, but they're a team of three dedicated to SQL backups, managing databases in their hundreds and are many times bigger then a schools "big" 5gb database.

[edutech4schools]

I have had a chat with the LA and they say you do not need the transaction logs to import a Sims / FMS database if you use the DBatach tool from Capita. They have said they have done this lots of times without any issues.

 

I am just testing

[jinnantonnixx]

On the face of it, I would suggest that they've made a mistake. They've chosen a model (Full) which implies the use of logs, but their recovery method suggests that they should be using the Simple model. Edited June 24, 2014 by jinnantonnixx

[matt40k]

On the face of it, I would suggest that they've made a mistake. They've chosen a model (Full) which implies the use of logs, but their recovery method suggests that they should be using the Simple model.

 

Yup. Might be worth changing tack, perhaps raise it as my .ldf is rather large and let them follow the breadcrumbs.

[edutech4schools]

On the face of it, I would suggest that they've made a mistake. They've chosen a model (Full) which implies the use of logs, but their recovery method suggests that they should be using the Simple model.

 

Apparently the info they are using has come directly from Capita.

[edutech4schools]

This is confusing me. Just had this detailed reply from the LA.

 

We follow guidelines from Capita for backups of SIMS and FMS. Capita recommends a .bak file for each database – SIMS and FMS – and this is the method that we use in our schools and also advise technicians to employ. We have fully tested this method on our school servers, with regard to both backups and restores. We also recommend restoring using the Capita DBAttach utility which is located via the Start menu in the SIMS Applications folder or in the binn folder of the SQL installation path.

 

Transaction logs are part of a much more complicated method of backup that is usually implemented for large organisations that have to restore to a specific period of time during a given day. These are not required for a backup system such as SIMS in schools, which are set up for full backups generally once at the end of the day.

 

Here is a link to the Capita documents on SIMS and FMS backups:

 

http://www.capita-sims.co.uk/files/sims/downloads/backupsimsandfms.pdf

 

Capita’s preference is generally backing up again via their DBAttach tool, which is an alternative option to the Maintenance Plan route. However the DBAttach backup must either be run manually or from a command line, usually via a scheduled task. We tend to use Maintenance Plans as they are a bit simpler to implement and easy to automate. Both the Capita tool and SQL Maintenance Plans produce the required .bak files for each database.

 

So your current backup system, if set according to the LA guidelines, is correct for backing up the SIMS and FMS databases in your schools.

 

 

I hope this information is helpful. Thanks very much.

 

I am still concerned as I remember reading on support net that there is a difference in the way dbattach and sql manager backup the database.

[jinnantonnixx]

This is confusing me. Just had this detailed reply from the LA.

 

 

 

I am still concerned as I remember reading on support net that there is a difference in the way dbattach and sql manager backup the database.

 

.bak is just a file extension. It gives you no clue as to the nature of the backup file.

 

I'm 100% certain they meant to use simple rather than full.

Edited June 24, 2014 by jinnantonnixx

[vikpaw]

I agree with the others, proceed with caution. It's possible you are in Full mode because at some point, someone has run a script which puts the db into Full! I think this was a side effect of both the shrinklog batch file and the reindex patch.

 

Or someone just set it up wrong. The important part of their reply is

if set according to the LA guidelines

Check what they think they are , and i'm sure they'll come back and say you should be in Simple.

 

Find out who supports you, or who will support you in a disaster recovery scenario, and confirm the changes you will be making to the system. Namely, changing the recovery model to Simple, and ensure they agree with it. I'm pretty sure this will be your LA, so you need them to check your backup setup and compare to their guidelines and Capita's.

 

Once you change to Simple mode, your transaction log file will be redundant with regards to the backups, and if you do want/need to shrink it, you can do so after backing it up. There are instructions for this on here and on SupportNet.

 

On Capita's SupportNet - have a read of resource 14931 it will help explain how the passwords are stored, and also about backing up in various ways, though probably not the Management Studio method you want. It's v.useful though and also highlights the need to also backup docstorage and other files that are required to get a fully working SIMS setup, not just the db!

 

Strangely, when i went to check the above resource number i came across a patch which has been withdrawn, which was apparently created yesterday with this note:

Patch 14463 - This was a Datafix created to truncate the log file. This patch is structured incorrectly. The patch puts the database into simple recovery mode, shrinks the log file and then always returns the database back to full recovery mode meaning the log file grows again The school run out of space.

 

EDIT: just saw their link, and it's the same resource i referenced. I don't think it mentions the recovery model in there, but i'm sure ages and ages ago they publicised that Simple was the way to go.

Edited June 24, 2014 by vikpaw
addition

[edutech4schools]

So what is this simple method? It is not an option I can see in SQL manager.

[vikpaw]

It is accessible from SQL Management Studio

It's a recovery model. Right click the DB, select properties then look at the options panel.