SQLGeeksPro » Page 6 of 7 » SQL Server DBA

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

Please spread the word:

https://sqlgeekspro.com/rename-sqlagnetjobs/

{ 2 Comments }

Synchronous Database Mirroring Misconceptions

by Dev on

This article is about the Synchronous Database Mirroring misconceptions. Synchronous is one of the operating modes of SQL Database Mirroring also known as High Safety mode; High Safety of transactions in case of failover.

Let’s see what Microsoft TechNet says:

When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.
After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction’s log to disk. Note the wait for this message increases the latency of the transaction.

The common misconceptions/confusions are as follows:

Principal server first commits the transaction and then sends log records to the mirror and wait until mirror server writes the same to the database.
Principal sends its log to the mirror and once it commits in mirror then only it commits in principal.

If you know what happens during commit, it is a bit easier. Commit does not mean the transaction is written to the database (data file). When you commit a transaction SQL Server writes it to the log file on the disk, in other words it hardens the log. This confirms the durability of the transaction.

Let’s take an example of the following transaction:

Begin Tran

Statement 1

Statement 2

Commit;

SQL Server writes the log records in the log file of principal database as per normal process of logging. Mirror server follow the same.

When the application fires a commit statement, Synchronous Database Mirroring mechanism has to confirm, mirror server has written all the log records related to the transaction before sending confirmation to the application.

So, from above example transaction, It is possible that SQL Server has already written the Begin Tran and Statement 1 to the log in principal and mirror server.

The following diagram depicts the same:

Synchronous Database Mirroring

Now lets see what happens when application fires a Commit Tran:

1. Principal server receives the commit transaction from client.
2. The principal server writes the log records for the transaction to the log file and sends the log records to the mirror server at the same time as shown below.Note: Principal Server has written the log records to the log file. So one of the misconception is cleared here. Principal Server does not wait for acknowledgement from mirror to commit the transaction.
3. Principal server completes the I/O but it cannot send the confirmation to the client at this point.
4. The mirror server hardens the log in disk.
5. I/O on the mirror server completes.
6. Returns an acknowledgement to the principal server.
7. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation/Acknowledgement to the client.

The following diagram shows the complete cycle:

Synchronous Database Mirroring

Note: The misconception, Principal Server waits until Mirror Server writes the transactions to the mirror database is not true. Synchronous database mirroring confirms that the Mirror writes the log records to the log file and not data file, before sending acknowledgement, hence even if the principal crashes after that, all the log records of the committed transactions are durable and mirror can take over the role of principal. And that is why it is also know as High Safety Mode.

The mirror server continuously writes data to mirror database from redo queue. In Asynchronous mirroring the Principal Server does not wait for the Acknowledgment from the mirror.

I hope this clears few misconceptions or confusions of Synchronous database mirroring. Please let me know if you have any questions.

You may like the following articles as well:

Myth around Remote Query Timeout Option
Remove or Delete Tempdb data file without restarting SQL Server
Fact about Dedicated Administrator Connection (DAC)
SSRS Encryption Key Backup Information
SQL Database Restore using NetBackup

Please spread the word:

https://sqlgeekspro.com/synchronous_mirroring/

Tagged as: Mirroring

{ Add a Comment }

Issues with Uninstall OLE DB Provider for DB2

by Dev on

After reading this article you’ll be able to fix if standard method of uninstall OLE DB Provider for DB2 does not work. The requirement was to upgrade OLE DB provider for DB2 from version 3.0 to version 5.0. As per Microsoft Document you cannot upgrade OLE DB Provider for DB2 without uninstall of prior versions.

The following instructions are listed in Microsoft Document named “Microsoft OLE DB Provider for DB2 Version 5.0

Upgrade from Previous Version

Microsoft OLE DB Provider for DB2 V 5.0 does not upgrade previous releases. If you have the following previous versions installed, then you must remove them prior to installing the Microsoft OLE DB Provider for DB2 V 5.0.”

To uninstall the product

You can use Windows Programs and Features to remove the product.

Click Control Panel, click Programs, and then click Programs and Features. The Uninstall or change a program dialog appears.
In the Name list, double click Microsoft OLE DB Provider for DB2 Version 5.0. The Data Provider Installation Wizard appears.
Click Next to get started.
On the Program Maintenance dialog, click Remove.
On the Remove the Program dialog, click Remove.
When prompted by Windows User Account Control, click Yes.
On the Completion page, click Finish

This works in ideal condition. Uninstall of OLE DB Provider for DB2 does not always go well and throw weird error messages. The Uninstall may fail due to different reasons like existing DB2 version was not properly installed, someone deleted some of the MSI files or the registry corruption etc.

One of the error message looks like as follows:


MSI (s) (78:64) [21:44:40:923]: Note: 1: 1725
MSI (s) (78:64) [21:44:40:923]: Product: Microsoft OLE DB Provider for DB2 -- Removal failed.
MSI (s) (78:64) [21:44:40:923]: Windows Installer removed the product; Product Name: Microsoft OLE DB Provider for DB2 Product Version: 8.0.4294.0. Product Language: 1033. Manufacturer:Microsoft.Removal success or error status: 1603

As I mentioned earlier the error messages are strange and generic and googling did not help to resolve this particular issue. After this article it may help though 😉
In our case what worked was voiding the existing version of DB2 by changing the registry settings.
Please be careful and make sure to take a backup of your registry settings before following the below mentioned steps.

Steps to Rename The Registry Settings For Existing Version of DB2:

Go to the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Sna Server and rename “Sna Server” to “_Sna Server” as shown in the following screen shot:

Uninstall OLE DB Provider for DB2

Similarly make the following changes:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\_Host Integration Server
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Sna Server
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Host Integration Server
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\_00FAC227E6ED834428ED6E468B6B22B2
HKEY_CLASSES_ROOT\Installer\Products\ _00FAC227E6ED834428ED6E468B6B22B2
Than go to the Program Files Directory and for the Microsoft OLEDB Driver for DB2 and just put an (Underscore) in front of the directory name.

You may be thinking why only (Underscore), you can put anything meaningful like “DB2V3_Old”. It’ll work, but if you put symbol it’ll appear at the top and easy to identify what you have changed or for future reference.

I am leaving that decision up to you. The below screen shot shows how it looks in the registry after renaming:

Uninstall OLE DB Provider for DB2

Once you are done with all the changes your new install should work.

Programs and features looks like both the versions stays happily:

Uninstall OLE DB provider for DB2

You also like to know How to Download OLE DB Provider for DB2

SQL Database Restore using NetBackup
SSRS Encryption Key Backup Information
Remove or Delete Tempdb data file without restarting SQL Server
How to Fix if All logins get deleted due to Trigger Execution
Myth around Remote Query Timeout Option

Please spread the word:

https://sqlgeekspro.com/uninstall_db2_provider/

Tagged as: OLE DB Provider for DB2

{ Add a Comment }

How to Download OLE DB Provider for DB2

by Dev on

This article is about how to download OLE DB Provider for DB2. This is a Microsoft Data Provider which offers tools and technologies using which SQL Server can access DB2 Databases.

The requirement was just to install DB2 driver and so change time was kept minimal. But I had to spend more than an hour just to find the required file in Microsoft Site. So this five minutes read can save your lot of time.

You may give it a try to search it yourself before reading this article and will get you know why simple things are not always easy.

If you are in the middle of change like me and your search engine lands you here then you can directly go to the end of this article where I have provided direct links to download. Later you can come back and continue to read.

The following instructions are listed in Microsoft Document. This example shows the download of OLE DB Provider for DB2 Version 5.

To Install the Product:

Go to the Microsoft Download Center.
Download either the x86 (32-bit) or the x64 (64-bit) version of DB2OLEDB5_x64.msi installation program.
Double-click the .msi file to start the Installation Wizard
Click Next to get started.
License Agreement page, review the license terms, click the I accept the terms in the license agreement option, and then click Next.
Registration Information page, enter your Name and Company, and then click Next.
Feature Selection page, optionally click Browse to change the Folder name in which to install the product, optionally click Disk Cost to space required to install the product, and then click Next.
Ready to Install the Program page, click Install.
When prompted by Windows User Account Control, click Yes.
On the Installing page, view the status of the installation process.
On the Completion page, click Finish.

