Script pour exporter la configuration de SQL Server – Solutions standard à des problèmes aléatoires – Serveur d’impression

Récemment, on m'a demandé d'écrire un script qui permettrait d'exporter (ou de sauvegarder) autant d'informations de configuration que possible à partir d'une instance SQL Server.

Ceci est pratique lors de la migration d'une instance SQL Server vers un nouvel environnement et sert de liste de contrôle.

Le résultat a été le script suivant, qui exporte:

– Le nom d'instance de SQL Server

– La version, la plateforme et la construction de l'instance SQL Server

– La configuration au niveau du serveur (SP_CONFIGURE)

– Informations sur les bases de données jointes et leurs fichiers de base de données

– Les connexions serveur

– Les autorisations des utilisateurs pour chaque base de données

– Informations sur les informations d'identification enregistrées

– Informations sur les périphériques de sauvegarde du serveur

– Informations sur les points de terminaison système et de mise en miroir

– Informations sur les serveurs liés

– Informations sur les déclencheurs de connexion

– Informations sur les publications et les abonnements

– Informations sur les travaux de l'Agent SQL Server

– Informations sur les alertes SQL Server

– Informations sur les opérateurs SQL Server

– Informations sur les packages SSIS stockés dans MSDB

Le script:

UTILISER le maître
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
SELECT @@ SERVERNAME
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
SELECT @@ VERSION
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
ALLER
SP_CONFIGURE 'afficher les options avancées', 1
reconfigurer avec dérogation
ALLER
sp_configure
ALLER
SP_CONFIGURE 'afficher les options avancées', 0
reconfigurer avec dérogation
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
SELECT nom comme Database_Name, dbid comme Database_ID, cmptlevel comme Database_Compatibility_Level, nom de fichier comme Database_MDF_Location de SYSDATABASES
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
ACTIVER LE NOCOUNT
SI (id_objet ('tempdb .. # TMPFIXEDDRIVES') N'EST PAS NUL) DROP TABLE #TMPFIXEDDRIVES
SI (id_objet ('tempdb .. # TMPSPACEUSED') N'EST PAS NUL) DROP TABLE #TMPSPACEUSED
IF (object_id ('tempdb .. # HDB') IS NOT NULL) DROP TABLE #HDB
CRÉER LA TABLE #TMPFIXEDDRIVES (DRIVE CHAR (1), MBFREE INT)
INSÉRER DANS #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES
CRÉER LA TABLE #TMPSPACEUSED (DBNAME VARCHAR (255), FILEID INT, FILENME VARCHAR (255), SPACEUSED FLOAT)
CREATE TABLE #HDB (nom sysname non null, db_size varchar (25) non null, owner varchar (40) non null, dbid int non null, créé smalldatetime non null, statut varchar (500) non null, compatibilité_level int non null)
INSÉRER DANS #HDB exec sp_helpdb;
INSÉRER DANS #TMPSPACEUSED
EXEC ('sp_msforeachdb''use [?]; Sélectionnez '' ''? '' '' DBName, fileid, Name FileNme, fileproperty (Name, '' '' SpaceUsed '' '') SpaceUsed from sysfiles '' ')
SELECT @@ servername comme SQLServerInstance, A.Database_id comme Database_ID, A.NAME AS Database_Name,
CASE 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,
CASE D.FILEID WHEN 1 THEN XX.created ELSE NULL END comme Database_Creation_Date, C.DRIVE, C.MBFREE AS ​​Free_Space_of_the_Disk, D.FILEID as Database_File_ID, B.NAME AS Database_Filename,
CAS 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) comme SPACEFREE_MB,
ROND (100 – ((((B.SIZE * 8/1024) – (D.SPACEUSED / 128)) * 100) / CASE (B.SIZE * 8/1024) LORSQUE 0 PUIS 1 AUTRE (B.SIZE * 8 / 1024) FIN), 2) comme [%USED], b. taille,
b.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],
CAS B.TYPE WHEN 0 THEN A.compatibility_level ELSE NULL END AS [Compatibility_Level] , CAS D.FILEID WHEN 1 THEN BR.last_backup_finish_date
ELSE NULL END as [Backup], CASE D.FILEID WHEN 1 THEN BR.last_TRLog_backup_finish_date ELSE NULL END as TRBackup, CASE D.FILEID
QUAND 1 ALORS BR.last_restore_date ELSE NULL END as [Restore], DM.mirroring_role_desc + '(' + DM.mirroring_state_desc + ')' comme DBMirror_Info
DE LA BASE DE DONNÉES SYS A
INNER JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID
INNER JOIN #TMPFIXEDDRIVES C ON LEFT (B.PHYSICAL_NAME, 1) = C.DRIVE
INNER JOIN #TMPSPACEUSED D SUR A.NAME = D.DBNAME ET B.NAME = D.FILENME
INNER JOIN #HDB XX sur XX.dbid = A.Database_id
INNER JOIN (SELECT D.database_id, B.last_backup_finish_date, TR.last_TRLog_backup_finish_date, R.last_restore_date
FROM sys.databases D
LEFT JOIN (SELECT BS.database_name, max (BS.backup_finish_date) as last_backup_finish_date FROM msdb.dbo.backupset BS (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily MF (NOLOCK) ON BS.media_set_id = MF.media_set_id
WHERE BS.backup_start_date> = CAST (CONVERT (varchar (10), dateadd (mm, -3, getdate ()), 120) AS datetime)
ET BS.nom_serveur = @@ nomserveur et BS.type = 'D'
GROUP BY BS.database_name) B sur D.name = B.database_name LEFT JOIN (SELECT BS.database_name, max (BS.backup_finish_date) as last_TRLog_backup_finish_date
FROM msdb.dbo.backupset BS (NOLOCK) INNER JOIN msdb.dbo.backupmediafamily MF (NOLOCK) ON BS.media_set_id = MF.media_set_id
OERE BS.backup_start_date> = CAST (CONVERT (varchar (10), dateadd (mm, -1, getdate ()), 120) AS datetime) AND BS.server_name = @@ servername et BS.type = 'L'
GROUP BY BS.database_name) TR sur D.name = TR.database_name
LEFT JOIN (SELECT rh.destination_database_name, max (rh.restore_date) as last_restore_date FROM msdb.dbo.restorehistory rh (NOLOCK)
INNER JOIN msdb.dbo.backupset BS (NOLOCK) ON rh.backup_set_id = BS.backup_set_id
WHERE BS.type = 'D' AND RH.restore_date> = CAST (CONVERT (varchar (10), dateadd (mm, -3, getdate ()), 120) AS datetime)
GROUP BY rh.destination_database_name) R sur D.name = R.destination_database_name) BR sur A.Database_id = BR.database_id
LEFT JOIN msdb.sys.database_mirroring dm (nolock) sur A.database_id = dm.database_id
COMMANDER PAR Database_Name
SI (id_objet ('tempdb .. # TMPFIXEDDRIVES') N'EST PAS NUL) DROP TABLE #TMPFIXEDDRIVES
SI (id_objet ('tempdb .. # TMPSPACEUSED') N'EST PAS NUL) DROP TABLE #TMPSPACEUSED
IF (object_id ('tempdb .. # HDB') IS NOT NULL) DROP TABLE #HDB
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
EXEC sp_helplogins
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
DECLARE @DB_USers TABLE (DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar (max), create_date datetime, modify_date datetime)
INSERT @DB_USers EXEC sp_MSforeachdb '
utilisation [?]
SELECT ''? '' AS DB_Name,
case prin.name lorsque '' dbo '' puis prin.name + '' ('' + (sélectionnez SUSER_SNAME (owner_sid) dans master.sys.databases où name = ''? '') + '') '' else prin .name end AS UserName,
prin.type_desc AS LoginType,
isnull (USER_NAME (mem.role_principal_id), '' '') AS AssociatedRole, create_date, modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id = mem.member_principal_id
OERE prin.sid N'EST PAS NUL et prin.sid PAS DANS (0x00) et
prin.is_fixed_role <> 1 AND prin.name NOT LIKE '' ##% '' '
SELECT dbname, username, logintype, create_date, modify_date, STUFF ((SELECT ',' + CONVERT (VARCHAR (500), associatedrole)
FROM @DB_USers user2 WHERE user1.DBName = user2.DBName AND user1.UserName = user2.UserName FOR XML PATH ('')), 1,1, '') AS Permissions_user
FROM @DB_USers user1 GROUP BY dbname, username, logintype, create_date, modify_date ORDER BY DBName, username
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '
sélectionnez 'CREATE CREDENTIAL' + name + 'WITH IDENTITY =' '' + credential_identity + '' ', SECRET =' '''; ' de sys.credentials classer par nom;
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
SELECT 'Server[@Name=’ + quotename(CAST(serverproperty(N’Servername’) AS sysname), » ») + ‘]'+' / BackupDevice[@Name=’ + quotename(o.name, » ») + ‘]«AS [Urn],
o.name AS [Name], cas où 1 = msdb.dbo.fn_syspolicy_is_automation_enabled () et existe (sélectionnez * dans msdb.dbo.syspolicy_system_health_state
où target_query_expression_with_id comme 'Server / BackupDevice [@Name=’ + QUOTENAME(o.name,  » ») + ‘]% 'ESCAPE' ') puis 1 sinon 0 fin AS [PolicyHealthState]
DE sys.backup_devices o COMMANDER PAR [Name] ASC
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
sélectionnez * à partir de sys.endpoints
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
SELECT ss.server_id, ss.name, 'Server' = Case ss.Server_id lorsque 0 puis 'Current Server' sinon 'Remote Server' end
, ss.product, ss.provider, ss.catalog, 'Local Login' = case sl.uses_self_credential when 1 then 'Uses Self Credentials'
else ssp.name end, 'Remote Login Name' = sl.remote_name, 'RPC Out Enabled' = case ss.is_rpc_out_enabled lorsque 1 puis 'True'
else 'False' end, 'Data Access Enabled' = case ss.is_data_access_enabled when 1 then 'True' else 'False' end
, ss.modify_date FROM sys.Servers ss
JOINT GAUCHE sys.linked_logins sl ON ss.server_id = sl.server_id
JOIN GAUCHE sys.server_principals ssp ON ssp.principal_id = sl.local_principal_id
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '
SELECT SSM.definition FROM sys.server_triggers AS ST JOIN sys.server_sql_modules AS SSM ON ST.object_id = SSM.object_id
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
SI EXISTE (CHOISIR 1
FROM INFORMATION_SCHEMA.TABLES
OERE TABLE_TYPE = 'TABLE DE BASE'
AND TABLE_NAME = 'sysextendedarticlesview')
(SELECT sub.srvname, pub.name, art.name, art.dest_table, art.dest_owner
DE sysextendedarticlesvoir l'art
jointure interne syspublications pub on (art.pubid = pub.pubid)
jointure interne syssubscriptions sub on (sub.artid = art.artid))
ELSE SELECT 'Aucun article de publication ou de souscription n'a été trouvé'
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
UTILISER MSDB
ALLER
SELECT srv.srvname,
sj.name,
COALESCE (sj.description, ''),
ss.name,
ss.schedule_id,
sc.name,
ss.freq_type,
ss.freq_interval,
ss.freq_subday_type,
ss.freq_subday_interval,
ss.freq_relative_interval,
ss.freq_recurrence_factor,
COALESCE (STR (ss.active_start_date, 8), CONVERT (CHAR (8), GETDATE (), 112)),
STUFF (STUFF (REPLACE (STR (ss.active_start_time, 6), '', '0'), 3, 0, ':'), 6, 0, ':'),
STR (ss.active_end_date, 8),
STUFF (STUFF (REPLACE (STR (ss.active_end_time, 6), '', '0'), 3, 0, ':'), 6, 0, ':'),
sj.enabled,
ss.enabled
FROM msdb..sysschedules AS ss
INNER JOIN msdb..sysjobschedules AS sjs ON sjs.schedule_id = ss.schedule_id
INNER JOIN msdb..sysjobs AS sj ON sj.job_id = sjs.job_id
INNER JOIN sys.sysservers AS srv ON srv.srvid = sj.originating_server_id
INNER JOIN msdb..syscategories AS sc ON sc.category_id = sj.category_id
OERE ss.freq_type IN (1, 4, 8, 16, 32)
COMMANDER PAR srv.srvname,
sj.name,
ss.name
ALLER
UTILISER le maître
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
sélectionnez * dans msdb.dbo.sysalerts
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
SELECT nom, email_address, activé FROM MSDB.dbo.sysoperators ORDER BY nom
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '"
UTILISER MSDB
ALLER
sélectionner le nom, la description, créer à partir de sysssispackages où la description n'est pas comme «Package de collecteur de données système»
UTILISER le maître
ALLER
IMPRESSION '************************************************ ******************************************* '
IMPRESSION '************************************************ ******************************************* '

Script pour exporter la configuration de SQL Server – Solutions standard à des problèmes aléatoires – Serveur d’impression
4.9 (98%) 32 votes