• Article Topics

  • Become a member of the SQLDBASupport community and receive notifications of new posts by email

    Join 82 other followers

  • Are you writer? Become an Author! Mail to

    amitvaid81 @ gmail dot com

  • Founder

    This is journey of a SQL DBA. I now work as a Sr. Database Architect and Solution Consultant, generally in performance tuning, clustering or high availability. The opinions expressed herein are my own personal opinions through real time experience.

    Amit Vaid

  • Advertisements

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’



USE mydbname;

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


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;

DISABLE TRIGGER triggername ON tablename;



> Now you will get disabled trigger after executing this…

USE mydbname;

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


> To Enable the trigger again use this….

USE mydbname;

ENABLE TRIGGER triggername ON tablename;




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: