Personal Notes of Edwin Eu

Restore a Microsoft SQL Server 2008 R2 production database (AXEurope_PRD) to a point in time to different server

In Microsoft SQL Server on 07/24/2013 at 9:04 AM

Problem

I have 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  (AXEU_PRD) into DYNAMICS TEST ENVIRONMENT Confidential\T1 .

Restore AXEU_PRD 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 the Full Backup

RESTORE

 

 

FILELISTONLY

   FROM   DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_1of10_20130721.bak’,

          DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_2of10_20130721.bak’,

          DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_3of10_20130721.bak’,

DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_4of10_20130721.bak’,

DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_5of10_20130721.bak’,

DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_6of10_20130721.bak’,

DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_7of10_20130721.bak’,

DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_8of10_20130721.bak’,

DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_9of10_20130721.bak’,

DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_10of10_20130721.bak’

GO

RESTORE DATABASE[AXEU_PRD_20130723]

FROM DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_1of10_20130721.bak’,

        DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_2of10_20130721.bak’,

        DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_3of10_20130721.bak’,

        DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_4of10_20130721.bak’,

        DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_5of10_20130721.bak’,

        DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_6of10_20130721.bak’,

        DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_7of10_20130721.bak’,

        DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_8of10_20130721.bak’,

        DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_9of10_20130721.bak’,

        DISK=  ‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_10of10_20130721.bak’

WITH  MOVE‘PRODUO_AX50SP1_TEST’TO‘R:\MP_T1SQLDATA1\T1SQLDATA1\AXEU_PRD.MDF’,

        MOVE‘PRODUO_AX50SP1_TEST_Log’TO‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_PRD_LOG\AXEU_PRD.LDF’,

STANDBY =‘R:\MP_T1SQLDATA1\T1SQLDATA1\AXEU_PRD_20130723.STN’

GO

—— Restore a Differential Backup

RESTORE DATABASE  [AXEU_PRD_20130723]

  FROM  DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_backup_2013_07_22_180003_2144074.bak’

  WITHSTANDBY  =‘R:\MP_T1SQLDATA1\T1SQLDATA1\AXEU_PRD_20130723.STN’

GO

—— Restore a Transaction Log Backup

—— Transaction log dated 7/22/2013 7:00 PM

RESTORE LOG[AXEU_PRD_20130723]

FROM DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_20130723000000.trn’WITH  FILE= 1, 

     STANDBY=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,   NOUNLOAD,  STATS= 10

GO

—— Transaction log dated 7/22/2013 8:00 PM

RESTORE LOG[AXEU_PRD_20130723]

FROM DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_20130723010000.trn’WITH  FILE= 1, 

     STANDBY=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

—— Transaction log dated 7/22/2013 9:00 PM

RESTORE LOG[AXEU_PRD_20130723]

FROM DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_20130723020000.trn’WITH  FILE= 1, 

STANDBY=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,  NOUNLOAD, STATS= 10

GO

—-  Transaction Log dated 7/22/2013 10:00 PM

RESTORE LOG[AXEU_PRD_20130723]

   FROM  DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_20130723030000.trn’WITH  FILE= 1, 

   STANDBY=N’R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

—- Transaction Log dated 7/22/2013 11:00 PM

RESTORE LOG[AXEU_PRD_20130723]

FROM  DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_20130723040000.trn’WITH  FILE= 1, 

STANDBY=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

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

RESTORE LOG[AXEU_PRD_20130723]

  FROM  DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_Log\AXEU_PRD_20130723050000.trn’WITH  FILE= 1, 

  STANDBY=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

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

RESTORE LOG[AXEU_PRD_20130723]

   FROM  DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_Log\AXEU_PRD_20130723060000.trn’WITH  FILE= 1, 

   STANDBY=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

 

— Transaction Log dated 7/23/2013 02:00 AM

RESTORE LOG[AXEU_PRD_20130723]

  FROM  DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_20130723070001.trn’WITH  FILE= 1, 

  STANDBY=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

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

RESTORE LOG[AXEU_PRD_20130723]

   FROM  DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_Log\AXEU_PRD_20130723080001.trn’WITH  FILE= 1, 

   STANDBY=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10

GO

 

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

RESTORE LOG[AXEU_PRD_20130723]

   FROM  DISK=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\AXEU_PRD_20130723090000.trn’WITH  FILE= 1, 

   STANDBY=‘R:\MP_T1SQLBACKUP1\T1SQLBACKUP1\AXEU_Backup\ROLLBACK_UNDO_AXEU_PRD_20130723.BAK’,  NOUNLOAD,  STATS= 10, 

   STOPAT=N’2013-07 23T03:30:00′

GO

 

—– The Final Step to bring database online.

RESTORE DATABASE[AXEU_PRD_20130723]

WITH RECOVERY

 

Window Server 2012

In Microsoft SQL Server, SharePoint 2010 on 02/21/2013 at 9:50 PM

I always try to keep up with new technology and excited to learn and explorer.

Microsoft recently release Microsoft Window Server 2012, Microsoft SQL Server 2012 and Microsoft SharePoint Server 2013.

I am excited and  looking forward to explorer, learn and  master those new skills.

First, it’s my pleasure to share some Microsoft Window Server 2012 installation screens:

Window2012_pic1

 

 

 

 

 

 

 

 

Microsoft Window Server 2012 “WELCOME TO SERVER MANAGER” is totally new designs and looks.

Window2012_pic2

 

 

 

 

 

 

 

 

 

 

 

One things that I noticed on Microsoft Window Server 2012.   There is no START menu.

The START menu is  located in the Lower Left Window Server 2012.

Window2012_pic3

Restored the production corrupted database

In Microsoft SQL Server on 11/09/2012 at 8:13 AM

I am working on a client who running Microsoft SQL Server 2008 R2 Express that implented in Retail stores.

The database architecture setup was very unique due to the nature of business.

Each retail store has itown SQL Server 2008 R2 Express that connected to POS machine.

However, the database recovery model was setup to be SIMPLE.  (It’s not a best practice) in case a disaster.

In addition, the log file was named with extension MDF instead of LDF.

One day, the database was corrupted and down for 4 days.  The company DBAs was unable to recover the database.

I was assigned to recovery those SQL database.

Here are my steps to recover the corrupted database.

—- Step Create the exact database name

—- Step 1 Create the exact database name

CREATE DATABASE AxRetailPOS ON  PRIMARY

( NAME = N’AxRetailPOS’,

    FILENAME = N’D:\SQL_DATA\DATA\AxRetailPOS.mdf’,

    SIZE= 8MB, MAXSIZE = UNLIMITED, FILEGROWTH = 16MB)

LOG ON

( NAME = N’AxRetailPOS_log.mdf’,

 FILENAME = N’E:\SQL_LOG\AxRetailPOS_log.mdf’,

SIZE = 8MB, MAXSIZE = UNLIMITED, FILEGROWTH = 16MB)

GO

—- Step 2

Stop the SQL Server Services

Move the original MDL file from older server (or location) to new server (or location).

By replacing just create MDF file.  Then, delete the LDF file of the new server just created.

— Step 3

Started the SQL Server services.

— Step 4

Make sure system table of master database allow to update the value

USE MASTER

GO

 exec SP_CONFIGURE ‘allow updates’, 1

RECONFIGURE WITH  OVERRIDE

GO

—Step 5 – Change database mode to emergency mode

 ALTER DATABASE AxRetailPOS SET  EMERGENCY

GO

SELECT * FROM  SYSDATABASES

 WHERE NAME = ‘AxRetailPOS’

 GO

— Update only one row in sysdatabase

BEGIN

  UPDATE sysdatabases

  SET status = 32768

  WHERE name = ‘AxRetailPOS’

 COMMIT TRAN

END

select * from  sysdatabases

 go

— Find out the database id

select name, filename from sysdatabases

 where dbid = ’7′

 go

 ALTER DATABASE AxRetailPOS Rebuild Log

ON (NAME = N’AxRetailPOS_Log’,

   FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SBHDB01\MSSQL\DATA\AxRetailPOS.ldf’)

GO

ALTER DATABASE AxRetailPOS Set  online

go

 DBCC rebuild_log (‘AxRetailPOS’, ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SBHDB01\MSSQL\DATA\AxRetailPOS_log.ldf’)

 GO