Showing posts with label database user mode. Show all posts
Showing posts with label database user mode. Show all posts

Thursday, June 23, 2016

Start the SQL Server instance in single user mode.




Click on SQL Configuration Manager
Click on SQL Services
Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box. ( make sure that the semicolon exists after the m.


-m;dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf


Once the desired work is done, make sure to remove the newly added parameter.



Monday, October 20, 2014

suspect mode: database

---once the database goes to suspect mode
--follow the steps to recover the database

--1. reset the database from suspect mode
--sp_resetstatus turns off the suspect flag on a database.
--This procedure updates the mode and status columns of the named database in sys.databases.
--Also note that only logins having sysadmin priveleges can perform this :


use master
GO
exec sp_resetstatus 'db1';
--2.set the database to emergency mode
--Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy
--only members of sysadmin fixed server roles have privileges to access it.
--The basic purpose for this is to facilitate troubleshooting

alter database db1 set emergency;

--3.run the consistency test
dbcc checkdb(db1)

--4. Roll the databse to single user mode
use master
GO
ALTER DATABASE db1 SET SINGLE_USER
WITH ROLLBACK IMMEDIATE


--5. this query will attempt to repir all the reported loss. This query can cause some data loss
dbcc checkdb('db1',REPAIR_ALLOW_DATA_LOSS)

--6.finally bring the database ONLINE and set to multiuser mode
ALTER DATABASE db1 SET MULTI_USER
WITH ROLLBACK IMMEDIATE