Use “Template” folder data files rather than rebuilding system databases in SQL Server 2012 during Master database corruption or other system DB corruption

I found one interesting thing to recover master database

There are multiple way to recover master databases but I found one interesting way that is also recommended way by MS.

As everyone know master database is really critical database to start SQL Server instance if master database got corrupt then your SQL Server Instance will not start.

There are some way through we can recover our Master database or start SQL Server instance:-

1)           We can restore backup of Master database if we are able to restart SQL Server in single user mode like if .ldf file missing or corrupt or any page corruption.

2)           We can copy and replace the .mdf and .ldf file from another server where the same SQL Server build running and it will work in case you don’t have backup or you are enable to restart SQL Server in single user mode.

3)           Most complicated and dangerous technique from my side that is Rebuild system database dangerous because whenever we rebuild the Master database it rebuild all the system database as well so before rebuild the master database it mandatory to take all the system and user database backup or copy paste all the .mdf and .ldf file in safe location if you didn’t take the backup then you will lose the all data and database from instance.So we need to take some per-step and post steps before rebuild master databases.

4)           It really interesting solution from my side and its provided by Microsoft as a system template.Have you ever heard about system database templates in SQL Server that exist in :-

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates

So, what next how we can recover master database by provided system database template.

Step 1>Copy Master .mdf and .ldf file from template folder and replace with old master file in the following path.

Now if you try to restart SQL Server it will not start because master database contain the all other database information as well so still we can’t start SQL Server because to start SQL Server we need other system database as well means master database contain all system database info ,so here we copy the master .mdf and .ldf file from template folder and we need to modify the other system database path as well that will help to start SQL Server.

If you try to start SQL server you will get below error:-

System error 1067 has occurred.

The process terminated unexpectedly.

If you check the windows event log you will see error like:-

Error states SQL Server enable to find path of Model database due to SQL Server unable to start.

So, now question is how will be achieve this then .Here is the main twist you have to modify the path of all system database and once it’s done it will work as it is.

So start how to achieve next steps:-

Start SQL Server with single user mode with below command by CMD:-

C:\Windows\system32>NET START MSSQLSERVER /m /t3608

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

Once it start with Single user mode run below command to modify the path of all system database.

C:\Windows\system32>sqlcmd

1> ALTER DATABASE model  MODIFY FILE  ( NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf')

2> go

The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.

1> ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf');

2> go

The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started.

Now you can check the path for all database where they exist to before modify with following command.

1> select * from sys.master_files

2> go

You can see below tempdb path in strange location

TempDB database Path

Tempdev

   e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\tempdb.mdf

Templog

 e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\templog.ldf

Model Database

For model we have already modify the path with above command so it’s showing in right drives

modeldev

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf

modellog

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf

MSDB database

Msdb still not modified so it’s showing in different location

MSDBData

e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf

MSDBLog

e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf

so same way we need to modify all database tempdb and MSDB as well.

If we ty to start SQL Server again without modify the path of tempdb SQl Server will not start and you can see below error in event viewer:-

C:\Windows\system32>NET START MSSQLSERVER

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service could not be started.

A service specific error occurred: 1814.

More help is available by typing NET HELPMSG 3547.

C:\Windows\system32>sqlcmd

1> ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf');

2> go

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

1> ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog1.ldf');

2> go

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

1> ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\msdbdata.mdf');

2> go

The file "msdbdata" has been modified in the system catalog. The new path will be used the next time the database is started.

Same way modify MSDB as well and start Sql Server it will work and please restore master database backup if you have else you will loss all the login and users even your own access as well.

If you don’t have Master database backup then you will lose your own access as well from instance .So other way to create new login in SQL Server by run SQL Server single user mode.

Run SQL server Single user mode create login and provide admin access

Please check if your error log also working as expected most of time error log lose the permission and we get error like:-

SQL Server error: 

Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

So it might be issue with your domain account so please provide "SYSADMIN"rights to account it will work.

Slipstream Installation 2012 with CU

Basically we always apply CU and service pack and CU after install main set-up of SQL server, but its little time consuming process .So, through slip stream you can achieve both thing only with simple command.

See below command:-

First part:- 

It’s a main setup path of your SQL server DVD where your SQL Server 2012 placed.

D:\Software\evaluation Edition SETUP SQL SERVER 2012\setup.exe

Second: - Second part where you have kept the CU or SP  for example here SP and Cu ()kept in (E:\SP2 folder)that you want apply on during installation.

setup.exe /action=install /updateenabled=true /updatesource="E:\SP2"

 
See below full command to install SQL Server with CU.

D:\Software\evaluation Edition SETUP SQL SERVER 2012\setup.exe  /action=install /updateenabled=true /updatesource="E:\SP"

After run the above command you can see the out like this and you can check in below screen shot.

If there is any other CU or GDR, SP’s you can put in the same folder it will automatically grab the latest services pack or CU and other if required.

If you have SQL Server 2008 setup and you want to do the same thing with 2008 you can run below command:-

D:\setup.exe /Action=Install /PCUSource=E:\sp /CUSource=E:\cu

So through Slipstream we can save much time to apply these things during installation

How many way to find running SQL Server Instance on Server?

Six way to find how many instance are running on your Server.

1. SQL Server Configuration Manager
2.    Services. Msc
3.   SQL CMD
4.  Detailed file
5.  Summary.txt
6. Discovery Report

1) SQL Server Configuration Manager

2) Services. MSc

Type services. Msc in run and enter

3 ) SQL CMD

4) Detailed file

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20150317_225942

5) Summary.txt

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

6) Discovery Report

This most interesting way to find instance and edition just go your SQL Server Setup media and click on setup.exe and click on the tool and select Installed "SQL Server feature discovery" option

