Quick-n-Dirty Restore Generator.

This script is for 2005...

Here is the flowerbox, script is at the link below.

/******************************************************************************
**
**Name: Quick-n-Diry restore script generator.sql
**
**Description: Quickly generate restore script from a backup device.
**
** Generate a restore script with the proper move
**statements automatically.
**
** Change values for @i_db_name -- Database name to restore as.
**@i_backup -- Backup location (Caveat)
**@i_data_location -- Restore to path.
**
** Caveat: If your backup is a multi-file/device backup only
**supply the first filename for @i_backup, however
**you will have to include the additional files/devices
**as part of the FROM DISK clause in the output.
**The script will build the first FROM clause,
**you will just need to add the rest to it manually.
**
**
**Return values: 0 - Success
**-1 - Error
**
**
**Author: G. Rayburn
**
**Date: 01/27/2006
**
*******************************************************************************
**Modification History
*******************************************************************************
**
**Initial Creation: 01/27/2006 G. Rayburn **
*******************************************************************************
**
******************************************************************************/


Script

No problemo, this is the 'port' of one I borrowed from here a while ago. It just took me a bit of time to post it. <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><br />FWIW, this does work on 2000 as well, it's just a little kludgy in the alter table section and requires you to run 1/2 of the script, comment the temptable build/population and then re-run the entire script. I can't for the life of me figure out why 2k complains about the in-line ALTER statement to add the one column.

External link is not accessible. Why don't you paste the script here?

Frank, there's a hyperlink to the actual script, below the flowerbox, but if you click the hyperlink you get a message "The page cannot be displayed." So jnai is asking if someone can post the actual script here.[<img src='/community/emoticons/emotion-1.gif' alt='' />]

Apologies boys and girls...the webserver has died on that end... <img src='/community/emoticons/emotion-6.gif' alt='' /><br /><br />SET NOCOUNT ON<br />GO<br />/******************************************************************************<br />**<br />**Name: Quick-n-Dirty restore script generator.sql<br />**<br />**Description: Quickly generate restore script from a backup device.<br />**<br />** Generate a restore script with the proper move<br />**statements automatically.<br />**<br />** Change values for @i_db_name -- Database name to restore as.<br />**@i_backup -- Backup location (Caveat)<br />**@i_data_location -- Restore to path.<br />**<br />** Caveat: If your backup is a multi-file/device backup only<br />**supply the first filename for @i_backup, however<br />**you will have to include the additional files/devices<br />**as part of the FROM DISK clause in the output. <br />**The script will build the first FROM clause, <br />**you will just need to add the rest to it manually.<br />**<br />**<br />**Return values: 0 - Success<br />**-1 - Error<br />**<br />**<br />**Author: G. Rayburn<br />**<br />**Date: 01/27/2006<br />**<br />*******************************************************************************<br />**Modification History<br />*******************************************************************************<br />**<br />**Initial Creation: 01/27/2006 G. Rayburn &lt;grayburn@collegeloan.com&gt;<br />**<br />*******************************************************************************<br />**<br />******************************************************************************/<br />DECLARE @retcode int<br />, @l_bkup_file_nm varchar(520)<br />, @l_bkup_file_location varchar(520)<br />, @l_index int<br />, @l_exec_stmt varchar(8000)<br />, @l_Old_file_location varchar(520)<br />, @l_New_file_location varchar(520)<br />, @l_file_nm varchar(520)<br />, @l_file_location varchar(520)<br />, @l_LogicalName varchar(256)<br />, @l_exec_stmt_len int<br />, @i_backup varchar(8000)<br />, @i_db_name sysname<br />, @i_data_location varchar(8000)<br /><br /><br />-- Change these variables:<br />SET @i_db_name = 'CadenceScenarios'<br />SET @i_backup = 'D:SQLDUMPCadenceScenarios_2006.03.20_FullBackup.BAK' -- See (Caveat) if your backup is multi-file...<br />SET @i_data_location = 'D:MSSQLDATA'<br /><br /><br />-- Don't change.<br />SET @l_index = 0<br />SET @l_bkup_file_nm = @i_backup<br /><br />-- Verify backup file exists.<br />EXEC master.sys.xp_fileexist @i_backup, @retcode OUTPUT<br />IF (@retcode) = 0<br />BEGIN<br />PRINT @i_backup + ' does not exist, exiting process.'<br />PRINT ''<br />RETURN<br />END<br /><br /><br />-- Get information on logical names and paths from the backup itself.<br />IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE NAME LIKE '#bkup_file_details%' )<br />DROP TABLE #bkup_file_details<br /><br />SET NOCOUNT ON<br /><br />CREATE TABLE #bkup_file_details<br />(<br /> LogicalName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />,Old_PhysicalName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />,[Type] char(1)<br />,FileGroupName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />,[Size] numeric(20,0)<br />,[MaxSize] numeric(20,0)<br />,FileID bigint<br />,CreateLSN numeric(25,0)<br />,DropLSN numeric(25,0) NULL<br />,UniqueID uniqueidentifier<br />,ReadOnlyLSN numeric(25,0)<br />,ReadWriteLSN numeric(25,0)<br />,BackupSizeInByte bigint<br />,SourceBlockSize int<br />,FilegroupID int<br />,LogGroupGUID uniqueidentifier NULL<br />,DifferentialBaseLSN numeric(25,0)<br />,DifferentialbaseGUID uniqueidentifier<br />,IsReadOnly bit<br />,IsPresent bit<br />)<br /><br />-- Get number and names of the files in the backup.<br />SELECT @l_exec_stmt = 'RESTORE FILELISTONLY FROM DISK = ''' + @i_backup + ''''<br /><br />INSERT INTO #bkup_file_details <br />EXEC (@l_exec_stmt)<br /><br />-- Add New_PhysicalName for cursor later on.<br />ALTER TABLE #bkup_file_details<br />ADD New_PhysicalName varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />-- Update New_PhysicalName with the target path.<br />SELECT @retcode = 0<br /><br />DECLARE file_details scroll CURSOR <br />FOR SELECT Old_PhysicalName<br />FROM #bkup_file_details<br /><br />OPEN file_details <br /><br />WHILE @retcode = 0<br />BEGIN<br />FETCH NEXT FROM file_details <br />INTO @l_Old_file_location<br /><br />IF(@@fetch_status &lt;&gt; 0)<br />BREAK<br /><br />SELECT @l_file_nm = @l_Old_file_location<br />SELECT @l_index = charindex('', @l_file_nm)<br /><br />WHILE(@l_index &lt;&gt; 0)<br />BEGIN<br />SELECT @l_file_nm = right(@l_file_nm, datalength(@l_file_nm)- @l_index)<br />SELECT @l_index = charindex('', @l_file_nm)<br />END<br /><br />SELECT @l_New_file_location = @i_data_location + '' + @l_file_nm<br /><br />UPDATE #bkup_file_details<br />SET New_PhysicalName = @l_New_file_location <br />WHERE Old_PhysicalName = @l_Old_file_location<br /><br />END<br /><br />CLOSE file_details <br />DEALLOCATE file_details<br /><br /><br />-- Generate first portion of the restore statement.<br />SELECT @l_exec_stmt = 'RESTORE DATABASE [' + @i_db_name + ']<br /><br />FROM DISK = ''' + @i_backup + ''' <br /><br /> WITH RECOVERY<br />, REPLACE<br />, STATS = 5'<br /><br />-- Generate the MOVE statements and append them to the first.<br />DECLARE file_details scroll CURSOR <br />FOR SELECT LogicalName<br />, New_PhysicalName<br />FROM #bkup_file_details<br /><br />OPEN file_details <br /><br />WHILE @retcode = 0<br />BEGIN<br />FETCH next FROM file_details <br />INTO @l_LogicalName,<br />@l_file_location<br /><br />IF(@@fetch_status &lt;&gt; 0)<br />BREAK<br /><br />SELECT @l_exec_stmt = @l_exec_stmt + '<br />, MOVE ''' + @l_LogicalName + ''' TO ''' + @l_file_location + ''''<br /><br />END<br /><br />CLOSE file_details <br />DEALLOCATE file_details <br /><br />-- DEBUG:<br />PRINT ''<br />PRINT (@l_exec_stmt)<br />PRINT ''<br />

I finally fixed the SQL Server 2000 version of this to run in one batch. ALTER TABLE, sadly cannot be run in an existing batch in 2000 (major bummer, man).<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />SET NOCOUNT ON<br />GO<br />/******************************************************************************<br />**<br />**Name: Quick-n-Diry restore script generator.sql<br />**<br />**Description: Quickly generate restore script from a backup device.<br />**<br />** Generate a restore script with the proper move<br />** statements automatically.<br />**<br />** Change values for @i_db_name -- Database name to restore as.<br />** @i_backup -- Backup location (Caveat)<br />** @i_data_location -- Restore to path.<br />**<br />** Caveat: If your backup is a multi-file/device backup only<br />**supply the first filename for @i_backup, however<br />**you will have to include the additional files/devices<br />**as part of the FROM DISK clause in the output. <br />**The script will build the first FROM clause, <br />**you will just need to add the rest to it manually.<br />**<br />**<br />**Return values: 0 - Success<br />**-1 - Error<br />**<br />**<br />**Author: G. Rayburn<br />**<br />**Date: 01/27/2006<br />**<br />*******************************************************************************<br />**Modification History<br />*******************************************************************************<br />**<br />**Initial Creation: 01/27/2006 G. Rayburn &lt;grayburn@collegeloan.com&gt;<br />** 03/16/2007 G. Rayburn -- Fixed ALTER issue for SQL2000.<br />**<br />*******************************************************************************<br />**<br />******************************************************************************/<br />SET NOCOUNT ON<br />GO<br /><br />DECLARE @retcode int<br />, @l_bkup_file_nm varchar(520)<br />, @l_bkup_file_location varchar(520)<br />, @l_index int<br />, @l_exec_stmt varchar(8000)<br />, @l_Old_file_location varchar(520)<br />, @l_New_file_location varchar(520)<br />, @l_file_nm varchar(520)<br />, @l_file_location varchar(520)<br />, @l_LogicalName varchar(256)<br />, @l_exec_stmt_len int<br />, @i_backup varchar(8000)<br />, @i_db_name sysname<br />, @i_data_location varchar(8000)<br /><br /><br />-- Change these variables:<br />SET @i_db_name = 'Foo_DB<br />SET @i_backup = '\FOOSERVERSQLDUMPFoo_DB.BAK' -- See (Caveat) if your backup is multi-file...<br />SET @i_data_location = 'D:MSSQLData'<div align="left"></div id="left"><br /><br /><br />-- Don't change.<br />SET @l_index = 0<br />SET @l_bkup_file_nm = @i_backup<br /><br />-- Verify backup file exists.<br />EXEC master..xp_fileexist @i_backup, @retcode OUTPUT<br />IF (@retcode) = 0<br />BEGIN<br />PRINT @i_backup + ' does not exist, exiting process.'<br />PRINT ''<br />RETURN<br />END<br /><br /><br />-- Get information on logical names and paths from the backup itself.<br />IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE NAME LIKE '##bkup_file_details_00%' )<br />DROP TABLE ##bkup_file_details_00<br /><br />CREATE TABLE ##bkup_file_details_00<br />(<br /> LogicalName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />,Old_PhysicalName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />,[Type] char(1)<br />,FileGroupName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />,[Size] numeric(20,0)<br />,[MaxSize] numeric(20,0)<br />)<br /><br />-- Get number and names of the files in the backup.<br />SELECT @l_exec_stmt = 'RESTORE FILELISTONLY FROM DISK = ''' + @i_backup + ''''<br /><br /> INSERT INTO ##bkup_file_details_00<br />EXEC (@l_exec_stmt);<br /><br /><br />-- Add New_PhysicalName for cursor later on.<br />-- Use secondary table, as 2000 cannot ALTER in a batch.<br />IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE NAME LIKE '##bkup_file_details%' )<br />DROP TABLE ##bkup_file_details<br /><br /><br />CREATE TABLE ##bkup_file_details<br />(<br /> LogicalName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />,Old_PhysicalName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />,[Type] char(1)<br />,FileGroupName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />,[Size] numeric(20,0)<br />,[MaxSize] numeric(20,0)<br />, New_PhysicalName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />)<br /><br />INSERT INTO ##bkup_file_details<br />SELECT<br />LogicalName<br />, Old_PhysicalName<br />, [Type]<br />, FileGroupName<br />, [Size]<br />, [MaxSize]<br />, NULL<br />FROM ##bkup_file_Details_00<br /><br /><br />-- Update New_PhysicalName with the target path.<br />SELECT @retcode = 0<br /><br />DECLARE file_details scroll CURSOR <br />FOR SELECT Old_PhysicalName<br />FROM ##bkup_file_details<br /><br />OPEN file_details <br /><br />WHILE @retcode = 0<br />BEGIN<br />FETCH NEXT FROM file_details <br />INTO @l_Old_file_location<br /><br />IF(@@fetch_status &lt;&gt; 0)<br />BREAK<br /><br />SELECT @l_file_nm = @l_Old_file_location<br />SELECT @l_index = charindex('', @l_file_nm)<br /><br />WHILE(@l_index &lt;&gt; 0)<br />BEGIN<br />SELECT @l_file_nm = right(@l_file_nm, datalength(@l_file_nm)- @l_index)<br />SELECT @l_index = charindex('', @l_file_nm)<br />END<br /><br />SELECT @l_New_file_location = @i_data_location + '' + @l_file_nm<br /><br />UPDATE ##bkup_file_details<br />SET New_PhysicalName = @l_New_file_location <br />WHERE Old_PhysicalName = @l_Old_file_location<br /><br />END<br /><br />CLOSE file_details <br />DEALLOCATE file_details<br /><br /><br />-- Generate first portion of the restore statement.<br />SELECT @l_exec_stmt = 'RESTORE DATABASE [' + @i_db_name + ']<br /><br />FROM DISK = ''' + @i_backup + ''' <br /><br /> WITH RECOVERY<br />, REPLACE<br />, STATS = 5'<br /><br />-- Generate the MOVE statements and append them to the first.<br />DECLARE file_details scroll CURSOR <br />FOR SELECT LogicalName<br />, New_PhysicalName<br />FROM ##bkup_file_details<br /><br />OPEN file_details <br /><br />WHILE @retcode = 0<br />BEGIN<br />FETCH next FROM file_details <br />INTO @l_LogicalName,<br />@l_file_location<br /><br />IF(@@fetch_status &lt;&gt; 0)<br />BREAK<br /><br />SELECT @l_exec_stmt = @l_exec_stmt + '<br />, MOVE ''' + @l_LogicalName + ''' TO ''' + @l_file_location + ''''<br /><br />END<br /><br />CLOSE file_details <br />DEALLOCATE file_details <br /><br />-- DEBUG:<br />PRINT ''<br />PRINT (@l_exec_stmt)<br />PRINT ''<br /><br /><br />DROP TABLE ##bkup_file_details<br />DROP TABLE ##bkup_file_details_00<br /></font id="code"></pre id="code">