.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

simple recovery model, backup plan best practise

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server
any guideline on backup plan for a simple recovery model? if there is no differential backup, only has daily full backup, when the sever crashes, will it lose all info after last backup? if the data file crashes, but log file exists ok, is it possible to restore info after last backup? such as tail log backup?

View Complete Post

More Related Resource Links

Simple Recovery Model Yet Transaction Log Is Full

Hi there, There's a database which has been configured with SIMPLE recovery model but yet the transaction log will grow and fill up the disk at times. Is there anything one can do to prevent the log from filling up the disk? Thanks in advance.

Database Switched to Simple Recovery Model


Hi there,

We came across a situation where the transaction log was full and we extended the disk that holds the transaction log. What happened after that is the log truncated and we noticed the recovery model of the database switched from FULL to SIMPLE. Does anyone come across this behaviour?

Thanks in advance.


Yong Hwee

Is a database online when it is changed from full to simple recovery model?



We have some databases that occaisally have very large ldf files and I have learned that the best way to shrink the ldf file is to change the backup mode to simple, shrink the log file, convert it back to full and do a full database backup so that the log file backups will run again. My question is during this process is the database available to the end users? When you guys do this do you feel it is nessesary to let the end users know or will they not notice what is going on?



Thanks! Kevin

Transaction log filling up in "Simple" recovery model, catch 22


I need to do the quaterly cleanup of our database involving moving historical data to our history db and delete-ing records in a our operational datastore. I've set the recovery model to "simple" as advertised in numerous fora and KB articles. Still the the transaction log goes through the roof evertime I do a

delete * from myTable
where datetime < '2006-10-01'

Where eventually it fails becouse transaction log is full....(Msg 9002)

If I do a

BACKUP LOG myDatabase
TO myBackup

sql claims that I cannot perform a transaction log backup (Msg 4208). So I 'm stuck with a large log file in the "simple" recovery model that can not be shrunk...

TLog backup size in Full vs. Bulk logged recovery model.

When executing some minimally logged operations one can notice higher in TLog growth in Full recovery vs that of Bulk. But when it comes to talking a TLog backup one can notice its bigger (because the modified extents are also backed up along with TLog) Questions: 1. Are the pages backedup only those which were modified by minimally logged operation? 2. TLog backups in bulk recovery will be bigger than those from Full recovery but it depends if or not any minimally logged operations were done.

Maintenance Plan Backup Failing Time to time

See ERRORO Below: I am using SQL Server 2008 64 bit enterprise edition and backing up on network drive. This differential backup run every weekdays and up to sat, it works just fine and sometimes fails every other week. Need some advice. I use a domain service account on SQL Server agent. Executed as user: DOMAIN\SERVER_ag_def. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:45:04 AM  Progress: 2010-09-11 00:45:34.09     Source: {8006A2EB-FD95-45C3-8426-554DAE1895B4}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2010-09-11 00:58:12.09     Source: Back Up Database (Differential)      Executing query "BACKUP DATABASE [DataCollectorDW] TO  DISK = N'\\w...".: 50% complete  End Progress  Progress: 2010-09-11 01:01:17.26     Source: Back Up Database (Differential)      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress  Progress: 2010-09-11 01:01:22.22     Source: Mai

SharePoint 2010 Content DB Recovery Model

Hello Everyone, the following link states that the content db for SharePoint 2010 uses simple recovery model. http://technet.microsoft.com/en-us/library/dd207314.aspx   whereas this link at SQL Server documentation says that Simple model is not suitable for production systems because it has risk of data loss http://msdn.microsoft.com/en-us/library/ms189275.aspx   These two look totally contradictory to me.  

how to understand full recovery model can restore to the point of failure


if I backup log hourly, 50 minutes after last log backup, sql server corrupts, I guess I can only restore to the last tlog backup, how to understand restore to the point of failure?

also if I use simple recovery model, backup differentially every hour, do I have the same data protection? I know answer is no, but how to understand it?



Question on Bulk-Logged Recovery Model


After reading this MSDN article , I'm a bit baffled about how bulk operations are logged...

If data extents are used for log backup, what data will be logged? Won't it be the data at a stage after several committed transactions have already been performed on those extents?

Just for my better understanding of the concept: Isn't it necessary to log the extents containing data at the time the bulk operation occured?

