July 7, 2008 at 10:34 am
Hi ,
I have a database maintaince plan which takes back up of all the databases in the instance.how can i restore all the databases ina single shot?
Thanks.
July 7, 2008 at 10:55 am
You can generate a restore script using the system tables.
I dug this out of my scripts, I think this will do it.
USE master
--get the database name and database Id from the system database
DECLARE dbnames_cursor CURSOR
FOR
SELECT Name
FROM sysdatabases
where name not in ('model', 'tempdb', 'pubs', 'northwind', 'master', 'msdb')
DECLARE @databaseName sysname
OPEN dbnames_cursor
--SET @dbname = name and @dbid = dbid
FETCH NEXT FROM dbnames_cursor INTO @databaseName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT
DECLARE @return INT
SELECT @backup_set_id_start = MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
SELECT @backup_set_id_end = MIN(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start
IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999
DECLARE @PRINT nvarchar(4000)
DECLARE @sql nvarchar(4000)
PRINT ''
SET @PRINT = (SELECT TOP 1 'RESTORE DATABASE [' + @databaseName + '] FROM DISK = '''
+ mf.physical_device_name + ''''
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_start )
PRINT @PRINT
Set @sql = ('DECLARE @PRINT nvarchar(4000); SET @PRINT = (SELECT ''WITH MOVE '''''' + LTRIM(RTRIM(name)) + '''''' TO '''''' + LTRIM(RTRIM(filename)) + '''''',''
from ' + @databaseName + '.dbo.sysfiles where fileid = 1); PRINT @PRINT')
exec sp_executesql @sql
DECLARE @i INT
set @i = 2
Set @sql = ('SELECT @return= max(fileid) from ' + @databaseName + '.dbo.sysfiles')
EXEC sp_executesql @sql, N'@return integer OUTPUT', @return OUTPUT
WHILE (@i < @return)
BEGIN
Set @sql = ('DECLARE @PRINT nvarchar(4000); SET @PRINT = (SELECT '' MOVE '''''' + LTRIM(RTRIM(name)) + '''''' TO '''''' + LTRIM(RTRIM(filename)) + '''''',''
from ' + @databaseName + '.dbo.sysfiles where fileid = ' + cast(@i as varchar(10)) + '); PRINT @PRINT')
exec sp_executesql @sql
set @i = @i + 1
END
Set @sql = ('DECLARE @PRINT nvarchar(4000); SET @PRINT = (SELECT '' MOVE '''''' + LTRIM(RTRIM(name)) + '''''' TO '''''' + LTRIM(RTRIM(filename)) + ''''''''
from ' + @databaseName + '.dbo.sysfiles where fileid = ' + cast(@i as varchar(10)) + '); PRINT @PRINT')
exec sp_executesql @sql
FETCH NEXT FROM dbnames_cursor INTO @databaseName
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
July 7, 2008 at 11:15 am
There is not automated way to do this, since it doesn't usually make sense.
there are lots of scripts here that you can search for if Ken's doesn't work for you.
July 8, 2008 at 5:30 am
Thanks Ken.....i will try your script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply