SQL Server Performance

Quick-n-Dirty Restore Generator.

Discussion in 'Contribute Your SQL Server Scripts' started by Haywood, Apr 24, 2006.

  1. Haywood New Member

    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
  2. Luis Martin Moderator

    Thanks for share.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Haywood New Member

    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.
  4. jnai New Member

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

    quote:Originally posted by jnai

    External link is not accessible. Why don't you paste the script here?
    ??? Is this related to this thread here?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  6. Adriaan New Member

    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=':)' />]
  7. FrankKalis Moderator

  8. FrankKalis Moderator

    Doesn't seem to work either. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  9. Haywood New Member

    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 />
  10. Haywood New Member

    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">

Share This Page