Useful Scripts Archives » Page 2 of 2

Browsing: Useful Scripts

Script To Provide Database Restore Report

by Dev on

This article is about a script to provide database restore report for all or selective databases.

How many of you got requests to restore database from one environment to other? Yeah, I know it is one of those common activities in day to day life of a DBA. If I ask how many of you had to provide a restore report of a single database? There will be many of you have an assertive answer.

But one of the not so common request is to provide restore report of all databases. Here I am going to provide the queries to pull restore report of all and selective databases. First of all lets see the restore report of all the databases.

Furthermore the result set is more useful if you get the following fields: Source Instance, Destination Instance, Source Database Name, Destination Database Name, Restore Type, Restore Date, the login id/user name who performed the restore and the backup file name used in the restore.

Note: I have commented out few fields in the query which are not so important in all situations. You may need to uncomment them as per your requirement.

Query to provide database restore report of all databases

SELECT mb.server_name AS SourceSQLInstance, @@servername AS DestinationSQLInstance, 
mb.database_name AS SourceDatabaseName,
rh.destination_database_name AS DestinationDatabaseName, rh.restore_date AS RestoreDate, 
CASE WHEN rh.restore_type='D' THEN 'FULL'
     WHEN rh.restore_type='L' THEN 'Log'
     WHEN rh.restore_type='I' THEN 'Differential'
END AS RestoreType,
--sd.collation_name,
--sd.compatibility_level,
--sd.recovery_model_desc, 
rh.[user_name], bf.physical_device_name AS BackupFileName
FROM msdb..[restorehistory] rh
INNER JOIN master.sys.databases sd
ON rh.destination_database_name = sd.name
INNER JOIN msdb..backupset mb
ON rh.backup_set_id=mb.backup_set_id
INNER JOIN msdb..backupmediafamily bf
ON mb.backup_set_id=bf.media_set_id
ORDER BY restore_date DESC

The sample result set:

Most noteworthy field in the above result set is backup file name. The complete path with the file name shown is the location where backup was taken and not the path from where the restore files was picked. In other words if you take a backup on X location and then copy the backup file to Y location and restore process picked the file from Y location the result set will show the X location. Hope I am clear enough.

Query to provide database restore report of single or few databases

SELECT mb.server_name AS SourceSQLInstance, @@servername AS DestinationSQLInstance, 
mb.database_name AS SourceDatabaseName,
rh.destination_database_name AS DestinationDatabaseName, rh.restore_date AS RestoreDate, 
CASE WHEN rh.restore_type='D' THEN 'FULL'
     WHEN rh.restore_type='L' THEN 'Log'
     WHEN rh.restore_type='I' THEN 'Differential'
END AS RestoreType,
--sd.collation_name,
--sd.compatibility_level,
--sd.recovery_model_desc, 
rh.[user_name], bf.physical_device_name AS BackupFileName
FROM msdb..[restorehistory] rh
INNER JOIN master.sys.databases sd
ON rh.destination_database_name = sd.name
INNER JOIN msdb..backupset mb
ON rh.backup_set_id=mb.backup_set_id
INNER JOIN msdb..backupmediafamily bf
ON mb.backup_set_id=bf.media_set_id
WHERE rh.destination_database_name IN ('Test_4','Test_restore')  --Put the database names
ORDER BY restore_date DESC

Result Set:

Hope this is helpful. Please feel free to let me know your thoughts by leaving your comments below.
You may like the below articles as well:

Script to List All Users with db_owner role in SQL 2000
List all permissions for a user in all or selective databases
Script to Rename SQL Agent Jobs
Script to find the SQL Agent Job Name in Execution
List of Users with db_owner Role in All Databases

Tagged as: T-SQL Script

{ Add a Comment }

Script to Rename SQL Agent Jobs

by Dev on

This article is about a script to Rename SQL Agent Jobs. First of all let me tell you the requirement.

The environment had a dedicated database for DBA’s which is used for several database maintenance and hence had a bunch of SQL Agent jobs associated. I am sure some of you also have similar setup.

