Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rb1373
Yak Posting Veteran
93 Posts |
 Posted - 2006-01-25 : 12:21:47
|
| What is the risk of not backing up the database when switching from simple to full recovery model? BOL recommends backing up the database after such a switch, but I want to know what is at risk if this is not done.Thanks,RaySS2K |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-25 : 13:32:27
|
| Until a full backup is done, you may not be able to recover from disaster. |
 |
|
|
rb1373
Yak Posting Veteran
93 Posts |
Posted - 2006-01-25 : 13:47:20
|
| But I have a backup from just before switching from full to simple. The switch from full to simple is made to rebuild historical data. If disaster were to strike, I would revert back to the last backup and lose the historical data which can be rebuilt.I guess I am primarily concerned about the ability to recover to a point in time after switching from simple to full and before the next full backup. During the simple to full switch, does the log sequence become invalidated? |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-01-25 : 14:41:47
|
quote:
During the simple to full switch, does the log sequence become invalidated?Go to Top of Page
Not quite. You just can not restore logs until they have a full backup they can "connect to". An example:10:00 Database is in full recovery mode.11:00 Maintenance is about to be done. A full backup is taken.11:30 the database is put into simple recovery.11:31 maintenance begins.12:30 maintenance ends12:31 database is put into full recovery8:00 Full backup takenIn this example, the transaction logs between 12:31 and 8:00 are useless, because there is no full backupto which they can trace a continuous line to in the past. Once the full backup is taken, then a continuous line of logs can be restored over that. Hope that helps. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-25 : 15:51:27
|
| Can't remember exactly, but I don't think "logging starts" until the FULL backup is taken - so Transaction Backups made BEFORE a Full Backup has been done don't actually backup anything.Kristen |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2006-01-25 : 17:35:03
|
| Pretty sure you can't backup a t-log for a database in SIMPLE recovery. Msg 4208, Level 16, State 0, Line 1The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.Logging actually happens but only for rollback/rollforward's sake. It is also a minimal logging as I recall. Not everything gets logged.Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-01-26 : 10:03:48
|
| I am pretty sure that logs are not truncated starting the moment you switch to full, it is just that the logs are useless without a starting point. Logging always happens, it is just not around long enough for most folks to notice, until they update that 10 million row table with no where clause. |
|
|
|
kfarlee
Microsoft SQL Server Product Team
9 Posts |
Posted - 2006-01-26 : 16:38:40
|
| When in Simple recovery mode, transactions are logged, but the logs are truncated whenever a checkpoint happens, up to the point where the oldest active transaction starts.So, we cannot count on any logs from a time when the database was in Simple mode.In this scenario, you have:Full BackupTime in Simple Mode with no logsSwitch to Full ModeSo, when rolling forward, there is no way to guarantee the ability to ge past the gap between the backup and the switch to full recovery mode.That's why the requirement is:You must perform a full backup AFTER switching to Full recovery mode before you can use any of the logs for recovery.Kevin FarleeSQL Server Engine PM |
|
|
|
|
|
|
|
|
switch from simple to full and no backup