SQL Server 2012 – Page 10

February 15, 2012June 4, 2013 Mustafa EL-Masry Script

How to know SQL Server version

select @@servernameASServerName

Select @@VERSIONAsSQLServerVerjion

selectserverproperty(‘Edition’)AsServerEdition

selectserverproperty(‘ProductLevel’)ServerProductLevel

selectserverproperty(‘BuildClrVersion’)BuildVersion

February 15, 2012 Mustafa EL-Masry Administration, Errors

TRY…CATCH and ERROR Handling

CREATE TABLE TPA
(ID INT , [NAME] NVARCHAR(50))
GO
INSERT TPA VALUES (1,’MOSTAFA’)
INSERT TPA VALUES (2,’ELMASRY’)

BEGIN TRY
— Generate a divide-by-zero error.
SELECT ID+[NAME] FROM TPA
END TRY
BEGIN CATCH
SELECT * FROM TPA
END CATCH;
GO

BEGIN TRY
— Generate a divide-by-zero error.
SELECT ID+[NAME] FROM TPA
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

February 14, 2012 Mustafa EL-Masry Administration, Errors

Repairing Suspect SQL Database

Before you run this script

Kill all connections and stop SQL Server
Perform a Checkdisk with repair option
Perform a Defragmentation
Now copy the files necessary (MDF, LDF and Backups)
Perform a backup
Restore the backup
Repair the Database (DBCC CHECKDB)
Backup the repaired DB

USE [master]
GO
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <dbname> SET SINGLE_USER
GO
DBCC CHECKDB <dbname>, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS
GO
ALTER DATABASE <dbname> SET MULTI_USER
GO

Take Care From this Script becouse it will make repaire and may be some Data will loss

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(‘yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

February 8, 2012 Mustafa EL-Masry Administration, SQl server Administration

How to remove Instance or Feature from SQL Server

I do this steps in my SQL Server 2012 and you can make this steps in Any version like 2005 – 2008 – 2008 R2 Also To remove or Add or repaire SQL Server :

1- Go to Programe and Feature

2- Select your SQL Server name

3- Write click on the name and select Uninstall

4- If you want to add New instance or to add New feature to exsiting instance select Add , Also if you want to repair your instance select the Second Choice Repair

5- in this example we will select Remove

5- After this step we will select the instance you want to remove it or you want to remove some feature from this instance

6- After this step clic Next to determine if you want to remove the instance of the whole or to remove Feature from this insance if you want to remove the instance sellect all thisng in the Screen , Else select the feature you want to be remove it .

7- After you determine waht you Need . make Next to make SQL Sever Check your role may be if you reomve this instance SQL Server will give you Error or Warning so this step is very imporatnt

8- Then Click Next to tell SQL Sever you are Arealdy to remove your Selected

9- after this step if you click Next SQL Server will reove your Selected so i think you must be Retreat yourself becouse the Next step is final {There is no room to return} if you want Click next if you Retreated yourself click cansel.

sorry i cant complete with you the final Step becouse i Retreated my self and i discovered i love Microsoft and i cant reove any tool from microsoft so Complete this step Alone

Good Luck

best Regardes

Mostafa Elmasry