Please let me know if any other way as well if i miss anyone here :)

Switch Recovery Model Simple to Full.

Hi all recently I found one interesting fact in Sql Server related to recovery model.As we are aware if we change the recovery model full to simple then we have to take full  backup else our differential and transaction backup will start fail.

What you think this is right statement right??

But its not mandatory to take full backup we can take differential backup and it will work the only thing is the pointer should be match with last full backup or differential backup .

You can use below query to check pointer is matching last full backup or not.

SELECT name,
       backup_start_date,
       TYPE,
       first_lsn,
       database_backup_lsn
FROM msdb.dbo.backupset
WHERE database_name = 'your db name';

 GO

I have simulated the same scenario as per my question and its working as well I am taking only differential backup after changing the recovery model simple to full and its success for all t-log backup .

DBCC CHECKDB Job start failing Due to Consistency Error

Hi all finally I got the chance to work on DBCC CHECK DB maintenance job failure due to consistency error. Please  see below step how to resolve this issue if occur on your environment.

 When DBCC CHECKDB job got failed due to consistency error follows to below steps to resolve the issue:-

Error message Like :-

1.  CHECKDB found 1 allocation errors and 0 consistency errors in table 'table_Name’ (object ID 1043456720).
2.  Msg 8951, Level 16, State 1, Line 1

Table error: table 'Table_name' (ID 198734567). Data row does not have a matching index rowin the index 'Index_name_PK' (ID 3). Possible missing or invalid keys for the index row matching:

Msg 8955, Level 16, State 1, Line 1

Data row (1:507955:1) identified by (Component = 'SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT' and Time = '2015-02-12 19:25:03.087' and UNIQUIFIER = 0) with index values 'RecordID = 112994885 and Component = 'SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT' and Time = '2015-02-12 19:25:03.087' and UNIQUIFIER = 0'.

Msg 8951, Level 16, State 1, Line 1

Identify corrupted Objects:-

Through above error we can identify on which object id and index id has consistency issue against Index but sometime its complicated because error provide only object id so below script will help to find exact corrupted object and related to index .

Use the below script to find index name and exact object name with object_ID:-

   Select * from [sys.objects] where ]object_id]='106157654320'

   Go

   EXEC sp_HelpIndex 'Table_Name';

 Prerequisite

Prerequisite before apply any changes on object or corrupted index

First try to run DBCC CHECKTBLE against corrupted Table if still showing same error takes further steps to resolve this.

Raise an incident with concern person or team who belong to corrupted database and ask him to reliable time when we can rebuild this corrupted index to resolve consistency issue.

Once you get approval go ahead with below steps:-

Make sure you have current backup if not take full back up on the server with Copy_Only command.

Take object backup on which you are going to rebuild or recreate the index.

Solution:-

  First try to rebuild the corrupted index by below script or GUI

 ALTER INDEX Index_name ON Table_Name

 REBUILD;

Once rebuild complete try to run CHECKDB again if still issue not fix then Drop the index and recreate.

Do not drop index directly try to generate script DROP and create index by GUI and re-run this script this script will drop and create index from scratch.

Now run DBCC CHECKDB(DB_Name) and DBCC CHECKTABLE(Table_Name) against corrupted database and Table it should be complete without any consistency issue.

MSSQL Server DDL Trigger For Prevent to Drop_Database,Create_Database Create_Login,Drop_Login for Particular Login

DDL triggers (auditing CREATE, ALTER, DROP and several system defined stored procedures which perform DDL operations) are often used for administrative tasks like regulating and auditing database operations and finally I got a chance to work one more interesting topic today and that is Trigger in SQL Server.

Trigger is really interesting thing in DBA’s life and it can give you lot for functionality and great security in very little code.

You can get easily all the information and code as well on internet and MSDN sites about triggers.

So what’s interesting here in my blog so let’s discuss about scenario about I am working J

Requirement:-

I wanted to create a DDL trigger that will Prevent to Drop_Database,Create_Database,Drop_Login and Create_Login for a particular logins not for all the user that  exist on that Server.

So, why we need to prevent only some Logins to perform this activity?

Actually my main requirement was to prevent drop a database accidently by DBA from my team cause some are junior DBA is also there and  DBA have all the rights so for safer side we want to make a better security.

We can make this process for all the Logins and it was really easy process if we want to prevent all the logins to perform DDL operation on server. But the issue is  why we didn’t do this cause we have lot of SQL Server and user who use different -different applications and whenever they setup any new application that application create a database in MSSQL Server automatically so if we setup this trigger server base then application team can’t setup application as well and they will start complaining to this like they have issue to setup application.so to avoid these kind of thing we want to create this trigger only for particular users that have main SYSADMIN rights.

So finally I write some code and test it against my local system and its working fine as per my expectation.

CREATE TRIGGER [TR_Prevent_CreateDrop_Database_Logins] ON all server FOR create_database,drop_database,create_login,drop_login AS
DECLARE @loginname nvarchar(100), 
  @eventData xml 
SET @eventData = eventdata() 
SELECT @LoginName= @eventData.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(50)') 
IF @loginname IN ('sa', 
                  'test') 
BEGIN 
  PRINT ' Are you sure to perform this actvity ??     If yes then Please disable the trigger "TR_Prevent_CreateDrop_Database_Logins" before perform this activity.' ROLLBACK;
end

go

enable TRIGGER [TR_Prevent_CreateDrop_Database_Logins] ON ALL server

Testing Phase:-

After executing above script trigger will create on below folder

Login with SA and tried to perform below steps:-

Now you can see the output of the above query and that we want J

Now we will try to run the same DDL command by different Logins [ASHISH-PC\ASHISH] that is not prevented by Trigger.

So, you can see the above out put its working fine J

It’s done!