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
Yuri: Boston DBA
This blog contains information related to Microsoft SQL Server and Oracle Administration: Installation, Configuration, Maintenance and Troubleshooting.
9/12/2012
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
Labels:
ALTER DATABASE MODIFY FILE,
Attach,
Audit,
Backup history,
Boston DBA,
database backups,
list of recent database backups,
SQL Server 2008,
SQLCMD,
sys.database_principals,
T-SQL,
Troubleshooting
Location:
Boston, MA, USA
8/16/2012
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.
' +
N'
'
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
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'
Event Type | ' + N'Object Name | ' + N'Date | ' + N'Current User | ' + N'TSQL Command |
---|
8/07/2012
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
8/02/2012
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.
7/19/2012
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
6/22/2012
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:
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
Labels:
ALTER DATABASE,
ALTER DATABASE MODIFY FILE,
Attach,
Backup history,
Boston DBA,
Detach,
move files,
Service Broker,
SQL Server 2008,
T-SQL,
Troubleshooting,
TRUSTWORTHY
Location:
Boston, MA, USA
6/15/2012
Change Data Capture "Violation of PRIMARY KEY" issue
The following error appeared in one of my cdc job’s history today:
1. Stop and disable cdc.DBNAME_capture job
2. Execute the following script:
4. Execute the following script:
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 GO3. 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)
Subscribe to:
Posts (Atom)