SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 sql backup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

neutral4
Starting Member

India
2 Posts

Posted - 05/09/2014 :  03:26:29  Show Profile  Reply with Quote
If I create a database in simple recovery mode in SQL Server 2005, then backup the database, then add 1000 records, then change the recovery mode to full, then add 500 more. Can I recover using the log backups?

jeffw8713
Aged Yak Warrior

USA
798 Posts

Posted - 05/12/2014 :  13:47:46  Show Profile  Reply with Quote
Not until you perform another full backup to initiate the log chain. You cannot even backup the transaction log until after a full backup is performed after switching the recovery model to full.

In fact, the transaction log knows that those records in the transaction log cannot be used - and treats them as if you are still in simple recovery model until the next full backup of the database has been taken.
Go to Top of Page

neutral4
Starting Member

India
2 Posts

Posted - 05/13/2014 :  01:39:27  Show Profile  Reply with Quote
thank u sir, thanks a lot..
Go to Top of Page

Shanky
Yak Posting Veteran

United Kingdom
71 Posts

Posted - 07/09/2014 :  07:05:44  Show Profile  Reply with Quote
quote:
Originally posted by neutral4

If I create a database in simple recovery mode in SQL Server 2005, then backup the database, then add 1000 records, then change the recovery mode to full, then add 500 more. Can I recover using the log backups?



When you change recovery model to full and add 500 records your database is actually in Pseudo simple recovery and of course you have not taken any full backup after changing to full recovery so you cannot take log backup. After taking full backup you can take log backups and then ca achieve point i time recovery. Its very easy to reproduce if you have SQL server instance, do it you will see for yourself the error flashed.

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000