Rebuild System Databases in SQL Server 2008

I recently had to rebuild my Master database to fix a corruption problem. Here’s how I did it in two steps using the command prompt

Step 1: Open your Command Prompt (Start > Run > Cmd). Switch to the directory where you have installed SQL Server. In most of the cases the directory is C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

image

Step 2: Run the following command to rebuild the system databases (Windows Authentication)

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=instancenm  /SQLSYSADMINACCOUNTS=ac

In my case, the instance name was Suprotim-PC and SysAdminAccount was Suprotim

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=SUPROTIM-PC /SQLSYSADMINACCOUNTS=SUPROTIM

image

If you have Mixed Authentication Mode Configured, then use the following command

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=SUPROTIM-PC /SQLSYSADMINACCOUNTS=SUPROTIM /SAPWD=sapassword

On running the command, a new command window will appear for a few seconds and if everything goes smooth, the command will return back to the original command window with no messages.

There should be some points considered before rebuilding system databases. To learn more about rebuilding system databases in SQL Server 2008, read this article http://msdn.microsoft.com/en-us/library/dd207003.aspx

To rebuild system databases in SQL Server 2005, read this article http://msdn.microsoft.com/en-us/library/ms144259(SQL.90).aspx#rebuilddatabase


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

1 comment:

SQL Recovery said...

I appreciate you for sharing this new tip. But I was using SQL Recovery Software to repair MDF File because I did not have updated backup. Now I will use DBCC & CHECKDB command to check & fix MDF corruption.