Posts Tagged ‘xp_instance_regread’

“How to find the SQL Server port number ?” has been an old topic being discussed in multiple forums. The common solution provided is using xp_instance_regread. It is one of the useful undocumented stored procedures when you need to read registry settings. In this article, I will give you two solutions. First one using xp_instance_regread and second, using sys.dm_server_registry, a new dynamic management view shipped in SQL Server 2008 R2

Method 1 : Using xp_instance_regread.
In this method we will use xp_instance_regread to find the port number.

--This script will work on SQL Server 2005 and above
DECLARE @HkeyLocal nvarchar(18)
Declare @Instance varchar(100)
DECLARE @MSSqlServerRegPath nvarchar(200)
DECLARE @PortNumber nvarchar(100)

SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\\Microsoft SQL Server\'
+ @Instance + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'

Print @MSSqlServerRegPath
EXEC xp_instance_regread @HkeyLocal
, @MSSqlServerRegPath
, N'TcpPort'
, @PortNumber OUTPUT
SELECT @PortNumber as [Port Number]

Port Number

(1 row(s) affected)

Method 2 : Using using sys.dm_server_registry.
In this method we will use sys.dm_server_registry to find the port number.

--This script will work on SQL Server 2012 and above
Use master
Select value_data as [Port Number]
From sys.dm_server_registry
Where registry_key like '%IPALL%' and
value_name ='TcpPort'

Port Number

(1 row(s) affected)

Conclusion :
If you compare both methods, you will feel the difference, because the code is much optimized in the second method. Also, you can utilize the result set of second method further without using any temporary table but on the other hand you need temporary table to utilize the result set further.

Read Full Post »

By default, SQL Server takes the backup in the following location (‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\’). But if you don’t take the backup in this location, each time you need to change the backup path.To avoid this, you can set your default database backup path.

Here is the solution :
You can change the default backup path from the given below script.

DECLARE @HkeyLocal nvarchar(18)
DECLARE @BackupDirectory nvarchar(512)
DECLARE @InstanceRegPath sysname
DECLARE @MSSqlServerRegPath nvarchar(31)
DECLARE @NewPath nvarchar(100)

SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
SET @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'

EXEC xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT  
SELECT @BackupDirectory -- Read the default backup directory path

-- Update/Change the default backup directory path to @NewPath
SET @NewPath='D:\DBBackup' -- Change the new default backup folder here 
EXEC xp_instance_regwrite @HkeyLocal,@InstanceRegPath, N'BackupDirectory', REG_SZ,@NewPath 

Here is screen image of before processing this script :

Here is the screen image after processing this script :

Your feedback is very much appreciated. Please do write.

Read Full Post »