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.)
*****************************************************************************************************************************************/
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
SQL INDONESIA 