Install looks like a cakewalk until you pass the very 1st step.

Once you go to Microsoft Download Center:

Download OLE DB Provider for DB2

Wow! Now what? Which option to choose or what should you search for?

Right here I wasted a lot of time. What you should search for is Feature Packs of SQL Server Version for which you want to install the OLE DB provider for DB2.

Download OLEDB Provider For DB2 Microsoft Download Center

Choose the link showing Feature Pack as shown below:

OLEDB Provider for DB2 Microsoft Download Center

You will get the download option as shown:

OLEDB Provider for DB2 Microsoft Download Center

Click on Download and you get the options of DB2OLEDB5_x64.msi file to choose from:

Download OLE DB Provider for DB2

Select the checkbox for DB2 and click on Next and the download process should start.

For SQL Server 2012 and prior versions you need to expand Install Instructions:

Download OLE DB Provider for DB2

Then search for DB2 keyword and you will get the option to download:

Download OLE DB2 Provider for DB2

Hope this is useful and please let me know by your valuable comments.

Direct links to Download OLE DB provider for DB2:

SQL Server 2016 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=52676

SQL Server 2014 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=42295

SQL Server 2012 Feature Pack:

https://www.microsoft.com/en-us/download/confirmation.aspx?id=29065

SQL Server 2008 R2 Feature Pack

https://www.microsoft.com/en-us/download/confirmation.aspx?id=16978

Please spread the word:

https://sqlgeekspro.com/oledb-db2/

Tagged as: OLE DB Provider for DB2

{ Add a Comment }

SQL Database Restore using NetBackup

by Dev on

In this article I am going to explain how to perform Database restore using NetBackup tool. I was fortunate enough to learn from experienced people, otherwise it was difficult to follow the materials provided. In this post I have tried to explain the database restore process in detail.

Launching NetBackup Tool and connecting to SQL Instance:

Click on Start > All Programs > Symantec NetBackup > NetBackup Agents > Right Click on NetBackup MS SQL Client > Run as Administrator as shown in the following example:

Note: Run as Administrator is a very important step, otherwise the tool won’t connect to SQL Instance and throws ambiguous error messages.

Image showing lunching NetBackup using Run As Admin option

In the User Account Control Window click on Yes.

Image of User Account Control Wizard

In the NetBackup Database Extension Window click on Ok to select the connection properties.

Image of NetBackup Database Extension Wizard to choose the connection properties

Now let’s see the ways to connect default and named SQL instances through NetBackup tool.

To Connect Default Instance:

In the SQL Server Connection properties window put the host name.
For Instance field, leave it as default click on Apply and then Close.

Image showing connection properties for default instance

Note: You do not have to put any credentials for UserId field. Leave it as default. SQL Server version, Security and Host Type will be populated once it is connected as shown in the above screen shot.

To Connect Named SQL Instance:

If the SQL Instance name is HostName\SQLInstance, you need to put the host and Instance name as shown in the following screen shot.
Click on Apply and then close.

Image about NetBackup connection to Named Instance

Now you are connected to the target Instance. You can always verify if you are connected to the correct instance from File > Set SQL Server Connection properties.

Database Restore Steps using NetBackup:

Go to File > Restore SQL Server Objects

Image showing NetBackup Restore step

Provide the SQL Server Instance name from which backup has to be restored, i.e. Source SQL Server name.

Image showing NetBackup history

You can use time filter to view the backups in particular date range.

Note: Unlike native backup of SQL Server, NetBackup tool does not create any .bak files which can be copied from source to destination. The backup files/images are scattered on storage. This is the reason you have to provide Source and Destination Server name during the restore process.

Click on Ok to go the next wizard “Restore Microsoft SQL Server Objects” >>Continue Reading

Please spread the word:

https://sqlgeekspro.com/netbackuprestore/

Tagged as: Backup Restore

{ Add a Comment }

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

Please spread the word:

https://sqlgeekspro.com/users-db_owner-access-databases/

Tagged as: db_owner role

{ 2 Comments }

SSRS Encryption Key Backup Information

by Dev on

Today I am going to discuss about an uncommon requirement and that is to find out SSRS Encryption Key backup information. In other words if there is any backup taken for the key and if yes then what is the location and backup date.

Now the question is very legitimate as they wanted to know if the reports can be restored in case situation demands. Not being a BI expert started my research from scratch.

Tried to query the “Keys” table in ReportServer database:

Image showing the keys table output As you can see no information about location of keys or at least last backup information.

A wild guess to check “backup_devices” view but no luck as expected:

Image showing output of backup_device view
Could not find any information in registry keys as well.
As per discussion with my colleague I installed SSRS on my personal laptop and then started a Process Monitor trace right before backing up the encryption key. During the process, a RSA machine key is created in C:\Users\ReportServer\AppData\Roaming\Microsoft\Crypto\RSA\.

This was like the light at the end of the tunnel and decided to dig further to discover something which would make me feel like a champion 😉

The below screen shot shows the generated machine key and path:

Image showing the machine key path

Initiated Encryption Key backup and checked in the process monitor:

Image showing the key backup path in process monitor I could see that the path showing the key backup as shown in the above screen shot.

Next tried to search if any information regarding the machine key has been logged:

That is searching for the path C:\Users\ReportServer\AppData\Roaming\Microsoft\Crypto\RSA\
Image of process monitor which does not have the Machine key creation information This “Cannot find string” message shattered my hope 🙁

To understand how the machine key got generated, I uninstalled SSRS and checked the same path and the key was still lying there. Did a reboot of the system but it was still there.
I manually deleted the key, reinstalled and configured SSRS and the key got created. So the machine key gets generated while configuring SSRS.
So my assumption:

Machine key gets generated while taking SSRS Encryption Key Backup was wrong.

Conclusion:

I have checked with many experts in different forums and the most fruitful one was https://www.sqlservercentral.com.
What we can conclude from all these is as of now SQL Server does not record the information of SSRS encryption key backup path. It make sense because the .SNK file could be moved to another location manually.
But I expect Microsoft to at least record the information about the successful backup just the way they record database backup information.

Work Around:

The only work around is to have a company policy of some sort on where to backup these keys. Like you may want to suggest to have a policy/documentation to do the backup every x months on x path.
Or use default location for the backup of the instance there are some things you could do using the registry.
And then use PowerShell to find the LastWriteTime of the .SNK file.

Here I have explained every way I tried and failed to find the required information. Sometimes it is important to know what all ways does not work so that you can think in other direction.
Well if you find the solution please let me know and that is why I request you to share this with your friends/network using the below social share icons.

Please spread the word:

https://sqlgeekspro.com/ssrs-encryption-key-backup-information/

Tagged as: SSRS

{ 1 Comment }

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

Please spread the word:

https://sqlgeekspro.com/script-to-find-the-sql-agent-job-name-in-execution/

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

Please spread the word:

https://sqlgeekspro.com/list-all-permissions-for-a-user-in-all-or-selective-databases/

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

Please spread the word:

https://sqlgeekspro.com/script-to-retrieve-database-backup-information-including-login-details/

Tagged as: T-SQL Script

{ 2 Comments }

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:

Let’s see what Microsoft TechNet says:

The common misconceptions/confusions are as follows:

The following diagram depicts the same:

Now lets see what happens when application fires a Commit Tran:

One of the error message looks like as follows:

Steps to Rename The Registry Settings For Existing Version of DB2:

To Install the Product:

Once you go to Microsoft Download Center:

Direct links to Download OLE DB provider for DB2:

Launching NetBackup Tool and connecting to SQL Instance:

To Connect Default Instance:

To Connect Named SQL Instance:

Database Restore Steps using NetBackup:

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:

Tried to query the “Keys” table in ReportServer database:

A wild guess to check “backup_devices” view but no luck as expected:

The below screen shot shows the generated machine key and path:

Initiated Encryption Key backup and checked in the process monitor:

Next tried to search if any information regarding the machine key has been logged:

Conclusion:

Work Around:

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:

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