Tag: T-SQL Script

Script To Provide Database Restore Report

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:Script to provide database restore report

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:
Script To Provide Database Restore Report

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

{ Add a Comment }

Script to Find the SQL Agent Job Name in Execution

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

{ Add a Comment }

List all permissions for a user in all or selective databases

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:

  1. 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.
  2. 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.
  3. 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:Permissions for a user

Click here to copy the code

One of the best books I have ever read and recommend to become next level DBA:

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:

Permission for user

Click here 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

{ Add a Comment }

Script to retrieve database backup information

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

{ Add a Comment }

Script to List All Users with db_owner role in SQL 2000

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

{ Add a Comment }