February 9, 2011 at 2:20 am
This is what I have done (here I have mentioned the time as well)
Recovery model: bulk
inserted 200 rows @ 1.41Pm
taken a full backup
Imported data from Excel File and the Query has been executed @ 1.42pm and it Executed for 26 seconds
(640016 row(s) affected)
Imported data from Excel File and the Query has been executed @ 1.43pm and it Executed for 17 seconds
(640016 row(s) affected)
Imported data from Excel File and the Query has been Executed @ 1.44pm and it Executed for 13 seconds
taken log back up
Now total No of Rows in the Table are 1920248 Rows
Now
(Each case refer to a new recovered database)
Case 1: Recovered a database using full and tlog to the time 1.42 pm 20 seconds
I Got 200 rows only to the new restored Database
Case 2: Recovered a database using full and tlog to the time 1.42 pm 50 seconds
I Got 640216 rows to the new restored Database
Case 3: Recovered a database using full and tlog to the time 1.43 pm 15 seconds
I Got 640216 rows to the new restored Database
Case 4: Recovered a database using full and tlog to the time 1.43 pm 50 seconds
I Got 1280232 rows to the new restored Database
Case 5:Recovered a database using full and tlog with out using point in time recover.
Got 1920248 rows
The thing here is, I got all the rows back which were inserted using Bulk Insert Statement
finally, my Question is what is the point in saying 'Point in time recovery not possible in Bulk Logged Recovery Model'
Please don't mind if any mistakes.. As I'm completely new to SQL Server 2005.....
Thanks in Advance... Hope Someone Replies...... 🙂
February 9, 2011 at 2:55 am
Point in time recovery is possible in bulk-logged recovery, unless there is a minimally-logged operation within the log over that period.
In a minimally-logged operation, the actual changes are not fully logged and the modified pages are included within the log backup. Since the pages are as-of the time of the log backup, not the time the change was made, the DB cannot be restored to a point within that log backup as there's no guarantee of consistency.
How did you import the data from excel?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2011 at 3:36 am
Thanks for the reply....... need to work on it....
Here is my answer for the question,
How did I imported data from excel sheet?
First change the Excel file into .CSV (Comma Delimited) format
Which will help the file (sampel.csv) to be opened in notepad and help u and SQL Server in understanding the file.
By Opening the file(sampel.csv) u can know about the attributes fieldterminator and rowterminator in bulk insert statement.
so here is final Query for the BULK INSERT......... remember one thing u should have a database table in the location where u want to insert the table with all the attributes and make sure it has got all data types as character which will reduce the risk... 🙂
BULK INSERT dbname.schemaname.tablename from 'C:\....' with ( fieldterminator='field_terminator ', rowterminator='row_terminator')
for finding the field terminator and row terminator open the file in notepad to check.
most usually for the format I used it has got fieldterminator as ',' and fieldterminator as ''
example:
Bulk insert sampledb.dbo.names from 'c:\sample.csv' with (fieldterminator=',', rowterminator='')
🙂
thank you... please any queries...??
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply