Restore multiple db's to new server with new file locations

  • Steven Webster-494809

    SSC Enthusiast

    Points: 199

    Comments posted to this topic are about the item Restore multiple db's to new server with new file locations

  • Kurt W. Zimmerman

    SSCrazy Eights

    Points: 8674

    These are the kinds of utilities I tend to create over and over due to ever changing requirements. Case in point, I had to rebuild a mirror server from scratch and create the databases in the same sequence as the principal server. I had about 30 some odd databases to place out there. I was able to create the databases from backups that resided on a separate file server which is used for housing my backups from a variety of servers.

    The job took over 5 hours to run but it did the job. What the job did was simply create a script which I executed on the mirror server. I ran it for a specified database backup date, in this case 9/4. Again, may not be the best solution, but it did the job and all is well.

    Here is a sample of my code:

    set nocount on

    declare @Database_id int

    declare @Database_Name varchar(500)

    declare @FileName_Data varchar(500)

    declare @FileName_Log varchar(500)

    declare @Logical_Data_Name varchar(100)

    declare @Logical_Log_Name varchar(100)

    declare @DB_File_Name varchar(100)

    declare @ID int

    declare @CRLF char(2) = char(13) + char(10)

    declare @BackupPath varchar(max) = '\\<someserver>\<someshare>\<somefolder>'

    declare @FullyQualifiedPath varchar(max)

    declare @DirectoryCmd varchar(8000)

    declare @TodayDate varchar(10)

    declare @ProcessDate datetime = '09/04/2013'

    declare @SQL varchar(max)

    set @TodayDate = cast(YEAR(@ProcessDate) as varchar) + '_' + right('0' + cast(MONTH(@ProcessDate) as varchar),2) + '_' + right('0' + cast(DAY(@ProcessDate) as varchar),2)

    declare @databases table (ID int primary key identity (1,1)

    ,database_id int

    ,Database_Name varchar(500)

    ,Logical_Data_Name varchar(500)

    ,FileName_Data varchar(500)

    ,Logical_Log_Name varchar(500)

    ,FileName_Log varchar(500)

    ,DB_File_Name varchar(500)

    )

    create table #FileNames (Physical_Name varchar(500)

    ,Logical_Name varchar(500)

    ,type_desc varchar(5))

    declare @DatabaseBackups table (database_id int

    ,BackupFileName varchar(max))

    insert into @databases (database_id, Database_Name)

    select database_id, name as Database_Name

    from sys.databases

    where database_id > 4

    order by database_id

    declare Database_Cursor cursor for

    select ID

    , database_id

    , Database_name

    from @databases

    open Database_Cursor

    fetch next from Database_Cursor into @ID, @Database_id, @Database_Name

    while @@FETCH_STATUS = 0

    begin

    set @SQL = 'delete from #FileNames;' + @CRLF

    set @SQL += 'use [' + @Database_Name + ']; ' + @CRLF

    set @SQL += 'insert into #FileNames(Physical_Name, Logical_Name, type_desc)' + @CRLF

    set @SQL += 'select physical_name, name, type_desc from sys.database_files' + @CRLF

    exec (@SQL)

    update @databases

    set FileName_Data = rows.Physical_Name

    ,Logical_Data_Name = rows.Logical_Name

    ,FileName_Log = logs.Physical_Name

    ,Logical_Log_Name = logs.Logical_Name

    from @databases db inner join

    #FileNames rows on rows.type_desc = 'ROWS'

    and db.database_id = @Database_id inner join

    #FileNames logs on logs.type_desc = 'LOG'

    and db.Database_id = @Database_id

    set @FullyQualifiedPath = @BackupPath + '\' + @Database_Name

    set @DirectoryCmd = 'dir /b /o:-d ' + @FullyQualifiedPath + '\*.bak'

    delete from @DatabaseBackups

    insert into @DatabaseBackups (BackupFileName)

    exec xp_cmdshell @DirectoryCmd

    delete from @DatabaseBackups

    where BackupFileName is null or

    charindex(@TodayDate, BackupFileName) = 0

    update @DatabaseBackups

    set database_id = @Database_id

    update @databases

    set DB_File_Name = DBUP.BackupFileName

    from @databases DB inner join

    @DatabaseBackups DBUP on DB.database_id = DBUP.database_id

    fetch next from Database_Cursor into @ID, @Database_id, @Database_Name

    end

    close Database_Cursor

    deallocate Database_Cursor

    drop table #FileNames

    select * from @databases

    declare RestoreDatabase_Cursor cursor for

    select Database_Name

    ,Logical_Data_Name

    ,FileName_Data

    ,Logical_Log_Name

    ,FileName_Log

    ,DB_File_Name

    from @databases

    open RestoreDatabase_Cursor

    fetch next from RestoreDatabase_Cursor into @Database_Name, @Logical_Data_Name, @FileName_Data, @Logical_Log_Name, @FileName_Log, @DB_File_Name

    while @@FETCH_STATUS = 0

    begin

    set @FullyQualifiedPath = @BackupPath + '\' + @Database_Name + '\' + @DB_File_Name

    set @SQL = 'RESTORE DATABASE [' + @Database_Name + ']' + @CRLF

    set @SQL += 'FROM DISK = N''' + @FullyQualifiedPath + '''' + @CRLF

    set @SQL += 'WITH FILE = 1' + @CRLF

    set @SQL += ', MOVE N''' + @Logical_Log_Name + ''' TO N''' + @FileName_Log + '''' + @CRLF

    set @SQL += ', NOUNLOAD' + @CRLF

    set @SQL += ', REPLACE' + @CRLF

    set @SQL += ', STATS = 10' + @CRLF

    print @SQL

    print '-- -- --'

    fetch next from RestoreDatabase_Cursor into @Database_Name, @Logical_Data_Name, @FileName_Data, @Logical_Log_Name, @FileName_Log, @DB_File_Name

    end

    close RestoreDatabase_Cursor

    deallocate RestoreDatabase_Cursor

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 3 posts - 1 through 3 (of 3 total)

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