Useful ALTER Statements


Change Recovery Models

Here I have tried to explain the process of changing recovery models to one or multiple databases at one step.

Please run the following query to find current recovery model set for each database …


SELECT name AS [Database Name], recovery_model_desc AS
[Recovery Model] FROM sys.databases

 

Now if you wish to change recovery model for any of your database, you may run this query


ALTER DATABASE mydbname SET recovery simple

 

Use following query to generate script for you, if you wish to change recovery models for all the databases in SQL instance, use the output of this query for multiple databases.


SELECT ‘ALTER DATABASE ‘ + name + ‘ SET Recovery Simple ‘ 
FROM sys.databases WHERE recovery_model_desc=‘full’

 

ALTER TRIGGER


USE mydbname;
GO

SELECT * FROM sys.triggers 
WHERE is_disabled = 0 -- for already Enabled triggers 
ORDER BY [Name];

GO

Note – The column is_disabled in the sys.triggers and check for its value.

> To know the Tablename check for parent_id value and run the following query.


SELECT * FROM sysobjects 
WHERE id=2041774331 -- Parent_id in sys.triggers table

 

> To disable the trigger use this…


USE mydbname;
GO

DISABLE TRIGGER triggername ON tablename;

GO

 

> Now you will get disabled trigger after executing this…


USE mydbname;
GO

SELECT * FROM sys.triggers 
WHERE is_disabled = 1 -- for already Disabled triggers 
ORDER BY [Name];

GO

> To Enable the trigger again use this….


USE mydbname;
GO

ENABLE TRIGGER triggername ON tablename;
GO

—————————————————————————————-

ALTER DATABASE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: