Restoration of Databases

Question

Post reply

Restoration of Databases

suri1984

SSC-Addicted

Points: 463
More actions
July 7, 2008 at 10:34 am

#129815

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.

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

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

J SSCertifiable Points: 7822 More actions · Answer 1

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