How to Change recovery model to simple and Backup Database, Restore Database (Dev & QA)



  SIMPLE RECOVERY MODEL:
  • The "Simple" recovery model is the most basic recovery model for SQL Server.  
  • Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file(mdf) the space that was used in the transaction log file is now re-usable by new transactions.
  •   Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the full backup or the latest differential backup that was completed.  
  • Also, since the space in the transaction log can be reused, the transaction log will not grow forever as in the Full recovery model.
How to set the simple recovery model using T-SQL.
ALTER DATABASE dbName SET RECOVERY recoveryOption
GO
Example: 
change saily database to "Simple" recovery model
ALTER DATABASE Saily SET RECOVERY SIMPLE
GO
How to set using SQL Server Management Studio
  • Right click on database name and select Properties 
  • Go to the Options page 
  • Under Recovery model select "Simple" 
  • Click "OK" to save

http://www.mssqltips.com/tutorialimages/2_Reco1.jpg
How to Backup database when the Recovery model is simple:
Type of backups you can run when the data is in the Simple recovery model:
  • Full backups 
  • Differential backups 
  • File and/or Filegroup backups 
  • Partial backups 
  • Copy-Only backups 


Posted by