Problem

I’ve been to restore a Microsoft SQL Server 2008 R2 production database to a development/test server in order to troubleshot Microsoft Dynamics AX problems.

Currently, I’m doing this process automatic with T-SQL scripts.

Solution

First,  I prepared the backup inventory and took or move the backup files into Development or Test server.

Second, I restored the full backup, then applied the differential.

Last, I applied the transaction log backup.

/***************************************************************************************************************************************

DATE : 07/23/2013

AUTHOR : Edwin Eu

COMPANY : Confidential

CLUSTER NODE1 : Confidential

SERVER INSTANCE NAME : Confidential\T1

DATABASE NAME : AXEU_PRD_20130723

DATABASE VERSION : Microsoft SQL Server 2008 R2 with Service Pack 1 Cumulative Update 2

***************************************************************************************************************************************

PURPOSES :

TO RESTORE BELGIUM DYNAMICS AX production database  (AXPRD) into DYNAMICS TEST ENVIRONMENT Confidential\T1 .

Restore AXPRD to a point in time to different server

Point in Time 07/23/2013 03:30 AM Central Time.

 

BACKUP SCHEDULE:

Full backup schedule on Every Sunday at 1:00 AM

Differential backup schedule on Monday, Tuesday, Wednesday, Thursday, Friday and Saturday at 1:00 AM

Log backup schedule Daily every one (1) hour

******************************************************************************************************************************************

NOTES:

SQL SERVER – FIX – ERROR : Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)

http://blog.sqlauthority.com/2011/04/13/sql-server-fix-error-msg-3201-level-16-cannot-open-backup-device-operating-system-error-5access-is-denied/

*****************************************************************************************************************************************/

RESTORE FILELISTONLY FROM
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_1of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_2of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_3of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_4of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_5of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_6of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_7of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_8of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_9of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_10of10_20130721.bak’

GO

RESTORE DATABASE[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_1of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_2of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_3of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_4of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_5of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_6of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_7of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_8of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_9of10_20130721.bak’,
DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_10of10_20130721.bak’

WITH  MOVE’ZZZ_AX_PRDZ_AX50SP1_TEST’TO’R:\MP_T1SQLDATA1\T1SQLDATA1\ZZZ_AX_PRD_PRD.MDF’,
MOVE’ZZZ_AX_PRDZ_AX50SP1_TEST_Log’TO’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\ZZZ_AX_PRD_PRD_LOG\ZZZ_AX_PRD_PRD.LDF’,
STANDBY =’R:\MP_T1SQLDATA1\T1SQLDATA1\ZZZ_AX_PRD_PRD_20130723.STN’

GO

—— Restore a Differential Backup
RESTORE DATABASE  [ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_backup_2013_07_22_180003_2144074.bak’
WITH STANDBY  =’R:\MP_T1SQLDATA1\T1SQLDATA1\ZZZ_AX_PRD_PRD_20130723.STN’
GO

—— Restore a Transaction Log Backup
—— Transaction log dated 7/22/2013 7:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723000000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,   NOUNLOAD,  STATS= 10
GO

—— Transaction log dated 7/22/2013 8:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723010000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

—— Transaction log dated 7/22/2013 9:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723020000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD, STATS= 10

GO

—-  Transaction Log dated 7/22/2013 10:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723030000.trn’WITH  FILE= 1,
STANDBY=N’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

—- Transaction Log dated 7/22/2013 11:00 PM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723040000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

—- Transaction Log dated 7/23/2013 12:00 AM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_Log\ZZZ_AX_PRD_PRD_20130723050000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

—- Transaction Log dated 7/23/2013 01:00 AM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_Log\ZZZ_AX_PRD_PRD_20130723060000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

— Transaction Log dated 7/23/2013 02:00 AM
RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723070001.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

—- Transaction Log dated 7/23/2013 03:00 AM

RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_Log\ZZZ_AX_PRD_PRD_20130723080001.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10
GO

—- Transaction Log dated 7/23/2013 03:30 AM

RESTORE LOG[ZZZ_AX_PRD_PRD_20130723] FROM
DISK=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ZZZ_AX_PRD_PRD_20130723090000.trn’WITH  FILE= 1,
STANDBY=’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_ZZZ_AX_PRD_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10,
STOPAT=N’2013-07 23T03:30:00′

GO

—– The Final Step to bring database online.
RESTORE DATABASE[ZZZ_AX_PRD_PRD_20130723]
WITH RECOVERY