question

"But sometimes we have found that support database is not inline with latest backup of production." You need to expand on this more in your question. What are you using or going by to determine it is not "inline" with the production? My guess would be whatever you are using to determine it is out of sync can be used to create your alert on, depending on what type of output you are looking at.

Why not put the check around the process of the restore of the backup - if this fails then it is out of sync. May be easier than trying to establish at some random point whether 2 databases are in/out of sync.

From your question, I got the feeling that you are weekly restoring the production backup? If it is correct then you can add an initial step to put the information of backup to be restored in a table through "RESTORE HEADERONLY" command. This way you can always check what backup been restored. So the process could be something like /*====THE PROCESS TO STORE INFORMATION OF THE LATEST BACKUP RESTORED======*/ CREATE TABLE #RestoreHeader ( SequentialId INT IDENTITY PRIMARY KEY , BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime, Compressed BIT, Position smallint, DeviceType tinyint, UserName nvarchar(128), ServerName nvarchar(128), DatabaseName nvarchar(128), DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20,0), FirstLSN numeric(25,0), LastLSN numeric(25,0), CheckpointLSN numeric(25,0), DatabaseBackupLSN numeric(25,0), BackupStartDate datetime, BackupFinishDate datetime, SortOrder smallint, CodePage smallint, UnicodeLocaleId int, UnicodeComparisonStyle int, CompatibilityLevel tinyint, SoftwareVendorId int, SoftwareVersionMajor int, SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName nvarchar(128), Flags int, BindingID uniqueidentifier, RecoveryForkID uniqueidentifier, Collation nvarchar(128), FamilyGUID uniqueidentifier, HasBulkLoggedData bit, IsSnapshot bit, IsReadOnly bit, IsSingleUser bit, HasBackupChecksums bit, IsDamaged bit, BeginsLogChain bit, HasIncompleteMetaData bit, IsForceOffline bit, IsCopyOnly bit, FirstRecoveryForkID uniqueidentifier, ForkPointLSN numeric(25,0) NULL, RecoveryModel nvarchar(60), DifferentialBaseLSN numeric(25,0) NULL, DifferentialBaseGUID uniqueidentifier, BackupTypeDescription nvarchar(60), BackupSetGUID uniqueidentifier NULL, CompressedBackupSize numeric(20,0) NULL ) INSERT [#RestoreHeader] ( [BackupName] ,[BackupDescription] ,[BackupType] ,[ExpirationDate] ,[Compressed] ,[Position] ,[DeviceType] ,[UserName] ,[ServerName] ,[DatabaseName] ,[DatabaseVersion] ,[DatabaseCreationDate] ,[BackupSize] ,[FirstLSN] ,[LastLSN] ,[CheckpointLSN] ,[DatabaseBackupLSN] ,[BackupStartDate] ,[BackupFinishDate] ,[SortOrder] ,[CodePage] ,[UnicodeLocaleId] ,[UnicodeComparisonStyle] ,[CompatibilityLevel] ,[SoftwareVendorId] ,[SoftwareVersionMajor] ,[SoftwareVersionMinor] ,[SoftwareVersionBuild] ,[MachineName] ,[Flags] ,[BindingID] ,[RecoveryForkID] ,[Collation] ,[FamilyGUID] ,[HasBulkLoggedData] ,[IsSnapshot] ,[IsReadOnly] ,[IsSingleUser] ,[HasBackupChecksums] ,[IsDamaged] ,[BeginsLogChain] ,[HasIncompleteMetaData] ,[IsForceOffline] ,[IsCopyOnly] ,[FirstRecoveryForkID] ,[ForkPointLSN] ,[RecoveryModel] ,[DifferentialBaseLSN] ,[DifferentialBaseGUID] ,[BackupTypeDescription] ,[BackupSetGUID] ,[CompressedBackupSize] ) EXEC('RESTORE HEADERONLY FROM DISK = N''Drive:\FolferPath\BackupFileName'' --WITH NOUNLOAD --OPTIONS ARE YOUR CHOICE ') INSERT INTO RESTOREINFORMATIONTABLE SELECT * FROM [#RestoreHeader] DROP TABLE [#RestoreHeader] --LATEST BACKUP RESTORED CHECK SELECT TOP 1 * FROM RESTOREINFORMATIONTABLE ORDER BY SequentialId DESC I have used the temporary table approach, so you could be able to filter the information you want to store. Since you did not mention your environment, this would work for 2008. For SQL server 2005, you may need to exclude the last column. **Having a description OR/AND name with good information are always handy for backups.** Moreover, some valuable information is also stored by default in msdb.dbo.restorehistory table. You can also extract the information from there, but for that it would be necessary that only the latest backups being restored. If you are sure then the simple query would be SELECT TOP 1 * --COLUMNS YOU CAN ALWAYS CHOOSE FROM msdb.dbo.[restorehistory] WHERE [destination_database_name] = 'DATABASENAME' ORDER BY [restore_history_id] DESC This way you see the restore date and can decide whether it is synched or not. But I would rather prefer the safer approach, which would need access to the Production server i.e. SELECT TOP 1 * --COLUMNS YOU CAN ALWAYS CHOOSE FROM msdb.dbo.[restorehistory] AS R INNER JOIN PRODUCTIONSERVER.msdb.[dbo].[backupset] AS B ON [R].[backup_set_id] = [B].[backup_set_id] WHERE R.[destination_database_name] = 'DATABASENAME' ORDER BY R.[restore_history_id] DESC The above scripts output could let you know what latest backup was being restored. But If it is a manual process, it had to be a very precise and delicate one handling all kind of scenarios like new columns, data type change, dropped columns etc. Otherwise, there is always a possibility of missing something. I totally agree with Shawn Melton that an alert should be generated in the same process, if something is out of sync OR if some error is generated. You also have other options to consider like Log backups, Differential Backups, Change Date Capture etc. depending upon your environment.