Tuesday, September 3, 2013

Rebuild System Database (Master.mdf) in SQL Server 2008

  • Open command prompt (Run -> cmd)
  • Change to directory "<<drive>>:\<<installation folder>>\Microsoft SQL Server\100\Setup Bootstrap\Release" (eg:  C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release)
  • USE THE FOLLOWING COMMAND 
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [/ SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

If you have SQL configured for Windows Authentication Mode :

               
setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance name> /SQLSYSADMINACCOUNTS=<accounts>


Eg: setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS=mypc\Administrators

If you have SQL configured for Mixed Authentication Mode

 setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance name> /SQLSYSADMINACCOUNTS=<accounts> /SAPWD=<password>

When setup has completed rebuilding the system databases, it will return to the command prompt with no messages

After rebuilding the databases, user instance disabled.
You can enable it either using the command prompt tool named sqlcmd or by using Management Studio.
If using Command tool:

C:\>SQLCmd -S.\sqlexpress -E
1> EXEC sp_configure 'user instances enabled', 1  
2> go  
Configuration option 'user instances enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.  
1> RECONFIGURE  
2> GO  

FOR MORE DETAILS
http://technet.microsoft.com/en-us/library/dd207003%28v=sql.100%29.aspx

http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8dabc79b-edef-45fe-a50a-7cf8515c6ed1/how-do-i-run-the-spconfigure-stored-procedure

1 comment:

  1. I found an application which you can try at free of cost to repair corrupt sql database and also to see preview of recovered sql data. Here is the link to download the software and use the free trial version.
    http://www.recoverfilesdata.com/sql-database-mdf-recovery.html

    ReplyDelete