REBUILD MASTER DATABASE WITH COMMAND PROMPT

If the services did not come online before you go for REBUILD database try to bring the server online by using the command below using TRACE FLAG. If this did not work  follow the rebuild process. First check with below command.

If the above command does not work please add "-f" parameter which is case sensitive  and try once.







If you are rebuilding NAMED INSATNCE of sql server please use provide the server name like below whether you start in single user mode or whether you start normally.











If you are  using domain account first image is the use ful image.



Sometimes this REBUILD database(above script or immediate image below) will not bring back existing databases back which are there in previously, so we need to address this in a different way. So go through the below links for reference.

Whether it is windows authentication or mixed mode authentication providing value to 'sa' account is compulsory.


After REBUILDING the master database if we want to restore the master database with earlier .BAK file, because REBUILD master database will clean up all other databases. If you reconnect to instance and see you wont see any earlier user databases in the instance. So you need to restore 'master' database with earlier backup file and follow the approach below.

1)To restore master database restore from the old backup need to follow the instructions below.
2) Start the sqlserver instance(here I am working with default instance and the name is MAPS)  in a single user mode. Before that open cmd with administrative mode as fresh








4) Now from the "cmd" go to "sqlcmd" in the command prompt run the commands one after another. Here the "GO" command is very important. If you don't put go in the command the command will not run. And always run the command below from C:\Windows\system32" level. Which means root level.




5)After restoring the database, run the "NET START MSSQLSERVER" from cmd and check the databases. 
6) Now if you connect to the SSMS and connect to the respective instance you can see all the databases which were disappeared earlier.






Comments

latesttechnologyblogs said…
Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man Microsoft SQL Certification
January 30, 2019 at 12:50 PM
Post a Comment

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Pre login Handshake or Connection Timeout Period

Image
Service was unable to open new database connection when requested. SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16225; handshake=14840; Connection string - Data Source=sqlserverintancenamehere;Initial Catalog=CatalogNamehere;Integrated Security=True;Persist Security Info=False;Max Pool Size=1000;Connect Timeout=20;Load Balance Timeout=120;Packet Size=4096;Application Name=Applicationname hereBusinessLayerHost.exe;Workstation ID=HostnameHere setspn -a MSSQLSvc/hostname.doaminname.net:1433 ServiceAccountNameHere need to run the above command in a command prompt, most of the time you would NOT have permissions to run this, please check with Active Directory Team or Wi...
Read more

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server

Image
/*  There is only one master key, you can not create  multiple */ use master go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPasswordHere' GO /*  Creating certificate in the master database, after creating  master key */ use master GO CREATE CERTIFICATE MyFirstCert WITH SUBJECT = 'TDECertificate' ; GO /* Enable databse encryption key but you can not see under this specific user database. */ USE G GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyFirstCert GO ALTER DATABASE G SET ENCRYPTION ON;  /*  Backing up certificate */ use master go BACKUP CERTIFICATE MyFirstCert TO FILE = 'C:\Certificates\MyFirstCert' WITH PRIVATE KEY ( FILE = 'C:\Certificates\MyFirstCertKey' , ENCRYPTION ...
Read more