July 7, 2017 at 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
July 7, 2017 at 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.
July 7, 2017 at 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? do you know what would happen to the transaction log backups before the full backup?
July 7, 2017 at 6:45 am
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
July 7, 2017 at 7:03 am
alex.palmer - Friday, July 7, 2017 6:44 AMI 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
July 7, 2017 at 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.
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.
July 7, 2017 at 7:15 am
alex.palmer - Friday, July 7, 2017 7:10 AMI 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
July 7, 2017 at 7:17 am
alex.palmer - Friday, July 7, 2017 7:10 AMI 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 backupThe 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
July 7, 2017 at 7:25 am
Yeh thanks for the replies. I hadn't refreshed my browser so I didn't see Gail's first reply.
Excellent as ever 🙂
July 11, 2017 at 5:49 am
Beatrix Kiddo - Friday, July 7, 2017 6:39 AMYou 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" 😉
July 11, 2017 at 5:50 am
alex.palmer - Friday, July 7, 2017 6:34 AMHiI'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