September 9, 2013 at 9:50 pm
Comments posted to this topic are about the item Restore multiple db's to new server with new file locations
September 10, 2013 at 8:05 am
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
April 27, 2016 at 4:50 pm
Thanks for the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply