Starting SQL Server in safe mode if engine does not start

-
Starting SQL Server In Safe Mode (if engine does not start)
If SQL Server Engine Does Not Start - for debugging there are other ways of starting SQL Server
If the SQL Server service will not start try running in safe mode
Can go to c:\program files\microsoft sql server\mssql10.mssqlserver\mssql\binn\ via command prompt
sqlservr -c -f (this gives a verbose output to screen when starting to help diagnose)
OR add -f to start parameters on microsoft sql server services screen to run safe mode as a service
Starting SQL Server In Single User Mode (used for recovering master database)
MSSQL Server can be run in single user mode by using the -m startup parameter on the mssqlserver service.  Use configuration manager - SQL Server Services - SQL Server - Advanced - Startup Parameters and apend -m; to start of parameters. No spaces between this and what follows.  In SQL Server 2012 and 2008 R2 add -m into Startup parameters as a seperate line (Startup parameters not underneath Advanced in this version).
It can also be started from the command prompt sqlservr -c -m -s<instancename>
If Engine does not start due to missing master database files, then master will need to be rebuild:
1: cmd as administrator
2: cd c:\program files\microsoft sql server\120\Setup Bootstrap\SQLServer2014\
3: setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=RAJDBAL01SQD_1 /SQLSYSADMINACCOUNTS=builtin\administrators /SAPWD=<sapassword>

Can now start service and proceed to restore master from backup.

 Setting Database to Single User Mode
ALTER DATABASE 'dbname' SET SINGLE_USER;
Setting Database to Multi User Mode
ALTER DATABASE 'dbname' SET MULTI_USER
Setting Database to Emergency Mode
Used if data cannot be accessed in suspect database, will be accessible read only by sysadmin
ALTER DATABASE 'dbname' SET EMERGENCY;
Setting Database to Online
ALTER DATABASE 'dbname' SET ONLINE;
Setting Database to Readonly
Useful for taking a final backup
ALTER DATABASE 'dbname' SET READ_ONLY WITH NO_WAIT;
Setting Database to Read Write
ALTER DATABASE 'dbname' SET READ_WRITE;

Comments

Post a Comment

Popular posts from this blog

SQL SERVER – Event ID 107- Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

-
SQL SERVER – Event ID 107- Report Server Windows Service cannot connect to the report server database. Error with Report Server One of my clients reported an error which is the subject of the blog. In the event viewer they were seeing below error:     Event Type: Error Event Source: Report Server Windows Service (MSSQLSERVER) Event Category: Management Event ID: 107 Computer: SQLSERVER Description: Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database The error is generic because it doesn’t tell the exact error which is causing a connectivity error. My immediate question was that, are there any more errors? So, I asked them to look at Reporting Service Log file. Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database. The application domain WindowsService_17 failed to initialize. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report se
Read more

SQL Server Builds Information

-
Image
Very Useful information about SQL Server Builds .  It is nicely presented in the blog . Please see  below for our reference . Microsoft SQL Server Version List What version of SQL Server do I have? This unofficial build chart lists all of the known Service Packs ( SP ), Cumulative Updates ( CU ), patches, hotfixes and other builds of MS SQL Server 2017, 2016, 2014, 2012, 2008 R2, 2008, 2005, 2000, 7.0, 6.5 and 6.0 that have been released. Useful articles: How to identify your SQL Server version and edition SQL Server Internal Database Versions Microsoft SQL Server Support Lifecycle Microsoft SQL Server Home Microsoft SQL Server Developer Center Microsoft TechNet: Microsoft SQL Server Microsoft Knowledge Base Sqlservr.exe versions   Quick summary:   RTM (no SP ) SP1 SP2 SP3 SP4   SQL Server 2017      codename vNext 14.0.1000.169 *new           SQL Server 2016 13.0.1601.5 13.0.4001.0 or 13.1.4001.0 13.0.5026.0 or 13.2.5026.0   S
Read more

Using DBCA silent install and disabling automatic memory management

-
Image
Avoid issue [DBT-11211] /app/oracle/product/12.2.0/dbhome_1/bin=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 20 -emConfiguration NONE [FATAL] [DBT-11211] The Automatic Memory Management option is not allowed when the total physical memory is greater than 4GB.    CAUSE: The current total physical memory is 11GB. Solution : disable automatic memory management with in the response file and you will be fine :) .  /app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -automaticMemoryManagement FALSE -memoryPercentage 20 -emConfiguration NONE Enter SYS user password: Enter SYSTEM user password: [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.    CAUS
Read more