CODEWITHSUNDEEP
sql-server
Amelia
Amelia

Reputation: 159

How to add loop of .bak files to restore in single sscript?

RESTORE DATABASE Status_backup_2015_05_31_230001_686899
FROM DISK = 'E:\Restore\Status_backup_2015_05_31_230001_686899.bak'
WITH  FILE = 1,
MOVE 'StatusBackup_Data' TO 'E:\Restore\Files\Status_backup_2015_05_31_230001_686899.mdf',
MOVE 'StatusBackup_Log'  TO 'E:\Restore\Files\Status_backup_2015_05_31_230001_686899.ldf',
NOUNLOAD,  REPLACE,  STATS = 1
GO

I have below script to restore database from backup. There are 9 more .bak files with date stamp

Status_backup_2015_10_31_230001_754565
Status_backup_2015_09_31_230001_865643
Status_backup_2015_08_31_230012_967686
Status_backup_2015_07_31_230001_676760
Status_backup_2015_06_31_230001_665455
Status_backup_2015_04_31_230001_355656
Status_backup_2015_03_31_230001_246754
Status_backup_2015_02_31_230001_646457
Status_backup_2015_01_31_230001_975644

I need to include all these as a loop into above script. I am trying to add but did not get right script to include. please can anyone suggest me how to add remaining .bak files as well.

Thanks.

Upvotes: 1

Views: 452

Answers (1)

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

Give this cursor-based approach a try:

CREATE TABLE #restoreFiles(
backupfile VARCHAR(100))

INSERT INTO #restoreFiles (backupfile)
VALUES ('Status_backup_2015_10_31_230001_754565'),
         ('Status_backup_2015_09_31_230001_865643'),
         ('Status_backup_2015_08_31_230012_967686'),
         ('Status_backup_2015_07_31_230001_676760'),
         ('Status_backup_2015_06_31_230001_665455'),
         ('Status_backup_2015_04_31_230001_355656'),
         ('Status_backup_2015_03_31_230001_246754'),
         ('Status_backup_2015_02_31_230001_646457'),
         ('Status_backup_2015_01_31_230001_975644')

DECLARE @fileToRestore VARCHAR(100), @backupLocation varchar(500), @mdfLocation varchar(500), @ldfLocation varchar(500)

DECLARE restorecursor CURSOR FOR
SELECT backupfile FROM #restoreFiles

OPEN restorecursor

FETCH NEXT FROM restorecursor
INTO @fileToRestore

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @backupLocation = 'E:\Restore\' + @fileToRestore + '\' + @fileToRestore +'.bak'
    SET @mdfLocation = 'E:\Restore\Files\' + @fileToRestore + '.mdf'
    SET @ldfLocation = 'E:\Restore\Files\' + @fileToRestore + '.ldf'
    RESTORE DATABASE @fileToRestore
    FROM DISK = @backupLocation
    WITH  FILE = 1,
    MOVE 'StatusBackup_Data' TO @mdfLocation,
    MOVE 'StatusBackup_Log'  TO @ldfLocation,
    NOUNLOAD,  REPLACE,  STATS = 1

    FETCH NEXT FROM restorecursor
    INTO @fileToRestore
END
CLOSE restorecursor
DEALLOCATE restorecursor

Upvotes: 0

Related Questions