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

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

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

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server

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?



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.

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!

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

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.

Point In Time Recovery of Database with Full-Text Search enabled SQL 2005


Is this possible? 



Backup made at 10:30

changes made in system

Tail of the log backup taken at 10:35

Restore to point in tim 10:33 

At this point I get the error System.data.sqlclient.sqlerror: The File "sysft_yadayada_1" was not fully restored by a database or file restore.  The entire file must be successfully restored before applying this backup set.

BUT when I restore the database to most recent possible I have no issues..

when I do a select * from sys.master_files    sysft_yadayada_1 does not exist in that list, but  sysft_yadayada_5 does... When we were initially configuring fulltext search for this application we had a couple hiccups first till we got it working and thats the filename that ended up sticking.


SQL2008 - Cannot Restore a new database from a full backup of a different one, database in use

I am trying to restore a brand new database from a copy of another one and am encountering an error message stating: System.Data.SqlClient.SqlError: The file <Backup source database file location> cannot be overwritten.  It is being used by database <Backup Source Database>. (Microsoft.SqlServer.Smo) I am running the Restore procedure with the REPLACE option and am wondering why it is stating that the source database is the database in use when I am trying to restore and overwrite a completely different database. This issue happens when running the replace both in C# using SMO and when manually restoring with SqlServer Management Studio Interesting note is, the original source database is created / deployed using a database project within VisualStudios 2010 and then deployed to SqlServer through VisualStudios. Any thoughts on why this is happening? Thanks in advance for any help!

Can I safely perform full backups without breaking log shipping? Can I do point in time restores if

I'm building a system using SQL Server 2008. I have log shipping set up across our WAN, and that's working fine. I need to perform local backups on the primary server so that I'm not relying on the (slow) WAN if it needs to be recovered from a server crash. Ideally, it would be nice to be able to perform point in time restores. I understand that I can use the transaction log backups, created for log shipping, to restore from. But I still need a full backup to start the restore from - and there seems to be some confusion regarding whether or not performing a regular full backup on the primary server will affect log shipping. Even on these forums, conflicting advice has been given, with some people saying it's fine as long as you don't do transaction log backups, and others saying you must run COPY ONLY backups (though they were talking about Server 2005). From what I understand, you can't do point in time if you use copy-only backups. I'm getting the impression that this used to be a problem under 2005, but under 2008 you can safely perform full backups while log shipping is running. Can anyone confirm my understanding before I make a career-altering error? :)

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.  

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

Do I need free space when doing full database restore in SQL Server 2005?


I have a drive 'F' with 40G free space. I try to restore a database whose data + log file size is 60G.

The restore fails. Is it because a full restore needs  free disk space of 60G in this case?


How to get share point Subsite Last Accessed date using Object Model in moss(Not Modified date)


Hi Guys,

My task is to check last accessed date to a sub site in share point like "http://asc-hv1-mossvm:4233/murali121". its not depended on particular log in user (so i.e any user is valied except system account).

if anybody have any idea please share with this post.

Thanks in Advance.



sql server will not start - Database 'model' cannot be opened. It is in the middle of a restore.

Sql server service will not start after server reboot:

2009-08-13 14:15:43.94 spid7s      SQL Trace ID 1 was started by login "sa".
2009-08-13 14:15:43.97 spid7s      Starting up database 'mssqlsystemresource'.
2009-08-13 14:15:43.97 spid7s      The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
2009-08-13 14:15:44.30 Server      A self-generated certificate was successfully loaded for encryption.
2009-08-13 14:15:44.30 spid7s      Server name is 'WIN-37R9WFWAJIB\MSDB1'. This is an informational message only. No user action is required.
2009-08-13 14:15:44.32 spid10s     Starting up database 'model'.
2009-08-13 14:15:44.32 Server      Server is listening on [ 'any' <ipv6> 49167].
2009-08-13 14:15:44.33 Server      Server is listening on [ 'any' <ipv4> 49167].
2009-08-13 14:15:44.33 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSDB1 ].
2009-08-13 14:15:44.33 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2009-08-13 14:15:44.35 spid10s     The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2009-08-13 14:15:44.38 Server      Server is listening on [ ::1 <ipv6> 491

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...

Moving full-text catalog from one server to another using backup / restore


Dear Sirs,
I'm having a problem with Full-Text Catalog in SQL SERVER 2005.
The problem is: I did a backup of a database that has a full-text catalog from one server and restored on another server.
The restore worked perfectly, but the catalog is down because it is pointing to the physical path from the source server.
To recover this full-text catalog I tried the commands below:

use master

sp_detach_db 'SiteEng2004'

(FILENAME = 'D:\SQL SERVER\DADOS\SiteEng2004\SiteEng2004_Data.MDF'),
(FILENAME = 'E:\SQL SERVER\LOGS\SiteEng2004\SiteEng2004_Log.LDF'),

Unfortunately it did not work and the following error message occurs :

Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "D:\SQL SERVER\DADOS\SiteEng2004". Operating system error 5: "5(error not found)".

Could anyone help me on what I should do to restore this full-text catalog on the destination server ?
Thanks in advance.

simple recovery model, backup plan best practise

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?

how to add buttons to ribbon in share point 2010 using object model?


how to add buttons to ribbon in share point 2010  using object model?

And how to add our custom ribbon in sp2010?

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