+ Post New Thread
Page 1 of 3 123 LastLast
Results 1 to 15 of 35
MIS Systems Thread, Backing up SQl transaction logs (starting to panic) in Technical; Hi, Our LA installed Sims/fms with sql2008r2, they also configured the database backups using the sql maintenance planer built in ...
  1. #1

    Join Date
    Apr 2010
    Posts
    2,032
    Thank Post
    82
    Thanked 187 Times in 154 Posts
    Rep Power
    83

    Backing up SQl transaction logs (starting to panic)

    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
    Last edited by edutech4schools; 24th June 2014 at 09:39 AM.

  2. #2

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,949
    Thank Post
    109
    Thanked 481 Times in 330 Posts
    Blog Entries
    2
    Rep Power
    281
    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.


    Code:
    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
    Last edited by jinnantonnixx; 24th June 2014 at 09:44 AM.

  3. #3

    Join Date
    Apr 2010
    Posts
    2,032
    Thank Post
    82
    Thanked 187 Times in 154 Posts
    Rep Power
    83
    Dumb question, but want to double check what do I do to run this script.

  4. #4

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,949
    Thank Post
    109
    Thanked 481 Times in 330 Posts
    Blog Entries
    2
    Rep Power
    281
    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.
    Last edited by jinnantonnixx; 24th June 2014 at 10:03 AM.

  5. #5

    Join Date
    Apr 2010
    Posts
    2,032
    Thank Post
    82
    Thanked 187 Times in 154 Posts
    Rep Power
    83
    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.

  6. #6

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,949
    Thank Post
    109
    Thanked 481 Times in 330 Posts
    Blog Entries
    2
    Rep Power
    281
    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/l...ql.105%29.aspx
    Last edited by jinnantonnixx; 24th June 2014 at 10:18 AM.

  7. #7

    Join Date
    Apr 2010
    Posts
    2,032
    Thank Post
    82
    Thanked 187 Times in 154 Posts
    Rep Power
    83
    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).

  8. #8

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,949
    Thank Post
    109
    Thanked 481 Times in 330 Posts
    Blog Entries
    2
    Rep Power
    281
    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.
    Last edited by jinnantonnixx; 24th June 2014 at 10:56 AM.

  9. #9

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,375
    Thank Post
    368
    Thanked 635 Times in 517 Posts
    Rep Power
    157
    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]

  10. #10

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,949
    Thank Post
    109
    Thanked 481 Times in 330 Posts
    Blog Entries
    2
    Rep Power
    281
    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.
    Last edited by jinnantonnixx; 24th June 2014 at 11:42 AM.

  11. #11
    SkywOrca's Avatar
    Join Date
    Sep 2008
    Posts
    80
    Thank Post
    0
    Thanked 17 Times in 16 Posts
    Rep Power
    47
    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.

  12. #12

    Join Date
    Apr 2010
    Posts
    2,032
    Thank Post
    82
    Thanked 187 Times in 154 Posts
    Rep Power
    83
    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?

  13. #13

    Join Date
    Apr 2010
    Posts
    2,032
    Thank Post
    82
    Thanked 187 Times in 154 Posts
    Rep Power
    83
    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?

  14. #14

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,375
    Thank Post
    368
    Thanked 635 Times in 517 Posts
    Rep Power
    157
    Quote Originally Posted by edutech4schools View Post
    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?



    Quote Originally Posted by jinnantonnixx View Post
    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...

    Quote Originally Posted by jinnantonnixx View Post
    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.
    Last edited by matt40k; 24th June 2014 at 12:23 PM.

  15. #15

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,949
    Thank Post
    109
    Thanked 481 Times in 330 Posts
    Blog Entries
    2
    Rep Power
    281
    Quote Originally Posted by edutech4schools View Post
    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.

SHARE:
+ Post New Thread
Page 1 of 3 123 LastLast

Similar Threads

  1. Backing up Microsoft SQL
    By RabbieBurns in forum Enterprise Software
    Replies: 10
    Last Post: 5th March 2012, 12:18 PM
  2. Backing up SQL 2008
    By glennda in forum Windows
    Replies: 5
    Last Post: 6th August 2010, 12:19 PM
  3. Backing up SQL database ?
    By Kyle in forum Windows
    Replies: 4
    Last Post: 8th June 2006, 09:36 PM
  4. Replies: 11
    Last Post: 26th May 2006, 10:01 AM
  5. Mac software for backing up DVDs
    By dagza in forum Mac
    Replies: 14
    Last Post: 23rd May 2006, 11:29 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •