Script to restore all databases

Script to Restore All Databases
Fonte: ConceptSQL
SET nocount ON
DECLARE @databaseName SYSNAME
CREATE TABLE #tmpcommands
(
id  INT IDENTITY(1, 1),
cmd VARCHAR(8000)
)
DECLARE dbnames_cursor CURSOR FOR
SELECT name
FROM   MASTER..sysdatabases
WHERE  name NOT IN ( ‘model’, ‘tempdb’, ‘pubs’, ‘northwind’ )
AND ( status & 32 ) = 0 — Do not include loading
AND ( status & 64 ) = 0 — Do not include loading
AND ( status & 128 ) = 0 — Do not include recovering
AND ( status & 256 ) = 0 — Do not include not recovered
AND ( status & 512 ) = 0 — Do not include offline
AND ( status & 32768 ) = 0 — Do not include emergency
AND ( status & 1073741824 ) = 0 — Do not include cleanly shutdown
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @databaseName
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( @@FETCH_STATUS <> -2 )
BEGIN
INSERT INTO #tmpcommands
(cmd)
VALUES      (‘—————-Script to Restore the ‘ +
@databaseName
+
‘ Database————–‘)
            DECLARE @backupStartDate DATETIME
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end 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
            INSERT INTO #tmpcommands
(cmd)
SELECT cmd
FROM  (SELECT backup_set_id,
‘RESTORE DATABASE ‘ + @databaseName +
‘ FROM DISK = ”’
+
mf.physical_device_name + ”’ WITH NORECOVERY –‘ cmd
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
UNION
SELECT backup_set_id,
‘RESTORE LOG ‘ + @databaseName + ‘ FROM DISK = ”’ +
mf.physical_device_name +
”’ WITH FILE = ‘ + CAST(position AS VARCHAR(10)) +
‘, NORECOVERY –‘
cmd
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
AND b.backup_set_id < @backup_set_id_end
AND b.TYPE = ‘L’
UNION
SELECT 999999999
AS
backup_set_id,
‘RESTORE DATABASE ‘ + @databaseName +
‘ WITH RECOVERY –‘
cmd)
a
ORDER  BY backup_set_id
END
      FETCH NEXT FROM dbnames_cursor INTO @DatabaseName
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
DECLARE @PrintCommand VARCHAR(8000)
DECLARE print_cursor CURSOR FOR
SELECT cmd
FROM   #tmpcommands
ORDER  BY id
OPEN print_cursor
FETCH NEXT FROM print_cursor INTO @PrintCommand
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( @@FETCH_STATUS <> -2 )
BEGIN
PRINT @PrintCommand
END
      FETCH NEXT FROM print_cursor INTO @PrintCommand
END
CLOSE print_cursor
DEALLOCATE print_cursor
DROP TABLE #tmpcommands

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s