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îtreALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"SELECT @@ SERVERNAMEALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"SELECT @@ VERSIONALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"ALLERSP_CONFIGURE 'afficher les options avancées', 1reconfigurer avec dérogationALLERsp_configureALLERSP_CONFIGURE 'afficher les options avancées', 0reconfigurer avec dérogationALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"SELECT nom comme Database_Name, dbid comme Database_ID, cmptlevel comme Database_Compatibility_Level, nom de fichier comme Database_MDF_Location de SYSDATABASESALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"ACTIVER LE NOCOUNTSI (id_objet ('tempdb .. # TMPFIXEDDRIVES') N'EST PAS NUL) DROP TABLE #TMPFIXEDDRIVESSI (id_objet ('tempdb .. # TMPSPACEUSED') N'EST PAS NUL) DROP TABLE #TMPSPACEUSEDIF (object_id ('tempdb .. # HDB') IS NOT NULL) DROP TABLE #HDBCRÉ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_InfoDE 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_dateFROM sys.databases DLEFT 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_idOERE 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_idWHERE 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_idLEFT JOIN msdb.sys.database_mirroring dm (nolock) sur A.database_id = dm.database_idCOMMANDER PAR Database_NameSI (id_objet ('tempdb .. # TMPFIXEDDRIVES') N'EST PAS NUL) DROP TABLE #TMPFIXEDDRIVESSI (id_objet ('tempdb .. # TMPSPACEUSED') N'EST PAS NUL) DROP TABLE #TMPSPACEUSEDIF (object_id ('tempdb .. # HDB') IS NOT NULL) DROP TABLE #HDBALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"EXEC sp_helploginsALLERIMPRESSION '************************************************ ******************************************* '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_dateFROM sys.database_principals prinLEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id = mem.member_principal_idOERE prin.sid N'EST PAS NUL et prin.sid PAS DANS (0x00) etprin.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_userFROM @DB_USers user1 GROUP BY dbname, username, logintype, create_date, modify_date ORDER BY DBName, usernameALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"sélectionnez 'CREATE CREDENTIAL' + name + 'WITH IDENTITY =' '' + credential_identity + '' ', SECRET =' '''; ' de sys.credentials classer par nom;ALLERIMPRESSION '************************************************ ******************************************* '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] ASCALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"sélectionnez * à partir de sys.endpoints ALLERIMPRESSION '************************************************ ******************************************* '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_idJOIN GAUCHE sys.server_principals ssp ON ssp.principal_id = sl.local_principal_idALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"SELECT SSM.definition FROM sys.server_triggers AS ST JOIN sys.server_sql_modules AS SSM ON ST.object_id = SSM.object_idALLERIMPRESSION '************************************************ ******************************************* '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_ownerDE sysextendedarticlesvoir l'artjointure 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é'ALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"UTILISER MSDBALLERSELECT 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.enabledFROM msdb..sysschedules AS ssINNER JOIN msdb..sysjobschedules AS sjs ON sjs.schedule_id = ss.schedule_idINNER JOIN msdb..sysjobs AS sj ON sj.job_id = sjs.job_idINNER JOIN sys.sysservers AS srv ON srv.srvid = sj.originating_server_idINNER JOIN msdb..syscategories AS sc ON sc.category_id = sj.category_idOERE ss.freq_type IN (1, 4, 8, 16, 32)COMMANDER PAR srv.srvname, sj.name, ss.nameALLERUTILISER le maîtreALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"sélectionnez * dans msdb.dbo.sysalerts ALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"SELECT nom, email_address, activé FROM MSDB.dbo.sysoperators ORDER BY nomALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* 'IMPRESSION '"UTILISER MSDBALLERsé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îtreALLERIMPRESSION '************************************************ ******************************************* 'IMPRESSION '************************************************ ******************************************* '

Click to rate this post! [Total: 0 Average: 0]