/******************************************************************************
***   Correction des bases SQL Server corrompues - principaux scrips SQL.   ***
*******************************************************************************
* Frédéric Brouard - SQLpro - MVP SQL Server *** SQL spot SARL (PARIS / PACA) *
* le blog : http://blog.developpez.com/sqlpro 	                              *
* le site : http://sqlpro.developpez.com                                      *
*******************************************************************************
* Principaux scripts Transact SQL associés à l'article                        *
******************************************************************************/

--> #01 - Pour retrouver la base de données (l’identifiant qui a été révélé est ???)
SELECT name 
FROM   sys.databases 
WHERE database_id = ???;   --> identifiant de la base

--> #02 - Pour retrouver dans la base $$$ l'objet contenu dans une page (pour nous la page ??? du fichier !!!), lancez le lot de commande :
DBCC TRACEON (3604) 
--> pour rediriger la sortie de la commande DBCC PAGE vers SSMS
GO
DBCC PAGE($$$, !!!, ???, 1)
--> syntaxe : nom_base, n° fichier, n° page, mode de visu (0, 1, 2 ou 3)
GO

--> #03 - Pour retrouver dans la base $$$ l'objet contenu dans une page ??? du fichier !!!, avec affichage en mode table
DBCC PAGE($$$, !!!, ???, 0) WITH TABLERESULTS
GO

--> #04 - Pour trouver l’index et la tableconcernée, faite la requête suivante :
SELECT s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,
       o.type_desc AS TABLE_TYPE, i.name AS INDEX_NAME,
	   CASE WHEN i.index_id <= 1 THEN 'TABLE'
	        WHEN i.type_desc = 'NONCLUSTERED' THEN 'INDEX'
			ELSE i.type_desc END as INDEX_TYPE
FROM   sys.objects AS o
       INNER JOIN sys.schemas AS s
	         ON o.schema_id = s.schema_id
	   INNER JOIN sys.indexes AS i
	         ON o.object_id = i.object_id
WHERE  o.object_id = 000   --> identifiant de l'objet
  AND  i.index_id = 000;   --> identifiant de l'index

--> #05 - identifie les contraintes FOREIGN KEY à « débrancher », en connaissant le nom de la table ??? et son schema !!! dont l’index 000 de contrainte PRIMARY KEY ou UNIQUE est à réparer
SELECT RC.CONSTRAINT_NAME, FK.TABLE_SCHEMA, FK.TABLE_NAME,
       'ALTER TABLE [' + FK.TABLE_SCHEMA + '].[' + FK.TABLE_NAME 
	   + '] DROP CONSTRAINT [' + FK.CONSTRAINT_NAME + '];' AS DROP_COMMAND
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS UK
       INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
	         ON UK.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
			 AND UK.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
	   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK
	         ON RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
			 AND RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
       INNER JOIN sys.objects AS o
	         ON o.object_id = OBJECT_ID(UK.TABLE_SCHEMA+'.'+UK.TABLE_NAME)
	   INNER JOIN sys.indexes AS i
	         ON o.object_id = i.object_id
			 AND i.name = UK.CONSTRAINT_NAME
WHERE  UK.TABLE_SCHEMA = '!!!'       --> schéma SQL de la table concernée
AND    UK.TABLE_NAME   = '???'       --> nom de la table concernée
AND    index_id        = 000         --> identifiant de l'index concerné

--> #06 - réparer en mode REPAIR_REBUILD dans la base $$$
ALTER DATABASE [$$$]
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKTABLE ('!!!.???', REPAIR_REBUILD); --> schema !!!, table ???

--> #07 - réparer en mode REPAIR_ALLOW_DATA_LOSS dans la base $$$
ALTER DATABASE [$$$]
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKTABLE ('!!!.???', REPAIR_ALLOW_DATA_LOSS)

--> #08 - Placement de la base $$$ en mode EMERGENCEY 
ALTER DATABASE [$$$] SET EMERGENCY
GO 

--> #09 - réparer la base en mode REPAIR_ALLOW_DATA_LOSS dans la base $$$ après mise en EMERGENCY
ALTER DATABASE [$$$] 
   SET SINGLE_USER;
GO
DBCC CHECKDB ([$$$], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; 
GO
ALTER DATABASE [$$$] SET MULTI_USER; 
GO

--> #10 - Vérification après réparation
DBCC CHECKDB ([$$$]);
GO
USE [$$$]
GO
DBCC CHECKCONSTRAINTS ();


-------------------------------------------------------------------------------
--> A1 - déplacement des fichiers d’une base
-------------------------------------------------------------------------------

USE [$$$] --> nom de la base à déplacer

SELECT name, type_desc, physical_name, 
       size * 8 / 1024.0 AS SIZE_MB
FROM   sys.database_files;

SELECT name, type_desc, physical_name, 
       size * 8 / 1024.0 AS SIZE_MB
FROM   sys.master_files
WHERE  database_id = DB_ID('MA_BASE'); --> nom de la base à déplacer
GO



DECLARE @NEWPATH NVARCHAR(256);
SET @NEWPATH = 'D:\DATABASES\'; --> nouveau chemin à prendre en compte

SELECT name, type_desc, physical_name, 
       size * 8 / 1024.0 AS SIZE_MB,
	   N'MOVE /Y "' + physical_name + N'" "' 
	   + @NEWPATH + N'"' AS OS_COMMAND
FROM   sys.master_files
WHERE  database_id = DB_ID('$$$'); --> nom de la base à déplacer
GO



DECLARE @OSCMD VARCHAR(8000);
SET @OSCMD = 'C:\DATABASES\'; --> nouveau chemin à prendre en compte
SET @OSCMD = '"' + @OSCMD +'"';
SELECT @OSCMD = '"' + physical_name +'" ' + @OSCMD
FROM   sys.master_files
WHERE  database_id = DB_ID('$$$'); --> nom de la base à déplacer
SELECT 'COPY /Y ' + @OSCMD;
GO



DECLARE @NEWPATH NVARCHAR(256);
SET @NEWPATH = N'D:\DATABASES\'; --> nouveau chemin à prendre en compte
DECLARE @SQLCMD NVARCHAR(max);

WITH 
T0 AS (SELECT DB_NAME(database_id) AS DATABASE_NAME, 
              REVERSE(LEFT(REVERSE(physical_name), 
                      CHARINDEX('\', REVERSE(physical_name)) - 1)) 
                 AS A_FILE,
              ROW_NUMBER() OVER(ORDER BY file_id) AS N,
              COUNT(*) OVER() AS P
       FROM   sys.master_files
       WHERE  database_id = DB_ID('$$$')), --> nom de la base à déplacer
T1 AS (SELECT DISTINCT N'CREATE DATABASE [' + DATABASE_NAME + N'] ON ' AS SQLCMD_HEAD
       FROM   T0),
T2 AS (SELECT CAST(N' (FILENAME = ''' + @NEWPATH + A_FILE + N''') ' AS NVARCHAR(max)) 
                 AS SQLCMD, N, P
       FROM   T0
       WHERE  N = 1
       UNION ALL
       SELECT SQLCMD + N', (FILENAME = ''' + @NEWPATH + A_FILE + N''') ', T0.N, T0.P
       FROM   T0 
              INNER JOIN T2 ON T2.N + 1= T0.N)
SELECT SQLCMD_HEAD + N' ' + SQLCMD + N' FOR ATTACH;'
FROM   T1
       CROSS JOIN T2
WHERE  N = P;