SQTExpress Maintenance Commands

--Temporary remove all Check Constraints

EXEC SP_MSFOREACHTABLE 'ALTER TABLE ? NoCheck Constraint All'

--Activate all Check Constraints

EXEC SP_MSFOREACHTABLE 'ALTER TABLE ? Check Constraint All'

--Set the Identity Insert on

set identity_insert emp_Employee ON

--Set the Identity Insert OFF

set identity_insert emp_Employee OFF

--Reset identity value programatically

DBCC CHECKIDENT('emp_Employee',RESEED,200) WITH NO_INFOMSGS

--Change the Datatype of Columns into the MS SQL 2005

ALTER TABLE emp_Employee ALTER COLUMN Item_Qty Numeric(18,2)

--Add new Columns into the MS SQL 2005

Alter table emp_Employee add TTT varchar(50) COLLATE DATABASE_DEFAULT DEFAULT NULL

--Remove columns into the MS SQL 2005

Alter table emp_Employee drop column TTT

--Delete all Records from the Table

Delete emp_Employee

--Drop the Table from the Database

Drop table emp_Employee

--Configure SQL Server Operations externally

EXEC master.dbo.sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

EXEC master.dbo.sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

EXEC master.dbo.sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

EXEC master.dbo.sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

--BackUp Database

BACKUP DATABASE Temp_Master TO DISK = N'C:\\Temp_Master.bak'

WITH NOFORMAT, NOINIT, NAME = N'Temp_Master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

--Restoring Database File, Note: You can't restore a database, which is currently in Use...

Use master

Go

RESTORE Temp_Master

FROM DISK = 'C:\\Temp_Master.bak'

GO