Project team came up with an updated version. As a result in the upgrade process we had to rename all the jobs. I am sure you don’t wanna do it manually for each instance. Especially if you have more than 100 instances 🙂

The below queries will help in following conditions:

Rename SQL Agent Jobs having particular naming convention
Change the name of SQL Agent Jobs by removing first few characters

Note: The queries can not be fully generic and needs to be customized as per your environment. Basically depends upon the job name.

Query to Rename SQL Agent Jobs having particular naming conventions:

The below example shows job names having names like ‘1_DBA job Name’, ‘2_DBA job Name’, to ‘9_DBA job Name’. Please make the necessary changes as per your environment as provided in the comments.

Script to rename SQL Agent Jobs

DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE '%_DBA%'  --Change the LIKE operator
   OPEN c1
   FETCH NEXT FROM c1 INTO @jobName
     IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
     WHILE @@fetch_status=0
    BEGIN
       SET @SQLStatement= 'EXEC msdb.dbo.sp_update_job @job_name =''' + @jobName + ''', @new_name =''OLD_' +@jobName +''''
       PRINT(@SQLStatement)
     --EXEC (@SQLStatement)   --Uncomment to Execute
       FETCH NEXT FROM c1 INTO @jobName
    END
   CLOSE c1
   DEALLOCATE c1

OutPut will produce set of statements:

Rename SQL Agent Jobs

The result set is for you to verify the way you want to rename the jobs. Once verified either you execute the statements Or you can uncomment the statement EXEC (@SQLStatement) in the above code and execute.

Screenshot showing the renamed jobs:

Query to rename SQL Agent jobs

Rollback query for the above changes:

The same script can be taken as reference to rename SQL Agent Jobs by removing first few characters.

DECLARE @SQLStatement VARCHAR(200)
DECLARE @jobName SYSNAME
DECLARE @jobName1 SYSNAME
   DECLARE c1 CURSOR FOR
   SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE 'OLD_%' 
   OPEN c1
   FETCH next FROM c1 INTO @jobName
   IF @@CURSOR_ROWS=0
     PRINT 'No Job found! Please re-check LIKE operator.'
   WHILE @@fetch_status=0
   BEGIN
       
       SELECT @jobName1 = SUBSTRING(@jobName,5,LEN(@jobName)) --Second parameter may need change as per the job name
       SET @SQLStatement= 'EXEC msdb.dbo.sp_update_job @job_name =''' + @jobName + ''', @new_name =''' +@jobName1 +''''
       --PRINT(@SQLStatement)
       EXEC (@SQLStatement)
           FETCH NEXT FROM c1 INTO @jobName
   END
   CLOSE c1
   DEALLOCATE c1

Hope this is helpful. Furthermore you are most welcome to tweak the queries as per your requirement. I am open to feedback therefore please feel free to comment.
In addition you may also like the below queries:

List all permissions for a user in all or selective databases
Script to List All Users with db_owner role in SQL 2000
Script to find the SQL Agent Job Name in Execution
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name

{ 2 Comments }

List of Users with db_owner Role in All Databases

by Dev on

Just after returning from Christmas holiday, I got a requirement to send a report having a list of users with db_owner role in all databases in SQL Server Instance. There was an internal audit going on and the auditor wanted that report.
I knew my dear friend “Internet” will help me get that report in lieu of few clicks. I really didn’t want to spend time on writing a whole new script as I was still in holiday mood.

Wait a sec! I think I already had something which I could modify a bit to get the work done. What I had was List all permissions for a user in all or selective databases

But the output is something which I cannot send a report since it shows blank rows for the databases on which the user does not have access to as shown below:
Image of query output showing some blank rows

So, I used a temporary table to get the result I wanted. In fact I have added the same in the above article as well upon realization of the problem.

There are two ways you can pull the same report.

Using Cursor to scan through all the databases.
Using the undocumented stored procedure sp_MSforeachdb.

Using Cursor to retrieve a List of users with db_owner role in all databases:

DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM master.sys.databases
WHERE state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  
 
SELECT @statement = 'use '+@dbname +';'+ 'SELECT

ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where pp.name=''db_owner'' and p.name<>''dbo'''

