The remote copy of database x has not been rolled forward to a point in time that is encompassed in the local copy

When setting up database mirroring, it is possible to receieve the following error when starting the mirroring session between the Principal and Mirror databases:

The remote copy of database x has not been rolled forward to a point in time that is encompassed in the local copy

Assuming the steps taken follow a pattern similar to below:

  1. BACKUP database DBMTest on SQLINS01
  2. BACKUP log DBMTest on SQLINS01
  3. Copy db and log backup files to SQLINS02
  4. RESTORE DBMTest with norecovery
  5. RESTORE log DBMTest with norecovery
  6. create endpoints on both SQLINS01 and SQLINS02
    CREATE ENDPOINT [Mirroring]
  7. Enable mirror on MIRROR Server SQLINS02
    :connect SQLINS02
    SET Partner = N'TCP://';
  8. Enable mirror on PRINCIPAL server SQLINS01
    :connect SQLINS01
    SET Partner = N'TCP://';

The error would appear on step 8

Normally the reason for the error is because the backup file for the log or full backup has not been initialized and therefore contains more than one backup of the database on file.

Add the WITH INIT option to the backups to create a fresh initialized backup file

Powershell: Invoke-Sqlcmd command not recognized

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, 
script file, or operable program. Check the spelling of the name, or if a path 
was included, verify that the path is correct and try again.

When trying to run the Invoke-sqlcmd command within the powershell utility, an error is returned suggesting the command is not recognized.

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "mysqlserver"


This happens because the Invoke-sqlcmd cmdlet is not included as part of PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). Before using invoke-sqlcmd you should install SSMS or the SQL Server 2008 Feature Pack

When sqlps has been installed, you may then load invoke-sqlcmd into powershell.exe by using the below script from Michiel Wories blog

Im still a novice at powershell so Ive ended up building the initiliaze into all my basic scripts using dot-source

# Use Dot-Source to Call the “Initialize-SQLPSEnvironment” Script. .{."\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\Initialize-SqlPsEnvironment.ps1"} $instanceNameList = Get-Content "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\ServerList.txt" foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -Query "select SERVERPROPERTY('ServerName') As ServerName, Name, crdate from sysdatabases" -ServerInstance $instanceName } # print results $results

The above script calls the initialize script, and then does a basic SQL Query

Find SQL Server Start Time Quickly

DECLARE @WorkingVariable datetime

SELECT @WorkingVariable = login_time
FROM master..sysprocesses

print @WorkingVariable

The above will simply return the date when the LAZY Writer process started.

SELECT create_date FROM sys.databases WHERE NAME='tempdb'

The above simply checks when Tempdb started

DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
  SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
  SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
    +'" has been Online for the past '+@hr+' hours & '+@min+' minutes.'
    + 'Instance start time is ' + CONVERT(varchar(23),@crdate, 113)

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses 
                            WHERE program_name like N'SQLAgent90%')
   PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
   PRINT 'SQL Server and SQL Server Agent are both running'

The above produces user friendly information on how many hours the Server has been up for, and other useful info.


Dropping Database – Delete Backup History taking a long time

Ive found this on numerous occasions and decided not to be lazy this time around and investigate why it takes so long to drop a database when the ‘Delete backup and Restore History Information for databases’ checkbox is ticked.

When thinking about it logically, when purging historical data with a where criteria against a set of tables in any database, the task would be faster when you have indexes in place. this is exactly the scenario which causes the GUI to seem like its hanging. However, if you check sys.dm_exec_requests you will be able to see CPU and IO actively moving and a DELETE command against the msdb, in the form of:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDB'

Running the following will show you which indexes are present on the msdb database as standard:

SELECT as [Table], as [Index], as [Column], ind.type_desc [Index Type]
FROM sys.indexes ind
inner join sys.index_columns ic
    on ind.object_id = ic. object_id and ind.index_id = ic. index_id
inner join sys.columns col
    on ic.object_id = col.object_id and ic.column_id = col.column_id
inner join sys.tables t
    on ind.object_id = t. object_id WHERE ind.is_unique = 0 and ind. is_unique_constraint = 0
    and in ( 'backupfile', 'backupfilegroup', 'backupmediafamily', 'backupmediaset', 'backupset', 'restorefile', 'restorefilegroup', 'restorehistory' ) ORDER BY t .name,

…Only 4 indexes are present as standard in the msdb database.

This problem can be alleviated by adding a set of indexes to some key tables in the msdb database.

This script is taken from Here

/************************************************************************ * * * Title: msdb index creation * * Author: Geoff N. Hiten * * Purpose: Index msdb database * * Date: 12/12/2005 * * Modifications: * ************************************************************************/ use msdb

--backupset Create index IX_backupset_backup_set_id on backupset(backup_set_id) go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) go
Create index IX_backupset_media_set_id on backupset(media_set_id) go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date) go
Create index IX_backupset_backup_start_date on backupset(backup_start_date) go

--backupmediaset Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) go

--backupfile Create index IX_backupfile_backup_set_id on backupfile(backup_set_id) go

--backupmediafamily Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) go

--restorehistory Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) go
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) go

--restorefile Create index IX_restorefile_restore_history_id on restorefile(restore_history_id) go

--restorefilegroup Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) go

This small addition will speed up the deletion process completely and make sure the GUI doesnt hang waiting for a background delete against the msdb to complete.

Useful scripts

I found that deleting backup history was slowing down, so i did the following:

USE msdb; GO
UPDATE STATISTICS backupmediafamily; GO
UPDATE STATISTICS backupmediaset; GO
UPDATE STATISTICS restorefilegroup; GO
UPDATE STATISTICS restorehistory; GO

And also added the following index, after looking at the estimated execution plan:

Create index IX_backupset_database_name on backupset(database_name) go

Some general queries:

--Find count of records older than certain date select count(*) from backupset with (nolock) where backup_start_date < '8/15/2009' --with database name select * from backupset with (nolock) where backup_start_date < '08/15/2011' and database_name='MyDB' --Find databases which have history data but no longer attached to instance select distinct(database_name) from backupset with (nolock) where database_name not in (select name from sys.databases) --Purge history older than certain date sp_delete_backuphistory @oldest_date='08/15/2009' --Purge history for a particular DB EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'myTest'