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
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
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
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.
ReplyDeleteGreat stuff, Thanks for sharing ! Rebuilding system databases is beginning to clear up. As i have found a SQL repair tool which is helpful in recovering data from corrupted or inaccessible .mdf file.
ReplyDelete