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 '