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!