These are just some of the scripts that I have used or come across to make my life that little bit easier.
1. Drop SQL users:
select 'DROP LOGIN [' + [name] + ']' from sys.server_principals where type_desc = 'SQL_LOGIN' and sid <> 0x01 and substring(name, 1, 1) <> '#'
2. Drop Windows users:
select 'DROP LOGIN [' + [name] + ']' from sys.server_principals where type = 'U'
3. Drop all jobs:
USE msdb; DECLARE @Index INT , @Count INT , @JobName VARCHAR(255) , @SQL NVARCHAR(4000) DECLARE @Jobs TABLE(ID INT IDENTITY, JobName VARCHAR(255)) INSERT INTO @Jobs(JobName) SELECT Name FROM dbo.sysjobs_view SELECT @Index = 1 , @Count = COUNT(JobName) FROM @Jobs BEGIN TRY WHILE (@Index <= @Count) BEGIN SELECT @JobName = JobName FROM @Jobs WHERE ID = @Index SET @SQL = 'EXEC sp_delete_job @job_name = ''' + @JobName + '''' EXECUTE SP_EXECUTESQL @SQL SET @Index = @Index + 1 END SELECT 'All the Jobs are removed successfully' END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS [Error Message] END CATCH
4. Set all databases single-user mode:
SELECT 'ALTER DATABASE ['+name+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' FROM MASTER.sys.databases WHERE name NOT IN ('master','tempdb','model','msdb','distribution')
5. Offline all databases:
SELECT 'ALTER DATABASE ['+name+'] SET OFFLINE WITH NO_WAIT' FROM MASTER.sys.databases WHERE name NOT IN ('master','tempdb','model','msdb','distribution')
6. Drop all databases:
SELECT 'DROP DATABASE ['+name+'] ' FROM MASTER.sys.databases WHERE name NOT IN ('master','tempdb','model','msdb','distribution')
7. Set databases with default options:
DECLARE @dbname nvarchar(500) DECLARE @ParamDef1 nvarchar(500) DECLARE @ParamDef2 nvarchar(500) DECLARE @ParamDef3 nvarchar(500) DECLARE @ParamDef4 nvarchar(500) DECLARE @ParamDef5 nvarchar(500) DECLARE @ParamDef6 nvarchar(500) DECLARE mycursor CURSOR for select name from SYS.databases where database_id > 4 order by name desc OPEN mycursor FETCH NEXT from mycursor into @dbname WHILE (@@FETCH_STATUS <> -1) BEGIN set @ParamDef1 = 'ALTER DATABASE ' + @dbname + ' SET AUTO_CLOSE OFF' set @ParamDef2 = 'ALTER DATABASE ' + @dbname + ' SET AUTO_CREATE_STATISTICS ON' set @ParamDef3 = 'ALTER DATABASE ' + @dbname + ' SET AUTO_SHRINK OFF' set @ParamDef4 = 'ALTER DATABASE ' + @dbname + ' SET AUTO_UPDATE_STATISTICS ON' set @ParamDef5 = 'ALTER DATABASE ' + @dbname + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF' set @ParamDef6 = 'ALTER DATABASE ' + @dbname + ' SET PAGE_VERIFY CHECKSUM WITH NO_WAIT' Execute sp_executesql @ParamDef1 Execute sp_executesql @ParamDef2 Execute sp_executesql @ParamDef3 Execute sp_executesql @ParamDef4 Execute sp_executesql @ParamDef5 Execute sp_executesql @ParamDef6 FETCH NEXT from mycursor into @dbname END CLOSE mycursor DEALLOCATE mycursor
8. Map orphaned users to a database:
DECLARE @sid VARCHAR(100) DECLARE @output VARCHAR(100) DECLARE @username varchar(25) DECLARE @fixuser CURSOR SET @sid = (SELECT DB_NAME() DatabaseName); SET @output = 'USE '+@sid+'' EXEC(@output) CREATE TABLE tblOrphanUsers (username sysname, usersid VARBINARY(MAX)) INSERT INTO tblOrphanUsers EXEC sp_change_users_login 'report' SET @fixuser = CURSOR FOR SELECT username FROM tblOrphanUsers OPEN @fixuser FETCH NEXT FROM @fixuser INTO @username WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Fixing username = '+@username EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM @fixuser into @username END CLOSE @fixuser DEALLOCATE @fixuser DROP TABLE tblOrphanUsers
9. Change the growth settings for the datafiles to MB (only if values are default. It will list those DB’s at the end of the script that need a manual tweak):
DECLARE @dbname nvarchar(500) DECLARE @lgname nvarchar(500) -- Drop temporary table if it exists IF OBJECT_ID('tempdb..growthinfo') IS NOT NULL DROP TABLE growthinfo; -- Create table to house database file information CREATE TABLE growthinfo ( databasename VARCHAR(128) ,name VARCHAR(128) ,fileid INT ,filename VARCHAR(1000) ,filegroup VARCHAR(128) ,size VARCHAR(25) ,maxsize VARCHAR(25) ,growth VARCHAR(25) ,usage VARCHAR(25)); -- Get database file information for each database SET NOCOUNT ON; INSERT INTO growthinfo EXEC sp_MSforeachdb 'use ? select ''?'',name, fileid, filename, filegroup = filegroup_name(groupid), ''size'' = convert(nvarchar(15), convert (bigint, size) * 8) + N'' KB'', ''maxsize'' = (case maxsize when -1 then N''Unlimited'' else convert(nvarchar(15), convert (bigint, maxsize) * 8) + N'' KB'' end), ''growth'' = (case status & 0x100000 when 0x100000 then convert(nvarchar(15), growth) + N''%'' else convert(nvarchar(15), convert (bigint, growth) * 8) + N'' KB'' end), ''usage'' = (case status & 0x40 when 0x40 then ''log only'' else ''data only'' end) from sysfiles '; -- Identify database files that use default auto-grow properties DECLARE mycursor CURSOR for SELECT databasename, name FROM growthinfo WHERE (usage = 'data only' AND growth = '1024 KB') OR (usage = 'log only' AND growth = '10%') ORDER BY databasename OPEN mycursor FETCH NEXT from mycursor into @dbname, @lgname WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE @ParamDef1 nvarchar(500) = 'ALTER DATABASE ' + @dbname + ' MODIFY FILE ( NAME = N'''+ @lgname +''', FILEGROWTH = 10MB )' Execute sp_executesql @ParamDef1 FETCH NEXT from mycursor into @dbname, @lgname END CLOSE mycursor DEALLOCATE mycursor -- print those DBs that don't have default values set select DB_NAME(mf.database_id) database_name , mf.name logical_name , CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB] , CASE mf.is_percent_growth WHEN 1 THEN 'Yes' ELSE 'No' END AS [is_percent_growth] , CASE mf.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%' WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB' END AS [growth_in_increment_of] from sys.master_files mf where mf.is_percent_growth = 1 -- get rid of temp table DROP TABLE growthinfo;
10. Generate information about all databases (v2000):
SELECT dbid, CONVERT(VARCHAR(25), DB.name) AS dbName, CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status], (SELECT COUNT(1) FROM master.dbo.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles, (SELECT SUM((size*8)/1024) FROM master.dbo.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB], (SELECT COUNT(1) FROM master.dbo.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles, (SELECT SUM((size*8)/1024) FROM master.dbo.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB], (SELECT SUM((size*8)/1024) FROM master.dbo.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM master.dbo.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB, convert(sysname,DatabasePropertyEx(name,'Updateability')) Updateability, convert(sysname,DatabasePropertyEx(name,'UserAccess')) UserAccess , convert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel , convert(sysname,DatabasePropertyEx(name,'Version')) Version , CASE CMPTLEVEL WHEN 60 THEN '60 (SQL Server 6.0)' WHEN 65 THEN '65 (SQL Server 6.5)' WHEN 70 THEN '70 (SQL Server 7.0)' WHEN 80 THEN '80 (SQL Server 2000)' WHEN 90 THEN '90 (SQL Server 2005)' WHEN 100 THEN '100 (SQL Server 2008)' END AS [compatibility level], CONVERT(VARCHAR(20), crdate, 103) + ' ' + CONVERT(VARCHAR(20), crdate, 108) AS [Creation date], ISNULL((SELECT TOP 1 CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)' FROM msdb.dbo.backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup] FROM master.dbo.sysdatabases DB ORDER BY dbName, [Last backup] DESC, NAME
11. Generate information about all databases (v2005/8):
SELECT database_id, CONVERT(VARCHAR(25), DB.name) AS dbName, CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status], state_desc, (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles, (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB], (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles, (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB], (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type!=0)+(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type=0) TotalSizeMB, user_access_desc AS [User access], recovery_model_desc AS [Recovery model], CASE compatibility_level WHEN 60 THEN '60 (SQL Server 6.0)' WHEN 65 THEN '65 (SQL Server 6.5)' WHEN 70 THEN '70 (SQL Server 7.0)' WHEN 80 THEN '80 (SQL Server 2000)' WHEN 90 THEN '90 (SQL Server 2005)' WHEN 100 THEN '100 (SQL Server 2008)' END AS [compatibility level], CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date], -- last backup ISNULL((SELECT TOP 1 CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' ' + 'seconds)' FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup], CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext], CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose], page_verify_option_desc AS [page verify option], CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only], CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink], CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics], CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics], CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby], CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown] FROM sys.databases DB ORDER BY dbName, [Last backup] DESC, NAME
12. Detach all databases:
set nocount on declare @dbname as varchar(80) declare @server_name as varchar(20) select @server_name = @@servername declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb') open rs_cursor Fetch next from rs_cursor into @dbname IF @@FETCH_STATUS <> 0 PRINT 'No database to backup...Please check your script!!!' WHILE @@FETCH_STATUS = 0 BEGIN print 'ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' print 'go' print 'sp_detach_db ' + @dbname print 'go' print 'print ''Detach of ' + upper(@dbname) + ' database successfully completed''' print 'go' PRINT ' ' FETCH NEXT FROM rs_cursor INTO @dbname END CLOSE rs_cursor deallocate rs_cursor print ' ' print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully detached'''
13. Delete all databases:
SELECT + 'ALTER DATABASE [' + [name] + '] SET single_user with rollback immediate;'+CHAR(13)+CHAR(10) + 'DROP DATABASE [' + [name] +'];'+CHAR(13)+CHAR(10) FROM [master].[sys].[databases] where [name] not in ( 'master', 'model', 'msdb', 'tempdb');
14. List Datafile/Logfile information:
SELECT DB_NAME(mf.database_id) AS databaseName, name as File_LogicalName, case when type_desc = 'LOG' then 'Log File' when type_desc = 'ROWS' then 'Data File' Else type_desc end as File_type_desc ,mf.physical_name FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id AND mf.file_id = divfs.file_id ORDER BY 1,3 ASC