Is this the reason why restore operations cannot be performed on a point-in-time basis?

Thanks for helping me understand...

Parallel plan for simple query ! Any solutions


I am getting a parallel plan for a simple query below. Any workarounds

	UPDATE tblProfile
	SET PlanName = NULL, PlanValue =NULL
	FROM tblProfile pfl
	INNER JOIN tblCustomerPlan ts 
		ON pfl.planID = ts.planID
	WHERE (ts.Name = 'VOID' OR ts.Name = 'IPDC')
	AND ProfileURN = @ProfileURN 

Maintenance Plan - Full Backup


Hey all

I am running a maintenance plan (Sql Server 2008 Enterprise) every night at mid-night. All the plan entails is doing a full backup of one database (about a 3GB database). We are getting timeouts on our site at exactly that point when the database is being backed up and they last up to 20min. I was under the impression that doing backups this way does not take the db down? Or am I wrong about this?

Has anyone else experienced this and if so what steps did you take to rectify it? Sorry if I have not provided other information, just let me know and I will give it to you.


Bulk and full recovery model


hi team,


i have one dought,


in Full Recovery model have bulk log opertaions restore.

bulk log Recovery model also have bulk log operaions and bulk insert operations 


what are the difference in these two bulks could any body please explaine me.





BULK INSERT under the full recovery model


Hi Experts, is there any performance difference between BULK INSERT and row-insert operations under full recovery model? My database is configured as full recovery model, so I want to konw do I still try use BULK operations to modify data? Thanks!

Backup Maintenance Plan Failure SQL Server 2005

Hi All,
I have three maintenance Plans Full, Differential and Transaction Log backup. Full and Transaction Log backup plans are working fine. In the all the maintenance plans I am taking backup of almost 60 databases.

The problem is with the differential backup plan. The backup plan is keep failing with the following errors:

FailedSad-1073548784) Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MY_Database' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'My_Database' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Giants_Security_4'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'E:\\MSSQL.1\\MSSQL\\Backup\\MY_Database\\MY_database_backup_200808240500.DIFF.BAK' WITH  FILE =

Backup maintenance plan fails due to space in t-sql statement...



I have a maintenance plan that  is failing on one backup. I went into look at the t-sql code and it shows a space after the database name. I copied the t-sql code to a query window | removed the spaces and the backup ran fine. My question is how do I remove the spaces for this one backup. The other servers in the maintenance plan work fine.

Note: I copied this part out so you can see "N'T:\SQL Backup\PackagingForms \" I removed the space after PackagingForms and it worked!

t-sql code:

EXECUTE master.dbo.xp_create_subdir N'T:\SQL Backup\PackagingForms '
BACKUP DATABASE [PackagingForms ] TO  DISK = N'T:\SQL Backup\PackagingForms \PackagingForms _backup_201010281050.bak' WITH NOFORMAT, NOINIT,  NAME = N'PackagingForms _backup_20101028105056', SKIP, REWIND, NOUNLOAD,  STATS = 10
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'PackagingForms ' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'PackagingForms ' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''PackagingForms '' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'T:\SQL Backup\PackagingForms \PackagingForms _backup_201010281050.bak'

Recovery Model Indicator - Which system table?




I hope you can help as I am really scratching my head on this one.  I am pulling together an assessment of the Disaster Recovery readiness for an organisation I am working at.  Part of the assessment I am doing is the recovery model of each of the databases.


I have scripts that are already pulling lots of data from 40+ servers and 500+ databases.  However, I cannot seem to find anywhere within the MASTER or MSDB or the database itself where the Recovery Model flag is held.  Obviously I can right click on the database and click properties and it is there, but I need to automate this task (as it will probably be a weekly assessment).


I have checked sysdatabases and almost every other table, but nothing obvious as to where this flag is.


Any ideas? 

Recovery model in Search Server Express 2010 Databases



We have installed a Search Server 2010 Express. The Database is a SQL Server 2008 SP2 (Standard Version)

I'm not a SQL Crack, I am more the SharePoint specialist.

Now my question: Can I set the recovery model for all databases to "Simple" mode? (instead of full mode). Or is there any problem in functionality if i do this?

The default settings are, that some of this are simple and other databases are full.

Thanks a lot for your help.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend