SQLGeeksPro

This article is about a simple script to delete SQL Agent Jobs. Recently I had faced a situation when I had to remove more than 100 SQL Agent jobs.

First of all let me tell you that there may be different scenarios when you may need to delete all SQL Agent jobs, few of them or all except few required jobs etc. I have tried to include couple of queries in this article.

I had blog about renaming several SQL Agent jobs. All I have done is little modification in the query.

Script To Delete SQL Agent Jobs Having Particular Naming Convention

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 as applicable
   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_delete_job @job_name =''' + @jobName + ''', @delete_unused_schedule=1' 
       PRINT(@SQLStatement)
     --EXEC (@SQLStatement)   --Uncomment to Execute
       FETCH NEXT FROM c1 INTO @jobName
    END
   CLOSE c1
   DEALLOCATE c1

The following screenshot shows SQL Agent Jobs having naming convention like ‘1_DBA job Name’, ‘2_DBA job Name’ and so on. This is just for example, you may have different names in your environment.

Besides that there  is another way you can use this query. The result set will provide you with the SQL delete statements if you run the above query as is. Therefore all you need to do is copy the output and  execute them in the SSMS.

The following screen shot shows the result set:

Now lets talk about the other way of using the query. If you uncomment the statement EXEC (@SQLStatement) and comment the PRINT(@SQLStatement) statement and then execute the query it will directly delete the jobs.

The output will not provide any sql statements as shown in the following screen shot. Therefore you should run the query this way only if you are pretty sure what you are doing.

Now lets see another scenario like when you need to delete all the jobs except the default one ‘syspolicy_purge_history’ job Click Next Page >>

Please spread the word:

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

Please spread the word: