October 1, 2009 at 2:35 pm
I am trying to restore 120 databases on a new server using litespeed is there some scripts out there which restores all databases instead doing one by one. ofcourse all files data files go into 1 drive and log files go into another drive.
October 2, 2009 at 4:26 pm
Tara,
Try the below mentioned script on test instance first. Also, you need to create a table named 'backupTable' under master database which will hold names of databases to be restored. Also, the backp file location is hardcoded in script so please change it accordingly.
--Restoring databases with multiple data and log files and by just specifying the backup folder location.
--Also, can specify the Desired Data file and Log file path.
Create PROCEDURE dbo.s_restorestatementforall
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @DBName SYSNAME
DECLARE @sSQL VARCHAR(2250)
DECLARE @sSQL1 VARCHAR(2250)
DECLARE @iRowCount INT
DECLARE @iRowCount1 INT
DECLARE @LogicalName SYSNAME
Declare @DataFilePath SYSNAME
Declare @LogFilePath SYSNAME
DECLARE @PhysicalName SYSNAME
Declare @Fileextension VARCHAR(10)
create table #TableNamesTemp
(LogicalName SYSNAME,PhysicalName SYSNAME,Type SYSNAME,
FileGroupName SYSNAME NULL,Size1 SYSNAME,MaxSize SYSNAME,FileId Int,
CreateLSN numeric(25,0),DropLSN numeric(25,0) NULL,UniqueId uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,ReadWriteLSN numeric(25,0) NULL,BackupSizeInBytes bigint,
SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0) NULL,DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,IsPresent bit)
create table #temp (FileNames SYSNAME)
Create table #t_TableNames_Temp(Database_name SYSNAME)
Create clustered index IDX1 on #TableNamesTemp(Type)
SET @DataFilePath='C:\Data\' ---Specify Data file path here
SET @LogFilePath='C:\Log\' ---Specify Log file path here
INSERT #t_TableNames_Temp
Select Name from master..backupTable order by name
SELECT @iRowCount1 = COUNT(*) FROM #t_TableNames_Temp
WHILE @iRowCount1 > 0
BEGIN
Set @sSQL=''
SELECT @DBName = Database_name from #t_TableNames_Temp
SELECT @sSQL = 'restore filelistonly from disk=''c:\Backup\'+@DBName+'.bak'''
INSERT #TableNamesTemp
EXEC (@sSQL)
SELECT @iRowCount = COUNT(*) FROM #TableNamesTemp
SELECT @sSQL = 'restore Database '+@DBName+ ' from disk=''c:\Backup\'+@DBName+'.bak'''
SELECT @sSQL = @sSQL+' with '
WHILE @iRowCount > 0
BEGIN
Set @sSQL1=''
--SELECT @LogicalName = LogicalName from #TableNamesTemp
If (Select count(*) from #TableNamesTemp where type ='D')>0
Begin
Set @PhysicalName=@DataFilePath
Select @LogicalName=LogicalName from #TableNamesTemp where type ='D'
Set @Fileextension='.mdf'
--Print (@logicalName)
END
Else
Begin
Set @PhysicalName=@LogFilePath
Select @LogicalName=LogicalName from #TableNamesTemp where type ='L'
Set @Fileextension='.ldf'
--Print (@logicalName)
End
Select @sSQL1='Move '+''''+@LogicalName+''''+' to '+''''+@PhysicalName+@LogicalName+@Fileextension+''''+','
Insert #temp values (@sSQL1)
DELETE FROM #TableNamesTemp WHERE @LogicalName = LogicalName -- and type='D'
SELECT @iRowCount = @iRowCount - 1
END
Select @sSQL=@sSQL+FileNames from #temp
Select @sSQL=@sSQL+'STATS=20'
Print (@sSQL)
--EXEC (@sSQL) --Once you uncomment it it illoverwrite the existing database without throwing any error message.
DELETE FROM #temp
DELETE FROM #t_TableNames_Temp WHERE @DBName = Database_name
SELECT @iRowCount1 = @iRowCount1 - 1
END
Let me know if it fails.
MJ
October 3, 2009 at 1:36 pm
Let me know if it fails.
Confidence! Love it! Nice script though. I'll take a copy of that one - could be usefull...Maybe one which you could submit in the official script section!
October 3, 2009 at 2:40 pm
Clive,
That script is not mine(lol). I got it from this site only. Hope script works ok for Tara and saves her time and effort.
Manu
October 6, 2009 at 9:11 am
My script
Execute and save to File :
SELECT TOP 100 PERCENT
N'-- ' + s.database_name + ' ' + CONVERT(nvarchar(25), s.backup_finish_date, 121) + char(13) + char(10)
+ N'RESTORE '
+ CASE s.type WHEN 'D' THEN N'DATABASE ' WHEN 'L' THEN N'LOG ' END
+ quotename(s.database_name) + ' '
+ N'FROM DISK=' + quotename(mf.physical_device_name, '''') + ' '
+ N'WITH NORECOVERY'
+ CASE s.type WHEN 'D' THEN N', REPLACE ' WHEN 'L' THEN N' ' END
as [-- Script de restauration]
FROM
msdb.dbo.backupset AS s inner join
msdb.dbo.backupmediafamily AS mf on mf.media_set_id = s.media_set_id inner join
(
SELECT
s2.database_name
, s2.type
, max(s2.backup_finish_date) d_dern_backup
FROM
msdb.dbo.backupset AS s2 inner join
msdb.dbo.backupmediafamily AS mf2 on mf2.media_set_id = s2.media_set_id
WHERE
s2.type ='D'
Group by
s2.database_name
, s2.type
) as last_backup
ON last_backup.database_name=s.database_name
and case when s.type='L' then s.backup_start_date else s.backup_finish_date end >=last_backup.d_dern_backup
WHERE
s.type in ('D','L')
and s.backup_finish_date > DATEADD(day, -7, GETDATE())
and s.backup_finish_date IS NOT NULL
and db_id(s.database_name) > 4
UNION ALL
SELECT DISTINCT TOP 100 PERCENT
N'-- ' + s2.database_name + ' ' + CONVERT(nvarchar(25), GETDATE(), 121) + char(13) + char(10)
+ N'RESTORE DATABASE ' + quotename(s2.database_name) + N' WITH RECOVERY'
FROM
msdb.dbo.backupset AS s2 inner join
msdb.dbo.backupmediafamily AS mf2 on mf2.media_set_id = s2.media_set_id
WHERE
s2.type ='L'
and db_id(s2.database_name) > 4
ORDER BY 1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply