Moving from Simple to Full Recovery Mode

  • Hi

    I'm moving some of our busy production databases from Simple to Full recovery mode.

    Ideally I'd make the switch to Full and implement the transaction log backup maintenance plan just before the Full Backups started, but they don't kick in until about 2am. 

    What are the consequences of changing the recovery mode and initiating the translation log backup hours before a full backup takes place? I'm worried that if I change to Full without the transaction log backup then they'll grow out of control (there are batch processes that run overnight) and I can't find anything that tells me what happens to transaction log backups if a full backup hasn't been taken

    Is there a best practice way of doing this?

    Cheers

    Alex

  • You can't you'll need to take a Full backup before you can take any log backups. Until you take a Full backup the database will be in a state known as pseudo-Simple.

  • I read that.  Does that mean it behaves as Simple recovery mode and overwrites transaction logs at checkpoints until a full backup is taken? do you know what would happen to the transaction log backups before the full backup?

  • As Beatrix said. The best thing to do is as soon as you switch the database recovery mode is take a backup, if you're able to. Otherwise, any transaction log backups that are attempted will fail until a backup is taken, and you won't actually gain any extra functionality, from a restore perspective, until that back up takes place.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • alex.palmer - Friday, July 7, 2017 6:44 AM

    I read that.  Does that mean it behaves as Simple recovery mode and overwrites transaction logs at checkpoints until a full backup is taken?

    Yes.

    do you know what would happen to the transaction log backups before the full backup?

    They fail.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I can't take a backup immediately afterwards. I just wanted to know if there was a downside to setting everything up and letting it start once the scheduled backup kicks in.

    I just ran an experiment on my test database. Switched to Full recovery and set up transaction log backup maintenance plan without taking a a full backup

    The transaction log backup fails with this message

    Microsoft(R) Server Maintenance Utility (Unicode) Version 10.0.6000
    Report was generated on "pr-sql-dba".
    Maintenance Plan: tansaction log backups
    Duration: 00:00:00
    Status: Warning: One or more tasks failed.
    Details:
    Back Up Database (Transaction Log) (pr-sql-dba)
    Backup Database on Local server connection
    Databases: AdventureWorks2008
    Type: Transaction Log
    Append existing
    Task start: 2017-07-07T13:55:01.
    Task end: 2017-07-07T13:55:01.
    Failed:(-1073548784) Executing the query "BACKUP LOG [AdventureWorks2008] TO DISK = N'D:\\MS..." failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
    BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    Command:BACKUP LOG [AdventureWorks2008] TO DISK = N''D:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks2008_backup_2017_07_07_135501_2183813.trn'' WITH NOFORMAT, NOINIT, NAME = N''AdventureWorks2008_backup_2017_07_07_135501_2174347'', SKIP, REWIND, NOUNLOAD, STATS = 10
    GO

    After I did a manual full backup it started working successfully.

  • alex.palmer - Friday, July 7, 2017 7:10 AM

    I can't take a backup immediately afterwards. I just wanted to know if there was a downside to setting everything up and letting it start once the scheduled backup kicks in.

    There's no downside as such, however, there is also no real upside, as nothing is gained until the backup is completed. Obviously, if your back up schedule is at 02:00, then setting it up in advance is probably a better idea (unless you want to get up and go to the office/RDP at 01:45 😉 ).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • alex.palmer - Friday, July 7, 2017 7:10 AM

    I just ran an experiment on my test database. Switched to Full recovery and set up transaction log backup maintenance plan without taking a a full backup

    The transaction log backup fails with this message

    Yup, that's what I said. Log backups that run before the full backup has finished will fail.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yeh thanks for the replies.  I hadn't refreshed my browser so I didn't see Gail's first reply.

    Excellent as ever 🙂

  • Beatrix Kiddo - Friday, July 7, 2017 6:39 AM

    You can't you'll need to take a Full backup before you can take any log backups. Until you take a Full backup the database will be in a state known as pseudo-Simple.

    That's not entriely correct, you only need to take a differential backup as a minimum to start the log chain

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • alex.palmer - Friday, July 7, 2017 6:34 AM

    Hi

    I'm moving some of our busy production databases from Simple to Full recovery mode.

    Ideally I'd make the switch to Full and implement the transaction log backup maintenance plan just before the Full Backups started, but they don't kick in until about 2am. 

    What are the consequences of changing the recovery mode and initiating the translation log backup hours before a full backup takes place? I'm worried that if I change to Full without the transaction log backup then they'll grow out of control (there are batch processes that run overnight) and I can't find anything that tells me what happens to transaction log backups if a full backup hasn't been taken

    Is there a best practice way of doing this?

    Cheers

    Alex

    Take a differential backup of the database to start the log chain once you have switched to full recovery model

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply