Moving System Databases in SQL Server 2008

Great article on this. Follow it to the letter from start to finish though, or you could wind up with a non-functioning SQL Server:
Moving System Databases

Change the Default Database File Path


USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
REG_SZ, N'S:\DATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog',
REG_SZ, N'L:\SQLLog'
GO

Default Database File Path

Code: 0x80012017 Description: The package path referenced an object that cannot be found

From the command line run this:

REGSVR32 "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll"

Make sure that the path in quotations is where the DTS.dll is stored.

Maintenance Plan Failure

Great Articles Explaining JOINs

http://www.sqlservercentral.com/articles/T-SQL/67941/

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Windows Server Uptime


Start -> Run -> cmd.exe
systeminfo | find "System Up Time"


INSERT INTO WHERE NOT EXISTS

-- If @UserID and @ServerID already exists
-- in table [tblSysAdmins], the values
-- will not be inserted

INSERT INTO [tblSysAdmins] ([UserID], [ServerID], [Authorize])
SELECT @UserID, @ServerID, @Authorize
WHERE NOT EXISTS
(SELECT *
FROM tblSysAdmins
WHERE [UserID] = @UserID
AND [ServerID] = @ServerID )

Query to get Users on SQL Server 2005

CREATE TABLE #TEMP(
UserName nvarchar(200),
GroupName nvarchar(50),
LoginName nvarchar(200),
DefDBName nvarchar(50),
DefSchemaName nvarchar(50),
UserID nvarchar(10),
SID nvarchar(200))

DECLARE @ID INT, @LAST INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)

SET @ID = (SELECT MIN(DATABASE_ID) FROM sys.databases)
SET @LAST = (SELECT MAX(DATABASE_ID) FROM sys.databases)

WHILE @ID <= @LAST 
 BEGIN 
   SET @DB_NAME = (SELECT NAME 
                   FROM sys.databases 
                   WHERE database_id = @ID)


    IF @DB_NAME IS NOT NULL 
      BEGIN 
        SET @SQL = 'INSERT INTO #TEMP 
        EXEC [' + @DB_NAME + '].[dbo].[sp_helpuser]' 
        EXEC (@SQL) 
      END 


  SET @ID = @ID + 1 
 END


 SELECT UserName 
 FROM #TEMP 
 WHERE UserName NOT IN 
  ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA') 
 GROUP BY UserName


 DROP TABLE #TEMP