- Incase of any failure login into an Instance due to forgot the login credentials
- Windows authentication credentials removed mistakenly and Sysadmin('sa') password also changed...
- we can fix this issue by restoring the Master database.
When should backup the Master database ?
1. If any changes done at Master database like whenever new login created/modified
2. New database created or Mapped the user with database.
What are the things needs to be considered before restore the Master database ?
1. If there is any changes to Master database after the database backup created.
2. Any login created/modified after the Master database backup created.
3. Any associated/mapped user with user databases after the Master database backup created.
4. Any user database created after the Master database backup created.
5. Any user database re-attached as it creates system tables to maintain the availability.
6. Any objects, logins, or databases have been deleted after Master database was backed up, those objects, logins, and databases should be deleted from Master database also.
7. Any user database no longer available that are referenced in a Master database backup, It report errors when restoring the Master database. Those databases should be dropped after the Master backup is restored.
8. The SQL Server instance stopped automatically once the Master database restore activity completed.
How to restore the Master database backup ?
Restoring Master database is not direct way like user defined database as it needs some additional workarround here..
1. Identify the Sqlservr.exe location.
2. Stop the SQL Server Instance.
3. Start the SQL Server in single user mode from command prompt...
If the SQL Server is Default instance:
If the SQL Server is Named instance:
Press Enter to proceed...
Once the system databases started as shown below... "Please don't close this screen"
4. Open the "SQL Server Management studio" from Start --> Program Files --> ...
5. Login into the Instance with Windows Authentication, The Instance doesn't have windows authentication credential, But we can login through Single user mode...
6. Expand the System Databases --> Right click on Master Database --> Tasks --> Restore --> Database... as shown below
7. Enter as Master in To Database area, Locate the Master database backup file with REPLACE Mode.
8. It may receive any error stating that "Single session is already running..." (or) "A transport-level error has occurred when receiving results from the server", Just click OK and Cancel to proceed further...
9. Now the command prompt can be closed once the restore process completed...
10. Start the SQL Server Instance service.
11. Open the "SQL Server Management studio" from Start --> Program Files --> ...
No comments:
Post a Comment