SQL Server xp_regread with T-SQL

The Tivoli Monitoring agent requires the  Error Log Path and the SQL Server Home Path ,  for installation

My first problem was to derive the t-sql  to gain the information for one sql server instance .
The second problem was to use a scripting framework to iterate through every sql server instance on the database server inventory 

The t-sql to derive the error log file was straightforward

select ServerProperty('ErrorLogFileName')

The code for the SQL Server path is an "undocumented" method . "Undocumented"  means Microsoft do not support it.  "Undocumented"  also means you have to figure out to how to use it without BOL.

The extended stored procedure  “xp_instance_regread” , in the master database,   derives values from the registry. 

The code below returns the SQL Server home for a given SQL Server instance

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot
OUTPUT
print @SmoRoot

The second problem involves getting this information from multiple sql server instances on the same network. Powershell is great for this purpose. The results were useful.

The script reads the instances from "C:\MyInstancesPROD_2005.txt"   and writes them out to "C:\projects\test.txt"

foreach ($svr in get-content "C:\InstancesPROD_2005.txt"){
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY('ProductVersion') AS Version, 'itmsql' AS LOGIN,SERVERPROPERTY('ErrorLogFileName') AS ErrorLog,@SmoRoot AS SQLPath
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize  
    $dt | out-File "C:\projects\test.txt" -append -encoding ASCII

    
}

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Best Practices in less than 6 minutes 1/3

SQL Best Practices in less than 6 minutes 1/3

Clear thinking in the long term pays off. If only more managers/developers followed these ideas we would have more efficient databases. This video goes into how not to design a database

Synchronise Users and Logins after SQL Server 2005 restore

I'm working on a whole bunch of moving SQL Server 2005  around . I'm backing up and restoring from
ServerA to ServerB .
A script I've found useful (courtesy of sqlservercentral.com) . This will synchronise the Database Users with the
SQL Server Logins, which tend to become orphaned .

-----------------------------------------CODE START--------------------------------------------

DECLARE @UserName nvarchar(255)

DECLARE Cursor_OrphanedUser cursor for

SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name

OPEN Cursor_OrphanedUser

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

WHILE (@@fetch_status = 0)

BEGIN

PRINT @UserName + ' Synchronization of Logins in Progress'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

END

CLOSE Cursor_OrphanedUser

DEALLOCATE Cursor_OrphanedUser

Go
------------------------------------------CODE END--------------------------------------------------

List all SQL Server users

I'm investigating how to set up a monthly report and return all users on a given SQL Server to a spreadsheet.
I will then forward this spreadsheet to all the application owners, who need to "sign off" the users.

It's like a monthly housekeeping exercise.
The script below (SQL Server 2005), identifies the users. I will need to add another call to the Active Directory to return their actual name .

--------------------------------CODE START----------------------------------
--
SET NOCOUNT ON
DECLARE @name varchar(20)

DECLARE logon_cursor CURSOR FOR 
select name from sys.server_principals WHERE 
name LIKE 'N%' AND name NOT LIKE 'NT AUTHORITY%'

OPEN logon_cursor

FETCH NEXT FROM logon_cursor 
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @name
  
   
   -- Get the next author.
FETCH NEXT FROM logon_cursor 
   INTO @name
END

CLOSE logon_cursor
DEALLOCATE logon_cursor
GO
-------------------------------CODE END-----------------------------------------

BackUp All Databases

Sometimes I just need to backup all the dbs in a situation of moving the databases to another server.
The script below is both 2000 and 2005 compatible .
You might want to consider adding the COPY keyword if you are using the script in 2005.
This will deal with the problem of adhoc backups breaking backup chains

You can alter the script to include system databases

--------------------------CODE START----------------------------------

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor
--------------------------CODE END-----------------------------------------------------