If Dynamics AX Production Server Fails Due to Its System Database failure
These are not the actual system database files….I repeat, these are Just templates which SQL Server will create behind the scenes during Installation Process. My actual System Databases are located in DATA folder as shown below
Once rebuild process is complete, You’ve to Restore all the User Databases from your Backups and Restore Master and Master Databases in case if you want to get back all your Logins, Jobs, Server objects etc….Yes! I agree that sounds like a very painful process….But if you’ve all your recent healthy backups and few other dependent objects…you are the champion:) So..if you don’t have a proper Backup/Restore Strategy in place…you are gone!!..You might even loose your Job for this single most important valid reason. In this post, let’s try to rebuild our entire SQL Server/Master Database.
I’ll simulate scenario by Shutting down SQL Server Services on this Instance and physically deleting the mdf and ldf files of system databases and let’s see how it looks and what are the errors you’ll be getting in this case.
Step1: Now my Data folder looks as shown below
Step 2: I tried to Start SQL Server Service from our Configuration Manager and the error messages which i’m getting are (see below Screenshots).
In the Event Viewer I’m seeing the below
we know that the issue is not with Invalid Startup Option since we deliberately deleted our Master.mdf file.
Step 3: Let’s see how to rebuild using SETUP.EXE
As i already mentioned above we no longer need installation media to rebuild SQL Server(you can use it as well if you want to). All you need to know is where did you choose the “Setup Bootstrap/program files” while Installing your SQL Server initially. ( standardizing all these Drives, locations and paths across the Servers in your organization greatly reduces the pain to maintaining all these important details individually at Server level). In my case it’s “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release” as you can see below
Step 4: Open Command Prompt(Run as Admin) and navigate to this folder where you can findSETUP.EXE and we can do the “QUIET Installation” passing the required parameters. In My case the Syntax was
Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="administrator" /SAPWD= "s3#info@2016!"
If you want to explore all other options and parameters available to us….you can issue “setup.exe/?” for help from windows as shown below.
In the above syntax every switch is Space Seperated. Here we are using Quiet Mode and the action is Rebuild master Database, Instance name is DR(For Default Instance: INSTANCENAME will be “MSSQLSERVER”), adding myself as a Local Admin and providing sa password(since mine is a mixed mode).
Work Around: I’ve selected “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe” instead of “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe“
Even if this doesn’t work for you, please try to make use of setup.exe from Installation media. 
These are not the actual system database files….I repeat, these are Just templates which SQL Server will create behind the scenes during Installation Process. My actual System Databases are located in DATA folder as shown below
Once rebuild process is complete, You’ve to Restore all the User Databases from your Backups and Restore Master and Master Databases in case if you want to get back all your Logins, Jobs, Server objects etc….Yes! I agree that sounds like a very painful process….But if you’ve all your recent healthy backups and few other dependent objects…you are the champion:) So..if you don’t have a proper Backup/Restore Strategy in place…you are gone!!..You might even loose your Job for this single most important valid reason. In this post, let’s try to rebuild our entire SQL Server/Master Database.
I’ll simulate scenario by Shutting down SQL Server Services on this Instance and physically deleting the mdf and ldf files of system databases and let’s see how it looks and what are the errors you’ll be getting in this case.
Step1: Now my Data folder looks as shown below
Step 2: I tried to Start SQL Server Service from our Configuration Manager and the error messages which i’m getting are (see below Screenshots).
In the Event Viewer I’m seeing the below
we know that the issue is not with Invalid Startup Option since we deliberately deleted our Master.mdf file.
Step 3: Let’s see how to rebuild using SETUP.EXE
As i already mentioned above we no longer need installation media to rebuild SQL Server(you can use it as well if you want to). All you need to know is where did you choose the “Setup Bootstrap/program files” while Installing your SQL Server initially. ( standardizing all these Drives, locations and paths across the Servers in your organization greatly reduces the pain to maintaining all these important details individually at Server level). In my case it’s “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release” as you can see below
Step 4: Open Command Prompt(Run as Admin) and navigate to this folder where you can findSETUP.EXE and we can do the “QUIET Installation” passing the required parameters. In My case the Syntax was
Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="administrator" /SAPWD= "s3#info@2016!"
If you want to explore all other options and parameters available to us….you can issue “setup.exe/?” for help from windows as shown below.
In the above syntax every switch is Space Seperated. Here we are using Quiet Mode and the action is Rebuild master Database, Instance name is DR(For Default Instance: INSTANCENAME will be “MSSQLSERVER”), adding myself as a Local Admin and providing sa password(since mine is a mixed mode).
Work Around: I’ve selected “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe” instead of “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe“
Even if this doesn’t work for you, please try to make use of setup.exe from Installation media.
No comments:
Post a Comment