{"id":676,"date":"2025-08-26T11:16:14","date_gmt":"2025-08-26T11:16:14","guid":{"rendered":"https:\/\/datacrazyworld.com\/?p=676"},"modified":"2025-08-26T11:17:24","modified_gmt":"2025-08-26T11:17:24","slug":"scripts-automaticos-para-restaurar-backups-en-sql-server","status":"publish","type":"post","link":"https:\/\/datacrazyworld.com\/index.php\/2025\/08\/26\/scripts-automaticos-para-restaurar-backups-en-sql-server\/","title":{"rendered":"Scripts autom\u00e1ticos para restaurar backups en SQL Server"},"content":{"rendered":"\n<p>En el art\u00edculo del pasado mes de <a href=\"https:\/\/datacrazyworld.com\/index.php\/2025\/03\/16\/automatiza-backups-de-sql-server-con-powershell\" target=\"_blank\" rel=\"noopener\" title=\"\">marzo<\/a> os mostr\u00e9 c\u00f3mo crear un script para realizar backups de bases de datos. Hoy vamos a dar un paso m\u00e1s y trabajar en el proceso inverso: <strong>la generaci\u00f3n autom\u00e1tica de scripts T-SQL para restaurar esos backups<\/strong>.<\/p>\n\n\n\n<p>Para ello he desarrollado un procedimiento almacenado llamado <strong>dbo.xp_GenerateRestoreScripts<\/strong>, que debe crearse en la base de datos <em>master<\/em> de la instancia de SQL Server correspondiente. La base de este script fue desarrollada originalmente por mis compa\u00f1eros del Grupo RETAbet, <a href=\"https:\/\/www.linkedin.com\/in\/marta-conde-solana-1b66a570\" target=\"_blank\" rel=\"noopener\" title=\"\">Marta Conde<\/a> y <a href=\"https:\/\/www.linkedin.com\/in\/luis-perez-martin-1b2b2b1b\" target=\"_blank\" rel=\"noopener\" title=\"\">Luis Felipe P\u00e9rez<\/a>, a quienes agradezco su trabajo. Sobre su versi\u00f3n he a\u00f1adido algunas mejoras que lo hacen m\u00e1s flexible y f\u00e1cil de usar.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>\ud83c\udfaf Objetivo del procedimiento<\/strong><\/h2>\n\n\n\n<p>El prop\u00f3sito de este <em>stored procedure<\/em> es sencillo pero muy potente: <strong>analizar un directorio ra\u00edz en busca de archivos de backup (FULL y DIFF) y generar de manera autom\u00e1tica las sentencias necesarias para restaurarlos<\/strong>.<\/p>\n\n\n\n<p>Adem\u00e1s, soporta distintos escenarios de recuperaci\u00f3n, permitiendo trabajar en tres modos principales:<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>Restaurar en la ubicaci\u00f3n original.<\/li>\n\n\n\n<li>Restaurar en la base de datos original pero moviendo los archivos a otra ruta.<\/li>\n\n\n\n<li>Restaurar en una nueva base de datos, con redirecci\u00f3n de archivos.<\/li>\n<\/ol>\n\n\n\n<p>Como extra, tambi\u00e9n puede generar salidas en <strong>modo verbose<\/strong>, muy \u00fatil si lo queremos integrar en scripts de PowerShell.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>\u2699\ufe0f Par\u00e1metros de entrada<\/strong><\/h2>\n\n\n\n<p>El procedimiento acepta varios par\u00e1metros que permiten personalizar su comportamiento. Los m\u00e1s relevantes son:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>@ModoRecuperaci\u00f3n (TINYINT)<\/strong> \u2192 Define el modo de restauraci\u00f3n (1, 2 o 3).<\/li>\n\n\n\n<li><strong>@backupPath (NVARCHAR(500))<\/strong> \u2192 Ruta ra\u00edz donde se encuentran los archivos de backup.<\/li>\n\n\n\n<li><strong>@Path_MDF_Destination \/ @Path_NDF_Destination \/ @Path_LDF_Destination<\/strong> \u2192 Nuevas rutas de destino para los archivos f\u00edsicos.<\/li>\n\n\n\n<li><strong>@dbName_suffix (NVARCHAR(200))<\/strong> \u2192 Sufijo que se a\u00f1ade al nombre de la base de datos y sus archivos al restaurar.<\/li>\n\n\n\n<li><strong>@RecoveryON (BIT)<\/strong> \u2192 Indica si se debe incluir la cl\u00e1usula WITH RECOVERY (1 por defecto).<\/li>\n\n\n\n<li><strong>@VerboseMode (BIT)<\/strong> \u2192 Muestra informaci\u00f3n detallada para diagn\u00f3stico (0 por defecto).<\/li>\n\n\n\n<li><strong>@GridResultSet (BIT)<\/strong> \u2192 Devuelve los comandos como conjunto de resultados (0 por defecto).<\/li>\n\n\n\n<li><strong>@ScanSubdirectories (BIT)<\/strong> \u2192 Indica si debe buscar tambi\u00e9n en subdirectorios (1 por defecto).<\/li>\n\n\n\n<li><strong>@databases (NVARCHAR(MAX))<\/strong> \u2192 JSON con la colecci\u00f3n de bases de datos a restaurar (ejemplo: [{\u00abdatabase_id\u00bb:5,\u00bbname\u00bb:\u00bbAdventureWorks2019&#8243;}]).<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>\ud83e\udde9 Funcionamiento paso a paso<\/strong><\/h2>\n\n\n\n<p>El procedimiento se organiza en <strong>cuatro bloques principales<\/strong>:<\/p>\n\n\n\n<p><strong>Bloque 1 \u2013 Recorrido de carpetas<\/strong><br>Se analiza el directorio indicado en @backupPath (y subdirectorios si aplica) utilizando el procedimiento del sistema master.sys.xp_dirtree. Con esa informaci\u00f3n se rellena la tabla @fileList con todos los archivos detectados.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN \/*1. BLOQUE PARA IR RECORRIENDO LAS DISTINTAS CARPETAS QUE ENCUENTRE DE FORMA RECURSIVA Y TENER TODO LO QUE HAY EN EL ARBOL*\/\n\n\tif @ScanSubdirectories=1 --esta hecho asi porque no se puede pasar depth como parametro\n\tbegin\n\t\tINSERT #DirectoryTree (subdirectory,depth,isfile)\n\t\tEXEC master.sys.xp_dirtree @backupPath,0,1\n\tend\n\telse\n\tbegin\n\t\tINSERT #DirectoryTree (subdirectory,depth,isfile)\n\t\tEXEC master.sys.xp_dirtree @backupPath,1,1 --SOLO BUSCA EN EL DIRECTORIO ACTUAL\n\tend\n\n\t;WITH fileparents (\n\t\tfileid\n\t\t,subdirectory\n\t\t,parentid\n\t\t,isfile\n\t\t)\n\tAS (\n\t\tSELECT c.fileid\n\t\t\t,c.subdirectory\n\t\t\t,(\n\t\t\t\tSELECT max(p.fileId)\n\t\t\t\tFROM #DirectoryTree p\n\t\t\t\tWHERE p.depth = c.depth - 1\n\t\t\t\t\tAND p.fileId &lt; c.fileId\n\t\t\t\t) parentId\n\t\t\t,c.isfile\n\t\tFROM #DirectoryTree c\n\t\t)\n\t\t,DirListing (\n\t\tfileid\n\t\t,subdirectory\n\t\t,isfile\n\t\t)\n\tAS (\n\t\tSELECT fileid\n\t\t\t,subdirectory + '' subdirectory\n\t\t\t,isfile\n\t\tFROM fileparents\n\t\tWHERE parentid IS NULL\n\t\n\t\tUNION ALL\n\t\n\t\tSELECT f.fileid\n\t\t\t,d.subdirectory + '\\' + f.subdirectory subdirectory\n\t\t\t,f.isfile\n\t\tFROM DirListing d\n\t\tINNER JOIN fileparents f ON f.parentid = d.fileid\n\t\t)\n\tinsert into @fileList (IsFile, fullpath)\n\tSELECT l.isfile\n\t\t,@backupPath + l.subdirectory\n\tFROM DirListing l\n\nEND<\/code><\/pre>\n\n\n\n<p><strong>Bloque 2 \u2013 Identificaci\u00f3n de backups<\/strong><br>Cada archivo de @fileList se eval\u00faa con RESTORE HEADERONLY FROM DISK.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Si el comando falla \u2192 no es un backup v\u00e1lido.<\/li>\n\n\n\n<li>Si funciona \u2192 se marca como archivo de backup.<\/li>\n<\/ul>\n\n\n\n<p>En este paso tambi\u00e9n se filtra el listado seg\u00fan el JSON recibido en @databases, de modo que solo se consideren las bases de datos especificadas.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN \/*2. COMPRUEBA TODOS LOS FICHEROS PARA SABER QUE SON BACKUPS Y SACAR SU INFORMACION*\/\n\tWHILE EXISTS (SELECT TOP 1 1 FROM @fileList where Id > @fileListid)\n\tBEGIN\n\t\tSELECT TOP 1 \n\t\t\t@backupFile_fullpath = fullpath\n\t\t\t,@fileListid = id\n\t\tFROM @fileList where Id > @fileListid\n\t\tORDER BY ID asc\n\n\t\t--a\u00f1adimos bloque try catch por si encontramos algun fichero que no es un backup. lo marca y sigue para adelante (PARA NO BASARME EN LAS EXTENSIONES)\n\t\tbegin try\n\t\t\tINSERT INTO #FilesInfo\n\t\t\t(\n\t\t\tBackupName\n\t\t\t, BackupDescription\n\t\t\t, BackupType\n\t\t\t, ExpirationDate\n\t\t\t, Compressed\n\t\t\t, Posici\u00f3n\n\t\t\t, DeviceType\n\t\t\t, UserName\n\t\t\t, ServerName\n\t\t\t, DatabaseName\n\t\t\t, DatabaseVersion\n\t\t\t, DatabaseCreationDate\n\t\t\t, BackupSize\n\t\t\t, FirstLSN\n\t\t\t, LastLSN\n\t\t\t, CheckpointLSN\n\t\t\t, DatabaseBackupLSN\n\t\t\t, BackupStartDate\n\t\t\t, BackupFinishDate\n\t\t\t, SortOrder\n\t\t\t, CodePage\n\t\t\t, UnicodeLocaleId\n\t\t\t, UnicodeComparisonStyle\n\t\t\t, CompatibilityLevel\n\t\t\t, SoftwareVendorId\n\t\t\t, SoftwareVersionMajor\n\t\t\t, SoftwareVersionMinor\n\t\t\t, SoftwareVersionBuild\n\t\t\t, MachineName\n\t\t\t, Marcas\n\t\t\t, BindingID\n\t\t\t, RecoveryForkID\n\t\t\t, Intercalaci\u00f3n\n\t\t\t, FamilyGUID\n\t\t\t, HasBulkLoggedData\n\t\t\t, IsSnapshot\t\n\t\t\t, IsReadOnly\t\n\t\t\t, IsSingleUser\t\n\t\t\t, HasBackupChecksums\n\t\t\t, IsDamaged\t\n\t\t\t, BeginsLogChain\t\n\t\t\t, HasIncompleteMetaData\t\n\t\t\t, IsForceOffline\t\n\t\t\t, IsCopyOnly\t\t\n\t\t\t, FirstRecoveryForkID\t\t\n\t\t\t, ForkPointLSN\t\n\t\t\t, RecoveryModel\t\n\t\t\t, DifferentialBaseLSN\t\n\t\t\t, DifferentialBaseGUID\t\n\t\t\t, BackupTypeDescription\t\n\t\t\t, BackupSetGUID\n\t\t\t, CompressedBackupSize\t\n\t\t\t, containment\t\n\t\t\t, KeyAlgorithm\t\n\t\t\t, EncryptorThumbprint\t\n\t\t\t, EncryptorType\t\n\t\t\t, LastValidRestoreTime --(2022)\n\t\t\t, TimeZone --(2022)\n\t\t\t, CompressionAlgorithm --(2022)\n\t\t\t)\n\t\t\tEXEC ('RESTORE HEADERONLY FROM DISK = ''' + @backupFile_fullpath + '''')\n\n\t\t\tupdate #FilesInfo set FullPath=@backupFile_fullpath where Id=(select max(Id) from #FilesInfo)\n\n\t\t\t--Ahora guardamos la informacion especifica de los ficheros de cada bbdd (sacado de su backup) para saber los mdf,ndf,ldf,etc.\n\t\t\tinsert into #FileListDatabase\n\t\t\tEXEC('RESTORE FILELISTONLY FROM DISK =  ''' + @backupFile_fullpath + '''')\n\n\t\tend try\n\t\tbegin catch\n\t\t\tDECLARE @ErrorMessage NVARCHAR(4000);  \n\t\t\tDECLARE @ErrorLine INT;  \n\t\t\tDECLARE @ErrorSeverity INT;  \n\t\t\tDECLARE @ErrorState INT;  \n\n\t\t\tselect @ErrorMessage = ERROR_MESSAGE()\n\t\t\t\t,@ErrorLine = ERROR_LINE()\n\t\t\t\t,@ErrorSeverity = ERROR_SEVERITY()\n\t\t\t\t,@ErrorState = ERROR_STATE()\n\t\t\n\t\t\t-- para devolver el fichero concreto que ha dado error\n\t\t\t-- select @backupPath, @backupFile\n\n\t\t\tRAISERROR (@ErrorMessage, -- Message text.\n\t\t\t\t\t\t@ErrorLine, --Error Line \n\t\t\t\t\t\t@ErrorSeverity, -- Severity.  \n\t\t\t\t\t\t@ErrorState -- State.  \n\t\t\t\t\t\t);  \t\n\t\tend catch\n\tEND\t\n\n\t--Actualizamos todos los backupname a fullpath porque no lo mete bien si esta en subdirectorios\n\tUPDATE #FilesInfo SET BackupName=FullPath\n\n\t--Devolver ficheros que no son backups\n\t---------------------------------------------------------------------------------------------------------\n\tIF @VerboseMode=0\n\tBEGIN\n\t\tselect l.*, 'No es backup'\n\t\tfrom @fileList l\n\t\t\tleft join #FilesInfo i\n\t\t\t\ton l.fullpath=i.FullPath\n\t\twhere i.FullPath is null\n\t\tand l.IsFile=1\n\tEND\n\n\t--CARGA LAS BBDDs desde el Json\n\tinsert into #databases (database_id, DatabaseName)\n\tSELECT *\n\tFROM OPENJSON (@databases)\n\tWITH\n\t(   database_id smallint '$.database_id'\n\t\t,DatabaseName sysname '$.name'\n\t) z\n\n\t--BBDD SIN NINGUN BACKUP FULL\n\tIF @VerboseMode=0\n\tBEGIN\n\t\tselect d.*, 'SIN BACKUP COMPLETO', f.*\n\t\tfrom #databases d\n\t\t\tleft join #FilesInfo f\n\t\t\t\ton d.DatabaseName=f.DatabaseName\n\t\t\t\tand f.BackupType = 1 -- Completo\n\t\twhere f.BackupName is null\n\tEND\nEND<\/code><\/pre>\n\n\n\n<p><strong>Bloque 3 \u2013 Selecci\u00f3n de backups v\u00e1lidos<\/strong><br>Se distinguen backups completos (FULL) y diferenciales (DIFF), y se seleccionan los m\u00e1s recientes para cada base de datos.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN \/*3. CON LA INFORMACION DE TODOS LOS BACKUPS SE ELIGE LOS QUE HAY QUE RESTAURAR PONIENDOLES  UN ORDEN A CADA UNO*\/\n\n\t-- A\u00f1adir columna OrdenRestauracion smallint en la tabla @FilesInfo\n\t-- A\u00f1adir columna OrdenRestauracion_BBDD smallint en la tabla @FilesInfo para elegir el orden de cada bbdd\n\tALTER TABLE #FilesInfo ADD OrdenRestauracion SMALLINT NULL, OrdenRestauracion_BBDD SMALLINT NULL\n\n\t--PRIMERO MARCO TODAS LAS BBDD CON UN ORDEN AUN SIN DETERMINAR!!!!. Se podria hacer por tama\u00f1o de la bbdd, por backup.. o a ver por que nos interesa\n\tupdate f set f.OrdenRestauracion_BBDD=d.database_id \n\tfrom #FilesInfo f\n\t\tinner join #databases d\n\t\t\ton f.DatabaseName=d.DatabaseName\n\n\t--Doy las n vueltas una por cada BBDD\n\twhile exists (select top 1 1 from #databases where Control=0)\n\tbegin\n\n\t\tselect top 1 @DBName = DatabaseName\n\t\tfrom #databases\n\t\twhere Control=0\n\n\t\t-- Comprobar que hay un backup completo y actualizar OrdenRestauraci\u00f3n 1 para el \u00faltimo backup encontrado\n\t\tIF EXISTS(SELECT TOP 1 1 FROM #FilesInfo WHERE BackupType = 1 and DatabaseName=@DBName)\n\t\tBEGIN\n\t\t\tSELECT TOP 1 \n\t\t\t\t@BackupName = BackupName\n\t\t\t\t, @LastLSN = LastLSN\n\t\t\tFROM\n\t\t\t\t#FilesInfo\n\t\t\tWHERE \n\t\t\t\tBackupType = 1 -- Completo\n\t\t\t\tand DatabaseName=@DBName\n\t\t\tORDER BY\n\t\t\t\tFirstLSN DESC\t\n\n\t\t\tUPDATE FI\n\t\t\tSET\tFI.OrdenRestauracion = 1\n\t\t\tFROM\n\t\t\t\t#FilesInfo AS FI\n\t\t\tWHERE\n\t\t\t\tFI.BackupType = 1 -- Completo\n\t\t\t\tAND FI.BackupName = @BackupName\n\t\t\t\tand fi.DatabaseName=@DBName\n\t\tEND\n\t\tELSE\n\t\tBEGIN\n\t\t\tupdate #databases set ErrorMessage='No hay un backup completo' where DatabaseName=@DBName\n\t\tEND\n\n\t\t-- Comprobar si hay un backup diferencial y actualizar OrdenRestauraci\u00f3n 2 para el \u00faltimo encontrado posterior al completo\n\t\tIF EXISTS(SELECT TOP 1 1 FROM #FilesInfo WHERE BackupType = 5 and DatabaseName=@DBName)\n\t\tBEGIN\n\t\t\tSET @BackupName = NULL\t\n\t\n\t\t\tSELECT TOP 1 \n\t\t\t\t@BackupName = BackupName\n\t\t\t\t, @LastLSN = LastLSN\n\t\t\tFROM\n\t\t\t\t#FilesInfo\n\t\t\tWHERE \n\t\t\t\tBackupType = 5 -- Diferencial\n\t\t\t\tAND FirstLSN >= @LastLSN\n\t\t\t\tand DatabaseName=@DBName\n\t\t\tORDER BY\n\t\t\t\tFirstLSN DESC\t\n\n\t\t\t-- Si hay backups diferenciales se establece el orden 2\n\t\t\tIF (@BackupName IS NOT NULL)\n\t\t\tBEGIN\n\t\t\t\tUPDATE FI\n\t\t\t\tSET\tFI.OrdenRestauracion = 2\n\t\t\t\tFROM\n\t\t\t\t\t#FilesInfo AS FI\n\t\t\t\tWHERE\n\t\t\t\t\tFI.BackupType = 5 -- Diferencial\n\t\t\t\t\tAND FI.BackupName = @BackupName\n\t\t\t\t\tand fi.DatabaseName=@DBName\n\t\t\tEND\n\t\tEND\n\n\t\t--Controles para avanzar en el bucle de control a todas las bbdd\n\t\tupdate #databases set Control=1 where DatabaseName=@DBName\n\n\tend --Fin del while para el bucle de todas las bbdd\n\t\t\n\tIF @VerboseMode=0\n\tBEGIN\n\t\t--Devolvemos las bbdd que no tienen un completo\n\t\tselect *\n\t\tfrom #databases\n\t\twhere ErrorMessage is not null\n\n\t\t--Y el detalle de lo que se ha cogido para restaurar\n\t\tselect f.BackupName, f.DatabaseName, f.BackupType, f.BackupTypeDescription, f.BackupSize, f.id, f.OrdenRestauracion, f.OrdenRestauracion_BBDD, f.*\n\t\tfrom #FilesInfo f\n\t\twhere OrdenRestauracion is not null\n\t\torder by f.OrdenRestauracion_BBDD, f.OrdenRestauracion\n\tEND\nEND<\/code><\/pre>\n\n\n\n<p><strong>Bloque 4 \u2013 Generaci\u00f3n de comandos<\/strong><br>Aqu\u00ed se construyen los comandos de restauraci\u00f3n en funci\u00f3n del modo de recuperaci\u00f3n elegido y los par\u00e1metros recibidos.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN \/*4. CREACION DE LOS COMANDOS DE RESTAURACION*\/\n\n\t--Cargamos las unidades que hay con su espacio disponible\n\tinsert into #Drives\n\tEXEC MASTER..xp_fixeddrives\n\n\t---------------------------------------------------------------------------------------------------------------------\n\t--Hacemos otro bucle para montar las sentencias para aquellas bbdd restaurables\n\t--Doy las n vueltas una por cada BBDD\n\twhile exists (select top 1 1 from #databases where Control=1 and ErrorMessage is null)\n\tbegin\n\n\t\tselect top 1 @DBName = DatabaseName\n\t\tfrom #databases\n\t\twhere ErrorMessage is null\n\n\t\t--REINICIO DE VARIABLES\n\t\tset @cmd=''\n\t\tset @cmdfilesmdf=''\n\t\tset @cmdfilesndf=''\n\t\tset @cmdfilesldf=''\n\t\tset @LogicalNamemdf=''\n\t\tset @LogicalNamendf=''\n\t\tset @LogicalNameldf=''\n\n\t\t-- 4.1 - Restaurar backup completo\n\t\tIF EXISTS(select top 1 * from #FilesInfo WHERE BackupType = 1 and DatabaseName=@DBName)\n\t\tBEGIN \n\n\t\t\tSET @BackupName = NULL\n\n\t\t\ttruncate table #FileListDatabase\n\n\t\t\tSELECT @BackupName = BackupName --+ '.Bak'\n\t\t\tFROM #FilesInfo  \n\t\t\tWHERE BackupType = 1 and DatabaseName=@DBName\n\t\t\t\tAND OrdenRestauracion is not null --(v.1.4.1)\n\n\t\t\t-- Restaurar en el original\n\t\t\tIF (@ModoRecuperacion = 1)\n\t\t\tBEGIN\n\t\t\t\tSET @cmd = 'RESTORE DATABASE &#91;' + @DBName + '] FROM DISK = '''  \n\t\t\t\t\t+ @BackupName + ''' WITH NORECOVERY, STATS = 5, REPLACE'\n\n\t\t\t\t--Sacamos info de ficheros del backup para comprobar si existen las unidades y tienen espacio\n\t\t\t\tinsert into #FileListDatabase\n\t\t\t\tEXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupName + '''')\n\n\t\t\t\t--Para devolver el tama\u00f1o total de la bbdd\n\t\t\t\tselect @DatabaseSizeMB = sum(Size)\/1048576.0\n\t\t\t\tfrom #FileListDatabase\n\t\t\t\twhere Type='D'\n\n\t\t\t\t--Devolver los datos como texto o grid\n\t\t\t\tif @GridResultSet=0\n\t\t\t\tBEGIN\n\t\t\t\t\tPRINT @cmd \n\t\t\t\tEND\n\t\t\t\tELSE\n\t\t\t\tBEGIN\n\t\t\t\t\tinsert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)\n\t\t\t\t\t\tselect @DBName, @DatabaseSizeMB, 1, @cmd\n\t\t\t\tEND\n\n\t\t\t\tif @VerboseMode=0\n\t\t\t\tBEGIN\n\t\t\t\t\t--Comprobacion por si la unidad donde va a restaurar no existe\n\t\t\t\t\t; with BackupSpace as\n\t\t\t\t\t(select LEFT(PhysicalName,CHARINDEX(':',PhysicalName,0)-1) as Drive, Size\/1048576.0 as SizeMB\n\t\t\t\t\tfrom #FileListDatabase f)\n\t\t\t\t\tselect 'Unidad ' + b.Drive + ': no existe para el backup ' + @BackupName + ' de ' + @DBName + ' y es necesaria'\n\t\t\t\t\tfrom BackupSpace b\n\t\t\t\t\t\tleft join #Drives d\n\t\t\t\t\t\t\ton b.Drive=d.Drive\n\t\t\t\t\twhere d.Drive is null\n\n\t\t\t\t\t--Comprobacion de si la unidad tiene espacio suficiente\n\t\t\t\t\t; with BackupSpace as\n\t\t\t\t\t(select LEFT(PhysicalName,CHARINDEX(':',PhysicalName,0)-1) as Drive, Size\/1048576.0 as SizeMB\n\t\t\t\t\tfrom #FileListDatabase f)\n\t\t\t\t\tselect 'Unidad ' + b.Drive + ': con ' + CAST(d.FreeMB AS varchar) +' libres y hacen falta ' + cast(b.SizeMB as varchar) + 'para el backup ' + @BackupName + ' de ' + @DBName\n\t\t\t\t\tfrom BackupSpace b\n\t\t\t\t\t\tinner join #Drives d\n\t\t\t\t\t\t\ton b.Drive=d.Drive\n\t\t\t\t\twhere b.SizeMB>=d.FreeMB\n\t\t\t\tEND\t\t\t\t\t\n\t\t\tEND\n\n\t\t\t-- Restaurar el original\/nuevo con los archivos en otra ubicaci\u00f3n\n\t\t\tIF (@ModoRecuperacion = 2) OR (@ModoRecuperacion=3)\n\t\t\tBEGIN\n\n\t\t\t\tIF @ModoRecuperacion=2 --Si es sobre la misma BBDD borramos suffix y a\u00f1adimos el REPLACE\n\t\t\t\tBEGIN\n\t\t\t\t\tset @dbName_suffix=''\n\t\t\t\t\tset @Replace='REPLACE, '\n\t\t\t\tEND\n\n\t\t\t\t--Sacamos info de ficheros del backup para comprobar los ficheros del backup (mdf, ndf y ldf)\n\t\t\t\tinsert into #FileListDatabase\n\t\t\t\tEXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupName + '''')\n\n\t\t\t\t--Para devolver el tama\u00f1o total de la bbdd\n\t\t\t\tselect @DatabaseSizeMB = sum(Size)\/1048576.0\n\t\t\t\tfrom #FileListDatabase\n\t\t\t\twhere Type='D'\n\n\t\t\t\t--Parte del mdf. solo hay uno (puede haber varios pero primario solo uno)\n\t\t\t\t----------------------MDF\n\t\t\t\tselect @LogicalNamemdf=LogicalName\n\t\t\t\tfrom #FileListDatabase\n\t\t\t\twhere Type='D' and CreateLSN=0\n\n\t\t\t\tset @cmdfilesmdf='MOVE N'''+ @LogicalNamemdf + ''' TO N'''+ @Path_MDF_Destination + @LogicalNamemdf + @dbName_suffix+'.mdf'','\n\n\t\t\t\t--ndf puede haber n asi que hacemos un bucle (pueden tener la extension que sea pero seran archivos secundarios)\n\t\t\t\t----------------------NDF\n\t\t\t\tSET @FileId = 1\n\n\t\t\t\twhile exists (select top 1 1 from #FileListDatabase where Type='D' and CreateLSN&lt;>0 and FileID>@FileId) --existe algun ndf sin procesar\n\t\t\t\tbegin\n\t\t\t\t\tselect top 1 @FileId=FileId, @LogicalNamendf=LogicalName\n\t\t\t\t\tfrom #FileListDatabase\n\t\t\t\t\twhere Type='D' and CreateLSN&lt;>0 and FileID>@FileId\n\t\t\t\t\torder by FileID\n\n\t\t\t\t\tselect @cmdfilesndf = @cmdfilesndf +  'MOVE N''' + @LogicalNamendf + ''' TO N'''+ @Path_NDF_Destination + @LogicalNamendf+ @dbName_suffix +'.ndf'','\n\n\t\t\t\tend\n\n\t\t\t\t--ldf puede haber n asi que hacemos un bucle (pueden tener la extension que sea pero seran archivos de log)\n\t\t\t\t----------------------LDF\n\t\t\t\tset @FileId = 1\n\n\t\t\t\twhile exists (select top 1 1 from #FileListDatabase where Type='L' and FileID>@FileId) --existe algun ndf sin procesar\n\t\t\t\tbegin\n\t\t\t\t\tselect top 1 @FileId=FileId, @LogicalNameldf=LogicalName\n\t\t\t\t\tfrom #FileListDatabase\n\t\t\t\t\twhere Type='L' and FileID>@FileId\n\t\t\t\t\torder by FileID\n\n\t\t\t\t\tselect @cmdfilesldf = @cmdfilesldf +  'MOVE N''' + @LogicalNameldf + ''' TO N'''+ @Path_LDF_Destination + @LogicalNameldf + @dbName_suffix +'.ldf'','\n\n\t\t\t\tend\n\n\t\t\t\tSET @cmd = 'RESTORE DATABASE &#91;' + @DBName + @dbName_suffix + '] FROM DISK = '''  \n\t\t\t\t\t+ @BackupName + ''' WITH NORECOVERY, STATS = 5, ' + @Replace\n\t\t\t\t\t+ @cmdfilesmdf\n\t\t\t\t\t+ @cmdfilesndf\n\t\t\t\t\t+ @cmdfilesldf\n\t\t\t\t\t+ '  NORECOVERY,  NOUNLOAD,  STATS = 5' \n\n\t\t\t\tIF @GridResultSet=0\n\t\t\t\tBEGIN\n\t\t\t\t\tPRINT @cmd \n\t\t\t\tEND\n\t\t\t\tELSE\n\t\t\t\tBEGIN\n\t\t\t\t\tinsert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)\n\t\t\t\t\t\tselect @DBName, @DatabaseSizeMB, 1, @cmd\n\t\t\t\tEND\n\t\t\tEND\n\t\tEND\n\n\t\t-- 4.2 - Restaurar backup diferencial\n\t\tIF EXISTS(select top 1 * from #FilesInfo WHERE BackupType = 5 AND DatabaseName=@DBName)\n\t\tBEGIN  \n\n\t\t\tSET @BackupName = NULL\n\n\t\t\tSELECT @BackupName = BackupName --+ '.Bak'\n\t\t\tFROM #FilesInfo  \n\t\t\tWHERE BackupType = 5 and DatabaseName=@DBName\n\t\t\t\tAND OrdenRestauracion is not null --(v.1.4.1)\n\n\t\t\t-- Restaurar en el original o Restaurar el original con los archivos en otra ubicaci\u00f3n\n\t\t\tIF ((@ModoRecuperacion = 1) OR (@ModoRecuperacion = 2))\n\t\t\tBEGIN\n\t\t\t\tSET @cmd = 'RESTORE DATABASE &#91;' + @DBName + '] FROM DISK = '''  \n\t\t\t\t\t+ @BackupName + ''' WITH NORECOVERY, STATS = 5' \n\n\t\t\t\tIF @GridResultSet=0\n\t\t\t\tBEGIN\n\t\t\t\t\tPRINT @cmd \n\t\t\t\tEND\n\t\t\t\tELSE\n\t\t\t\tBEGIN\n\t\t\t\t\tinsert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)\n\t\t\t\t\t\tselect @DBName, @DatabaseSizeMB, 2, @cmd\n\t\t\t\tEND\n\t\t\tEND\t\n\t\n\t\t\t-- Restaurar en otra bbdd\n\t\t\tIF (@ModoRecuperacion = 3)\n\t\t\tBEGIN\n\t\t\t\tSET @cmd = 'RESTORE DATABASE &#91;' + @DBName + @dbName_suffix + + '] FROM DISK = '''  \n\t\t\t\t\t+ @BackupName + ''' WITH NORECOVERY, STATS = 5' \n\n\t\t\t\tIF @GridResultSet=0\n\t\t\t\tBEGIN\n\t\t\t\t\tPRINT @cmd \n\t\t\t\tEND\n\t\t\t\tELSE\n\t\t\t\tBEGIN\n\n\t\t\t\t\tinsert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)\n\t\t\t\t\t\tselect @DBName, @DatabaseSizeMB, 2, @cmd\n\t\t\t\tEND\n\t\t\tEND\n\t\tEND\n\n\t\t-- 4.4 - Put database in a useable state \n\t\tif @RecoveryON=1\n\t\tBEGIN\n\t\t\t-- Hacer un Restore 2\n\t\t\tIF (@ModoRecuperacion = 3)\n\t\t\tBEGIN\n\t\t\t\tSET @cmd = 'RESTORE DATABASE &#91;' + @DBName + @dbName_suffix + '] WITH RECOVERY' \n\n\t\t\t\tif @GridResultSet=0\n\t\t\t\tBEGIN\n\t\t\t\t\tPRINT @cmd \n\t\t\t\tEND\n\t\t\t\tELSE\n\t\t\t\tBEGIN\n\t\t\t\t\tinsert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)\n\t\t\t\t\t\tselect @DBName, @DatabaseSizeMB, 3, @cmd\n\t\t\t\tEND\n\t\t\tEND\n\t\t\tELSE -- Restaurar en el original o Restaurar el original con los archivos en otra ubicaci\u00f3n\n\t\t\tBEGIN\n\t\t\t\tSET @cmd = 'RESTORE DATABASE &#91;' + @DBName + '] WITH RECOVERY' \n\t\t\t\n\t\t\t\tif @GridResultSet=0\n\t\t\t\tBEGIN\n\t\t\t\t\tPRINT @cmd \n\t\t\t\tEND\n\t\t\t\tELSE\n\t\t\t\tBEGIN\n\t\t\t\t\tinsert into @Results (DataBaseName, DataBaseSizeMB, ordercmd, cmd)\n\t\t\t\t\t\tselect @DBName, @DatabaseSizeMB, 3, @cmd\n\t\t\t\tEND\n\t\t\tEND\n\t\tEND\n\n\t\t--Controles para avanzar en el bucle de control a todas las bbdd\n\t\tupdate #databases set ErrorMessage=' ' where DatabaseName=@DBName\n\n\tend --Fin del while para el bucle de todas las bbdd\nEND<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>\ud83d\udccc Resultado final<\/strong><\/h2>\n\n\n\n<p>Como salida, el procedimiento devuelve los comandos T-SQL generados, listos para ser ejecutados en la restauraci\u00f3n de las bases de datos.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\t-- Retornamos los comandos\n\tselect DataBaseName, DataBaseSizeMB, ordercmd, cmd\n\tfrom @Results<\/code><\/pre>\n\n\n\n<p>De esta forma, se obtiene un proceso <strong>r\u00e1pido, autom\u00e1tico y flexible<\/strong> que puede integrarse f\u00e1cilmente en tareas de administraci\u00f3n diaria o en scripts de automatizaci\u00f3n.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>\ud83e\udde9 Ejemplo de uso<\/strong><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>\t-- recogemos en un Json las BBDDs no del sistema\n\t-- Otra opci\u00f3n seria pasar el listado que se quiere\n\tdeclare @databases nvarchar(max) \n\n\tset @databases = (select \n\t\tdatabase_id\n\t\t, name \n\tFROM sys.databases\n\twhere name not in ('master','tempdb','model','msdb')\n\tFOR JSON PATH)\n\t\n\tEXEC master.dbo.xp_GenerateRestoreScripts\n\t\t@ModoRecuperacion  = 3\n\t\t,@backupPath ='C:\\Backups\\'\n\t\t,@Path_MDF_Destination = 'C:\\Restores\\'\n\t\t,@Path_NDF_Destination = 'C:\\Restores\\'\n\t\t,@Path_LDF_Destination = 'C:\\Restores\\'\n\t\t,@dbName_suffix = '_2'\n\t\t,@RecoveryON = 1\n\t\t,@VerboseMode = 1 \n\t\t,@GridResultSet = 1\n\t\t,@ScanSubdirectories = 1\n\t\t,@databases = @databases<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>\ud83d\udcce Recursos<\/strong><\/h2>\n\n\n\n<p>Puedes consultar el c\u00f3digo completo en mi repositorio de <strong>GitHub<\/strong>: [\ud83d\udd17 <a href=\"https:\/\/github.com\/DataCrazyWorld\/DataCrazyWorld\/blob\/main\/Posts\/dbo.xp_GenerateRestoreScripts.sql\" target=\"_blank\" rel=\"noopener\" title=\"\">enlace aqu\u00ed<\/a>]<\/p>\n\n\n\n<p>\ud83d\udc49 En un post futuro integrar\u00e9 este script dentro de scripts de PowerShell para automatizar la restauraci\u00f3n completa de una o N bases de datos.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u00bfQuieres un script que te genere los comandos de restore en funci\u00f3n de los archivos de una carpeta? Aqu\u00ed te explico el funcionamiento de uno y el c\u00f3digo \ud83d\ude09<\/p>\n","protected":false},"author":2,"featured_media":614,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,9],"tags":[21,12],"class_list":["post-676","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver","category-tsql","tag-sqlserver","tag-tsql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/posts\/676","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/comments?post=676"}],"version-history":[{"count":2,"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/posts\/676\/revisions"}],"predecessor-version":[{"id":678,"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/posts\/676\/revisions\/678"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/media\/614"}],"wp:attachment":[{"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/media?parent=676"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/categories?post=676"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datacrazyworld.com\/index.php\/wp-json\/wp\/v2\/tags?post=676"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}