asked on

What is a good plan for DR (Disaster Recovery) for a MAS_500 7.4 DB?

I am looking for a comprehensive DR plan for a MAS 500 V7.4 hosted on Windows 2008 Server R2 in a SQL Server 2008 R2 (64-Bit).

I want to keep the transaction log and data files a reasonable size and be able to recover if needed. We are using the bulk-logged method (which I inherited) - is this the best way to go?

I want to be able to size or truncate the transaction logs without loosing the ability to recover my DB.

FYI - We have a relitvely small number of transactions and only use the GL.

Thanks In Advance,
Dan

ASKER CERTIFIED SOLUTION

Anuj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dantheanswerman

ASKER

So I have learned (Thanks to Pinal Dave of www.sqlauthority.com ) the golden rule of DB Backup:

‘After restoring full database backup, restore latest differential database backup and all the transaction log backup after that to get database to current state.’

-- Here is my DR script to restore a db - if someone can tell me how to automate this, that would be GRAND!@!:

LEGEND:
@DBName = the name of the DB...
@FullBackupFileName is the path and file name for the full backup...
@DiffBackupFileName is the path and file name to the differential backup ...
@TLogBackupFileName is the path and file name to the log file backup (.trn) ... and
@TailLogBackupFileName is the path and file name of the tail of the log backup

Stored Procedure (which I have tested several times):
_________________________________________________________________
alter PROC s_RestoreDBFromFiles
as
/*
-- template TO RESTORE A DB
____________________________________________________________________
1) BACKUP Tail of the Log - This eliminates any open transactions
      **Crucial step as ther other steps require single user mode - Restore might fail without this
BACKUP LOG @DBName TO disk = @TailLogBackupFileName WITH NORECOVERY;
2) RESTORE FULL BACKUP
RESTORE DATABASE @DBName FROM DISK = full_database_backup WITH NORECOVERY;
3) RESTORE MOST RECENT DIFFERENTIAL BACKUP (THESE ARE CUMULATIVE AND INCLUDE ALL TRANSACTIONS FROM THE LAST FULL BACKUP
RESTORE DATABASE @DBName FROM DISK = differential_backup WITH NORECOVERY;
4) RESTORE ALL LOG FILES (IN SEQUENTIAL ORDER SINCE THE LAST DIFFERENTIAL BACKUP)
      -- IF NO DIFFERENTIAL BACKUP EXISTS, RECOMMIT ALL LOG FILES FROM THE LAST FULL BACKUP
RESTORE LOG @DBName FROM DISK = trans_log_backup WITH NORECOVERY;
      -- Repeat this till you restore last log backup
5) RESTORE THE TRANSACTION TAIL LOG FILE
RESTORE LOG @DBName from DISK = trans_tail_log backup WITH NORECOVERY;
-- 6) SET THE DATABASE TO RECOVERY MODE
RESTORE DATABASE DATABASE WITH RECOVERY;
________________________________________________________
ACTUAL template:
________________________________________________________
BACKUP LOG @DBName TO disk = @TailLogBackupFileName WITH NORECOVERY
RESTORE DATABASE @DBName FROM DISK = @FullBackupFileName WITH NORECOVERY
RESTORE DATABASE @DBName FROM DISK = @DiffBackupFileName WITH NORECOVERY
RESTORE LOG @DBName FROM DISK = @TLogBackupFileName WITH NORECOVERY -- REPEAT AS NEEDED FOR ALL TRANS FILES
RESTORE LOG @DBName FROM DISK = @TailLogBackupFileName WITH NORECOVERY
RESTORE DATABASE @DBName WITH RECOVERY

____________________________________________________________________
*/
-- template

declare @BackupFolder nvarchar(500) = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\'
declare @DBName nvarchar(500) = 'acuity_app'
declare @FullBackupFileName nvarchar(500) = @BackupFolder + @DBName + '\' + 'acuity_app_backup_2012_04_16_135646_2551337.bak'
declare @DiffBackupFileName nvarchar(500) = @BackupFolder + @DBName + '\' + 'acuity_app_backup_2012_04_16_135845_7099159.bak'
declare @TLogBackupFileName nvarchar(500) = @BackupFolder + @DBName + '\' + 'acuity_app_backup_2012_04_16_122751_2162917.trn'
declare @TailLogBackupFileName nvarchar(500) = @BackupFolder + @DBName + '\' + @DBName + '_Tail.bak'
print @FullBackupFileName
print @DiffBackupFileName
print @TLogBackupFileName

BACKUP LOG @DBName TO disk = @TailLogBackupFileName WITH NORECOVERY;
RESTORE DATABASE @DBName FROM disk = @FullBackupFileName WITH NORECOVERY
RESTORE DATABASE @DBName FROM disk = @DiffBackupFileName WITH NORECOVERY
RESTORE LOG @DBName FROM disk = @TLogBackupFileName WITH NORECOVERY
-- Repeat this till you restore last log backup
RESTORE LOG @DBName FROM disk = @TailLogBackupFileName WITH NORECOVERY
RESTORE DATABASE @DBName WITH RECOVERY

nkameron

Hi Dan,

Another though is using a DR service, specifically designed for MAS 500 / SQL environment, with a live form of advanced "log-shipping" to an off site, hosted replica of your MAS environment:

http://www.evs-sw.com/services/disaster-recovery.html