In continuation of our DBA Cheatcode series :-
I started this series on Queries\T-SQL commands that we use in our day to day operations or those queries which should be handy for any SQL DBA(Junior or senior) out there. In this series, I’ll try to share as many queries as I have or I use. There are 1000s features in SQL Server and each feature has its own set of infinite queries so any blog cannot cover all queries and all features but I will try to cover the queries that is needed in our day to day activities as a DBA. Here is the third installment of this series.(Database Backup\Restore Special)
Comments are most welcome by readers which can also enhance my database of queries.
**DISCLAIMER : I have personally used all these queries but these may or may not work in your environment depending on your server setting, server edition\version etc. Please test these queries in lower environment first before executing these in production directly**
Previous Blogs of this Series :-
CHEATCODE 2 — REPLICATION SPECIFIC
Note :- I would like to thank countless sites and articles present there on google which helped me save all these queries to my personal QUERY DATABASE. This series is for knowledge sharing pupose only and you can share these queries again to your colleagues and friends.
QUERY 1 :- SCRIPT TO SHOW ALL RUNNING BACKUPS\RESTORES AND THEIR ESTIMATED COMPLETION TIME :-
This one script is very useful in our day to day operations as DBA in which we need to provide ETAs to customer for database refresh or backup related tasks. Below query provides detailed information about it :-
SELECT percent_complete, CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), ' + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, ' + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,command, s.text, start_time, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time, dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
QUERY 2 :- RESTORE HISTORY FOR A PARTICULAR INSTANCE :-
This script provides complete restore history of all databases present in the instance with restore time, backup used, Backup time and other details :-
SELECT [rs].[destination_database_name], [rs].[restore_date], [bs].[backup_start_date], [bs].[backup_finish_date], [bs].[database_name] as [source_database_name], [bmf].[physical_device_name] as [backup_file_used_for_restore] FROM msdb..restorehistory rs INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id] INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ORDER BY [rs].[restore_date] DESC
QUERY 3 :- SCRIPT TO SCRIPTOUT ALL ROLES AND PERMISSIONS OF A DATABASE :-
Its a very important and useful script for any database restore related task. In most of the scenarios, you need to perform database refresh operation between different environments which have different user permissions all to gather. So in order to get the user permissions as they were prior to restore, you need to generate permission script before database refresh and need to apply it after the database refresh. Below script does wonder here and script out all roles, users and their permissions :-
print @@servername print db_name() --Script to Reverse Engineer SQL Server Object Role Permissions --Written By Bradley Morris --In Query Analyzer be sure to go to --Query -> Current Connection Options -> Advanced (Tab) --and set Maximum characters per column --to a high number, such as 10000, so --that all the code will be displayed. begin declare @dRoleName [sysname] DECLARE _outer CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR select 'RoleName' = name from sysusers where (issqlrole = 1 or isapprole = 1) AND [name] NOT IN ( 'public', 'INFORMATION_SCHEMA', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_denydatareader', 'db_denydatawriter' ) OPEN _outer FETCH NEXT FROM _outer INTO @dRoleName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @DatabaseRoleName [sysname] --SET @DatabaseRoleName = '{Database Role Name}' SET @DatabaseRoleName = @dRoleName SET NOCOUNT ON DECLARE @errStatement [varchar](8000), @msgStatement [varchar](8000), @DatabaseRoleID [smallint], @IsApplicationRole [bit], @ObjectID [int], @ObjectName [sysname] SELECT @DatabaseRoleID = [uid], @IsApplicationRole = CAST([isapprole] AS bit) FROM [dbo].[sysusers] WHERE [name] = @DatabaseRoleName AND ( [issqlrole] = 1 OR [isapprole] = 1 ) AND [name] NOT IN ( 'public', 'INFORMATION_SCHEMA', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_denydatareader', 'db_denydatawriter' ) IF @DatabaseRoleID IS NULL BEGIN IF @DatabaseRoleName IN ( 'public', 'INFORMATION_SCHEMA', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_denydatareader', 'db_denydatawriter' ) SET @errStatement = 'Role ' + @DatabaseRoleName + ' is a fixed database role and cannot be scripted.' ELSE SET @errStatement = 'Role ' + @DatabaseRoleName + ' does not exist in ' + DB_NAME() + '.' + CHAR(13) + 'Please provide the name of a current role in ' + DB_NAME() + ' you wish to script.' RAISERROR(@errStatement, 16, 1) END ELSE BEGIN SET @msgStatement = '--Security creation script for role ' + @DatabaseRoleName + CHAR(13) + '--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) + '--Created By: ' + SUSER_NAME() + CHAR(13) + '--Add Role To Database' + CHAR(13) IF @IsApplicationRole = 1 SET @msgStatement = @msgStatement + 'EXEC sp_addapprole' + CHAR(13) + CHAR(9) + '@rolename = ''' + @DatabaseRoleName + '''' + CHAR(13) + CHAR(9) + '@password = ''{Please provide the password here}''' + CHAR(13) ELSE BEGIN set @msgStatement = '' SET @msgStatement = @msgStatement + 'EXEC sp_addrole ' + '@rolename =''' + @DatabaseRoleName + ''' go' END SET @msgStatement = @msgStatement PRINT @msgStatement DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT DISTINCT([sysobjects].[id]), '[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']' FROM [dbo].[sysprotects] INNER JOIN [dbo].[sysobjects] ON [sysprotects].[id] = [sysobjects].[id] WHERE [sysprotects].[uid] = @DatabaseRoleID OPEN _sysobjects FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName WHILE @@FETCH_STATUS = 0 BEGIN SET @msgStatement = '' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 193 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'SELECT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 195 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'INSERT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 197 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'UPDATE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 196 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'DELETE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 224 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'EXECUTE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 26 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'REFERENCES,' IF LEN(@msgStatement) > 0 BEGIN IF RIGHT(@msgStatement, 1) = ',' SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1) SET @msgStatement = 'GRANT ' + @msgStatement + ' ON ' + @ObjectName + ' TO ' + @DatabaseRoleName + ' go' PRINT @msgStatement END SET @msgStatement = '' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 193 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'SELECT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 195 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'INSERT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 197 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'UPDATE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 196 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'DELETE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 224 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'EXECUTE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 26 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'REFERENCES,' IF LEN(@msgStatement) > 0 BEGIN IF RIGHT(@msgStatement, 1) = ',' SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1) SET @msgStatement = 'DENY' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseRoleName PRINT @msgStatement END FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName END CLOSE _sysobjects DEALLOCATE _sysobjects --PRINT 'GO' END FETCH NEXT FROM _outer INTO @dRoleName end end --------------For users begin DECLARE @DatabaseUserName [sysname] declare @UName sysname SET NOCOUNT ON DECLARE --@errStatement [varchar](8000), --@msgStatement [varchar](8000), @DatabaseUserID [smallint], @ServerUserName [sysname], @RoleName [varchar](8000) --@ObjectID [int], --@ObjectName [varchar](261) DECLARE _dbusers CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT [sysusers].name FROM [dbo].[sysusers] INNER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid] OPEN _dbusers FETCH NEXT FROM _dbusers INTO @UName WHILE @@FETCH_STATUS = 0 begin --cursor ends for all users set @DatabaseUserName=@UName SELECT @DatabaseUserID = [sysusers].[uid], @ServerUserName = [master].[dbo].[syslogins].[loginname] FROM [dbo].[sysusers] INNER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid] WHERE [sysusers].[name] = @DatabaseUserName IF @DatabaseUserID IS NULL BEGIN SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) + 'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.' RAISERROR(@errStatement, 16, 1) END ELSE BEGIN SET @msgStatement ='' SET @msgStatement = --'--Add User To Database' + CHAR(13) + 'EXEC [sp_grantdbaccess]' + ' @loginame =''' + @ServerUserName + ''',' + ' @name_in_db =''' + @DatabaseUserName + ''' GO' PRINT @msgStatement DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT [name] FROM [dbo].[sysusers] WHERE [uid] IN ( SELECT [groupuid] FROM [dbo].[sysmembers] WHERE [memberuid] = @DatabaseUserID ) OPEN _sysusers FETCH NEXT FROM _sysusers INTO @RoleName WHILE @@FETCH_STATUS = 0 BEGIN SET @msgStatement = 'EXEC [sp_addrolemember] ' + '@rolename = ''' + @RoleName + ''',' + ' @membername = ''' + @DatabaseUserName + ''' go' PRINT @msgStatement FETCH NEXT FROM _sysusers INTO @RoleName END SET @msgStatement = '' + CHAR(13) PRINT @msgStatement DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT DISTINCT([sysobjects].[id]), '[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']' FROM [dbo].[sysprotects] INNER JOIN [dbo].[sysobjects] ON [sysprotects].[id] = [sysobjects].[id] WHERE [sysprotects].[uid] = @DatabaseUserID OPEN _sysobjects FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName WHILE @@FETCH_STATUS = 0 BEGIN SET @msgStatement = '' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'SELECT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'INSERT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'UPDATE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'DELETE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'EXECUTE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205) SET @msgStatement = @msgStatement + 'REFERENCES,' IF LEN(@msgStatement) > 0 BEGIN IF RIGHT(@msgStatement, 1) = ',' SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1) SET @msgStatement = 'GRANT' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseUserName PRINT @msgStatement END SET @msgStatement = '' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'SELECT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'INSERT,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'UPDATE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'DELETE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'EXECUTE,' IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206) SET @msgStatement = @msgStatement + 'REFERENCES,' IF LEN(@msgStatement) > 0 BEGIN IF RIGHT(@msgStatement, 1) = ',' SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1) SET @msgStatement = 'DENY' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseUserName PRINT @msgStatement END FETCH NEXT FROM _sysobjects INTO @ObjectID,@ObjectName END CLOSE _sysobjects DEALLOCATE _sysobjects end close _sysusers deallocate _sysusers FETCH NEXT FROM _dbusers INTO @UName END close _dbusers deallocate _dbusers --end --END end --sp_helptext sp_helprolemember ---------------------------------Generating script to add role members -------------------------------- set nocount on if object_id('tempdb..#t') is not null drop table #t select DbRole = g.name, MemberName = u.name, MemberSID = u.sid into #t from sysusers u, sysusers g, sysmembers m where g.uid = m.groupuid and g.issqlrole = 1 and u.uid = m.memberuid and 1=2 insert into #t exec sp_helprolemember --select * from #t --sp_addrolemember declare @dbrole varchar(800) declare @membername varchar(800) DECLARE _addrole CURSOR FOR SELECT DbRole, MemberName FROM #t OPEN _addrole FETCH NEXT FROM _addrole INTO @dbrole, @membername WHILE @@FETCH_STATUS = 0 BEGIN --print @dbrole + @membername print 'sp_addrolemember @rolename =''' + @dbrole + ''', @membername = ''' + @membername + ''' go' FETCH NEXT FROM _addrole INTO @dbrole, @membername end close _addrole deallocate _addrole print db_name()
QUERY 4 :- Restore SQL Database from MDF file ( without LDF file)
In the below script I have created the database, create a table in that, dropped its log file and created the database with the .mdf file.
-- created database with .mdf and .ldf file CREATE DATABASE [TEST] ON PRIMARY ( NAME = N'TEST', FILENAME = N'C:\TEST_Data.mdf') LOG ON ( NAME = N'TEST_log', FILENAME = N'C:\TEST_log.ldf') GO -- inserting data into database use TEST go CREATE TABLE customer ( customer_id int not null, customer_name varchar(50) not null, address varchar(50), city varchar(50), state varchar(25), zip_code varchar(10), ) -- inserting records insert into customer values(1,'John','9290-9300 Transit Road','Amherst','NY','14051') insert into customer values(2,'Sam','4030 Maple Ave.','Amherst','NY','14051') insert into customer values(3,'Jason','4888 State Route 30','Amherst','NY','14051') insert into customer values(4,'Joe','1651 Clark Street','Amherst','NY','14051') go -- Selecting Data and verifying Data is inserted select * from TEST..customer -- deleting the log file -- detaching the database file USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'TEST' GO -- now next step is delete the file manually or you can do it from command prompt EXEC xp_cmdshell 'del C:\Test_log.ldf' EXEC xp_cmdshell 'dir C:\Test_log.ldf' -- script to attach the database USE [master] GO CREATE DATABASE TEST ON ( FILENAME = N'C:\TEST_Data.mdf' ) FOR ATTACH GO
SCRIPT 5 :- RANDOM USEFUL SCRIPTS FOR DATABASE BACKUP & RESTORE
Some Randome scripts for Backup\restore :- –- Script to Get the backup file properties RESTORE FILELISTONLY FROM DISK = ‘D:\TEST.bak’ –- Script to delete the backup history of the specific databsae EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’TEST1′ GO --Script to get basic details of a backup file RESTORE HEADERONLY FROM DISK = 'E:\Restore\backroom.bak' --Script to check consistency of a backup file RESTORE VERIFYONLY FROM DISK ='E:\Restore\backroom.bak' --Script to check backup media information for the backup device. RESTORE LABELONLY FROM DISK = 'E:\Restore\Sample.bak' -- Script to fix all Orphan users after restore :- DECLARE @username varchar(25) DECLARE fixusers CURSOR FOR SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name OPEN fixusers FETCH NEXT FROM fixusers INTO @username WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers INTO @username END CLOSE fixusers DEALLOCATE fixusers -- Script to Drop all users of source backup after restore declare @user_name varchar(100), @exec_sql varchar(2000) declare user_cursor cursor for select name from sysusers where issqlrole = 0 and hasdbaccess = 1 and name <> 'dbo' open user_cursor FETCH NEXT FROM user_cursor into @user_name WHILE @@FETCH_STATUS = 0 BEGIN set @exec_sql = 'exec sp_revokedbaccess ' + '['+ @user_name +']' --print @exec_sql execute (@exec_sql) FETCH NEXT FROM user_cursor into @user_name END close user_cursor deallocate user_cursor
Happy Learning.
One response to “SQL DBA Cheatcode Part 3 : Few Important Queries for any DBA”
[…] Few Important Queries for any DBA – Backup Restore […]