November 15, 2012 at 7:04 am
Hi,
I am trying to automate a restore on a DB backup file. I have found some pretty neat scripts that can do this. They create a DB Restore sproc where you can pass in DB name, source etc but haven't found an example that lets you move the data file locations for the restore.
Can anyone point me the direction of script that will do this?
Thanks
Dave
November 15, 2012 at 7:25 am
I can't imagine it would be too difficult to add an additional parameter to accept "destination directory", and use that with the MOVE option of restore database command. If you post the script, we'll take a look...
November 15, 2012 at 7:37 am
Heres a copy of the script ideally i would like to create parameters for the folder locations of Data and Log file paths
ALTER 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply