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

dantheanswerman
dantheanswerman used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA
Top Expert 2011
Commented:
Bulk Recovery model is okay, if you are not worried about the Point in time recovery. But Microsoft recommendation :-
Bulk Recovery Model should be used only intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.

By Bulk recovery model, you can still take your log backups and restore your transactional log backups except you cannot perform the Point in time recovery. If you are not worried about the Point in time recovery this model is okay.

Full Backups - You must have a full backups, the schedule is depend on the size of the database and your backup storage, if you have plenty of storage, take daily full backups.
and your backup job should be run after the consistency jobs + Index defragment \ Update stats.

Diffferential Backups - This needs a prior full backups and has all the data pages or filegroups that are changed since last backups. Differential backups reduces the number of transaction log backup to be restored while restoring the database. The schedule depends on the frequency of transactional log backups and full backups.

Transactional Log backups - Helps to do point in time does not works with Simple recovery model. you should have more frequent transactional log backups say every 5-10 minutes.

Along with this all, you need a fault tolerance disk for storing these backups, and you should validate your backups frequently, you should restore the backups to a test machine regularly.

Author

Commented:
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

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial