Automatically Restores All database ".BAK" Files

  • Hi,

    is there a way to update the script that i will tell where i want to put the LDF and MDF files if i want to separate them?

    THX

  • It is said this will work for .BAK files...can this be used for .dmp files as well and can anyone help me with that...i have 200 db's to migrate to SQL 2005 and was looking for a script to generate the restore db script for me....appreciate any help...ty.

  • Sorry for the late reply 'm so busy in my work. Maybe i can do something to modify the script. What do you want? different location for mdf and ldf but only 2 paths?

    for example

    1 directory for mdf (all mdf files)

    and

    1 directory for ldf (all ldf files)

    "-=Still Learning=-"

    Lester Policarpio

  • vambati (7/30/2008)


    It is said this will work for .BAK files...can this be used for .dmp files as well and can anyone help me with that...i have 200 db's to migrate to SQL 2005 and was looking for a script to generate the restore db script for me....appreciate any help...ty.

    Questions :

    1. Migrate from sql 2000 to sql 2005? The script is not tested for server migration sql 2000 to sql 2005. It is only tested for sql 2000 to sql 2000 and sql 2005 to sql 2005

    2. I think i can tweak the script to accept .dmp files as long as the backup process is a native sql backup.

    3. If you are restoring sql 2005 to sql 2005 server the 2 tables mentioned in the script must be changed with the appropriate table schema for sql 2005 (see posts of pau for the 2 tables schema for 2005)

    "-=Still Learning=-"

    Lester Policarpio

  • Lester Policarpio (7/31/2008)


    Sorry for the late reply 'm so busy in my work. Maybe i can do something to modify the script. What do you want? different location for mdf and ldf but only 2 paths?

    for example

    1 directory for mdf (all mdf files)

    and

    1 directory for ldf (all ldf files)

    yes exactly.one directory for MDF files and other directory for LDF files.

  • avipenina (7/31/2008)


    Lester Policarpio (7/31/2008)


    Sorry for the late reply 'm so busy in my work. Maybe i can do something to modify the script. What do you want? different location for mdf and ldf but only 2 paths?

    for example

    1 directory for mdf (all mdf files)

    and

    1 directory for ldf (all ldf files)

    yes exactly.one directory for MDF files and other directory for LDF files.

    Please try the script below and reply if it fits your need...

    /*************************************************************

    Script made by : Lester A. Policarpio

    Email Address : lpolicarpio2001@yahoo.com

    Date Created : September 03, 2007

    --=UPDATES=--

    January 17, 2008

    - Solved outputed value of the @restoredb variable

    - Solved the "invalid length parameter" issue

    May 6, 2008

    - Removed unused variables

    - Add the headeronly command to accept non sql backup format

    (dbname_db_YYYYMMDDHHMM.BAK)

    - Add more comments

    May 12, 2008

    - Accept Backup Files With Multiple NDFs

    May 23, 2008

    - Solved the problem when RESTORE HEADERONLY produces more than 1 value

    --=LIMITATIONS=--

    - This script is tested for backup files ".BAK" only

    *************************************************************/

    SET NOCOUNT ON

    --Drop Tables if it exists in the database

    if exists (select name from sysobjects where name = 'migration_lester')

    DROP TABLE migration_lester

    if exists (select name from sysobjects where name = 'header_lester')

    DROP TABLE header_lester

    if exists (select name from sysobjects where name = 'cmdshell_lester')

    DROP TABLE cmdshell_lester

    --Create Tables

    --(cmdshell_lester table for the cmdshell command)

    --(migration_lester table for the restore filelistonly command)

    --(header_lester table for the restore headeronly command)

    CREATE TABLE cmdshell_lester( fentry varchar(1000))

    CREATE TABLE migration_lester(LogicalName varchar(1024),

    PhysicalName varchar(4000),type char(1),FileGroupName varchar(50),

    size real,MaxSize real)

    CREATE TABLE header_lester (BackupName varchar(50),

    BackupDescription varchar(100),BackupType int,

    ExpirationDate nvarchar(50),Compressed int,Position int,

    DeviceType int,UserName varchar(30),ServerName varchar(30),

    DatabaseName varchar(50),DatabaseVersion int,

    DatabaseCreationDate datetime,BackupSize bigint,FirstLsn binary,

    LastLsn binary,CheckpointLsn binary,DifferentialBasLsn binary,

    BackupStartDate datetime,BackupFinishDate datetime,SortOrder int,

    CodePage int,UnicodeLocaleid int,UnicodeComparisonStyle int,

    CompatibilityLevel int,SoftwareVendorId int,SoftwareVersionMajor int,

    SoftwareVersionMinor int,SoftwareVersionBuild int,

    MachineName varchar(50),Flags int,BindingId nvarchar(50),

    RecoveryForkId nvarchar(50),Collation nvarchar(50))

    --Declare Variables

    DECLARE @path varchar(1024),@restore varchar(1024)

    DECLARE @restoredb varchar(2000),@extension varchar(1024),@newpath_ldf varchar(1024)

    DECLARE @pathension varchar(1024),@newpath_mdf varchar(1024),@header varchar(500)

    --Set Values to the variables

    SET @newpath_mdf = 'D:\' --new path wherein you will put the mdf

    SET @newpath_ldf = 'C:\' --new path wherein you will put the ldf

    SET @path = 'D:\' --Path of the Backup File

    SET @extension = 'BAK'

    SET @pathension = 'dir /OD '+@Path+'*.'+@Extension

    --Insert the value of the command shell to the table

    INSERT INTO cmdshell_lester exec master..xp_cmdshell @pathension

    --Delete non backup files data, delete null values

    DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%'

    DELETE FROM cmdshell_lester WHERE FEntry is NULL

    --Create a cursor to scan all backup files needed to generate the restore script

    DECLARE @migrate varchar(1024)

    DECLARE migrate CURSOR FOR

    select substring(FEntry,40,50) as 'FEntry'from cmdshell_lester

    OPEN migrate

    FETCH NEXT FROM migrate INTO @migrate

    WHILE (@@FETCH_STATUS = 0)BEGIN

    --Added feature to get the dbname of the backup file

    SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@Migrate+''''

    INSERT INTO header_lester exec (@header)

    --Get the names of the mdf and ldf

    set @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''

    INSERT INTO migration_lester EXEC (@restore)

    --Update value of the table to add the new path+mdf/ldf names

    UPDATE migration_lester SET physicalname = reverse(physicalname)

    UPDATE migration_lester SET physicalname =

    substring(physicalname,1,charindex('\',physicalname)-1)

    select * from migration_lester

    UPDATE migration_lester SET physicalname = @newpath_mdf+reverse(physicalname) where type = 'D'

    UPDATE migration_lester SET physicalname = @newpath_ldf+reverse(physicalname) where type = 'L'

    --@@@@@@@@@@@@@@@@@@@@

    --Set a value to the @restoredb variable to hold the restore database script

    IF (select count(*) from migration_lester) = 2

    BEGIN

    SET @restoredb = 'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)

    +' FROM DISK = '+ ''''+@path+@migrate+''''+' WITH MOVE '+''''

    +(select logicalname from migration_lester where type = 'D')+''''

    +' TO '+ ''''+( select physicalname from migration_lester WHERE physicalname like '%mdf%')

    +''''+', MOVE '+''''+ (select logicalname from migration_lester where type = 'L')

    +''''+' TO '+''''+( select physicalname from migration_lester

    WHERE physicalname like '%ldf%')+''''

    print (@restoredb)

    END

    IF (select count(*) from migration_lester) > 2

    BEGIN

    SET @restoredb =

    'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)+

    ' FROM DISK = '+''''+@path+@migrate+''''+'WITH MOVE '

    DECLARE @multiple varchar(1000),@physical varchar(1000)

    DECLARE multiple CURSOR FOR

    Select logicalname,physicalname from migration_lester

    OPEN multiple

    FETCH NEXT FROM multiple INTO @multiple,@physical

    WHILE(@@FETCH_STATUS = 0)

    BEGIN

    SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+''

    FETCH NEXT FROM multiple INTO @multiple,@physical

    END

    CLOSE multiple

    DEALLOCATE multiple

    SET @restoredb = substring(@restoredb,1,len(@restoredb)-5)

    print (@restoredb)

    END

    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

    -- Run print @restoredb first to view the databases to be restored

    -- When ready, run exec (@restoredb)

    -- EXEC (@restoredb)

    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

    --Clear data inside the tables to give way for the next

    --set of informations to be put in the @restoredb variable

    TRUNCATE TABLE migration_lester

    TRUNCATE TABLE header_lester

    FETCH NEXT FROM migrate INTO @migrate

    END

    CLOSE migrate

    DEALLOCATE migrate

    --@@@@@@@@@@@@@@@@@@@

    --Drop Tables

    DROP TABLE migration_lester

    DROP TABLE cmdshell_lester

    DROP TABLE header_lester

    "-=Still Learning=-"

    Lester Policarpio

  • @vambati

    if the conditions below apply i think you can use the script even if the backup is .dmp extension

    1. MSSQL 2000

    2. Backup Process is a "Native SQL Backup"

    - Just change the value in the @extension variable from 'BAK' to ' your extension'

    - also change this syntax

    DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%'

    TO

    DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%your extension%'

    Please reply for the result.......

    "-=Still Learning=-"

    Lester Policarpio

  • @vambati,@avipenina

    Guys any feedback on the answers i gave you???

    "-=Still Learning=-"

    Lester Policarpio

  • Lester,

    Great idea. I need a script that does this. Has it been run/tested against SQL Server 2005? When I run this script it fails b/c the restore headeronly command returns 51 columns, but the work table has significantly fewer columns on it.

    Chris.

  • Sorry for the late reply refer to the previous post in this thread. You will see 1 member posting the 2 table schema of the tables you are saying. Change the 2 tables with the tables provided in the said post and reply your feedback if it is working

    "-=Still Learning=-"

    Lester Policarpio

  • Thanks Lester, I ended up re-writing it so that it would insert into those tables and it worked great.

    Chris.

  • I'm glad it works!!! 😀

    "-=Still Learning=-"

    Lester Policarpio

  • Hi,

    i use the latest script post and i get this error message

    Insert Error: Column name or number of supplied values does not match table definition.

    all my servers are Collation Binary (case sensitive,accent sensitive).

    THX

  • I think that is what causing the error since you are in case sensitive mode. Try changing the created tables with the actual table listed by Microsoft in the web then give it a try

    "-=Still Learning=-"

    Lester Policarpio

  • Hi - thanks for the script Lester, it will save me a lot of time!! This is the version I used to restore from 2000 to 2008 - some slight tweaks of the header_lester and migration_lester tables to match the outputs from the RESTORE HEADONLY and RESTORE FILELISTONLY statements. Also had to change the position of the substring function from 40 to 37 to match what I was getting back from the 'dir /od' command:

    /*************************************************************

    Script made by : Lester A. Policarpio

    Email Address : lpolicarpio2001@yahoo.com

    Date Created : September 03, 2007

    --=UPDATES=--

    January 17, 2008

    - Solved outputed value of the @restoredb variable

    - Solved the "invalid length parameter" issue

    May 6, 2008

    - Removed unused variables

    - Add the headeronly command to accept non sql backup format

    (dbname_db_YYYYMMDDHHMM.BAK)

    - Add more comments

    May 12, 2008

    - Accept Backup Files With Multiple NDFs

    May 23, 2008

    - Solved the problem when RESTORE HEADERONLY produces more than 1 value

    --=LIMITATIONS=--

    - This script is tested for backup files ".BAK" only

    -- SCRIPT NEEDS TO BE ALTERED IF BACKUP EXTENSION IS NOT ".BAK"

    *************************************************************/

    SET NOCOUNT ON

    --Drop Tables if it exists in the database

    if exists (select name from sysobjects where name = 'migration_lester')

    DROP TABLE migration_lester

    if exists (select name from sysobjects where name = 'header_lester')

    DROP TABLE header_lester

    if exists (select name from sysobjects where name = 'cmdshell_lester')

    DROP TABLE cmdshell_lester

    --Create Tables

    --(cmdshell_lester table for the cmdshell command)

    --(migration_lester table for the restore filelistonly command)

    --(header_lester table for the restore headeronly command)

    CREATE TABLE cmdshell_lester( fentry varchar(1000))

    CREATE TABLE migration_lester(

    LogicalName varchar(1024),

    PhysicalName varchar(4000),

    type char(1),

    FileGroupName varchar(50),

    size real,

    MaxSize real,

    FileIDbigint,

    CreateLSNnumeric(25,0),

    DropLSNnumeric(25,0),

    UniqueIDbinary,

    ReadOnlyLSNnumeric(25,0),

    ReadWriteLSNnumeric(25,0),

    BackupSizeInBytesbigint,

    SourceBlockSizeint,

    FileGroupIDint,

    LogGroupGUIDbinary,

    DifferentialBaseLSNnumeric(25,0),

    DifferentialBaseGUIDbinary,

    IsReadOnlybit,

    IsPresentbit,

    TDEThumbprint binary

    )

    CREATE TABLE header_lester (

    BackupName varchar(50),

    BackupDescription varchar(100),

    BackupType int,

    ExpirationDate nvarchar(50),

    Compressed int,

    Position int,

    DeviceType int,

    UserName varchar(30),

    ServerName varchar(30),

    DatabaseName varchar(50),

    DatabaseVersion int,

    DatabaseCreationDate datetime,

    BackupSize bigint,

    FirstLsn binary,

    LastLsn binary,

    CheckpointLsn binary,

    DifferentialBasLsn binary,

    BackupStartDate datetime,

    BackupFinishDate datetime,

    SortOrder int,

    CodePage int,

    UnicodeLocaleid int,

    UnicodeComparisonStyle int,

    CompatibilityLevel int,

    SoftwareVendorId int,

    SoftwareVersionMajor int,

    SoftwareVersionMinor int,

    SoftwareVersionBuild int,

    MachineName varchar(50),

    Flags int,

    BindingId nvarchar(50),

    RecoveryForkId nvarchar(50),

    Collation nvarchar(50),

    FamilyGUID binary,

    HasBulkLoggedData int,

    IsSnapshot int,

    IsReadOnly int,

    IsSingleUser int,

    HasBackupChecksums int,

    IsDamaged int,

    BeginsLogChain int,

    HasIncompleteMetaData int,

    IsForceOffline int,

    IsCopyOnly int,

    FirstRecoveryForkIDbinary,

    ForkPointLSN binary,

    RecoveryModel int,

    DifferentialBaseLSN binary,

    DifferentialBaseGUID binary,

    BackupTypeDescription nvarchar(50),

    BackupSetGUID binary,

    CompressedBackupSize bigint

    )

    --Declare Variables

    DECLARE @path varchar(1024),@restore varchar(1024)

    DECLARE @restoredb varchar(2000),@extension varchar(1024),@newpath_ldf varchar(1024)

    DECLARE @pathension varchar(1024),@newpath_mdf varchar(1024),@header varchar(500)

    --Set Values to the variables

    SET @newpath_mdf = 'D:\DataFiles' --new path wherein you will put the mdf

    SET @newpath_ldf = 'E:\LogFiles' --new path wherein you will put the ldf

    SET @path = 'C:\Backups\' --Path of the Backup File

    SET @extension = 'BAK'

    SET @pathension = 'dir /OD '+@Path+'*.'+@Extension

    --Insert the value of the command shell to the table

    INSERT INTO cmdshell_lester exec master..xp_cmdshell @pathension

    --Delete non backup files data, delete null values

    DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%'

    DELETE FROM cmdshell_lester WHERE FEntry is NULL

    --Create a cursor to scan all backup files needed to generate the restore script

    DECLARE @migrate varchar(1024)

    DECLARE migrate CURSOR FOR

    select substring(FEntry,37,50) as 'FEntry'from cmdshell_lester

    OPEN migrate

    FETCH NEXT FROM migrate INTO @migrate

    WHILE (@@FETCH_STATUS = 0)BEGIN

    --Added feature to get the dbname of the backup file

    SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@Migrate+''''

    INSERT INTO header_lester exec (@header)

    --Get the names of the mdf and ldf

    set @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''

    INSERT INTO migration_lester EXEC (@restore)

    --Update value of the table to add the new path+mdf/ldf names

    UPDATE migration_lester SET physicalname = reverse(physicalname)

    UPDATE migration_lester SET physicalname =

    substring(physicalname,1,charindex('\',physicalname)-1)

    UPDATE migration_lester SET physicalname = @newpath_mdf+reverse(physicalname) where type = 'D'

    UPDATE migration_lester SET physicalname = @newpath_ldf+reverse(physicalname) where type = 'L'

    --@@@@@@@@@@@@@@@@@@@@

    --Set a value to the @restoredb variable to hold the restore database script

    IF (select count(*) from migration_lester) = 2

    BEGIN

    SET @restoredb = 'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)

    +' FROM DISK = '+ ''''+@path+@migrate+''''+' WITH MOVE '+''''

    +(select logicalname from migration_lester where type = 'D')+''''

    +' TO '+ ''''+( select physicalname from migration_lester WHERE physicalname like '%mdf%')

    +''''+', MOVE '+''''+ (select logicalname from migration_lester where type = 'L')

    +''''+' TO '+''''+( select physicalname from migration_lester

    WHERE physicalname like '%ldf%')+''''

    print (@restoredb)

    END

    IF (select count(*) from migration_lester) > 2

    BEGIN

    SET @restoredb =

    'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)+

    ' FROM DISK = '+''''+@path+@migrate+''''+'WITH MOVE '

    DECLARE @multiple varchar(1000),@physical varchar(1000)

    DECLARE multiple CURSOR FOR

    Select logicalname,physicalname from migration_lester

    OPEN multiple

    FETCH NEXT FROM multiple INTO @multiple,@physical

    WHILE(@@FETCH_STATUS = 0)

    BEGIN

    SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+''

    FETCH NEXT FROM multiple INTO @multiple,@physical

    END

    CLOSE multiple

    DEALLOCATE multiple

    SET @restoredb = substring(@restoredb,1,len(@restoredb)-5)

    print (@restoredb)

    END

    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

    -- Run print @restoredb first to view the databases to be restored

    -- When ready, run exec (@restoredb)

    -- EXEC (@restoredb)

    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

    --Clear data inside the tables to give way for the next

    --set of informations to be put in the @restoredb variable

    TRUNCATE TABLE migration_lester

    TRUNCATE TABLE header_lester

    FETCH NEXT FROM migrate INTO @migrate

    END

    CLOSE migrate

    DEALLOCATE migrate

    --@@@@@@@@@@@@@@@@@@@

    --Drop Tables

    DROP TABLE migration_lester

    DROP TABLE cmdshell_lester

    DROP TABLE header_lester

Viewing 15 posts - 31 through 45 (of 79 total)

You must be logged in to reply to this topic. Login to reply