4zhjf3QZ

1USE master
2GO
3PRINT ‘*******************************************************************************************’
4PRINT ‘*******************************************************************************************’
5PRINT ‘<SQL Server Instance Name>’
6SELECT @@SERVERNAME
7GO
8PRINT ‘*******************************************************************************************’
9PRINT ‘*******************************************************************************************’
10PRINT ‘<SQL Server Version, Edition and Build>’
11SELECT @@VERSION
12GO
13PRINT ‘*******************************************************************************************’
14PRINT ‘*******************************************************************************************’
15PRINT ‘<The server wide configuration>’
16GO
17SP_CONFIGURE ‘show advanced options’,1
18reconfigure with override
19GO
20sp_configure
21GO
22SP_CONFIGURE ‘show advanced options’,0
23reconfigure with override
24GO
25PRINT ‘*******************************************************************************************’
26PRINT ‘*******************************************************************************************’
27PRINT ‘<List of Attached Databases>’
28SELECT name as Database_Name, dbid as Database_ID, cmptlevel as Database_Compatibility_Level, filename as Database_MDF_Location from SYSDATABASES
29GO
30PRINT ‘*******************************************************************************************’
31PRINT ‘*******************************************************************************************’
32PRINT ‘<Information for all the databases and their files>’
33SET NOCOUNT ON
34IF (object_id( ‘tempdb..#TMPFIXEDDRIVES’ ) IS NOT NULL) DROP TABLE #TMPFIXEDDRIVES
35IF (object_id( ‘tempdb..#TMPSPACEUSED’ ) IS NOT NULL) DROP TABLE #TMPSPACEUSED
36IF (object_id( ‘tempdb..#HDB’ ) IS NOT NULL) DROP TABLE #HDB
37CREATE TABLE #TMPFIXEDDRIVES (DRIVE CHAR(1), MBFREE INT) 
38INSERT INTO #TMPFIXEDDRIVES 
39EXEC xp_FIXEDDRIVES 
40CREATE TABLE #TMPSPACEUSED (DBNAME VARCHAR(255), FILEID INT,FILENME VARCHAR(255), SPACEUSED FLOAT) 
41CREATE TABLE #HDB (name sysname not null,db_size varchar(25) not null,owner varchar(40) not null,dbid int not null,created smalldatetime not null,status varchar(500) not null,compatibility_level int not null)
42INSERT INTO #HDB exec sp_helpdb;
43INSERT INTO #TMPSPACEUSED 
44EXEC( ‘sp_msforeachdb”use [?]; Select ””?”” DBName,fileid, Name FileNme, fileproperty(Name,””SpaceUsed””) SpaceUsed from sysfiles”’) 
45SELECT @@servername as SQLServerInstance, A.Database_id as Database_ID,A.NAME AS Database_Name,
46CASE D.FILEID WHEN 1 THEN ltrim(XX.db_size) ELSE NULL END as Database_Size ,CASE D.FILEID  WHEN 1 THEN XX.owner ELSE NULL END as Database_Owner,
47CASE D.FILEID WHEN 1 THEN XX.created ELSE NULL END as Database_Creation_Date ,C.DRIVE, C.MBFREE AS Free_Space_of_the_Disk, D.FILEID as Database_File_ID, B.NAME AS Database_Filename, 
48CASE B.TYPE WHEN 0 THEN ‘DATA’ ELSE TYPE_DESC END AS FILETYPE, (B.SIZE * 8 / 1024)AS FILESIZE_MB, ROUND((B.SIZE * 8 / 1024) – (D.SPACEUSED / 128),2) as SPACEFREE_MB,
49ROUND(100-((((B.SIZE * 8 / 1024) – (D.SPACEUSED / 128))*100)/ CASE(B.SIZE * 8 / 1024) WHEN 0 THEN 1 ELSE (B.SIZE * 8 / 1024)  END ),2) as [%USED], b.size,
50b.max_size, b.growth, b.is_percent_growth, B.PHYSICAL_NAME, CASE B.TYPE WHEN 0 THEN A.recovery_model_desc ELSE NULL END AS [Recovery_Model], 
51CASE B.TYPE WHEN 0 THEN A.compatibility_level ELSE NULL END AS [Compatibility_Level] ,CASE D.FILEID WHEN 1 THEN BR.last_backup_finish_date 
52ELSE NULL END as [Backup],CASE D.FILEID  WHEN 1 THEN BR.last_TRLog_backup_finish_date ELSE NULL  END as TRBackup ,CASE D.FILEID 
53WHEN 1 THEN BR.last_restore_date ELSE NULL END as [Restore],  DM.mirroring_role_desc+'(‘+DM.mirroring_state_desc+’)’ as DBMirror_Info
54FROM SYS.DATABASES A  
55INNER JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID 
56INNER JOIN #TMPFIXEDDRIVES C ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE 
57INNER JOIN #TMPSPACEUSED D ON A.NAME = D.DBNAME AND B.NAME = D.FILENME 
58INNER JOIN #HDB XX on XX.dbid= A.Database_id 
59INNER JOIN (SELECT D.database_id,B.last_backup_finish_date,TR.last_TRLog_backup_finish_date,R.last_restore_date
60FROM sys.databases D
61LEFT JOIN (SELECT BS.database_name ,max(BS.backup_finish_date) as last_backup_finish_date FROM msdb.dbo.backupset BS (NOLOCK)
62INNER JOIN msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id 
63WHERE  BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)
64AND BS.server_name = @@servername and BS.type=’D’
65GROUP BY BS.database_name ) B on D.name=B.database_name LEFT JOIN (SELECT BS.database_name ,max(BS.backup_finish_date) as last_TRLog_backup_finish_date 
66FROM  msdb.dbo.backupset BS (NOLOCK) INNER JOIN msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id
67WHERE BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-1,getdate()),120) AS datetime) AND BS.server_name = @@servername and BS.type=’L’
68GROUP BY BS.database_name) TR on D.name=TR.database_name 
69LEFT JOIN (SELECT rh.destination_database_name, max(rh.restore_date) as last_restore_date FROM msdb.dbo.restorehistory rh (NOLOCK)
70INNER JOIN msdb.dbo.backupset BS (NOLOCK) ON rh.backup_set_id=BS.backup_set_id
71WHERE BS.type= ‘D’ AND RH.restore_date >=CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)
72GROUP BY rh.destination_database_name) R on D.name=R.destination_database_name) BR on A.Database_id=BR.database_id
73LEFT JOIN msdb.sys.database_mirroring dm (nolock) on A.database_id=dm.database_id
74ORDER BY Database_Name
75IF (object_id( ‘tempdb..#TMPFIXEDDRIVES’ ) IS NOT NULL) DROP TABLE #TMPFIXEDDRIVES
76IF (object_id( ‘tempdb..#TMPSPACEUSED’ ) IS NOT NULL) DROP TABLE #TMPSPACEUSED
77IF (object_id( ‘tempdb..#HDB’ ) IS NOT NULL) DROP TABLE #HDB
78GO
79PRINT ‘*******************************************************************************************’
80PRINT ‘*******************************************************************************************’
81PRINT ‘<Information for all the server logins>’
82EXEC sp_helplogins
83GO
84PRINT ‘*******************************************************************************************’
85PRINT ‘*******************************************************************************************’
86PRINT ‘<The permissions of the users for each database>’
87DECLARE @DB_USers TABLE(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
88INSERT @DB_USers EXEC sp_MSforeachdb’
89use [?]
90SELECT ”?” AS DB_Name,
91case prin.name when ”dbo” then prin.name + ” (”+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =”?”) + ”)” else prin.name end AS UserName,
92prin.type_desc AS LoginType,
93isnull(USER_NAME(mem.role_principal_id),””) AS AssociatedRole ,create_date,modify_date
94FROM sys.database_principals prin
95LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
96WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
97prin.is_fixed_role <> 1 AND prin.name NOT LIKE ”##%”’
98SELECT dbname,username ,logintype ,create_date ,modify_date ,STUFF((SELECT ‘,’ + CONVERT(VARCHAR(500),associatedrole)
99FROM @DB_USers user2 WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName FOR XML PATH(”) ),1,1,”) AS Permissions_user
100FROM @DB_USers user1 GROUP BY dbname,username ,logintype ,create_date ,modify_date ORDER BY DBName,username
101GO
102PRINT ‘*******************************************************************************************’
103PRINT ‘*******************************************************************************************’
104PRINT ‘<Script out any Credentials under Security>’
105select   ‘CREATE CREDENTIAL ‘ + name + ‘ WITH IDENTITY = ”’ + credential_identity + ”’, SECRET = ”<Put Password Here>”;’  from sys.credentials  order by name;
106GO
107PRINT ‘*******************************************************************************************’
108PRINT ‘*******************************************************************************************’
109PRINT ‘<List all Server Backup Devices>’
110SELECT ‘Server[@Name=’ + quotename(CAST(serverproperty(N’Servername’) AS sysname),””) + ‘]’ + ‘/BackupDevice[@Name=’ + quotename(o.name,””) + ‘]’ AS [Urn],
111o.name AS [Name], case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state 
112where target_query_expression_with_id like ‘Server/BackupDevice\[@Name=’ + QUOTENAME(o.name, ””) + ‘\]%’ ESCAPE ‘\’) then 1 else 0 end AS [PolicyHealthState] 
113FROM sys.backup_devices o ORDER BY [Name] ASC
114GO
115PRINT ‘*******************************************************************************************’
116PRINT ‘*******************************************************************************************’
117PRINT ‘<List all System and Mirroring endpoints>’
118select * from sys.endpoints 
119GO
120PRINT ‘*******************************************************************************************’
121PRINT ‘*******************************************************************************************’
122PRINT ‘<List all Linked Servers and their associated login>’
123SELECT ss.server_id ,ss.name ,’Server ‘ = Case ss.Server_id   when 0 then ‘Current Server’   else ‘Remote Server’   end
124,ss.product   ,ss.provider  ,ss.catalog  ,’Local Login ‘ = case sl.uses_self_credential   when 1 then ‘Uses Self Credentials’
125else ssp.name end ,’Remote Login Name’ = sl.remote_name ,’RPC Out Enabled’    = case ss.is_rpc_out_enabled when 1 then ‘True’
126else ‘False’ end ,’Data Access Enabled’ = case ss.is_data_access_enabled when 1 then ‘True’ else ‘False’ end
127,ss.modify_date FROM sys.Servers ss  
128LEFT JOIN sys.linked_logins sl ON ss.server_id = sl.server_id
129LEFT JOIN sys.server_principals ssp ON ssp.principal_id = sl.local_principal_id
130GO
131PRINT ‘*******************************************************************************************’
132PRINT ‘*******************************************************************************************’
133PRINT ‘<Script out the Logon Triggers of the server, if any exist>’
134SELECT SSM.definition FROM sys.server_triggers AS ST JOIN sys.server_sql_modules AS SSM ON ST.object_id = SSM.object_id
135GO
136PRINT ‘*******************************************************************************************’
137PRINT ‘*******************************************************************************************’
138PRINT ‘<REPLICATION – List Publication or Subscription articles>’
139IF EXISTS (SELECT 1 
140           FROM INFORMATION_SCHEMA.TABLES 
141           WHERE TABLE_TYPE=’BASE TABLE’ 
142           AND TABLE_NAME=’sysextendedarticlesview’) 
143(SELECT  sub.srvname,  pub.name, art.name, art.dest_table,art.dest_owner
144FROM sysextendedarticlesview art
145inner join syspublications pub on (art.pubid = pub.pubid)
146inner join syssubscriptions sub on (sub.artid = art.artid))
147ELSE SELECT ‘No Publication or Subcsription articles were found’
148GO
149PRINT ‘*******************************************************************************************’
150PRINT ‘*******************************************************************************************’
151PRINT ‘<List all SQL Server Agent jobs>’
152USE MSDB
153GO
154SELECT        srv.srvname,
155              sj.name,
156              COALESCE(sj.description, ”),
157              ss.name,
158              ss.schedule_id,
159              sc.name,
160              ss.freq_type,
161              ss.freq_interval,
162              ss.freq_subday_type,
163              ss.freq_subday_interval,
164              ss.freq_relative_interval,
165              ss.freq_recurrence_factor,
166              COALESCE(STR(ss.active_start_date, 8), CONVERT(CHAR(8), GETDATE(), 112)),
167              STUFF(STUFF(REPLACE(STR(ss.active_start_time, 6), ‘ ‘, ‘0’), 3, 0, ‘:’), 6, 0, ‘:’),
168              STR(ss.active_end_date, 8),
169              STUFF(STUFF(REPLACE(STR(ss.active_end_time, 6), ‘ ‘, ‘0’), 3, 0, ‘:’), 6, 0, ‘:’),
170              sj.enabled,
171              ss.enabled
172FROM          msdb..sysschedules AS ss
173INNER JOIN    msdb..sysjobschedules AS sjs ON sjs.schedule_id = ss.schedule_id
174INNER JOIN    msdb..sysjobs AS sj ON sj.job_id = sjs.job_id
175INNER JOIN    sys.sysservers AS srv ON srv.srvid = sj.originating_server_id
176INNER JOIN    msdb..syscategories AS sc ON sc.category_id = sj.category_id
177WHERE         ss.freq_type IN(1, 4, 8, 16, 32)
178ORDER BY      srv.srvname,
179              sj.name,
180              ss.name
181GO
182USE master
183GO
184PRINT ‘*******************************************************************************************’
185PRINT ‘*******************************************************************************************’
186PRINT ‘<List of SQL Server Agent – Alerts>’
187select * from  msdb.dbo.sysalerts 
188GO
189PRINT ‘*******************************************************************************************’
190PRINT ‘*******************************************************************************************’
191PRINT ‘<List of SQL Server Agent – Operators>’
192SELECT name, email_address, enabled FROM MSDB.dbo.sysoperators ORDER BY name
193GO
194PRINT ‘*******************************************************************************************’
195PRINT ‘*******************************************************************************************’
196PRINT ‘<List of SSIS packages in MSDB>’
197USE MSDB
198GO
199select name, description, createdate from sysssispackages where description not like ‘System Data Collector Package’
200USE master
201GO
202PRINT ‘*******************************************************************************************’
203PRINT ‘*******************************************************************************************’
204
205Tags Export SQL SP_CONFIGURE