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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy