Bookmark and Share
Recovering an offline database step by step
(Publish Date: 2014-5-28 4:57pm, Total Visits: 481, Today: 2, This Week: 4, This Month: 11)

Recovering an offline database step by step

-- check the database state to see RECOVERY_PENDING;


    SELECT name, state_desc
    FROM sys.databases WHERE name='Marketdev';
    GO

-- Perform a tail-log backup

    BACKUP LOG Marketdev
    TO DISK = "D:\MSSQLBACKUP\Marketdev_tail_log.trn'
    WITH INIT, CONTINUE_AFTER_ERROR
    GO

-- Restore the full database backup to start the restore sequence

    RESTORE DATABASE Marketdev
    FROM DISK = 'D:\MSSQLBACKUP\Marketdev_full.bak'
    WITH NORECOVERY;
    GO

-- Restore all the log backups with norecovery

    RESTORE LOG Marketdev
      FROM DISK = 'D:\MSSQKBACKUP\MarketdevLog.trn'
    WITH NORECOVERY;
    GO

    ......

-- Restore the tail log backup and recover the database

    RESTORE LOG Marketdev
      FROM DISK = 'D:\MSSQKBACKUP\Marketdev_tail_log.trn'
    WITH RECOVERY;
    GO

-- check the database state again

    SELECT name, state_desc
    FROM sys.databases WHERE name='Marketdev';
    GO