Point-In-time restore is not supported If a log backup taken under the BULK-LOGGED recovery model contains bulk-logged changes. Trying to perform point-in-time recovery on a LOG backup that contains bulk changes will cause the following Error.
Msg 4341, Level 16, State 1, Line 1
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
I would like to walk-through the scenario here...
USE MASTER
GO
/*Creating new Database named : DB1*/
CREATE DATABASE DB1
GO
/*Setting Database recovery mode to BULK_LOGGED*/
ALTER DATABASE DB1 SET RECOVERY BULK_LOGGED
GO
/*Creating a table named : Table1*/
USE DB1
GO
CREATE TABLE table1
(
id INT IDENTITY(1,1),
column1 VARCHAR(10)
)
GO
/*Inserting a record into "Table1" */
INSERT table1 VALUES('Pandian')
GO
/*Taking Full BackUp*/
BACKUP DATABASE db1 TO DISK='e:\backup\Full.Bak' WITH INIT
GO
/*Performing some BULK operation*/
SELECT * INTO table2 FROM table1
GO
/*Inserting data into "table2" */
INSERT table2 VALUES('SQL DBA')
GO
/*Identifying current timestamp*/
SELECT GETDATE()
GO
-- 2011-02-27 19:55:28.760
/*Taking Log BackUp*/
USE MASTER
GO
BACKUP LOG db1 TO DISK='e:\backup\Log.Trn' WITH INIT
GO
/*Droping the table*/
USE DB1
GO
DROP TABLE table2
GO
/*Identifying the Bulk-Operation performed any, from the Log backup file*/
USE MASTER
GO
RESTORE HEADERONLY FROM DISK='e:\backup\log.trn'
GO
/*Result from the above statement*/
We have a column called "HasBulkLoggedData", The column has value '1', means the Log backup contains Bulk operation
/*Performing DATA restore on DB1 database with NoRecovery Model for continue with Log restore*/
RESTORE DATABASE DB1 FROM DISK='e:\backup\Full.Bak' WITH NORECOVERY, REPLACE
GO
/*Performing LOG restore on DB1 database upto Partiular time */
RESTORE LOG DB1 FROM DISK='e:\backup\log.trn' WITH RECOVERY, STOPAT ='2011-02-27 19:55:28.760'
GO
Now, we should get an error as given below
Msg 4341, Level 16, State 1, Line 1
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
Now, the database seems to be middle of the Restore..., How to recover that ?
RESTORE DATABASE db1 WITH RECOVERY
GO
Note:But, we can restore LOG backup fully without any Point-in-time in this scenario!
Msg 4341, Level 16, State 1, Line 1
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
I would like to walk-through the scenario here...
USE MASTER
GO
/*Creating new Database named : DB1*/
CREATE DATABASE DB1
GO
/*Setting Database recovery mode to BULK_LOGGED*/
ALTER DATABASE DB1 SET RECOVERY BULK_LOGGED
GO
/*Creating a table named : Table1*/
USE DB1
GO
CREATE TABLE table1
(
id INT IDENTITY(1,1),
column1 VARCHAR(10)
)
GO
/*Inserting a record into "Table1" */
INSERT table1 VALUES('Pandian')
GO
/*Taking Full BackUp*/
BACKUP DATABASE db1 TO DISK='e:\backup\Full.Bak' WITH INIT
GO
/*Performing some BULK operation*/
SELECT * INTO table2 FROM table1
GO
/*Inserting data into "table2" */
INSERT table2 VALUES('SQL DBA')
GO
/*Identifying current timestamp*/
SELECT GETDATE()
GO
-- 2011-02-27 19:55:28.760
/*Taking Log BackUp*/
USE MASTER
GO
BACKUP LOG db1 TO DISK='e:\backup\Log.Trn' WITH INIT
GO
/*Droping the table*/
USE DB1
GO
DROP TABLE table2
GO
/*Identifying the Bulk-Operation performed any, from the Log backup file*/
USE MASTER
GO
RESTORE HEADERONLY FROM DISK='e:\backup\log.trn'
GO
/*Result from the above statement*/
We have a column called "HasBulkLoggedData", The column has value '1', means the Log backup contains Bulk operation
/*Performing DATA restore on DB1 database with NoRecovery Model for continue with Log restore*/
RESTORE DATABASE DB1 FROM DISK='e:\backup\Full.Bak' WITH NORECOVERY, REPLACE
GO
/*Performing LOG restore on DB1 database upto Partiular time */
RESTORE LOG DB1 FROM DISK='e:\backup\log.trn' WITH RECOVERY, STOPAT ='2011-02-27 19:55:28.760'
GO
Now, we should get an error as given below
Msg 4341, Level 16, State 1, Line 1
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
Now, the database seems to be middle of the Restore..., How to recover that ?
RESTORE DATABASE db1 WITH RECOVERY
GO
Note:But, we can restore LOG backup fully without any Point-in-time in this scenario!
