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

Or

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

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

Mustafa EL-Masry Configuration, Configuration

sp_configure to set the value of the backup_compression_default setting


This Feature in SQL Server 2008 , 2012

USE master

Go

 EXEC sys.sp_configure N’backup compression default’, N’1’

 GO

 RECONFIGURE WITH OVERRIDE

 GO

Mustafa EL-Masry Configuration, Configuration

Configuring SQL Server Surface Area before using OPENROWSET


–Configuring SQL Server Surface Area before using OPENROWSET

USE

[master]

GO

 

sp_configure

‘show advanced options’,1

GO

 

reconfigure

withoverride

GO

 

sp_configure

‘Ad Hoc Distributed Queries’,1

GO

 

reconfigure

withoverride

GO

 

Mustafa EL-Masry Script

Bulk insert In SQL Server


/*

Bulk insert And OPENROWEST

*/

http://msdn.microsoft.com/en-us/library/aa225968(v=sql.80).aspx

—————————*******************Bulk Insert******************—————————–

–Step 1 Create Table [CSVTest]

CREATE

TABLE [dbo].[CSVTest]

(

[ID] [int]

NULL,

[FirstName] [varchar]

(40)COLLATE Arabic_CI_AS NULL,

[LastName] [varchar]

(40)COLLATE Arabic_CI_AS NULL,

[nvarchar]

(50)COLLATE Arabic_CI_AS NULL

—Step 2 Create Text File on D:\ Location

—Step 3 Try Bulk Insert

go

BULK

INSERT

[CSVTest]

FROM

‘D:\TxtFile2.txt’

WITH

(

BATCHSIZE

= 2,—- Show The Rows Affected

FIRSTROW

= 2,—– Start the insert from Row number 2

FIELDTERMINATOR

=‘,’,

ROWTERMINATOR

=‘\n’,

LASTROW

= 4,—- End the insert when you Arraive to row number 4

MAXERRORS

= 1 — Number of errors After this Number The SQL not complete the insert the defult value is 10

)

GO

–Step No 4 Check the Data inserted

Select

*from [CSVTest]

Delete

from [CSVTest]

Drop

Table [CSVTest]