with script norecovery name how from different bak sql-server tsql database-restore

script - Restauración de SQL Server totalmente automatizada



script restore database sql server 2000 (10)

Aquí está el proceso de restauración T-SQL restaurado completamente automatizado. Acepta tres (3) parámetros.

  1. Base de datos objetivo
  2. Base de datos fuente
  3. Ubicación del nombre de archivo de copia de seguridad totalmente calificado
    ( //yourserver/yourshare/backupfile.bak o simplemente c:/backup.bak )


CREATE PROC [dbo].[restoreDB] @p_strDBNameTo SYSNAME, @p_strDBNameFrom SYSNAME, @p_strFQNRestoreFileName VARCHAR(255) AS DECLARE @v_strDBFilename VARCHAR(100), @v_strDBLogFilename VARCHAR(100), @v_strDBDataFile VARCHAR(100), @v_strDBLogFile VARCHAR(100), @v_strExecSQL NVARCHAR(1000), @v_strExecSQL1 NVARCHAR(1000), @v_strMoveSQL NVARCHAR(4000), @v_strREPLACE NVARCHAR(50), @v_strTEMP NVARCHAR(1000), @v_strListSQL NVARCHAR(4000), @v_strServerVersion NVARCHAR(20) SET @v_strREPLACE = '''' IF exists (select name from sys.databases where name = @p_strDBNameTo) SET @v_strREPLACE = '', REPLACE'' SET @v_strListSQL = '''' SET @v_strListSQL = @v_strListSQL + ''IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''##FILE_LIST''''))'' SET @v_strListSQL = @v_strListSQL + ''BEGIN'' SET @v_strListSQL = @v_strListSQL + '' DROP TABLE ##FILE_LIST '' SET @v_strListSQL = @v_strListSQL + ''END '' SET @v_strListSQL = @v_strListSQL + ''CREATE TABLE ##FILE_LIST ('' SET @v_strListSQL = @v_strListSQL + '' LogicalName VARCHAR(64),'' SET @v_strListSQL = @v_strListSQL + '' PhysicalName VARCHAR(130),'' SET @v_strListSQL = @v_strListSQL + '' [Type] VARCHAR(1),'' SET @v_strListSQL = @v_strListSQL + '' FileGroupName VARCHAR(64),'' SET @v_strListSQL = @v_strListSQL + '' Size DECIMAL(20, 0),'' SET @v_strListSQL = @v_strListSQL + '' MaxSize DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' FileID bigint,'' SET @v_strListSQL = @v_strListSQL + '' CreateLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' DropLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' UniqueID UNIQUEIDENTIFIER,'' SET @v_strListSQL = @v_strListSQL + '' ReadOnlyLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' ReadWriteLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' BackupSizeInBytes DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' SourceBlockSize INT,'' SET @v_strListSQL = @v_strListSQL + '' filegroupid INT,'' SET @v_strListSQL = @v_strListSQL + '' loggroupguid UNIQUEIDENTIFIER,'' SET @v_strListSQL = @v_strListSQL + '' differentialbaseLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' differentialbaseGUID UNIQUEIDENTIFIER,'' SET @v_strListSQL = @v_strListSQL + '' isreadonly BIT,'' SET @v_strListSQL = @v_strListSQL + '' ispresent BIT'' SELECT @v_strServerVersion = CAST(SERVERPROPERTY (''PRODUCTVERSION'') AS NVARCHAR) IF @v_strServerVersion LIKE ''10.%'' BEGIN SET @v_strListSQL = @v_strListSQL + '', TDEThumbpr DECIMAL'' --PRINT @v_strServerVersion END SET @v_strListSQL = @v_strListSQL + '')'' EXEC (@v_strListSQL) INSERT INTO ##FILE_LIST EXEC (''RESTORE FILELISTONLY FROM DISK = '''''' + @p_strFQNRestoreFileName + '''''''') DECLARE curFileLIst CURSOR FOR SELECT ''MOVE N'''''' + LogicalName + '''''' TO N'''''' + replace(PhysicalName, @p_strDBNameFrom, @p_strDBNameTo) + '''''''' FROM ##FILE_LIST SET @v_strMoveSQL = '''' OPEN curFileList FETCH NEXT FROM curFileList into @v_strTEMP WHILE @@Fetch_Status = 0 BEGIN SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + '', '' FETCH NEXT FROM curFileList into @v_strTEMP END CLOSE curFileList DEALLOCATE curFileList PRINT ''Killing active connections to the "'' + @p_strDBNameTo + ''" database'' -- Create the sql to kill the active database connections SET @v_strExecSQL = '''' SELECT @v_strExecSQL = @v_strExecSQL + ''kill '' + CONVERT(CHAR(10), spid) + '' '' FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid EXEC (@v_strExecSQL) PRINT ''Restoring "'' + @p_strDBNameTo + ''" database from "'' + @p_strFQNRestoreFileName + ''" with '' PRINT '' data file "'' + @v_strDBDataFile + ''" located at "'' + @v_strDBFilename + ''"'' PRINT '' log file "'' + @v_strDBLogFile + ''" located at "'' + @v_strDBLogFilename + ''"'' SET @v_strExecSQL = ''RESTORE DATABASE ['' + @p_strDBNameTo + '']'' SET @v_strExecSQL = @v_strExecSQL + '' FROM DISK = '''''' + @p_strFQNRestoreFileName + '''''''' SET @v_strExecSQL = @v_strExecSQL + '' WITH FILE = 1,'' SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL SET @v_strExecSQL = @v_strExecSQL + '' NOREWIND, '' SET @v_strExecSQL = @v_strExecSQL + '' NOUNLOAD '' SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE --PRINT ''---------------------------'' --PRINT @v_strExecSQL --PRINT ''---------------------------'' EXEC sp_executesql @v_strExecSQL

No soy muy fluido con los comandos de SQL Server.

Necesito un script para restaurar una base de datos desde un archivo .bak y mover los archivos logical_data y logical_log a una ruta específica.

Puedo hacer:

restore filelistonly from disk=''D:/backups/my_backup.bak''

Esto me dará un conjunto de resultados con una columna LogicalName , a continuación, necesito usar los nombres lógicos del conjunto de resultados en el comando de restauración:

restore database my_db_name from disk=''d:/backups/my_backups.bak'' with file=1, move ''logical_data_file'' to ''d:/data/mydb.mdf'', move ''logical_log_file'' to ''d:/data/mylog.ldf''

¿Cómo puedo capturar los nombres lógicos del primer conjunto de resultados en variables que se pueden suministrar al comando "mover"?

Creo que la solución puede ser trivial, pero soy bastante nuevo en SQL Server.


no está seguro de cómo añadir comentarios debajo de una solución particular, pero sólo he implementado la solución aportada por encima Mevdiven ...

Hay un pequeño problema con la tabla de la gota en mi entorno (Servidor 08 r2). He tenido que modificar esto para usar el identificador de objeto con el fin de colocar correctamente.

También he tenido problemas debido a un gran número de particiones en el archivo de copia de seguridad, así que tuve que cambiar la cadena para ser nvarchar (MAX).

También he añadido la capacidad para restaurar la base de datos en otro directorio (como nuestro dev vs entornos prod tiene diferentes caminos)

CREATE PROC [dbo].[restoreDB] @p_strDBNameTo SYSNAME, @p_strDBNameFrom SYSNAME, @p_strBackupDirectory VARCHAR(255), @p_strRestoreDirectory VARCHAR(255), @p_strFQNBackupFileName VARCHAR(255) AS DECLARE @v_strDBFilename VARCHAR(200), @v_strDBLogFilename VARCHAR(200), @v_strDBDataFile VARCHAR(200), @v_strDBLogFile VARCHAR(200), @v_strExecSQL NVARCHAR(MAX), @v_strMoveSQL NVARCHAR(MAX), @v_strREPLACE NVARCHAR(50), @v_strTEMP NVARCHAR(1000), @v_strListSQL NVARCHAR(4000), @v_strServerVersion NVARCHAR(20) SET @v_strREPLACE = '''' IF exists (select name from sys.databases where name = @p_strDBNameTo) SET @v_strREPLACE = '', REPLACE'' SET @v_strListSQL = '''' SET @v_strListSQL = @v_strListSQL + ''IF OBJECT_ID(''''tempdb..##FILE_LIST'''') IS NOT NULL DROP TABLE ##FILE_LIST '' SET @v_strListSQL = @v_strListSQL + ''CREATE TABLE ##FILE_LIST ('' SET @v_strListSQL = @v_strListSQL + '' LogicalName VARCHAR(64),'' SET @v_strListSQL = @v_strListSQL + '' PhysicalName VARCHAR(130),'' SET @v_strListSQL = @v_strListSQL + '' [Type] VARCHAR(1),'' SET @v_strListSQL = @v_strListSQL + '' FileGroupName VARCHAR(64),'' SET @v_strListSQL = @v_strListSQL + '' Size DECIMAL(20, 0),'' SET @v_strListSQL = @v_strListSQL + '' MaxSize DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' FileID bigint,'' SET @v_strListSQL = @v_strListSQL + '' CreateLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' DropLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' UniqueID UNIQUEIDENTIFIER,'' SET @v_strListSQL = @v_strListSQL + '' ReadOnlyLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' ReadWriteLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' BackupSizeInBytes DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' SourceBlockSize INT,'' SET @v_strListSQL = @v_strListSQL + '' filegroupid INT,'' SET @v_strListSQL = @v_strListSQL + '' loggroupguid UNIQUEIDENTIFIER,'' SET @v_strListSQL = @v_strListSQL + '' differentialbaseLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' differentialbaseGUID UNIQUEIDENTIFIER,'' SET @v_strListSQL = @v_strListSQL + '' isreadonly BIT,'' SET @v_strListSQL = @v_strListSQL + '' ispresent BIT'' SELECT @v_strServerVersion = CAST(SERVERPROPERTY (''PRODUCTVERSION'') AS NVARCHAR) IF @v_strServerVersion LIKE ''10.%'' BEGIN SET @v_strListSQL = @v_strListSQL + '', TDEThumbpr DECIMAL'' --PRINT @v_strServerVersion END SET @v_strListSQL = @v_strListSQL + '')'' EXEC (@v_strListSQL) INSERT INTO ##FILE_LIST EXEC (''RESTORE FILELISTONLY FROM DISK = '''''' + @p_strFQNBackupFileName + '''''''') DECLARE curFileLIst CURSOR FOR SELECT ''MOVE N'''''' + LogicalName + '''''' TO N'''''' + replace(replace(PhysicalName, @p_strDBNameFrom, @p_strDBNameTo), @p_strBackupDirectory, @p_strRestoreDirectory) + '''''''' FROM ##FILE_LIST SET @v_strMoveSQL = cast('''' as nvarchar(max)) OPEN curFileList FETCH NEXT FROM curFileList into @v_strTEMP WHILE @@Fetch_Status = 0 BEGIN SET @v_strMoveSQL = @v_strMoveSQL + cast(@v_strTEMP as nvarchar(max)) + cast('', '' as nvarchar(max)) FETCH NEXT FROM curFileList into @v_strTEMP END CLOSE curFileList DEALLOCATE curFileList PRINT ''Killing active connections to the "'' + @p_strDBNameTo + ''" database'' -- Create the sql to kill the active database connections SET @v_strExecSQL = '''' SELECT @v_strExecSQL = @v_strExecSQL + ''kill '' + CONVERT(CHAR(10), spid) + '' '' FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid EXEC (@v_strExecSQL) PRINT ''Restoring "'' + @p_strDBNameTo + ''" database from "'' + @p_strFQNBackupFileName + ''" with '' PRINT '' data file "'' + @v_strDBDataFile + ''" located at "'' + @v_strDBFilename + ''"'' PRINT '' log file "'' + @v_strDBLogFile + ''" located at "'' + @v_strDBLogFilename + ''"'' SET @v_strExecSQL = cast(''RESTORE DATABASE ['' as nvarchar(max)) + cast(@p_strDBNameTo as nvarchar(max)) + cast('']'' as nvarchar(max)) SET @v_strExecSQL = @v_strExecSQL + cast('' FROM DISK = '''''' as nvarchar(max)) + cast(@p_strFQNBackupFileName as nvarchar(max)) + cast('''''''' as nvarchar(max)) SET @v_strExecSQL = @v_strExecSQL + cast('' WITH FILE = 1,'' as nvarchar(max)) SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL SET @v_strExecSQL = @v_strExecSQL + cast('' NOREWIND, '' as nvarchar(max)) SET @v_strExecSQL = @v_strExecSQL + cast('' NOUNLOAD '' as nvarchar(max)) SET @v_strExecSQL = @v_strExecSQL + cast(@v_strREPLACE as nvarchar(max)) --If want to print string need to do in sections due to limitation of print string length PRINT ''Exec string: '' +cast(len(@v_strExecSQL) as nvarchar(max))+ '' ***:'' PRINT substring(@v_strExecSQL,0,3999) PRINT substring(@v_strExecSQL,4000,7999) PRINT substring(@v_strExecSQL,8000,11999) PRINT substring(@v_strExecSQL,12000,15999) PRINT substring(@v_strExecSQL,16000,19999) PRINT substring(@v_strExecSQL,20000,23999) PRINT substring(@v_strExecSQL,24000,27999) PRINT substring(@v_strExecSQL,28000,31999) PRINT substring(@v_strExecSQL,32000,35999) EXEC sp_executesql @v_strExecSQL GO



Utilizando

como referencias, se me ocurrió esto ... y creo que funciona (no probado para copias de seguridad con múltiples archivos)

DECLARE @FileList TABLE ( LogicalName nvarchar(128) NOT NULL, PhysicalName nvarchar(260) NOT NULL, Type char(1) NOT NULL, FileGroupName nvarchar(120) NULL, Size numeric(20, 0) NOT NULL, MaxSize numeric(20, 0) NOT NULL, FileID bigint NULL, CreateLSN numeric(25,0) NULL, DropLSN numeric(25,0) NULL, UniqueID uniqueidentifier NULL, ReadOnlyLSN numeric(25,0) NULL , ReadWriteLSN numeric(25,0) NULL, BackupSizeInBytes bigint NULL, SourceBlockSize int NULL, FileGroupID int NULL, LogGroupGUID uniqueidentifier NULL, DifferentialBaseLSN numeric(25,0)NULL, DifferentialBaseGUID uniqueidentifier NULL, IsReadOnly bit NULL, IsPresent bit NULL, TDEThumbprint varbinary(32) NULL ); declare @RestoreStatement nvarchar(max), @BackupFile nvarchar(max); set @BackupFile = ''D:/mybackup.bak'' SET @RestoreStatement = N''RESTORE FILELISTONLY FROM DISK=N'''''' + @BackupFile + '''''''' INSERT INTO @FileList EXEC(@RestoreStatement); declare @logical_data nvarchar(max), @logical_log nvarchar(max); set @logical_data = (select LogicalName from @FileList where Type = ''D'' and FileID = 1) set @logical_log = (select LogicalName from @FileList where Type = ''L'' and FileID = 2)


esto podría ayudar. Quería crear una secuencia de comandos para un cliente con las variables mínimas necesarias para establecer múltiples copias de seguridad de la base de datos restauradas desde una única ubicación. Traté de evitar el uso de SQL dinámico porque creo que todo se pone un poco complicado.

-- Use VARCHAR as the restore statement doesn''t like NVARCHAR DECLARE @data_file_path VARCHAR(512), @log_file_path VARCHAR(512), @backup_path VARCHAR(512), @backup_extension VARCHAR(4), @mdf_extension VARCHAR(4), @ldf_extension VARCHAR(4) -- ** VARIABLES THAT MUST BE SET **-- SET @data_file_path = ''E:/DataPath/' SET @log_file_path = ''F:/LogPath'' SET @backup_path = ''B:/BackUpPath'' -- **----------------------------**-- SET @backup_extension = ''.bak'' SET @mdf_extension = ''.mdf'' SET @ldf_extension = ''.ldf'' DECLARE @DATABASES_TO_RESTORE TABLE (rownum int IDENTITY (1, 1) PRIMARY KEY NOT NULL, backup_name VARCHAR(64), restore_as VARCHAR(64)) -- ** Declare the Databases to be Restored ** -- INSERT INTO @DATABASES_TO_RESTORE SELECT ''Intranet'', ''Intranet_Test'' UNION SELECT ''TestAudit'', ''TestAudit_Test'' -- ** -------------------------------------** -- DECLARE @max_rows INT, @row_count INT SET @row_count = 1 SELECT @max_rows=count(*) FROM @DATABASES_TO_RESTORE WHILE @row_count <= @max_rows BEGIN DECLARE @backup_name VARCHAR(32), @restore_as VARCHAR(32), @logical_data_name VARCHAR(64), @logical_log_name VARCHAR(64), @data_file_full_path VARCHAR(512), @log_file_full_path VARCHAR(512), @full_backup_path VARCHAR(MAX) SELECT @backup_name = backup_name, @restore_as = restore_as FROM @DATABASES_TO_RESTORE WHERE rownum = @row_count SET @full_backup_path = @backup_path + @backup_name + @backup_extension DECLARE @filelist TABLE (LogicalName NVARCHAR(128) NOT NULL, PhysicalName NVARCHAR(260) NOT NULL, [Type] CHAR(1) NOT NULL, FileGroupName NVARCHAR(120) NULL, Size NUMERIC(20, 0) NOT NULL, MaxSize NUMERIC(20, 0) NOT NULL, FileID BIGINT NULL, CreateLSN NUMERIC(25,0) NULL, DropLSN NUMERIC(25,0) NULL, UniqueID UNIQUEIDENTIFIER NULL, ReadOnlyLSN NUMERIC(25,0) NULL , ReadWriteLSN NUMERIC(25,0) NULL, BackupSizeInBytes BIGINT NULL, SourceBlockSize INT NULL, FileGroupID INT NULL, LogGroupGUID UNIQUEIDENTIFIER NULL, DfferentialBaseLSN NUMERIC(25,0)NULL, DifferentialBaseGUID UNIQUEIDENTIFIER NULL, IsReadOnly BIT NULL, IsPresent BIT NULL, TDEThumbprint VARBINARY(32) NULL) INSERT into @filelist EXEC (''RESTORE FilelistOnly FROM DISK = '''''' + @full_backup_path + '''''''') IF @@ROWCOUNT = 2 BEGIN SELECT @logical_data_name = LogicalName FROM @filelist WHERE [Type] = ''D'' SELECT @logical_log_name = LogicalName FROM @filelist WHERE [Type] = ''L'' SET @data_file_full_path = @data_file_path + @restore_as + @mdf_extension SET @log_file_full_path = @log_file_path + @restore_as + @ldf_extension RESTORE DATABASE @restore_as FROM DISK = @full_backup_path WITH FILE = 1, MOVE @logical_data_name TO @data_file_full_path, MOVE @logical_log_name TO @log_file_full_path END ELSE PRINT ''CANNOT RESTORE DATABASE '' + @restore_as + '' THE BACKUP CONTAINS MORE THAN 1 BACKUP SET'' SELECT @row_count = @row_count + 1 END


/* Automate restore w/o needing to know the logical file names. Specify destination database name, database backup source filename and .MDF, .LDF and .NDF directories. I do nightly automated database restores, and I''ve been using this code for about a month. Works for sql server 2008, might work for 2005. Created by wtm 5/27/2010 */ -- BEGIN - MODIFY THIS CODE - create a blank db if not exists(select * from master.sys.databases where [name]=''sc'') begin create database sc end go -- END - MODIFY THIS CODE - create a blank db declare @strDatabase varchar(130)=''sc'' -- MODIFY THIS LINE - db name declare @strBackupFile varchar(500)=''c:/docs/db-backups/sc.bak'' -- MODIFY THIS LINE - source db backup file declare @strRestoreMDFFilesTo varchar(500)=''c:/docs/sqldata/' -- MODIFY THIS LINE - destination restore directory for main files declare @strRestoreLDFFilesTo varchar(500)=''c:/docs/sqldata/' -- MODIFY THIS LINE - destination restore directory for tlog files declare @strRestoreNDFFilesTo varchar(500)=''c:/docs/sqldata/' -- MODIFY THIS LINE - destination restore directory for non-main files -- other variables used declare @strSQL nvarchar(max) declare @strOriginalPhysicalName varchar(150) declare @strPhysicalName varchar(150) declare @strLogicalName varchar(150) declare @intReturn int -- begin restoring begin try drop table #tmpFilelist end try begin catch end catch create table #tmpFilelist ( LogicalName varchar(64), PhysicalName varchar(130), [Type] varchar(1), FileGroupName varchar(64), Size decimal(20, 0) ,MaxSize decimal(25, 0), FileID bigint, CreateLSN decimal(25,0), DropLSN decimal(25,0), UniqueID uniqueidentifier ,ReadOnlyLSN decimal(25,0), ReadWriteLSN decimal(25,0), BackSizeInBytes decimal(25,0), SourceBlockSize int ,filegroupid int, loggroupguid uniqueidentifier, differentialbaseLSN decimal(25,0), differentialbaseGUID uniqueidentifier ,isreadonly bit, ispresent bit, TDEThumbpr decimal ) if not exists(select * from sc.sys.tables) or exists(select * from sc.sys.tables where [name]=''not-an-original-table'') -- MODIFY THIS LINE - business logic to see if we need to restore the database at all begin print ''Restoring ''+@strDatabase+'' db ...'' use master exec msdb.dbo.sp_delete_database_backuphistory @database_name = @strDatabase use [master] exec(''alter database ''+@strDatabase+'' set single_user with rollback immediate'') use [master] exec(''drop database ''+@strDatabase) insert into #tmpFilelist exec(''restore filelistonly from disk = ''''''+@strBackupFile+'''''''') set @strSQL=''restore database [''+@strDatabase+''] from disk=''''''+@strBackupFile+'''''' with '' set @strSQL=@strSQL+ ''file=1 '' set @strSQL=@strSQL+ '',nounload '' set @strSQL=@strSQL+ '',replace '' set @strSQL=@strSQL+ '',stats=10 '' -- show restore status every 10% while exists(select * from #tmpFilelist) begin select top 1 @strOriginalPhysicalName=PhysicalName, @strLogicalName=LogicalName from #tmpFilelist set @strPhysicalName=@strOriginalPhysicalName set @strPhysicalName=reverse(@strPhysicalName) set @strPhysicalName=left(@strPhysicalName, charindex(''/', @strPhysicalName)-1) set @strPhysicalName=reverse(@strPhysicalName) set @strPhysicalName=replace(@strPhysicalName, ''.'', ''_''+@strDatabase+''.'') if @strPhysicalName like ''%.mdf'' set @strPhysicalName=@strRestoreMDFFilesTo+@strPhysicalName else if @strPhysicalName like ''%.ldf'' set @strPhysicalName=@strRestoreLDFFilesTo+@strPhysicalName else set @strPhysicalName=@strRestoreNDFFilesTo+@strPhysicalName set @strSQL=@strSQL+ '',move ''''''+@strLogicalName+'''''' to ''''''+@strPhysicalName+'''''' '' delete from #tmpFilelist where PhysicalName=@strOriginalPhysicalName end execute @intReturn=sp_executesql @strSQL end


Tuve el mismo problema, pero en mi entorno tengo muchos archivos de copia de seguridad (copias de seguridad más rápidas) y tuve que restaurar a una ubicación personalizada. Esta consulta obtiene la última información de respaldo completa y restaura la ruta que especifique. Probado en SQL 2005/2008.

SET NOCOUNT ON Declare @BackupFiles varchar(500), @data_file_path VARCHAR(512), @log_file_path VARCHAR(512), @RestoreFileList varchar(2000), @RestoreStatement varchar(3000), @MoveFiles varchar(2000), @DBName varchar(150) DECLARE @filelist TABLE (LogicalName NVARCHAR(128) NOT NULL, PhysicalName NVARCHAR(260) NOT NULL, [Type] CHAR(1) NOT NULL, FileGroupName NVARCHAR(120) NULL, Size NUMERIC(20, 0) NOT NULL, MaxSize NUMERIC(20, 0) NOT NULL, FileID BIGINT NULL, CreateLSN NUMERIC(25,0) NULL, DropLSN NUMERIC(25,0) NULL, UniqueID UNIQUEIDENTIFIER NULL, ReadOnlyLSN NUMERIC(25,0) NULL , ReadWriteLSN NUMERIC(25,0) NULL, BackupSizeInBytes BIGINT NULL, SourceBlockSize INT NULL, FileGroupID INT NULL, LogGroupGUID UNIQUEIDENTIFIER NULL, DfferentialBaseLSN NUMERIC(25,0)NULL, DifferentialBaseGUID UNIQUEIDENTIFIER NULL, IsReadOnly BIT NULL, IsPresent BIT NULL, TDEThumbprint VARBINARY(32) NULL) SET @data_file_path = ''E:/SQLData/' SET @log_file_path = ''E:/SQLLog/' SET @DBName = ''Adventureworks'' --Get last full backup: SELECT @BackupFiles=Coalesce(@BackupFiles + '','', '''') + ''DISK = N''''''+physical_device_name+'''''''' FROM msdb..backupset S JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id WHERE backup_set_id = ( SELECT max(backup_set_id) FROM msdb..backupset S JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id WHERE S.database_name = @DBName and Type = ''D'') SELECT @RestoreFileList= ''RESTORE FILELISTONLY FROM '' + @BackupFiles + '' WITH FILE = 1 '' IF (@@microsoftversion / 0x1000000) & 0xff >= 10 --TDE capability Begin INSERT into @filelist (LogicalName,PhysicalName,Type,FileGroupName,Size,MaxSize,FileID,CreateLSN,DropLSN,UniqueID,ReadOnlyLSN,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,FileGroupID,LogGroupGUID,DfferentialBaseLSN,DifferentialBaseGUID,IsReadOnly,IsPresent,TDEThumbprint) EXEC (@RestoreFileList) End Else Begin INSERT into @filelist (LogicalName,PhysicalName,Type,FileGroupName,Size,MaxSize,FileID,CreateLSN,DropLSN,UniqueID,ReadOnlyLSN,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,FileGroupID,LogGroupGUID,DfferentialBaseLSN,DifferentialBaseGUID,IsReadOnly,IsPresent) EXEC (@RestoreFileList) End --next version, do a count on filename, any >1 put in alternate data/log location. SELECT @MoveFiles=Coalesce(@MoveFiles + '','' , '''') + ''MOVE N'''''' + LogicalName + '''''' to N'''''' + Case When type = ''D'' Then @data_file_path+Right(physicalname, charindex(''/',reverse(physicalname),1)-1) when type = ''L'' Then @log_file_path+Right(physicalname, charindex(''/',reverse(physicalname),1)-1) Else ''Full Text - code not complete'' END +'''''''' From @filelist SELECT @RestoreStatement=''RESTORE DATABASE [AuctionMain] FROM '' + @BackupFiles + '' WITH FILE = 1, '' + @MoveFiles + '', NOUNLOAD, REPLACE, STATS = 20'' Print @RestoreStatement Exec(@RestoreStatement)


Aún otra modificación / implementaciones. Aquí están mis 2 centavos. Modifiqué el script de Mevdiven anteriormente, de modo que restaurará el archivo en el directorio de datos de la base de datos actual. Tengo un problema porque no quiero usar la ubicación definida en el archivo de respaldo.

Agarro el directorio de datos utilizado por el primer archivo maestro

SELECT top(1) @v_strRestorePath = physical_name FROM sys.master_files

Y usar eso como mi ruta de datos de destino.

También encontré que la tabla ## FILE_LIST estaba dando vueltas, así que la dejé cerca del final.

El extra - '' son solo para que el SQL se vea bien en el desbordamiento de la pila

USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID(''[dbo].[restoreDB]'') IS NOT NULL DROP PROC [dbo].[restoreDB] GO CREATE PROC [dbo].[restoreDB] @p_strDBNameTo SYSNAME, @p_strDBNameFrom SYSNAME, @p_strFQNRestoreFileName VARCHAR(255) AS DECLARE @v_strDBFilename VARCHAR(100), @v_strDBLogFilename VARCHAR(100), @v_strDBDataFile VARCHAR(100), @v_strDBLogFile VARCHAR(100), @v_strExecSQL NVARCHAR(1000), @v_strExecSQL1 NVARCHAR(1000), @v_strMoveSQL NVARCHAR(4000), @v_strREPLACE NVARCHAR(50), @v_strTEMP NVARCHAR(1000), @v_strListSQL NVARCHAR(4000), @v_strServerVersion NVARCHAR(20), @v_strRestorePath varchar(500) SET @v_strREPLACE = '''' IF exists (select name from sys.databases where name = @p_strDBNameTo) SET @v_strREPLACE = '', REPLACE'' SET @v_strListSQL = '''' SET @v_strListSQL = @v_strListSQL + ''IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''##FILE_LIST''''))'' SET @v_strListSQL = @v_strListSQL + ''BEGIN'' SET @v_strListSQL = @v_strListSQL + '' DROP TABLE ##FILE_LIST '' SET @v_strListSQL = @v_strListSQL + ''END '' SET @v_strListSQL = @v_strListSQL + ''CREATE TABLE ##FILE_LIST ('' SET @v_strListSQL = @v_strListSQL + '' LogicalName VARCHAR(64),'' SET @v_strListSQL = @v_strListSQL + '' PhysicalName VARCHAR(130),'' SET @v_strListSQL = @v_strListSQL + '' [Type] VARCHAR(1),'' SET @v_strListSQL = @v_strListSQL + '' FileGroupName VARCHAR(64),'' SET @v_strListSQL = @v_strListSQL + '' Size DECIMAL(20, 0),'' SET @v_strListSQL = @v_strListSQL + '' MaxSize DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' FileID bigint,'' SET @v_strListSQL = @v_strListSQL + '' CreateLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' DropLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' UniqueID UNIQUEIDENTIFIER,'' SET @v_strListSQL = @v_strListSQL + '' ReadOnlyLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' ReadWriteLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' BackupSizeInBytes DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' SourceBlockSize INT,'' SET @v_strListSQL = @v_strListSQL + '' filegroupid INT,'' SET @v_strListSQL = @v_strListSQL + '' loggroupguid UNIQUEIDENTIFIER,'' SET @v_strListSQL = @v_strListSQL + '' differentialbaseLSN DECIMAL(25,0),'' SET @v_strListSQL = @v_strListSQL + '' differentialbaseGUID UNIQUEIDENTIFIER,'' SET @v_strListSQL = @v_strListSQL + '' isreadonly BIT,'' SET @v_strListSQL = @v_strListSQL + '' ispresent BIT'' SELECT @v_strServerVersion = CAST(SERVERPROPERTY (''PRODUCTVERSION'') AS NVARCHAR) IF @v_strServerVersion LIKE ''10.%'' BEGIN SET @v_strListSQL = @v_strListSQL + '', TDEThumbpr DECIMAL'' --PRINT @v_strServerVersion END SET @v_strListSQL = @v_strListSQL + '')'' EXEC (@v_strListSQL) -- We want to get the current data path from this server as the backup file paths may not be the same on the server -- especially wehen switching between Express/Standard instances SELECT top(1) @v_strRestorePath = physical_name FROM sys.master_files; set @v_strRestorePath = REPLACE(@v_strRestorePath, RIGHT(@v_strRestorePath, CHARINDEX(''/', REVERSE(@v_strRestorePath))-1),'''') --print @v_strRestorePath --'' INSERT INTO ##FILE_LIST EXEC (''RESTORE FILELISTONLY FROM DISK = '''''' + @p_strFQNRestoreFileName + '''''''') -- want to see whats in the fillist? --SELECT * FROM ##FILE_LIST DECLARE curFileLIst CURSOR FOR -- Here we restore each file to the current server restore path. Right(...) grabs the file name from the back up SELECT ''MOVE N'''''' + LogicalName + '''''' TO N'''''' + @v_strRestorePath + Replace(RIGHT(PhysicalName, CHARINDEX(''/', REVERSE(PhysicalName))-1),@p_strDBNameFrom, @p_strDBNameTo) + '''''''' --'' FROM ##FILE_LIST SET @v_strMoveSQL = '''' OPEN curFileList FETCH NEXT FROM curFileList into @v_strTEMP WHILE @@Fetch_Status = 0 BEGIN SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + '', '' FETCH NEXT FROM curFileList into @v_strTEMP END CLOSE curFileList DEALLOCATE curFileList PRINT ''Killing active connections to the "'' + @p_strDBNameTo + ''" database'' -- Create the sql to kill the active database connections SET @v_strExecSQL = '''' SELECT @v_strExecSQL = @v_strExecSQL + ''kill '' + CONVERT(CHAR(10), spid) + '' '' FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid EXEC (@v_strExecSQL) PRINT ''Restoring "'' + @p_strDBNameTo + ''" database from "'' + @p_strFQNRestoreFileName + ''" with '' PRINT '' data file "'' + @v_strDBDataFile + ''" located at "'' + @v_strDBFilename + ''"'' PRINT '' log file "'' + @v_strDBLogFile + ''" located at "'' + @v_strDBLogFilename + ''"'' SET @v_strExecSQL = ''RESTORE DATABASE ['' + @p_strDBNameTo + '']'' SET @v_strExecSQL = @v_strExecSQL + '' FROM DISK = '''''' + @p_strFQNRestoreFileName + '''''''' SET @v_strExecSQL = @v_strExecSQL + '' WITH FILE = 1,'' SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL SET @v_strExecSQL = @v_strExecSQL + '' NOREWIND, '' SET @v_strExecSQL = @v_strExecSQL + '' NOUNLOAD '' SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE --PRINT ''---------------------------'' --PRINT @v_strExecSQL --PRINT ''---------------------------'' --For Some reason the file list hangs when I was debugging remove it. IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''##FILE_LIST'')) BEGIN DROP TABLE ##FILE_LIST END EXEC sp_executesql @v_strExecSQLter

Espero que esto ayude a alguien también!


Ninguna de las versiones incluye SQL 2000. Esto funcionará en todas ellas:

use master -- -- check SQL Server version DECLARE @sql_ver int; CREATE TABLE #tmp_sql_ver ( [Index] int, [Name] nvarchar(100), [iVal] int, [cVal] nvarchar(100) ) INSERT INTO #tmp_sql_ver EXEC(''xp_msver ProductVersion''); IF (SELECT cast(cVal as char(2)) FROM #tmp_sql_ver) = ''8.'' SET @sql_ver = 8; ELSE SET @sql_ver = 9; DROP TABLE #tmp_sql_ver; -- -- get mdf/ldf names DECLARE @mdf_name varchar(50) DECLARE @ldf_name varchar(50) DECLARE @RestoreFileListOnly_columns varchar(2000) IF (@sql_ver = 8) BEGIN SET @RestoreFileListOnly_columns = '' LogicalName nvarchar(128), PhysicalName nvarchar(260), [Type] char(1), FileGroupName nvarchar(128), [Size] numeric(20,0), [MaxSize] numeric(20,0), '' END ELSE BEGIN SET @RestoreFileListOnly_columns = '' LogicalName nvarchar(128), PhysicalName nvarchar(260), [Type] char(1), FileGroupName nvarchar(128), [Size] numeric(20,0), [MaxSize] numeric(20,0), FileID bigint, CreateLSN numeric(25,0), DropLSN numeric(25,0) NULL, UniqueID uniqueidentifier, ReadOnlyLSN numeric(25,0) NULL, ReadWriteLSN numeric(25,0) NULL, BackupSizeInBytes bigint, SourceBlockSize int, FileGroupID int, LogGroupGUID uniqueidentifier NULL, DifferentialBaseLSN numeric(25,0) NULL, DifferentialBaseGUID uniqueidentifier, IsReadOnly bit, IsPresent bit '' DECLARE @tmp_ver NVARCHAR(20) SELECT @tmp_ver = CAST(SERVERPROPERTY (''PRODUCTVERSION'') AS NVARCHAR) IF @tmp_ver LIKE ''1[01].%'' BEGIN SET @RestoreFileListOnly_columns = @RestoreFileListOnly_columns + '', TDEThumbpr DECIMAL'' END END IF EXISTS (SELECT [table_name] FROM information_schema.tables WHERE [table_name] = ''tmp_RestoreFileListOnly'') BEGIN DROP TABLE [tmp_RestoreFileListOnly]; END EXEC (''CREATE TABLE tmp_RestoreFileListOnly (''+@RestoreFileListOnly_columns+'');''); INSERT INTO tmp_RestoreFileListOnly EXEC(''RESTORE FILELISTONLY FROM DISK = '''''' + @bkpfile + '''''''') PRINT ''RESTORE FILELISTONLY FROM DISK = '''''' + @bkpfile + '''''''' --IF @@ROWCOUNT <> 2 RETURN SELECT @mdf_name = LogicalName FROM tmp_RestoreFileListOnly WHERE Type = ''D'' SELECT @ldf_name = LogicalName FROM tmp_RestoreFileListOnly WHERE Type = ''L'' DROP TABLE tmp_RestoreFileListOnly


RESTORE FILELISTONLY produce un conjunto de resultados que está documentado en MSDN . Luego debe iterar este conjunto de resultados y crear un RESTAURACIÓN ... MOVER ... La forma en que captura e itera el conjunto de resultados depende de su entorno. En una aplicación C # usaría un SqlDataReader. En T-SQL puro usaría INSERT ... EXEC .

El esqueleto de una solución SQL pura sería:

declare @filelist table (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FilegroupName varchar(10), size int, MaxSize bigint, field int, createlsn bit, droplsn bit, uniqueid uniqueidentifier, readonlylsn bit, readwritelsn bit, backupsizeinbytes bigint, sourceblocksize int, filegroupid int, loggroupguid uniqueidentifier, differentialbaselsn bit, differentialbaseguid uniqueidentifier, isreadonly bit, ispresent bit, tdethumbprint varchar(5)); insert into @filelist exec sp_executesql N''restore filelistonly from disk=''''D:/backups/my_backup.bak''''''; set @sql = N''RESTORE database my_database from disk ''''D:/backups/my_backup.bak'''' with ''; select @sql = @sql + N'' move '' + LogicalName + N'' to '' udf_localFilePath(PhysicalName) + N'','' from @filelist; set @sql = substring(@sql, 1, len(@sql)-1); -- remove last '','' exec sp_executesql @sql;

Este no es un código de trabajo real, sino solo para que te hagas una idea. También puede usar un cursor en lugar de la construcción no estándar de asignación-dentro-consulta de @sql

Tenga en cuenta que la lista de columnas en el conjunto de resultados de RESTORE FILELISTONLY difiere entre las versiones de SQL Server. Consulte las especificaciones de la versión de destino para la lista correcta.