List of recent database backups

Usually we need to find list of recent database backups. When the backup was taken and where it was saved. Below script will get a 5 recent backups

DECLARE @DBNAME VARCHAR(128)

SET @DBNAME = 'DB_Name'

SELECT TOP 5 
 A.database_name
 ,B.physical_device_name
 ,A.media_set_id
 ,A.backup_size
 ,A.backup_start_date
 ,A.backup_finish_date
 
FROM msdb.dbo.backupset A
 INNER JOIN msdb.dbo.backupmediafamily B
ON 
 A.media_set_id = B.media_set_id
WHERE 
A.Database_Name= @DBNAME

ORDER BY A.backup_finish_date DESC

Audit server roles membership

When you have a huge environment and some senior developers have sysadmin rights on some of you DB servers(Yeah yeah, I know it's bad) it's good to have a trigger to audit server roles membership. Also it's good to receive an email when server roles membership changes. Below is my trigger to audit server roles membership, I use 2 events: ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER.

ALTER TRIGGER MYSERVERRROLEAUDIT
ON ALL SERVER
FOR ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER
AS

SET NOCOUNT ON
DECLARE @eventtable TABLE (EventType NVARCHAR(MAX), ObjectName varchar(100), EventDate datetime, CurrentUser varchar(100), TSQLCommand NVARCHAR(MAX))
DECLARE @EventType NVARCHAR(MAX)
DECLARE @SchemaName NVARCHAR(MAX)
DECLARE @DBName VARCHAR(100)
DECLARE @Subj VARCHAR(200)
DECLARE @ObjectName varchar(100)
DECLARE @ObjectType varchar(100)
DECLARE @Message NVARCHAR(MAX)
DECLARE @TSQL NVARCHAR(MAX)

SELECT 
 @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')  
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')  
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
,@DBName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
,@TSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

insert into @eventtable
select @EventType, @ObjectName, getdate(), SUSER_SNAME(), @TSQL


SET @Message =N'' +
    N'' +
    N'' +
    N'' +
    N'' +
    N'' +
    CAST ( ( SELECT td = EventType, '',
    td = ObjectName, '',
    td = EventDate, '',
    td = CurrentUser, '',
    td = TSQLCommand, ''
    FROM  @eventtable
FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'
Event Type
Object Name
Date
Current User
TSQL Command
' 

SELECT @Subj='Role membership has changed on '+@@servername+' server'   
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'your_profile_name',
    @recipients = 'your@distributionlist.com',
    @body = @Message,
    @importance= 'High',
    @subject =@Subj,
    @body_format = 'HTML';

SET NOCOUNT OFF
GO

Script User Mappings for all SQL server logins sp_addrolemember

Sometimes after you've copied all users from one server to another (sp_help_revlogin) you need to copy all mappings (sp_addrolemember) as well. Execute the script from below in order to Script User Mappings for all SQL server logins and generate sp_addrolemember for all users.

SELECT 'EXEC sp_addrolemember '''+rp.name+''', '''+mp.name+'''' FROM sys.database_role_members drm
JOIN sys.database_principals rp ON (drm.role_principal_id = rp.principal_id)
JOIN sys.database_principals mp ON (drm.member_principal_id = mp.principal_id)
WHERE mp.name NOT IN ('dbo')
GO

DBCA hangs after clicking Finish Button

A few days ago I've faced an issue when DBCA hangs after clicking Finish Button. I was running DBCA via Putty and Xming on my Windows 7 machine. After some investigations I've found that this issue is caused by some missing Xming fonts.

Script deployment to multiple servers

I use the following script in order to deploy a T-SQL script to multiple servers:

SET NOCOUNT ON
GO

DECLARE @object_name SYSNAME                      
DECLARE @cmdstr NVARCHAR(300)                        
DECLARE @servername SYSNAME                             
DECLARE @instancename SYSNAME                      
DECLARE @inputfile NVARCHAR(128)                      
DECLARE @outputfile NVARCHAR(128)                     
DECLARE @prodsrvs TABLE(srvname sysname)
                
DECLARE crs_srv CURSOR
LOCAL 
FAST_FORWARD
READ_ONLY
FOR 
  SELECT srvname
  FROM @prodsrvs

INSERT INTO @prodsrvs(srvname)
SELECT 'Server-01'
UNION ALL SELECT 'Server-02'
UNION ALL SELECT 'Server-03'
UNION ALL SELECT 'Server-04'

OPEN crs_srv
FETCH NEXT FROM crs_srv INTO @servername

WHILE @@FETCH_STATUS=0                   
BEGIN

SELECT @cmdstr = 'sqlcmd -S ' + @servername + ' -i "\\PathToScript\Script.sql" -o "\\PathToScript\script_output.txt" '

EXEC dbo.xp_cmdshell @cmdstr
PRINT(@servername)

WAITFOR DELAY '00:00:05'

FETCH NEXT FROM crs_srv INTO @servername               
END

CLOSE crs_srv
DEALLOCATE crs_srv

SET NOCOUNT OFF
GO

Detach/Attach VS ALTER DATABASE MODIFY FILE

It's not a news that Detach/Attach method for moving database affects different aspects. When a read-only database is detached and then reattached, the backup information about the current differential base is lost. Service Broker gets disabled and it requires exclusive access to re-enable it back. TRUSTWORTHY also get disabled during detach/attach operation.
So in order not to get trapped it's better to use ALTER DATABASE MODIFY FILE command. Below script generated ALTER DATABASE commands, you just need to enter your database name and new locations for data and log files:

DECLARE @NewDataFolder nvarchar(1000);
DECLARE @NewLogFolder nvarchar(1000);
DECLARE @FileTable table (lkey int identity (1,1) primary key, [name]nvarchar(100), physical_name nvarchar(1000), [type] int );
DECLARE @CmdTable table (command nvarchar(1000));
DECLARE @sql nvarchar(4000);
DECLARE @count int;
DECLARE @RowNum int;
DECLARE @DbName nvarchar(100);
DECLARE @OldPath nvarchar(1000);
DECLARE @Type int;
DECLARE @LogicalName nvarchar(100);
DECLARE @ParmDefinition nvarchar(1000);
DECLARE @FileName nvarchar(100);
DECLARE @NewPath nvarchar(1000);

--Make sure that @NewDataFolder and @NewLogFolder has '\' at the end
SET @DbName = 'YourDBName'
SET @NewDataFolder = 'C:\SQLData\'
SET @NewLogFolder = 'C:\SQLLog\'

SET @sql = 'select name, physical_name, type from  ' + @DbName + '.sys.database_files'
INSERT INTO @FileTable
EXEC sp_executesql @sql 

WHILE @@rowcount > 0    
 BEGIN
  
  SET @OldPath = (SELECT TOP 1 physical_name  FROM @FileTable)
  SET @Type = (SELECT TOP 1 [type]  FROM @FileTable)
  SET @LogicalName = (SELECT TOP 1 [name]  FROM @FileTable)
  SET @FileName = (SELECT REVERSE(SUBSTRING(REVERSE(@OldPath), 0, CHARINDEX('\', REVERSE(@OldPath), 1))))
  IF @type = 0
  BEGIN
   SET @NewPath = @NewDataFolder + @FileName
  END
  ELSE
  BEGIN
   SET @NewPath = @NewLogFolder + @FileName
  END
  SET @sql = 'ALTER DATABASE ' + @DbName + ' MODIFY FILE (NAME = ' + @LogicalName + ', FILENAME = ''' + @NewPath   + ''')'
  INSERT INTO @CmdTable SELECT @sql 
  DELETE FROM @FileTable WHERE [name] = @LogicalName 
END 
SELECT * FROM @CmdTable

Change Data Capture "Violation of PRIMARY KEY" issue

The following error appeared in one of my cdc job’s history today:

Unable to add entries to the Change Data Capture LSN time mapping table to reflect dml changes applied to the tracked tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.

When I’ve checked the sys.dm_cdc_errors there were Violation of PRIMARY KEY errors:

Violation of PRIMARY KEY constraint 'lsn_time_mapping_clustered_idx'. Cannot insert duplicate key in object 'cdc.lsn_time_mapping'

 I found a solution on sqlservercentral.com and decided to share it. So if you're facing this issue you can execute the following steps to fix it:

1. Stop and disable cdc.DBNAME_capture job
2. Execute the following script:

SELECT * INTO bkp_lsn_time_mapping FROM cdc.lsn_time_mapping
GO
TRUNCATE TABLE cdc.lsn_time_mapping
GO

3. Enable and start the job
4. Execute the following script:

INSERT INTO cdc.lsn_time_mapping
SELECT * FROM bkp_lsn_time_mapping
WHERE start_lsn not in (SELECT start_lsn FROM cdc.lsn_time_mapping)

Easiest way to configure mirroring

First of all we need to restore database on Mirror server in NORECOVERY and apply all outstanding logs with NORECOVERY as well. In order to find all outstanding logs and generate RESTORE commands you may use the script from below:

/* 
--------------------------------------
--Find all outstanding logs.
--Generate RESTORE WITH NORECOVERY commands and execute.
-------------------------------------- 
*/

DECLARE
 @dbname SYSNAME
,@mostrecentfull DATETIME
,@mostrecentdiff DATETIME
,@mostrecentdb DATETIME
,@logapplystartdate DATETIME
,@phydevice NVARCHAR(260)
,@errstat INT
,@sqlcmd VARCHAR(2048)

--db name set
SET @dbname='YourDBName'

SELECT @mostrecentdb = MAX(backup_start_date)
FROM msdb.dbo.backupset
WHERE (type='D' OR type='I')
AND database_name=@dbname

DECLARE crs_logstoapply CURSOR
LOCAL
FAST_FORWARD 
FOR
 SELECT b.physical_device_name
 FROM msdb.dbo.backupmediafamily b INNER JOIN msdb.dbo.backupset s 
 ON b.media_set_id = s.media_set_id 
 WHERE s.type='L'
 AND s.database_name=@dbname
 AND s.backup_start_date >= @mostrecentdb
 ORDER BY s.backup_start_date ASC


OPEN crs_logstoapply

FETCH NEXT FROM crs_logstoapply INTO @phydevice

WHILE @@fetch_status=0
BEGIN

 --restore the next log
 PRINT 'RESTORE LOG ' + @dbname + ' FROM DISK=''' + @phydevice + ''' WITH NORECOVERY' 

 FETCH NEXT FROM crs_logstoapply INTO @phydevice
 
END

CLOSE crs_logstoapply
DEALLOCATE crs_logstoapply
GO

The next steps are: Endpoint configuration and setting partners. We can use SQLCMD mode to configure these steps from one place. In order to enable SQLCMD mode go to your MSSMS menu Query->SQLCMD Mode. Then you may use the script from below, just modify database and server names

:SETVAR PrincipalServer PrincipalServerName01
:SETVAR MirrorServer MirrorServerName02
:SETVAR Database2Mirror YourDatabaseName
go


:ON ERROR EXIT
go

:CONNECT $(PrincipalServer)

CREATE ENDPOINT [Endpoint_Mirroring]
STATE=STARTED  --STARTED STOPPED DISABLED
AS TCP ( LISTENER_PORT = 5022 ) 
FOR DATABASE_MIRRORING 
(
 AUTHENTICATION = WINDOWS NEGOTIATE  --NTLM KERBEROS NEGOTIATE

,ENCRYPTION = DISABLED  --DISABLED SUPPORTED REQUIRED 
-- ALGORITHM --RC4, AES, AES RC4, or RC4 AES

,ROLE = PARTNER  
)

USE master ;
GO

CREATE LOGIN [Somedomain\otherpartnerlogin] FROM WINDOWS ;
GO

-- Grant connect permissions on endpoint to login account 

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\otherpartneruser]; --unnecessary if the user is an Administrator
GO

:CONNECT $(MirrorServer)

CREATE ENDPOINT [Endpoint_Mirroring]
STATE=STARTED  --STARTED STOPPED DISABLED
AS TCP ( LISTENER_PORT = 5022 ) 
FOR DATABASE_MIRRORING 
(
 AUTHENTICATION = WINDOWS NEGOTIATE  --NTLM KERBEROS NEGOTIATE

,ENCRYPTION = DISABLED  --DISABLED SUPPORTED REQUIRED 
-- ALGORITHM --RC4, AES, AES RC4, or RC4 AES

,ROLE = PARTNER  
)

USE master ;
GO

CREATE LOGIN [Somedomain\otherpartnerlogin] FROM WINDOWS ;
GO

-- Grant connect permissions on endpoint to login account 

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\otherpartneruser]; --unnecessary if the user is an Administrator
GO

:CONNECT $(MirrorServer)
 
ALTER DATABASE $(Database2Mirror) 
SET PARTNER = 'TCP://PrincipalServerName01.yournetwork.com:5022'
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror) 
SET PARTNER = 'TCP://MirrorServerName02.yournetwork.com:5022'
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror) SET PARTNER SAFETY OFF;
GO

Database mirroring monitor displays "Not connected..." error

When you see the below error in your Database mirroring monitor that means that monitor cannt connect to mirror server.

You need to do the following to fix that:

1. Go to Menu->Action-> Manage Server Instance Connections
2. Set up connection for each Server instance with user that have access to mirror server.