INSERT INTO #UserPermission
EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #UserPermission

DROP TABLE #UserPermission

Sample output:

Image showing output of all users with db_owner role in all databases using cursor

Using sp_MSforeachdb to retrieve the same information:

CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

INSERT #UserPermission
EXEC sp_MSforeachdb '

use [?]

BEGIN

  SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
  pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
  FROM sys.database_role_members roles
  JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
  JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
  where pp.name=''db_owner'' and p.name<>''dbo'' 

END '

SELECT * FROM  #UserPermission

DROP TABLE #UserPermission

Sample output is same as above:

Query to Retrieve List of all users having db_owner role in Selective databases:

DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #UserPermission
(
ServerName SYSNAME,
DbName SYSNAME,
UserName SYSNAME,
TypeOfLogIn VARCHAR(50),
PermissionLevel VARCHAR(50),
TypeOfRole VARCHAR(50)
)

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('master','ReportServerSSRS','ReportServerSSRSTempDB') -- change the database names as applicable
AND state_desc='online'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ 'SELECT
ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
pp.name as PermissionLevel, pp.type_desc as TypeOfRole
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where pp.name=''db_owner'' and p.name<>''dbo'''

INSERT INTO #UserPermission
EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #UserPermission

DROP TABLE #UserPermission

Sample output of selective databases:

Using sp_MSforeachdb to list users with db_owner role only in user databases:

CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

INSERT #UserPermission
EXEC sp_MSforeachdb '

use [?]

IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN

  SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
  pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
  FROM sys.database_role_members roles
  JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
  JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
  where pp.name=''db_owner'' and p.name<>''dbo''   

END '

SELECT * FROM  #UserPermission

DROP TABLE #UserPermission

Sample result set:

Image showing output of sp_MSforeach db for user databases

Hope this helps. If you have any suggestion please feel free to put in comments section.

You may also find the following scripts useful for your day to day life:
Script to find the SQL Agent Job Name in Execution
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000

Tagged as: db_owner role

{ 2 Comments }

Script to Find the SQL Agent Job Name in Execution

by Dev on

In initial days I had to scratch my head for a long time to find out the SQL Agent Job Name in Execution just to provide details to customer or to find out the job causing issues.
The below simple query will be helpful in the following situations:

Several agent jobs showing in “sp_who2 active” results set and you want to identify them.
You have identified the root blocker as a SQL Agent job and need the problem job name to notify Customer.
Customer requested to find out the status of a job in progress etc.

`sp_who2 active` output does not show the job name:

Image of Sp_who2 active output which does not show job name

The below simple query will help you find the information:

SELECT * FROM msdb.dbo.sysjobs WHERE job_id=CAST(<jobid> AS UNIQUEIDENTIFIER)

Considering the above example:

SELECT * FROM msdb.dbo.sysjobs WHERE job_id=CAST(0xD81B43320D3F2843B9A6A40CA120F14B AS UNIQUEIDENTIFIER)

Image of the query result showing job name

Let me know if it helps and any suggestions are always welcome.
You may also find the below scripts useful:
List all permissions for a user in all or selective databases
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000

Tagged as: T-SQL Script

{ 2 Comments }

List all permissions for a user in all or selective databases

by Dev on

Today I am gonna talk about the requirement of finding out all permissions for a user in all or selective databases.

The query here will help you in following situations:

Customer has provided you with a user id and couple of database names and they wanted to find out all the permissions the user has on those databases.
You have a task to provide access for a user id to some of the databases. You have provided the access and now want to pull the report to send to customer.
You have been given a user id and asked to find out all permissions in all user databases.

This may not be exactly what you are looking for but certainly will give you some idea to make it your way.

Retrieve all permissions for a user in selective databases:

Link to Download the code:

Query for Selective Database(s) permissions

In this example I have listed the permissions for “Test_User” on databases “database1” and “database2″as shown the below screen shot:

Retrieve all permissions for a user in all user databases:

Download the code:

Query to copy the code

Here is an example output:

Please feel free to let me know if there is any better way you can think of to retrieve the same information.

Check out the below scripts as well:
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name prior to Failover
Script to List All Users with db_owner role in SQL 2000

Tagged as: T-SQL Script

{ 2 Comments }

Script to retrieve database backup information

by Dev on

Backup script is something which is a very common need of a DBA in day to day life.This article will provide you with a base query to retrieve database backup information.

I am not able to give credit to anybody as I have seen lot of similar queries in the internet and so could not find the original writer.

Hmm! then what is the need of having another one in this blog? Well, I have experienced that sometimes a simple query suffice your need rather than a fancy one.

The below queries will help answering the following questions:

Do you have recent database backups?
What type of backup is being taken for particular databases?
What is the backup size?
Who has taken the backup or which application login id is being used to take backup?
What is the Recovery model of the databases being backup up?
How long it took to backup?

Backup details of all the databases of all backup types:

SELECT server_name, database_name, 
CASE 
   WHEN type='D' THEN 'Full Backup' 
   WHEN type='L' THEN 'Log Backup'
   WHEN type='I' THEN 'Diff Backup' 
END AS BackupType, 
backup_start_date, backup_finish_date, DATEDIFF(SS,  backup_start_date, backup_finish_date)/60 AS [Duration(Minutes)],
CAST(backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', user_name AS UserName  
FROM msdb..backupset 
ORDER BY backup_finish_date DESC

The following screen shot is to show you an example output:

You can use this as a base and tweak it here and there as per your requirements. Few examples are given below.

Retrieve the same information for particular database(s) and for a particular type:

SELECT server_name, database_name, 
CASE 
   WHEN type='D' THEN 'Full Backup' 
   WHEN type='L' THEN 'Log Backup'
   WHEN type='I' THEN 'Diff Backup' 
END AS BackupType, 
backup_start_date, backup_finish_date, DATEDIFF(SS,  backup_start_date, backup_finish_date)/60 AS [Duration(Minutes)],
CAST(backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', user_name AS UserName  FROM msdb..backupset
WHERE database_name IN ('master','model','msdb')
AND type='D' 
ORDER BY backup_finish_date DESC

Include recovery model of the databases in the same output list:

SELECT b.server_name, b.database_name, 
CASE 
   WHEN type='D' THEN 'Full Backup' 
   WHEN type='L' THEN 'Log Backup'
   WHEN type='I' THEN 'Diff Backup' 
END AS BackupType, 
d.recovery_model_desc AS RecoveryModel,
b.backup_start_date, b.backup_finish_date, DATEDIFF(SS,  b.backup_start_date, b.backup_finish_date)/60 AS [Duration(Minutes)],
CAST(b.backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', b.user_name AS UserName  FROM msdb..backupset b
JOIN sys.databases d
ON b.database_name = d.name
WHERE database_name IN ('master','model','msdb')
AND type='D' 
ORDER BY backup_finish_date DESC

Please let me know if it helps by leaving your comments below.

Further more you can go through the below link for some addition scripts:

https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

You might also want to look at the below scripts:
Script to get Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000

Tagged as: T-SQL Script

{ 2 Comments }

Script to get SQL Cluster Failover Time and Node Name

by Dev on

A DBA’s life becomes easier if there is a script to get the required information. This article is about a script to get SQL Cluster Failover time and Node Name prior to failover and little more.

You can go through the following link to know what and how to check the same information in SQL Server Error log. I have also explained what all other related information can be found. The output of the below query will make more sense if you read the article first.

SQL Cluster Instance Failover Time and Node Name prior to Failover

After writing the above article I thought of working on the script to scan through all the error log files. This way I can find not only the latest SQL Cluster failover time and Node name but also history of failover information. This may help in situations where you are facing frequent failover issues or you need a failover history report.

CREATE TABLE #ErrorLog(
   LogDate DATETIME,
   ErrorSource NVARCHAR(MAX),
   ErrorMessage NVARCHAR(MAX)
)

CREATE TABLE #NumberOfLogs(
   ID INT PRIMARY KEY NOT NULL,
   LogDate DATETIME NOT NULL,
   LogFileSize bigint
)

INSERT INTO #NumberOfLogs(ID,LogDate,LogFileSize)
EXEC master.dbo.xp_enumerrorlogs

DECLARE @ErrorLogID INT

DECLARE cNumberOfLogs CURSOR FOR
   SELECT ID
   FROM #NumberOfLogs

OPEN cNumberOfLogs
FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
   WHILE @@FETCH_STATUS = 0
   
   BEGIN
       INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
       EXEC sp_readerrorlog @ErrorLogID, 1, 'NETBIOS'
        
       INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
       EXEC sp_readerrorlog @ErrorLogID, 1, 'SQL Server is terminating'
       FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
   END 
   
CLOSE cNumberOfLogs
DEALLOCATE cNumberOfLogs

SELECT LogDate, ErrorMessage FROM #ErrorLog

DROP TABLE #ErrorLog
DROP TABLE #NumberOfLogs

The below screen shot shows a sample result set:

If this is helpful, please let me know by leaving your comments below. I would love to hear from you if you have any better way to get the same information.

You may also like to check the Script to List All Users with db_owner role in SQL 2000.

{ Add a Comment }

Script to List All Users with db_owner role in SQL 2000

by Dev on

The Requirement was to provide a report to list all users with db_owner role. No big deal right? But the fun part was there were many servers running SQL 2000 version. Of course first thing first, search in the internet. Got few but not quite which I was looking for.

So ultimately I wrote the below query to provide the required details in SQL 2000. I have kept the option open to list not only for db_owner but also for any other database role.
You can tweak it as per your requirements.

SELECT p.name AS UserName,
      CASE WHEN p.isntgroup =1 AND p.isntuser=0 THEN 'Windows Group'
           WHEN p.isntgroup =0 AND p.isntuser=1 THEN 'Windows Login'
           WHEN p.issqlrole=1 THEN 'Database Role'
      ELSE 'SQL Login' END AS 'Login Type',
      USER_NAME(roles.groupuid) AS 'AssociatedRole' 
      FROM sysmembers roles
           JOIN sysusers p ON roles.memberuid = p.uid
           JOIN sysusers pp ON roles.groupuid = pp.uid AND  p.uid NOT IN (0,1,2,3,4) AND
           USER_NAME(roles.groupuid) IN ('db_owner', 'db_datareader')

The following screen shot shows the output:

I hope this helps. This post can only be treated as complete once you put your thoughts in the comment section.
You might also want to check the link which includes video to know the Myth around Remote Query Timeout Option

Tagged as: T-SQL Script

{ Add a Comment }

Browsing: Useful Scripts

Query to provide database restore report of all databases

The sample result set:

Query to provide database restore report of single or few databases

Result Set:

The below queries will help in following conditions:

Query to Rename SQL Agent Jobs having particular naming conventions:

OutPut will produce set of statements:

Screenshot showing the renamed jobs:

Rollback query for the above changes:

Using Cursor to retrieve a List of users with db_owner role in all databases:

Sample output:

Using sp_MSforeachdb to retrieve the same information:

Sample output is same as above:

Query to Retrieve List of all users having db_owner role in Selective databases:

Sample output of selective databases:

Using sp_MSforeachdb to list users with db_owner role only in user databases:

Sample result set:

sp_who2 active output does not show the job name:

The below simple query will help you find the information:

Retrieve all permissions for a user in selective databases:

Retrieve all permissions for a user in all user databases:

The below queries will help answering the following questions:

Backup details of all the databases of all backup types:

The following screen shot is to show you an example output:

Retrieve the same information for particular database(s) and for a particular type:

Include recovery model of the databases in the same output list:

The below screen shot shows a sample result set:

`sp_who2 active` output does not show the job name: