Script Library
To change the name of a default instance of SQL, run the following procedures:
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
To change the name of a named instance of SQL, run the following procedures:
sp_dropserver <old_name\instancename>
GO
sp_addserver <new_name\instancename>, local
GO
Restart SQL Server to update the name.
SQL 2000 – Change ownership of all user tables to DBO
DECLARE tabcurs CURSOR
FOR
SELECT ‘{owner}.’ + [name]
FROM sysobjects
WHERE xtype = ‘U’
OPEN tabcurs
DECLARE @tname NVARCHAR(517)
FETCH NEXT FROM tabcurs INTO @tname
WHILE @@fetch_status = 0
BEGIN
EXEC sp_changeobjectowner @tname, ‘dbo’
FETCH NEXT FROM tabcurs INTO @tname
END
CLOSE tabcurs
DEALLOCATE tabcurs
Source: http://blogs.x2line.com/al/articles/155.aspx
SQL 2000 – Find last backup date
SELECT
T1.name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),’Not Yet Taken’) as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),’NA’) as UserName
FROM dbo.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name where T1.name <> ‘tempdb’
GROUP BY T1.name
ORDER BY T1.name
Source: http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LastBackUpDate
SQL 2005/2008 – Find last backup date
SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),’Not Yet Taken’) as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),’NA’) as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name where T1.Name <> ‘tempdb’
GROUP BY T1.Name
ORDER BY T1.Name
Source: http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LastBackUpDate
Fix Orphaned users
Run this in each database the the user exists
EXEC sp_change_users_login ‘update_one’, ‘<user>’, ‘<user>’
