SansSQL: SQLCMD

Thursday, April 7, 2011

T-SQL Query to find the SQL Server Protocols Status

Here is a T-SQL Query that I have developed to get the status of the SQL Server Protocols.
This query reads data from the registry.
DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @value_Out VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),
'MSSQLSERVER'))

CREATE TABLE #SQLServerProtocols
(ProtocolName nvarchar(25),
Value nvarchar(10),
Data bit)

if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey_InstanceName,
  @value_name = @InstanceName,
  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Sm'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null


SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Np'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Via'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null
END

SELECT ProtocolName, IsEnabled=CASE WHEN Data=1 THEN 'Enabled' 
ELSE 'Disabled' END FROM #SQLServerProtocols

DROP TABLE #SQLServerProtocols

Download this script from here.
Posted by Sandesh Segu 4 comments
Labels: , , , , , , , , , ,

Sunday, March 20, 2011

Automating SQL Server Express Backups

As we all know that many of the third party applications uses SQL Server Express Edition to store their backend data. And also these application will be used in Live environments which requires backing up of the databases to recover data during a disaster.
Since there is no SQL Server agent in Express Edition, we cannot schedule SQL Backups or any other DB Maintenance activities using SQL Scheduler. So for this purpose, we have to make use of the windows scheduler.
For Automating the backup process, i have developed the below query which can be used for multiple purpose and can be scheduled using windows scheduler as well as SQL Agent.

Script:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_BackupDatabases]') AND type in (N'P', N'PC'))
BEGIN
PRINT 'Stored Procedure "sp_BackupDatabases" already exists in the database. Dropping the SP to create a newer Version.'
DROP PROCEDURE [dbo].[sp_BackupDatabases]
END
GO

CREATE PROC sp_BackupDatabases (@BackupDBType nvarchar(10)='Help', @DBName nvarchar(max)=NULL, @BackupPath nvarchar(max)=NULL )
AS
/*
Author: Sandesh Segu
Website: http://www.SansSQL.com
*/
SET NOCOUNT ON

DECLARE @DateTime nvarchar(25)
SET @DateTime=LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),120),':',''),'-',''),' ',''),12)

IF @BackupDBType not in ('ALL','System','User','Specific','Help')
BEGIN
RAISERROR ('Incorrect Parameter Value Passed. @BackupDBType Parameters should be ''ALL'',''System'',''User'',''Specific''',16,1)
END

IF @BackupDBType<> 'HELP' and @BackupPath IS NULL
BEGIN
RAISERROR ('Specify the path to backup databases. @BackupPath must be specified',16,1)
END
IF (RIGHT(@BackupPath,1))<>'\'
BEGIN
Select @BackupPath=@BackupPath+'\'
END
ELSE
BEGIN
Select @BackupPath=@BackupPath
END

IF @BackupDBType='Help' AND @DBName IS NULL AND @BackupPath IS NULL
BEGIN
Print 'Usage of this Stored Proc can be in any of the below format.'
Print '-------------------------------------------------------------------------'
Print '1. To Backup All Databases in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''ALL'', @BackupPath = ''C:\Backup''' + Char(10)
Print '2. To Backup only System Databases in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''System'', @BackupPath = ''C:\Backup''' + Char(10)
Print '3. To Backup only User Databases in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''User'', @BackupPath = ''C:\Backup''' + Char(10)
Print '4. To Backup specific (One) Database(s) in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''Specific'', @DBName = ''AdventureWorks'', @BackupPath = ''C:\Backup''' + Char(10)
Print '5. To Backup specific (more than One) Database(s) in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''Specific'', @DBName = ''AdventureWorks,master,msdb'', @BackupPath = ''C:\Backup'''
Print '-------------------------------------------------------------------------'
END

IF exists (select * from sys.objects where name='BackupDatabases')
DROP TABLE BackupDatabases
CREATE TABLE BackupDatabases
(DBName nvarchar(100),
DatabaseID int,
[BackupStatement] nvarchar(max))

IF @BackupDBType='ALL' AND @DBName IS NULL AND @BackupPath IS NOT NULL
BEGIN
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE name <>'tempdb'
END

IF @BackupDBType='System' AND @DBName IS NULL AND @BackupPath IS NOT NULL
BEGIN
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE name in ('master', 'model','msdb')
END

IF @BackupDBType='User' AND @DBName IS NULL AND @BackupPath IS NOT NULL
BEGIN
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE database_id>4
END

IF @BackupDBType='Specific' AND @DBName IS NULL
BEGIN
RAISERROR ('Specify the Database Name(s) to Backup. @DBName must be specified.',16,1)
END

IF @BackupDBType='Specific' AND @DBName IS NOT NULL AND @BackupPath IS NOT NULL
BEGIN
DECLARE @DelimiterPos int
-- Find the first comma
SET @DelimiterPos = PATINDEX( '%,%', @DBName)
-- If a delimiter was found, @DelimiterPos will be > 0.
WHILE @DelimiterPos > 0
BEGIN
-- Insert the value between the start of the string and the first delimiter, into the table variable.
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE name in (SELECT CAST(LTRIM(RTRIM((SUBSTRING(@DBName, 1, @DelimiterPos -1)))) AS nvarchar      ))                  
-- Trim the string of the first value and delimiter.
SET @DBName = SUBSTRING(@DBName, @DelimiterPos +1, LEN(@DBName) - @DelimiterPos)
                       
-- Look for the next delimiter in the string.
SET @DelimiterPos = PATINDEX( '%,%', @DBName)
END
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE name in (SELECT CAST(LTRIM(RTRIM((@DBName))) AS nvarchar))
END

UPDATE BackupDatabases SET BackupStatement= 'Print ''Backup of Database '+DBName+' Started''; Backup Database ['+DBName+'] TO DISK='''+@BackupPath+DBName+'_Backup_'+@DateTime+'.bak'' WITH INIT, STATS=10'
Print 'The Requested database(s) are being backed up to the location "'+@BackupPath+'"'
WHILE (Select COUNT(*) from BackupDatabases)>0
BEGIN
DECLARE @BackupStatement nvarchar(max)
SELECT @BackupStatement= [BackupStatement] FROM BackupDatabases
Exec sp_executesql @BackupStatement
Delete from BackupDatabases where [BackupStatement]=@BackupStatement
END
DROP TABLE BackupDatabases

SET NOCOUNT OFF
GO

Usage:
Usage of this Stored Proc can be in any of the below format.
-------------------------------------------------------------------------
1. To Backup All Databases in a instance
   Exec sp_BackupDatabases @BackupDBType = 'ALL', @BackupPath = 'C:\Backup'

2. To Backup only System Databases in a instance
   Exec sp_BackupDatabases @BackupDBType = 'System', @BackupPath = 'C:\Backup'

3. To Backup only User Databases in a instance
   Exec sp_BackupDatabases @BackupDBType = 'User', @BackupPath = 'C:\Backup'

4. To Backup specific (One) Database(s) in a instance
   Exec sp_BackupDatabases @BackupDBType = 'Specific', @DBName = 'AdventureWorks', @BackupPath = 'C:\Backup'

5. To Backup specific (more than One) Database(s) in a instance
   Exec sp_BackupDatabases @BackupDBType = 'Specific', @DBName = 'AdventureWorks,master,msdb', @BackupPath = 'C:\Backup'

To Delete the old backups I use the below query.
DECLARE @TwoDaysOld VARCHAR(50)
Set @TwoDaysOld=CAST(DATEADD(d, -2, GETDATE()) AS VARCHAR)
Select @TwoDaysOld
Exec master.dbo.xp_delete_file 0,N'C:\Backups\',N'bak',@TwoDaysOld
This will delete any .bak files in the given location which are older than 2 days.

So after creating the sp_BackupDatabases stored proc, if we want to schedule the backups using windows scheduler than we have create a batch file (.bat file) which will be called in the windows scheduler at the scheduled time.
sqlcmd -S(local)\SQLEXPRESS -E -Q"DECLARE @TwoDaysOld VARCHAR(50) Set @TwoDaysOld=CAST(DATEADD(d, -2, GETDATE()) AS VARCHAR)
Exec master.dbo.sp_BackupDatabases @BackupDBType = 'User', @BackupPath = 'C:\Backup'
Exec master.dbo.xp_delete_file 0,N'D:\Backups\',N'bak',@TwoDaysOld"

Scripts can be downloaded from the below locations

Posted by Sandesh Segu 6 comments
Labels: , , , , , , , , , ,

Saturday, February 12, 2011

Different ways to find the SQL Server Port Number

We all know that SQL Server by default listens to the port number 1433. But, due to security reasons, the default port number will/can be changed to a different port in order to minimize hacks on SQL Server.

There are many ways using which you can find the port number of the SQL server which it is listening to.
  1. Using the SQL Server Configuration Manager
  2. Using Registry
  3. Using Error Log
  4. etc...
Using SQL Server Configuration Manager:
Click Start >> Run
For SQL Server 2005 Type "SQLServerManager.msc" and click OK
For SQL Server 2008 Type "SQLServerManager10.msc" and click OK

This will Open the SQL Server Configuration Manager for you.
Now in the Left Pane, expand "SQL Server Network Configuration" and click on "Protocols for <InstanceName>"
Now In the right pane, right-click on the "TCP/IP" protocol and go to properties

In the TCP/IP properties, go to the "IPAddresses" Tab and scroll down and locate the section "IPAll"
In the "IPAll" Section, the "TCP Port" represents the Port Number SQL Server is listening to.

Note: <InstanceName> is your SQL Server Instance Name

Using Registry:
To find the SQL Server Port number using registry, execute the below query.
DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER'))

--For SQL Server 2000
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8
BEGIN
if @InstanceName='MSSQLSERVER'
Begin
SET @RegKey='SOFTWARE\Microsoft\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\'
END
ELSE
BEGIN
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\'
END

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'TcpPort',
  @value = @value OUTPUT
 
Select @@SERVERNAME as ServerName,@value as PortNumber
END

--For SQL Server 2005 and up
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey_InstanceName,
  @value_name = @InstanceName,
  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'TcpPort',
  @value = @value OUTPUT
 
Select @@SERVERNAME as ServerName,@value as PortNumber
END

Using Error Log:
To find the SQL Server Port number using error Log, execute the below query.
SET NOCOUNT ON
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8
BEGIN
Create Table ##ErrorLog_2K
(ErrorLog nvarchar(1000),
ContinuationRow int )

INSERT INTO ##ErrorLog_2K
Exec master..xp_readerrorlog

SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(ErrorLog,5),1,4) as [PortNumber]
FROM ##ErrorLog_2K where ErrorLog like '%SQL Server listening on 1%'

DROP TABLE ##ErrorLog_2K
END

if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
Create Table ##ErrorLog
(Logdate datetime,
ProcessInfo nvarchar(100),
[Text] nvarchar(1000))

INSERT INTO ##ErrorLog exec master..xp_readerrorlog

SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(text,6),1,4) as [PortNumber]
FROM ##ErrorLog where text like 'Server is listening on % ''any'' %'

DROP TABLE ##ErrorLog
END
SET NOCOUNT OFF

You can download the scripts from here.
  1. SansSQL_FindPortNumber_UsingRegistry.sql
  2. SansSQL_FindPortNumber_UsingErrorLog.sql
Posted by Sandesh Segu 455 comments
Labels: , , , , , , , , , ,
Subscribe to: Posts (Atom)

Ads