{"id":2474,"date":"2011-07-03T15:20:26","date_gmt":"2011-07-03T14:20:26","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=2474"},"modified":"2019-09-13T14:38:58","modified_gmt":"2019-09-13T13:38:58","slug":"pourquoi-il-faut-sauvegarder-les-bases-systemes","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/","title":{"rendered":"Pourquoi il faut sauvegarder les bases syst\u00e8mes"},"content":{"rendered":"<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F2474&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F2474&#038;title=Pourquoi%20il%20faut%20sauvegarder%20les%20bases%20syst%C3%A8mes\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Pourquoi%20il%20faut%20sauvegarder%20les%20bases%20syst%C3%A8mes&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F2474\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a><p>On peut presque dire que sur 90% des instances que l&#8217;on audite, les bases syst\u00e8mes ne sont JAMAIS sauvegard\u00e9es.<\/p>\n<p>Souvent c&#8217;est un aspect de l&#8217;administration qui est n\u00e9glig\u00e9: on effectue des sauvegardes des bases utilisateurs, mais les bases syst\u00e8mes ne contiennent pas de donn\u00e9es m\u00e9tier donc elles ne font pas partie\u00a0 du plan de maintenance. C&#8217;est une erreur qui peut se payer tr\u00e8s cher lorsque la base syst\u00e8me devient inutilisable.<\/p>\n<p>Dans cet article, nous allons voir comment se sortir de deux cas de corruption de la base master, qui est la cl\u00e9 de vo\u00fbte de SQL Server:<\/p>\n<ul>\n<li>Lors du d\u00e9marrage, le fichier master.mdf n&#8217;est pas lisible.<\/li>\n<li>Lors du d\u00e9marrage, le fichier master.mdf est lisible mais le recovery sur la base master \u00e9choue.<\/li>\n<\/ul>\n<p>Dans les deux cas, la cons\u00e9quence est la m\u00eame: SQL Server ne peut pas d\u00e9marrer.<\/p>\n<h2>Bases syst\u00e8me et r\u00f4les:<\/h2>\n<p>Les bases syst\u00e8mes sont au nombre de 4 (on ne parle ni de distribution qui est li\u00e9e \u00e0 la r\u00e9plication ni de resource qui n&#8217;est pas visible par d\u00e9faut).<\/p>\n<ul>\n<li><strong>master:<\/strong> elle contient tous les \u00e9l\u00e9ments de configuration et de fonctionnement de l&#8217;instance: logins, liste des bases, options de configuration, liste des fichiers, des serveurs li\u00e9s, endpoints, etc&#8230; Sans base master, pas de SQL Server. Donc il faut la sauvegarder.<\/li>\n<li><strong>msdb: <\/strong>elle contient toutes les informations n\u00e9cessaires pour que l&#8217;agent puisse fonctionner: jobs, alertes, op\u00e9rateurs, schedules, avec en plus de nombreuses tables d&#8217;historique (backupset, backupfile, restorefile, suspect_pages, etc&#8230;). Sans base msdb, pas d&#8217;agent SQL, pas d&#8217;informations statistiques. Donc il faut la sauvegarder.<\/li>\n<li><strong>model<\/strong>: sert de d\u00e9finition pour toute nouvelle cr\u00e9ation de base. N\u00e9cessaire lors de la s\u00e9quence de boot de l&#8217;instance pour permettre la cr\u00e9ation de tempdb. Beaucoup diront qu&#8217;il n&#8217;est pas\u00a0 n\u00e9cessaire de sauvegarder la base model, et l&#8217;argument est discutable: sans base model, pas de base tempdb au d\u00e9marrage et donc pas de SQL Server. Sans sauvegarde, il faudrait retrouver une base depuis une instance en m\u00eame version avec la m\u00eame collation, alors que SQL Server met moins d&#8217;une seconde \u00e0 sauvegarder cette base de 2Mb. Est-ce que \u00e7a ne vaut vraiment pas le coup de l&#8217;inclure dans le plan de maintenance ?<\/li>\n<li><strong>tempdb<\/strong>: base servant \u00e0 stocker temporairement le r\u00e9sultat d&#8217;agr\u00e9gats (tris, regroupements, etc&#8230;), le version store, les facts de DBCC CHECKDB, parmi d&#8217;autres choses&#8230; Cette base est recr\u00e9\u00e9e de z\u00e9ro \u00e0 chaque red\u00e9marrage. Bien que l&#8217;issue de sa cr\u00e9ation soit critique pour la suite de la s\u00e9quence de d\u00e9marrage, les donn\u00e9es qui s&#8217;y trouvent ne le sont pas. De la sorte, il n&#8217;est pas n\u00e9cessaire de la sauvegarder.<\/li>\n<\/ul>\n<h2>S\u00e9quence de d\u00e9marrage de SQL Server:<\/h2>\n<p>Pour bien comprendre quoi faire lorsque la base master est endommag\u00e9e, il faut d&#8217;abord d\u00e9tailler la s\u00e9quence de d\u00e9marrage de SQL Server. En rouge les phases importantes pour ce qui nous int\u00e9resse dans cet article:<\/p>\n<p><strong>Etape 1<\/strong>:<\/p>\n<pre><span style=\"color: #0000ff;\"><span style=\"color: #999999;\">Server\u00a0\u00a0 \u00a0Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)\u00a0\u00a0\u00a0 Nov 24 2008 13:01:59 ... Server\u00a0\u00a0 \u00a0(c) 2005 Microsoft Corporation. Server\u00a0\u00a0 \u00a0All rights reserved. Server\u00a0\u00a0 \u00a0Server process ID is 168048. Server\u00a0\u00a0 \u00a0Authentication mode is MIXED. Server\u00a0\u00a0 \u00a0Logging SQL Server messages in file 'C:\\UTRECHT\\MSSQL.1\\MSSQL\\LOG\\ERRORLOG'. Server\u00a0\u00a0 \u00a0This instance of SQL Server last reported using a process ID of 3332 at 21\/06\/2011 ... Server\u00a0\u00a0 \u00a0Registry startup parameters:<\/span> <span style=\"color: #ff0000;\">S<\/span><span style=\"color: #ff0000;\">erver\u00a0\u00a0 \u00a0\u00a0 -d C:\\UTRECHT\\MSSQL.1\\MSSQL\\DATA\\master.mdf Server\u00a0\u00a0 \u00a0\u00a0 -e C:\\UTRECHT\\MSSQL.1\\MSSQL\\LOG\\ERRORLOG Server\u00a0\u00a0 \u00a0\u00a0 -l C:\\UTRECHT\\MSSQL.1\\MSSQL\\DATA\\mastlog.ldf<\/span> <\/span><span style=\"color: #0000ff;\">\u00a0<\/span><\/pre>\n<p>SQL Server va lire ses informations de d\u00e9marrage dans la base de registre (sous HKLM\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL.1\\MSSQLServer\\Parameters):<br \/>\n&#8211; Le fichier de donn\u00e9es de la base master (-d)<br \/>\n&#8211; Le journal de transactions de la base master (-l)<br \/>\n&#8211; Le fichier ERRORLOG (-e)<br \/>\n&#8211; \u00c9ventuellement une liste de traceflags (-T, non indiqu\u00e9 dans cet exemple)<\/p>\n<p><strong>Etape 2:<\/strong><\/p>\n<pre><span style=\"color: #0000ff;\"><span style=\"color: #999999;\">Serveur\u00a0\u00a0 \u00a0SQL Server is starting at normal priority base (=7)... Serveur\u00a0\u00a0 \u00a0Detected 2 CPUs. This is an informational message; no user action is required. Serveur\u00a0\u00a0 \u00a0Using dynamic lock allocation.\u00a0 Initial allocation of 2500 Lock blocks and 5000 ... Serveur\u00a0\u00a0 \u00a0Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC) ... Serveur\u00a0\u00a0 \u00a0The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be ... Serveur\u00a0\u00a0 \u00a0Database mirroring has been enabled on this instance of SQL Server.<\/span> <span style=\"color: #ff0000;\">spid4s\u00a0\u00a0 \u00a0Starting up database 'master'. spid4s\u00a0\u00a0 \u00a06 transactions rolled forward in database 'master' (1) ... spid4s\u00a0\u00a0 \u00a00 transactions rolled back in database 'master' (1)... spid4s\u00a0\u00a0 \u00a0Recovery is writing a checkpoint in database 'master' (1) ...<\/span> <\/span><\/pre>\n<p><strong> en 3 sous \u00e9tapes<\/strong>:<br \/>\n&#8211; 2.1 SQL Server ouvre le fichier master.mdf et le journal de transactions.<br \/>\n&#8211; 2.2: il ex\u00e9cute un recovery sur la base master.<br \/>\n&#8211; 2.3: master est mise en ligne \u00e0 la fin du recovery.<\/p>\n<p>On note qu&#8217;\u00e0 partir de ce point le thread principal forke un premier fils (spid4s). Le suffixe &#8216;<em>s<\/em>&#8216; appos\u00e9 au SPID indique qu&#8217;il s&#8217;agit d&#8217;une session syst\u00e8me. Une fois la base master en ligne, cette session va demeurer pendant toute la dur\u00e9e d&#8217;ex\u00e9cution de SQL Server sous la d\u00e9nomination &#8216;SIGNAL HANDLER&#8217;. C&#8217;est ce thread qui sera charg\u00e9 d&#8217;intercepter ensuite une demande STOP envoy\u00e9e par le Gestionnaire de Services.<\/p>\n<p><strong>Etape 3:<\/strong><\/p>\n<pre><span style=\"color: #0000ff;\"><span style=\"color: #999999;\">spid4s\u00a0\u00a0 \u00a0SQL Trace ID 1 was started by login \"sa\".<\/span> <span style=\"color: #888888;\">spid4s\u00a0\u00a0 \u00a0Starting up database 'mssqlsystemresource'. spid4s\u00a0\u00a0 \u00a0The resource database build version is 9.00.4035 ...<\/span> <span style=\"color: #999999;\">spid4s\u00a0\u00a0 \u00a0Server name is 'UTRECHT'...<\/span> <span style=\"color: #ff0000;\">spid9s\u00a0\u00a0 \u00a0Starting up database 'model'. spid9s\u00a0\u00a0 \u00a0Clearing tempdb database. spid9s\u00a0\u00a0 \u00a0Starting up database 'tempdb'. <\/span><\/span><\/pre>\n<p><strong> <\/strong>SQL Server ouvre la base model, effectue un recovery et cr\u00e9\u00e9 la base tempdb. Ces actions sont effectu\u00e9es par une session syst\u00e8me diff\u00e9rente (spid9s).<\/p>\n<p><strong>Etape 4:<\/strong><\/p>\n<pre><span style=\"color: #0000ff;\"><span style=\"color: #999999;\">Serveur\u00a0\u00a0 \u00a0A self-generated certificate was successfully loaded for encryption. Serveur\u00a0\u00a0 \u00a0Server is listening on [ 'any' &lt;ipv6&gt; 1433]. Serveur\u00a0\u00a0 \u00a0Server is listening on [ 'any' &lt;ipv4&gt; 1433]. Serveur\u00a0\u00a0 \u00a0Server local connection provider is ready to accept connection on ... Serveur\u00a0\u00a0 \u00a0Server named pipe provider is ready to accept connection on ... Serveur\u00a0\u00a0 \u00a0Server is listening on [ ::1 &lt;ipv6&gt; 1434]. Serveur\u00a0\u00a0 \u00a0Server is listening on [ 127.0.0.1 &lt;ipv4&gt; 1434]. Serveur\u00a0\u00a0 \u00a0Dedicated admin connection support was established for listening locally on port ... Serveur\u00a0\u00a0 \u00a0SQL Server is now ready for client connections... spid12s\u00a0\u00a0 \u00a0The Service Broker protocol transport is disabled or not configured. spid12s\u00a0\u00a0 \u00a0The Database Mirroring protocol transport is disabled or not configured. spid12s\u00a0\u00a0 \u00a0Service Broker manager has started.<\/span> <span style=\"color: #ff0000;\">spid16s\u00a0\u00a0 \u00a0Starting up database 'msdb'. spid17s\u00a0\u00a0 \u00a0Starting up database 'CAPDATA'. spid18s\u00a0\u00a0 \u00a0Starting up database 'FoodMart2005'. spid19s\u00a0\u00a0 \u00a0Starting up database 'demorecovery'. spid20s\u00a0\u00a0 \u00a0Starting up database 'distribution'. <\/span><\/span><\/pre>\n<p>Enfin derni\u00e8re \u00e9tape, les bases utilisateurs sont ouvertes en parall\u00e8le et un recovery est ex\u00e9cut\u00e9 sur chacune d&#8217;entre elles. Noter les diff\u00e9rents threads syst\u00e8me utilis\u00e9s pour acc\u00e9l\u00e9rer la mise en ligne.<\/p>\n<p>Il faut savoir que ces \u00e9tapes de d\u00e9marrage sont d\u00e9brayables. On peut indiquer \u00e0 SQL Server de s&#8217;arr\u00eater :<br \/>\n&#8211; Soit juste apr\u00e8s l&#8217;ouverture du fichier master.mdf mais AVANT le d\u00e9but du recovery de master entre les \u00e9tapes 2.1 et 2.2 (trace flag -T3607)<br \/>\n&#8211; Soit juste apr\u00e8s le recovery de master, entre les \u00e9tape 2.3 et 3\u00a0 (trace flag -T 3608).<\/p>\n<p>&nbsp;<\/p>\n<h2>Corruption de la base master, quoi faire:<\/h2>\n<h3>Cas n\u00b01: le fichier master.mdf est illisible:<\/h3>\n<p>Lorsqu&#8217;un probl\u00e8me survient entre les \u00e9tapes 2.1 et 2.2, le fichier master.mdf est illisible et ne peut pas \u00eatre ouvert. Le recovery ne peut pas d\u00e9buter et l&#8217;instance s&#8217;arr\u00eate:<\/p>\n<pre><span style=\"color: #0000ff;\">2011-07-03 15:25:50.27 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'master'. <span style=\"color: #ff0000;\">2011-07-03 15:25:50.30 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 824, Severity: 24, State: 6. 2011-07-03 15:25:50.30 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Server detected a logical consistency-based I\/O error: restore pending. It occurred during a read of page (1:0) in database ID 1 at offset 0000000000000000 in file 'E:\\MSSQL10.SQL1\\MSSQL\\DATA\\master_corrupted.mdf'.<\/span>\u00a0 Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. 2011-07-03 15:25:50.33 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 5173, Severity: 16, State: 1. 2011-07-03 15:25:50.33 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.\u00a0<span style=\"color: #ff0000;\"> If this is an existing database, the file may be corrupted and should be restored from a backup.<\/span><\/span><\/pre>\n<p>Dans ce cas, c&#8217;est l&#8217;enveloppe du fichier master.mdf qui est en cause, et on ne peut pas mettre de point d&#8217;arr\u00eat avant l&#8217;\u00e9tape 2.1, donc on est oblig\u00e9 de recr\u00e9er une enveloppe saine pour la base master, que l&#8217;on ait un backup ou non.<\/p>\n<p>En version 2005, on \u00e9tait oblig\u00e9 de repartir du DVD d&#8217;installation et relancer un <strong><span style=\"color: #0000ff;\">setup \/wait REINSTALL=SQL_Engine REBUILDDATABASE<span style=\"color: #0000ff;\">=<\/span><\/span><span style=\"color: #0000ff;\">1 <\/span><\/strong>pour reconstruire toutes les bases syst\u00e8me. Donc au passage, si on n&#8217;avait pas non plus de sauvegarde de msdb, adieu jobs, op\u00e9rateurs, alertes&#8230;<\/p>\n<p>Depuis la version 2008, fort heureusement, un mini-setup et des templates des bases syst\u00e8mes se trouvent dans le r\u00e9pertoire de base de SQL Server, et on n&#8217;a plus besoin du DVD d&#8217;installation:<\/p>\n<ul>\n<li>Le setup embarqu\u00e9: c:\\Program Files\\Microsoft SQL Server\\100\\Setup Bootstrap\\Release\\Setup.exe<\/li>\n<li>Les templates des bases syst\u00e8mes: sous le r\u00e9pertoire des binaires de l&#8217;instance, sous Binn\\Templates.<\/li>\n<\/ul>\n<p>On relance donc une r\u00e9installation des bases syst\u00e8mes \u00e0 partir de l\u00e0:<\/p>\n<pre><strong><span style=\"color: #0000ff;\">DOS&gt;setup.exe \/QUIET \/ACTION=REBUILDDATABASE \/INSTANCENAME=SQL1 \/SQLSYSADMINACCOUNTS=user1 <\/span><span style=\"color: #0000ff;\">\/SAPWD=m0np@$$w0rdS@ \/SQLCOLLATION=French_CI_AS<\/span><\/strong><span style=\"color: #0000ff;\"> Microsoft (R) SQL Server 2008 Setup 10.00.2735.00 Copyright (c) Microsoft Corporation.\u00a0 All rights reserved. DOS&gt;<\/span><span style=\"color: #0000ff;\">\u00a0 <\/span><\/pre>\n<ul>\n<li>\/ACTION=REBUILDDATABASE : indique que l&#8217;on souhaite reconstruire les bases syst\u00e8mes.<\/li>\n<li>\/INSTANCE=: est le nom de l&#8217;instance \u00e0 reconstruire, on aurait utilis\u00e9 MSSQLSERVER s&#8217;il avait s&#8217;agit d&#8217;une instance par d\u00e9faut.<\/li>\n<li>\/SQLSYSADMINACCOUNTS= : donne le ou les comptes \u00e0 ajouter au r\u00f4le sysadmin lors de la r\u00e9installation, c&#8217;est un minimum pour pouvoir ensuite se connecter.<\/li>\n<li>\/SAPWD= : le mot de passe pour le compte SA lorsque l&#8217;authentification est MIXED.<\/li>\n<li>\/SQLCOLLATION= : la collation de l&#8217;instance.<\/li>\n<\/ul>\n<p>On peut v\u00e9rifier la bonne ex\u00e9cution du setup dans le fichier Summary.txt sous ~SetupBootstrap\\Log:<\/p>\n<pre><span style=\"color: #0000ff;\">Overall summary: <span style=\"color: #008000;\"> Final result:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Passed<\/span> <span style=\"color: #008000;\"> Exit code (Decimal):\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/span> <span style=\"color: #008000;\"> Exit message:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Passed<\/span> Start time:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2011-07-03 15:50:37 End time:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2011-07-03 15:52:36 Requested action:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RebuildDatabase<\/span><\/pre>\n<p>Puis il faut red\u00e9marrer l&#8217;instance en mode mono-utilisateur dans une fen\u00eatre DOS:<\/p>\n<pre><span style=\"color: #0000ff;\"><strong>E:\\MSSQL10.SQL1\\MSSQL\\Binn&gt;sqlservr -m -s SQL1<\/strong> 2011-07-03 15:53:25.63 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (Intel X86) Jan\u00a0 8 2010 20:19:57 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 6.0 &lt;X86&gt; (Build 6001: Service Pack 1) (VM) 2011-07-03 15:53:25.63 Server\u00a0\u00a0\u00a0\u00a0\u00a0 (c) 2005 Microsoft Corporation. 2011-07-03 15:53:25.63 Server\u00a0\u00a0\u00a0\u00a0\u00a0 All rights reserved. 2011-07-03 15:53:25.64 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server process ID is 952. 2011-07-03 15:53:25.64 Server\u00a0\u00a0\u00a0\u00a0\u00a0 System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'. 2011-07-03 15:53:25.64 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Authentication mode is MIXED. 2011-07-03 15:53:25.64 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Logging SQL Server messages in file 'E:\\MSSQL10.SQL1\\MSSQL\\Log\\ERRORLOG'. 2011-07-03 15:53:25.64 Server\u00a0\u00a0\u00a0\u00a0\u00a0 This instance of SQL Server last reported using a process ID of 816 at 7\/3\/2011 3:52:34 PM (local) 7\/3\/2011 1:52:34 PM (UTC). ... 2011-07-03 15:53:25.64 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Registry startup parameters: -d E:\\MSSQL10.SQL1\\MSSQL\\DATA\\master.mdf -e E:\\MSSQL10.SQL1\\MSSQL\\Log\\ERRORLOG -l E:\\MSSQL10.SQL1\\MSSQL\\DATA\\mastlog.ldf 2011-07-03 15:53:25.64 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Command Line Startup Parameters: <span style=\"color: #ff0000;\"> -m<\/span> -s SQL1 SQL1 2011-07-03 15:53:25.67 Server\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Server is starting at normal priority base (=7). This is an informational message only... 2011-07-03 15:53:25.67 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Detected 1 CPUs. This is an informational message; no user action is required. 2011-07-03 15:53:25.70 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Perfmon counters for resource governor pools and groups failed to initialize and are disabled. 2011-07-03 15:53:25.70 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Using dynamic lock allocation.\u00a0 Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node... 2011-07-03 15:53:25.82 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Node configuration: node 0: CPU mask: 0x00000001 Active CPU mask: 0x00000001. This message provides a description of the NUMA configuration for this computer... 2011-07-03 15:53:25.85 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Database Mirroring Transport is disabled in the endpoint configuration. 2011-07-03 15:53:25.86 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Warning ****************** 2011-07-03 15:53:25.87 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 <span style=\"color: #ff0000;\">SQL Server started in single-user mode.<\/span> This an informational message only. No user action is required. 2011-07-03 15:53:25.87 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'master'. 2011-07-03 15:53:25.98 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Recovery is writing a checkpoint in database 'master' (1)... 2011-07-03 15:53:26.05 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Server Audit is starting the audits. This is an informational message. No user action is required. 2011-07-03 15:53:26.05 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Server Audit has started the audits. This is an informational message. No user action is required. 2011-07-03 15:53:26.07 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQL1'. 2011-07-03 15:53:26.17 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Trace ID 1 was started by login \"sa\". 2011-07-03 15:53:26.18 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'mssqlsystemresource'. 2011-07-03 15:53:26.22 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 The resource database build version is 10.00.2757... 2011-07-03 15:53:26.63 spid9s\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'model'. 2011-07-03 15:53:26.98 Server\u00a0\u00a0\u00a0\u00a0\u00a0 A self-generated certificate was successfully loaded for encryption. 2011-07-03 15:53:26.99 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Server name is 'VMSUSER1\\SQL1'. This is an informational message only. No user action is required. 2011-07-03 15:53:27.17 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server is listening on [ 'any' &lt;ipv6&gt; 49161]. 2011-07-03 15:53:27.21 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server is listening on [ 'any' &lt;ipv4&gt; 49161]. 2011-07-03 15:53:27.24 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server local connection provider is ready to accept connection on [ \\\\.\\pipe\\SQLLocal\\SQL1 ]. 2011-07-03 15:53:27.24 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server local connection provider is ready to accept connection on [ \\\\.\\pipe\\MSSQL$SQL1\\sql\\query ]. 2011-07-03 15:53:27.28 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server is listening on [ ::1 &lt;ipv6&gt; 49162]. 2011-07-03 15:53:27.28 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server is listening on [ 127.0.0.1 &lt;ipv4&gt; 49162]. 2011-07-03 15:53:27.28 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Dedicated admin connection support was established for listening locally on port 49162. 2011-07-03 15:53:27.29 Server\u00a0\u00a0\u00a0\u00a0\u00a0 The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQLServer service... 2011-07-03 15:53:27.30 Server\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Server is now ready for client connections. This is an informational message; no user action is required. 2011-07-03 15:53:27.36 spid9s\u00a0\u00a0\u00a0\u00a0\u00a0 Clearing tempdb database. 2011-07-03 15:53:27.38 spid11s\u00a0\u00a0\u00a0\u00a0 A new instance of the full-text filter daemon host process has been successfully started. 2011-07-03 15:53:27.44 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'msdb'. 2011-07-03 15:53:28.04 spid9s\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'tempdb'. <span style=\"color: #0000ff;\">2011-07-03 15:53:28.23 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Recovery is complete. This is an informational message only. No user action is required.<\/span><\/span><\/pre>\n<p>Dans une seconde fen\u00eatre de commande, se connecter et restaurer le backup :<\/p>\n<pre><span style=\"color: #0000ff;\"><strong>C:\\Users\\user1&gt;sqlcmd -E -S .\\SQL1 <\/strong><strong>1&gt; restore database master from disk='E:\\MSSQL10.SQL1\\MSSQL\\Backup\\master.bak' 2&gt; go<\/strong> Processed 376 pages for database 'master', file 'master' on file 1. Processed 4 pages for database 'master', file 'mastlog' on file 1. The master database has been successfully restored. Shutting down SQL Server. SQL Server is terminating this process.<\/span><\/pre>\n<p>Quand on est bien r\u00f4d\u00e9, la restauration ne prend pas plus de cinq minutes. Une fois la base restaur\u00e9e, SQL Server s&#8217;arr\u00eate, et il ne reste plus qu&#8217;\u00e0 le red\u00e9marrer via net start ou la console Configuration Manager. Et le service est de nouveau en ligne.<\/p>\n<h3>Cas n\u00b02: le fichier master.mdf est lisible mais le recovery \u00e9choue:<\/h3>\n<p>C&#8217;est le cas utilis\u00e9 dans la d\u00e9mo. Lorsqu&#8217;un probl\u00e8me survient entre les \u00e9tapes 2.2 et 2.3, le fichier master.mdf est ouvert et le recovery commence, mais \u00e9choue avant d&#8217;avoir pu aller au bout. M\u00eame cons\u00e9quence, la base master ne peut \u00eatre ouverte et l&#8217;instance doit s&#8217;arr\u00eater:<\/p>\n<pre><span style=\"color: #0000ff;\">2011-07-03 16:11:32.98 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'master'. <span style=\"color: #ff0000;\">2011-07-03 16:11:34.89 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 824, Severity: 24, State: 2.<\/span> <span style=\"color: #ff0000;\">2<\/span><span style=\"color: #ff0000;\"><span style=\"color: #ff0000;\">01<\/span>1-07-03 16:11:34.89 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Server detected a logical consistency-based I\/O error: incorrect pageid (expected 1:84; actual 12336:808464432). It occurred during a read of page (1:84) in database ID 1 at offset 0x000000000a8000 in file 'E:\\MSSQL10.SQL1\\MSSQL\\DATA\\master_corrupted.mdf'.\u00a0 <\/span> Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. <span style=\"color: #ff0000;\">2011-07-03 16:11:34.94 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Error: 3314, Severity: 21, State: 1. 2011-07-03 16:11:34.94 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 During undoing of a logged operation in database 'master', an error occurred at log record ID (196:64:2).<\/span> Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database. <span style=\"color: #ff0000;\">2<\/span><span style=\"color: #ff0000;\"><span style=\"color: #ff0000;\">0<\/span>11-07-03 16:11:34.94 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.<\/span> For more information about how to rebuild the master database, see SQL Server Books Online.<\/span><\/pre>\n<p>Contrairement au cas pr\u00e9c\u00e9dent, l&#8217;\u00e9tape d&#8217;ouverture de master.mdf est valid\u00e9e donc si on a un backup on peut intercaler un point d&#8217;arr\u00eat et dire \u00e0 SQL Server de stopper la s\u00e9quence de d\u00e9marrage juste avant le recovery, en utilisant le traceflag -T3607:<\/p>\n<pre><span style=\"color: #0000ff;\"><strong>E:\\MSSQL10.SQL1\\MSSQL\\Binn&gt;sqlservr -m -T 3607 -s SQL1<\/strong> 2011-07-03 15:19:21.42 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (Intel X86) Jan\u00a0 8 2010 20:19:57 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 6.0 &lt;X86&gt; (Build 6001: Service Pack 1) (VM) 2011-07-03 15:19:21.47 Server\u00a0\u00a0\u00a0\u00a0\u00a0 (c) 2005 Microsoft Corporation. 2011-07-03 15:19:21.47 Server\u00a0\u00a0\u00a0\u00a0\u00a0 All rights reserved. 2011-07-03 15:19:21.48 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server process ID is 560. 2011-07-03 15:19:21.48 Server\u00a0\u00a0\u00a0\u00a0\u00a0 System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'. 2011-07-03 15:19:21.48 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Authentication mode is MIXED. 2011-07-03 15:19:21.48 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Logging SQL Server messages in file 'E:\\MSSQL10.SQL1\\MSSQL\\Log\\ERRORLOG'. 2011-07-03 15:19:21.53 Server\u00a0\u00a0\u00a0\u00a0\u00a0 This instance of SQL Server last reported using a process ID of 1996 at 7\/2\/2011 11:52:16 PM (local) 7\/2\/ 2011 9:52:16 PM (UTC). This is an informational message only; no user action is required. 2011-07-03 15:19:21.53 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Registry startup parameters: -d E:\\MSSQL10.SQL1\\MSSQL\\DATA\\master_corrupted.mdf -e E:\\MSSQL10.SQL1\\MSSQL\\Log\\ERRORLOG -l E:\\MSSQL10.SQL1\\MSSQL\\DATA\\mastlog_corrupted.ldf 2011-07-03 15:19:21.54 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Command Line Startup Parameters: <span style=\"color: #ff0000;\"> -m -T 3607<\/span> 3607 -s SQL1 SQL1 2011-07-03 15:19:21.62 Server\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. 2011-07-03 15:19:21.62 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Detected 1 CPUs. This is an informational message; no user action is required. 2011-07-03 15:19:22.18 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Perfmon counters for resource governor pools and groups failed to initialize and are disabled. 2011-07-03 15:19:22.22 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Using dynamic lock allocation.\u00a0 Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.\u00a0 ... 2011-07-03 15:19:23.13 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Node configuration: node 0: CPU mask: 0x00000001 Active CPU mask: 0x00000001. This message provides a description of the NUMA configuration for this computer. ... 2011-07-03 15:19:23.34 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Database Mirroring Transport is disabled in the endpoint configuration. 2011-07-03 15:19:23.40 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Warning ****************** <span style=\"color: #ff0000;\">2<\/span><span style=\"color: #ff0000;\"><span style=\"color: #ff0000;\">01<\/span>1-07-03 15:19:23.41 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Server started in single-user mode. This an informational message only. No user action is required. 2011-07-03 15:19:23.41 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Starting without recovery. This is an informational message only. No user action is required.<\/span> 2011-07-03 15:19:23.56 spid7s\u00a0\u00a0\u00a0\u00a0\u00a0 Starting up database 'master'. 2011-07-03 15:19:24.99 Server\u00a0\u00a0\u00a0\u00a0\u00a0 A self-generated certificate was successfully loaded for encryption. 2011-07-03 15:19:25.02 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server is listening on [ 'any' &lt;ipv6&gt; 49161]. 2011-07-03 15:19:25.03 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server is listening on [ 'any' &lt;ipv4&gt; 49161]. 2011-07-03 15:19:25.05 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server local connection provider is ready to accept connection on [ \\\\.\\pipe\\SQLLocal\\SQL1 ]. 2011-07-03 15:19:25.05 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server local connection provider is ready to accept connection on [ \\\\.\\pipe\\MSSQL$SQL1\\sql\\query ]. 2011-07-03 15:19:25.07 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server is listening on [ ::1 &lt;ipv6&gt; 49162]. 2011-07-03 15:19:25.08 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Server is listening on [ 127.0.0.1 &lt;ipv4&gt; 49162]. 2011-07-03 15:19:25.08 Server\u00a0\u00a0\u00a0\u00a0\u00a0 Dedicated admin connection support was established for listening locally on port 49162. 2011-07-03 15:19:25.12 Server\u00a0\u00a0\u00a0\u00a0\u00a0 The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos... 2011-07-03 15:19:25.13 Server\u00a0\u00a0\u00a0\u00a0\u00a0 SQL Server is now ready for client connections. This is an informational message; no user action is required.<\/span><\/pre>\n<p>Ensuite on ouvre un second prompt DOS et on restaure la sauvegarde de master :<\/p>\n<pre><strong><span style=\"color: #0000ff;\">C:\\Users\\user1&gt;sqlcmd -E -S .\\SQL1<\/span> <\/strong><span style=\"color: #0000ff;\"><strong>1&gt; restore database master from disk='E:\\MSSQL10.SQL1\\MSSQL\\Backup\\master.bak' 2&gt; go<\/strong> Processed 376 pages for database 'master', file 'master' on file 1. Processed 4 pages for database 'master', file 'mastlog' on file 1. The master database has been successfully restored. Shutting down SQL Server. SQL Server is terminating this process.<\/span><\/pre>\n<p>L\u00e0 encore, SQL Server d\u00e9tecte qu&#8217;une restauration de la base master vient d&#8217;avoir lieu, et stoppe l&#8217;instance imm\u00e9diatement ensuite. Il suffit de red\u00e9marrer le service via SQL Configuration Manager et notre application est de nouveau disponible, tout \u00e7a en moins de 2 minutes.<\/p>\n<p>Sans backup dans ce cas, il aurait fallu reconstruire une enveloppe de la base master comme dans le cas pr\u00e9c\u00e9dent, puis remettre \u00e0 jour tous les logins, serveurs li\u00e9s, options de configuration, patches, endpoints, etc&#8230; et enfin rattacher les bases utilisateur.<\/p>\n<h2>Conclusion:<\/h2>\n<p>La conclusion est simple: <span style=\"text-decoration: underline;\"><strong>sauvegardez vos bases syst\u00e8me<\/strong><\/span>. Si master se retrouve endommag\u00e9e, sans backup le temps pass\u00e9 \u00e0 remonter toutes les informations sera beaucoup plus important, et parfois il ne sera pas possible de tout remonter (les mots de passe des logins par exemple, certaines options de configuration modifi\u00e9es). Le pr\u00e9judice peut \u00eatre tr\u00e8s \u00e9lev\u00e9: une interruption intempestive co\u00fbte cher \u00e0 l&#8217;entreprise: p\u00e9nalit\u00e9s financi\u00e8res par rapport aux clients, crise d&#8217;image, etc&#8230; alors qu&#8217;ajouter trois petites bases syst\u00e8me dans un plan de maintenance ne vous co\u00fbte quasiment rien.<\/p>\n<p>A+. David B.<\/p>\n<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F2474&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F2474&#038;title=Pourquoi%20il%20faut%20sauvegarder%20les%20bases%20syst%C3%A8mes\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Pourquoi%20il%20faut%20sauvegarder%20les%20bases%20syst%C3%A8mes&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F2474\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a>","protected":false},"excerpt":{"rendered":"<p>On peut presque dire que sur 90% des instances que l&#8217;on audite, les bases syst\u00e8mes ne sont JAMAIS sauvegard\u00e9es. Souvent c&#8217;est un aspect de l&#8217;administration qui est n\u00e9glig\u00e9: on effectue des sauvegardes des bases utilisateurs, mais les bases syst\u00e8mes ne&hellip; <a href=\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/\" class=\"more-link\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":7918,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[201,206,202],"class_list":["post-2474","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver","tag-corruption","tag-howto","tag-traceflag"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Pourquoi il faut sauvegarder les bases syst\u00e8mes - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Pourquoi il faut sauvegarder les bases syst\u00e8mes - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"On peut presque dire que sur 90% des instances que l&#8217;on audite, les bases syst\u00e8mes ne sont JAMAIS sauvegard\u00e9es. Souvent c&#8217;est un aspect de l&#8217;administration qui est n\u00e9glig\u00e9: on effectue des sauvegardes des bases utilisateurs, mais les bases syst\u00e8mes ne&hellip; Continuer la lecture &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2011-07-03T14:20:26+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-13T13:38:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2011\/07\/basckupsys.png\" \/>\n\t<meta property=\"og:image:width\" content=\"667\" \/>\n\t<meta property=\"og:image:height\" content=\"232\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"David Baffaleuf\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"David Baffaleuf\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"18 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/\"},\"author\":{\"name\":\"David Baffaleuf\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\"},\"headline\":\"Pourquoi il faut sauvegarder les bases syst\u00e8mes\",\"datePublished\":\"2011-07-03T14:20:26+00:00\",\"dateModified\":\"2019-09-13T13:38:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/\"},\"wordCount\":1518,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"corruption\",\"howto\",\"traceflag\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/\",\"name\":\"Pourquoi il faut sauvegarder les bases syst\u00e8mes - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2011-07-03T14:20:26+00:00\",\"dateModified\":\"2019-09-13T13:38:58+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Pourquoi il faut sauvegarder les bases syst\u00e8mes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\",\"name\":\"David Baffaleuf\",\"sameAs\":[\"http:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Pourquoi il faut sauvegarder les bases syst\u00e8mes - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/","og_locale":"fr_FR","og_type":"article","og_title":"Pourquoi il faut sauvegarder les bases syst\u00e8mes - Capdata TECH BLOG","og_description":"On peut presque dire que sur 90% des instances que l&#8217;on audite, les bases syst\u00e8mes ne sont JAMAIS sauvegard\u00e9es. Souvent c&#8217;est un aspect de l&#8217;administration qui est n\u00e9glig\u00e9: on effectue des sauvegardes des bases utilisateurs, mais les bases syst\u00e8mes ne&hellip; Continuer la lecture &rarr;","og_url":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2011-07-03T14:20:26+00:00","article_modified_time":"2019-09-13T13:38:58+00:00","og_image":[{"width":667,"height":232,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2011\/07\/basckupsys.png","type":"image\/png"}],"author":"David Baffaleuf","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"David Baffaleuf","Dur\u00e9e de lecture estim\u00e9e":"18 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/"},"author":{"name":"David Baffaleuf","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf"},"headline":"Pourquoi il faut sauvegarder les bases syst\u00e8mes","datePublished":"2011-07-03T14:20:26+00:00","dateModified":"2019-09-13T13:38:58+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/"},"wordCount":1518,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["corruption","howto","traceflag"],"articleSection":["SQL Server"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/","url":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/","name":"Pourquoi il faut sauvegarder les bases syst\u00e8mes - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2011-07-03T14:20:26+00:00","dateModified":"2019-09-13T13:38:58+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/pourquoi-il-faut-sauvegarder-les-bases-systemes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Pourquoi il faut sauvegarder les bases syst\u00e8mes"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf","name":"David Baffaleuf","sameAs":["http:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/2474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=2474"}],"version-history":[{"count":152,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/2474\/revisions"}],"predecessor-version":[{"id":7919,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/2474\/revisions\/7919"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7918"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=2474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=2474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=2474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}