This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.

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!

How to update sequence number for existing records ?

CREATE TABLE #Table1
(
Id INT NULL,
Column1 VARCHAR(50)
)
GO

INSERT #Table1(Column1) VALUES('Failover Clustering')
INSERT #Table1(Column1) VALUES('Mirroring')
INSERT #Table1(Column1) VALUES('Replication')
INSERT #Table1(Column1) VALUES('Log Shipping')
INSERT #Table1(Column1) VALUES('Database Snapshot')
INSERT #Table1(Column1) VALUES('Database Backup')
GO

DECLARE @Seq INT
SELECT @Seq = 0
UPDATE #Table1 SET Id = @Seq , @Seq = @Seq + 1
GO


SELECT * FROM #Table1
GO

DROP TABLE #Table1
GO

Cannot change the state of non-data files or files in the primary filegroup.

When we try to set a particular Data file OFFLINE, The following Error occurred

Msg 5077, Level 16, State 2, Line 1
Cannot change the state of non-data files or files in the primary filegroup.


Why the Err ?
1. The file should be a Data file.
2. The file should be there in seperate Filegroup otherthan PRIMARY.
3. Log file can not be set OFFLINE as It can not be placed in seperate filegroup otherthan PRIMARY.

To set Data file OFFLINE:
ALTER DATABASE <Database Name> MODIFY FILE(NAME='<Data File Name>',OFFLINE)

To identify the file state(OFFLINE/ONLINE):
USE <Database Name>
GO
SELECT
Name [File Name], Type_Desc [Data File Type],State_Desc [State] FROM SYS.MASTER_FILES WHERE NAME LIKE DB_NAME() + '%'

How to make the Data file ONLINE:
- Restore the backup file is the way to set the data file ONLINE
- Restore all the backup files (Full, Differential, Log(If any))

USE master
GO
RESTORE DATABASE <Database Name>
FILE
= N'<Data File1>',
FILE = N'<Data File2>' FROM DISK = N'<Backup file Path.Bak>'
WITH FILE = 1,
NORECOVERY,
REPLACE
GO
--
--
--
RESTORE DATABASE <Database Name>
FILE
= N'<Data File1>',
FILE = N'<Data File2>' FROM DISK = N'<Backup file Path.Bak>'
WITH FILE = 1,
REPLACE
GO

- All the Data file(s) changed to ONLINE.

What are the Database Mail sent from SQL Server - Between two date


DECLARE
@StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '2011-02-01',
@EndDate = '2011-02-08 23:59:59'

SELECT
A.Name [Mail Account Name],
A.Display_Name [Mail Account Display Name],
P.Name [Mail Profile Name],
A.EMail_Address [From Mail ID],
SI.Recipients [To Mail ID],
SI.Copy_Recipients [CC Mail ID],
SI.Blind_Copy_Recipients [BCC Mail ID],
SI.[Subject] [Subject],
SI.Body [Body],
SI.Send_Request_Date [Request Date],
SI.Sent_Date [Sent Date],
SI.Sent_Status [Mail Status]
FROM MSDB.dbo.sysmail_sentitems SI JOIN MSDB.dbo.sysmail_profile p
ON (SI.Profile_Id = P.Profile_Id)
JOIN MSDB.dbo.sysmail_account A
ON (A.Account_ID =SI.Sent_Account_ID)
WHERE SI.Sent_Date BETWEEN @StartDate AND @EndDate
ORDER BY SI.sent_date DESC
GO

To Cleanup Backup and Restore history

To remove the Backup and Restore related history from the system tables older than the specified date:

USE msdb
GO
EXEC sp_delete_backuphistory '2011-01-31'
GO

To remove the Backup and Restore related history from the system tables for the specified Database:

USE msdb
GO
EXEC sp_delete_Database_backuphistory 'SQLDB1'
GO

Backup/Restore history removed from the following system tables
1.backupfile
2.backupfilegroup
3.backupmediafamily
4.backupmediaset
5.backupset
6.restorefile
7.restorefilegroup
8.restorehistory