![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>Troisi\u00e8me article de la s\u00e9rie Une approche pragmatique de la production SQL Server<\/a>, il est question ici de la gestion des sauvegardes du parc. L\u2019id\u00e9e est de trouver une politique applicable \u00e0 toutes les instances du parc SQL Server.<\/p>\n Quel type de sauvegardes ?<\/em><\/strong> Quelles options pour la sauvegarde ?<\/em><\/strong> Pour une utilisation simplifi\u00e9e, cr\u00e9ez un unique script (ou proc\u00e9dure stock\u00e9e) pour g\u00e9rer les diff\u00e9rents types de sauvegarde d\u2019une base. Ce script doit permettre la sauvegarde compl\u00e8te, diff\u00e9rentielle et de journaux, et g\u00e9rer les cas de bases non disponibles et de bases fra\u00eechement cr\u00e9\u00e9es (sur lesquelles une sauvegarde diff\u00e9rentielle ou de journaux est impossible tant qu\u2019une sauvegarde compl\u00e8te n\u2019a pas \u00e9t\u00e9 effectu\u00e9e). Id\u00e9alement, le script proposera la sauvegarde de type \u201cCOPY_ONLY\u201d, tr\u00e8s pratique lorsqu\u2019il s\u2019agit de recopier les bases d\u2019une instance sur une autre instance.<\/p>\n Voici un exemple de proc\u00e9dure stock\u00e9e de sauvegarde qui prend en charge ces fonctionnalit\u00e9s:<\/p>\n Les sauvegardes devront \u00eatre externalis\u00e9es au plus t\u00f4t, soit en sauvegardant directement sur un r\u00e9pertoire distant, soit en mettant en place une mise sur bande ou un d\u00e9placement vers une machine de centralisation.<\/p>\n Troisi\u00e8me article de la s\u00e9rie Une approche pragmatique de la production SQL Server, il est question ici de la gestion des sauvegardes du parc. Petit rappel, en plus des bases utilisateurs, toutes vos bases syst\u00e8mes doivent \u00eatre sauvegard\u00e9es: Pourquoi il… Continuer la lecture
\nPetit rappel, en plus des bases utilisateurs, toutes vos bases syst\u00e8mes doivent \u00eatre sauvegard\u00e9es:
\nPourquoi il faut sauvegarder les bases syst\u00e8me<\/a><\/p>\n
\nLe parc pouvant \u00eatre compos\u00e9 de bases \u00e0 vocations diverses, il est n\u00e9cessaire de couvrir les activit\u00e9s principales: forte activit\u00e9 transactionnelle, forte volum\u00e9trie, bases d\u2019archive, bases en miroir.
\nD\u2019exp\u00e9rience, une politique de type \u201cSauvegarde compl\u00e8te hebdomadaire + Sauvegarde diff\u00e9rentielle quotidienne + Sauvegarde de journaux de transactions toutes les demi heures\u201d convient en g\u00e9n\u00e9ral tr\u00e8s bien.
\nL\u2019emplacement et le nom des fichiers de sauvegarde suivront id\u00e9alement la r\u00e8gle \u00e9tablie (cf Article 1). La politique de nommage <nom de la base>_<type de sauvegarde>_<date>_<heure>.BAK\u00a0permet d\u2019identifier facilement un fichier de sauvegarde.<\/p>\n
\nDepuis la version 2008 R2, la compression de sauvegardes est disponible pour les \u00e9ditions Enterprise et Standard. Vous gagnerez du temps et de l\u2019espace disque en utilisant cette option, elle est donc fortement recommand\u00e9e.
\nProfitez de la sauvegarde pour v\u00e9rifier l\u2019int\u00e9grit\u00e9 de vos pages. C\u2019est automatiquement le cas lors des sauvegardes compress\u00e9es.
\nPr\u00e9f\u00e9rez l\u2019utilisation des fichiers uniques aux Media Sets (qui permettent de stocker plusieurs sauvegardes dans un m\u00eame fichier), ces derniers peuvent \u00eatre probl\u00e9matiques lors du passage sur bande des fichiers (le mediaset entier sera pass\u00e9 sur bande int\u00e9gralement d\u00e8s qu’une sauvegarde sera ajout\u00e9e).<\/p>\nEXEC sp_configure 'show advanced options',1\r\nreconfigure\r\n\r\nEXEC sp_configure 'xp_cmdshell',1\r\nreconfigure\r\nGO\r\n\r\nIF OBJECT_ID('sauvegarde_base') IS NOT NULL\r\nDROP PROCEDURE sauvegarde_base\r\nGO\r\n\r\nCREATE PROCEDURE sauvegarde_base (\r\n@nom_base sysname,\r\n@type_sauvegarde varchar(50) = 'COMPLET',\r\n@repertoire_destination varchar(500) = 'DEFAULT'\r\n)\r\nAS\r\n\/***************************************************************************************\r\nB. Vesan, Cap Data Consulting, F\u00e9vrier 2012\r\nCette procedure permet de sauvegarder une base de donn\u00e9es ou son journal transactionnel.\r\n@type_sauvegarde (type de la sauvegarde ) peut valoir:\r\nCOMPLET pour une sauvegarde compl\u00e8te, option par d\u00e9faut\r\nCOPY_ONLY pour une sauvegarde compl\u00e8te sans interrompre la s\u00e9quence des sauvegardes\r\nDIFFERENTIEL pour une sauvegarde diff\u00e9rentielle\r\nLOG pour une sauvegarde du journal de transactions.\r\n@repertoire_destination contient le chemin accueillant la sauvegarde.\r\nun r\u00e9pertoire est cr\u00e9\u00e9 dans le chemin sp\u00e9cifi\u00e9\r\nLorsque sa valeur n'est pas pr\u00e9cis\u00e9e, la sauvegarde s'effectuera sur l'emplacement sp\u00e9cifi\u00e9 dans\r\nla table \"parametre\", dont la d\u00e9finition peut \u00eatre trouv\u00e9e ici:\r\nhttp:\/\/blog.capdata.fr\/index.php\/table_parametre\r\nLe format des fichiers est:\r\n<nom de la base>_<type de sauvegarde>_<date>_<heure>.BAK\r\nex: RMLBD_COMPLET_20120201_131738.BAK\r\nLa proc\u00e9dure retournera 0 en cas de succ\u00e8s, 1 en cas d'\u00e9chec.\r\n***************************************************************************************\/\r\nBEGIN\r\nSET NOCOUNT ON\r\nDECLARE @cmd varchar(4000),@prefixe_fichier varchar(100),@destination varchar(4000),\/*@compteur int,*\/@code_retour int,@msg varchar(1000),@fichier_source varchar(500)\r\nCREATE TABLE #TABLE_OUTPUT(ligne varchar(500))\r\nCREATE TABLE #TABLE_SOURCE (source varchar(500),cpt int)\r\nCREATE TABLE #TABLE_DESTINATION (destination varchar(500),cpt int)\r\n\r\nIF upper(@type_sauvegarde) NOT IN ('COMPLET','DIFFERENTIEL','COPY_ONLY','LOG')\r\nBEGIN\r\nSET @msg = 'Echec de l'' ex\u00e9cution de sauvegarde_base : le type de sauvegarde '+ISNULL(@type_sauvegarde,'(valeur nulle)')+' n''est pas reconnu'\r\nRAISERROR (@msg, 16, 1) WITH NOWAIT,LOG\r\nreturn 1\r\nEND\r\n\r\nIF DB_ID(@nom_base) IS NULL\r\nBEGIN\r\nSET @msg = 'Echec de l'' ex\u00e9cution de sauvegarde_base : La base '+ISNULL(@nom_base,'(valeur nulle)')+' n''existe pas'\r\nRAISERROR (@msg, 16, 1) WITH NOWAIT,LOG\r\nreturn 1\r\nEND\r\n\r\nIF ( DATABASEPROPERTYEX(@nom_base,'Status ')!='ONLINE' OR EXISTS (select 1 FROM sys.databases WHERE name=@nom_base AND source_database_id IS NOT NULL))\r\nBEGIN\r\nSET @msg = 'La base '+@nom_base+' n''est pas disponible ou il s''agit d''un snapshot'\r\nPRINT @msg\r\nreturn 0\r\nEND\r\n\r\nIF @repertoire_destination = 'DEFAULT'\r\nBEGIN\r\nIF EXISTS (SELECT * FROM outils_dba.sys.tables where name='parametres')\r\nBEGIN\r\nSELECT @repertoire_destination = CAST( valeur AS varchar(500)) FROM dbo.parametres WHERE nom_parametre='repertoire_sauvegarde' AND cible=@nom_base\r\nIF @repertoire_destination = 'DEFAULT' SELECT @repertoire_destination = CAST( valeur AS varchar(500)) FROM dbo.parametres WHERE nom_parametre='repertoire_sauvegarde' AND cible IS NULL\r\nEND\r\nELSE\r\nBEGIN\r\nSET @msg = 'Echec de l'' ex\u00e9cution de sauvegarde_base : Aucune destination n''est d\u00e9finie pour la base '+ @nom_base\r\nRAISERROR (@msg, 16, 1) WITH NOWAIT,LOG\r\nreturn 1\r\nEND\r\nEND\r\n\r\n----------- Si aucune sauvegarde compl\u00e8te n'existe pour la base et que la sauvegarde demand\u00e9e est une Diff ou TLog, il faut demander une sauvegarde compl\u00e8te:\r\n\r\nIF ((upper(@type_sauvegarde) NOT IN ('COMPLET','COPY_ONLY')) AND NOT EXISTS (select 1 from msdb.dbo.backupset where database_name=@nom_base and type='D' and is_copy_only = 0))\r\n\r\nEXEC sauvegarde_base @nom_base, 'COMPLET', @repertoire_destination\r\n\r\n----------- G\u00e9r\u00e9ration de la cha\u00eene de caract\u00e8res correspondant \u00e0 la destination\r\nset @prefixe_fichier = REPLACE(@nom_base,' ','_') + '_' + upper(@type_sauvegarde) + '_'+ convert(varchar(8),getdate(),112) + '_' + RIGHT('0'+CONVERT(varchar(2),datepart(hh,getdate())),2)+ RIGHT('0'+CONVERT(varchar(2),datepart(mi,getdate())),2)+RIGHT('0'+CONVERT(varchar(2),datepart(ss,getdate())),2)\r\nset @cmd = 'mkdir \"'+@repertoire_destination + '\\'+REPLACE(@nom_base,' ','_')+'\"'\r\n\r\n-- Le r\u00e9pertoire est cr\u00e9\u00e9 \u00e0 la vol\u00e9e\r\nTRUNCATE TABLE #TABLE_OUTPUT\r\nINSERT INTO #TABLE_OUTPUT\r\nEXECUTE @code_retour=master.dbo.xp_cmdshell @cmd\r\n\r\n-- Si le r\u00e9pertoire ne peut \u00eatre cr\u00e9\u00e9 pour une cause autre que le fait qu'il existe d\u00e9j\u00e0, on sort en erreur\r\nIF (@code_retour !=0 AND NOT EXISTS (select * from #TABLE_OUTPUT WHERE ligne like '%already exists%' OR ligne like '%existe d%'))\r\nBEGIN\r\nSELECT @msg = 'Echec de l'' ex\u00e9cution de sauvegarde_base lors de l''appel \u00e0 '+ISNULL(@cmd,'?')+':'+ligne FROM #TABLE_OUTPUT WHERE ligne IS NOT NULL\r\nRAISERROR (@msg, 16, 1) WITH NOWAIT,LOG\r\nreturn 1\r\nEND\r\nSET @destination = 'DISK='''+LTRIM(RTRIM(@repertoire_destination)) + '\\'+REPLACE(@nom_base,' ','_')+'\\'+@prefixe_fichier+'.BAK'''\r\n\r\n-- G\u00e9n\u00e9ration de la commande BACKUP DATABASE ou BACKUP LOG en fonction du type de sauvegarde demand\u00e9\r\nIF (upper(@type_sauvegarde)='LOG')\r\nBEGIN\r\nIF databasepropertyex(@nom_base,'Recovery')='SIMPLE'\r\nBEGIN\r\nSET @msg = 'Echec de l'' ex\u00e9cution de sauvegarde_base : La base '+ISNULL(@nom_base,'(valeur nulle)')+' est en mode de recouvrement SIMPLE, les sauvegardes de LOG sont donc impossibles'\r\nRAISERROR (@msg, 16, 1) WITH NOWAIT,LOG\r\nreturn 1\r\nEND\r\nSET @cmd = 'BACKUP LOG ['+@nom_base+'] TO '+@destination +' WITH INIT'\r\nEND\r\nELSE SET @cmd = 'BACKUP DATABASE ['+@nom_base+'] TO '+@destination +' WITH INIT'\r\nIF (upper(@type_sauvegarde)='COPY_ONLY')\r\nSET @cmd = @cmd + ', COPY_ONLY'\r\n\r\nIF (upper(@type_sauvegarde)='DIFFERENTIEL')\r\nSET @cmd = @cmd + ', DIFFERENTIAL'\r\n\r\nPRINT @cmd\r\nEXECUTE(@cmd)\r\n\r\nIF NOT EXISTS (select 1 from msdb.dbo.backupmediafamily where physical_device_name like '%'+@prefixe_fichier+'%')\r\nBEGIN\r\nSET @msg='Echec de l'' ex\u00e9cution de sauvegarde_base lors de l''appel \u00e0 '+ISNULL(@cmd,'?') +'. Consultez le journal d''erreurs pour plus d''informations'\r\nRAISERROR (@msg, 16, 1) WITH NOWAIT,LOG\r\nreturn 1\r\nEND\r\nEND\r\nGO<\/pre>\n<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"