{"id":129612,"date":"2009-11-25T03:00:00","date_gmt":"2009-11-25T08:00:00","guid":{"rendered":"tag:blogger.com,1999:blog-1652419620964346731.post-3717345632914950518"},"modified":"2009-11-25T03:00:00","modified_gmt":"2009-11-25T08:00:00","slug":"how-to-change-the-recovery-model-on-all-databases-at-once-in-sql-2005-management-studio","status":"publish","type":"post","link":"https:\/\/mereja.media\/index\/129612","title":{"rendered":"How to Change The Recovery Model On All Databases at Once in SQL 2005 Management Studio"},"content":{"rendered":"

In Microsoft SQL there are three ways to set your recovery model. Each method has their own pluses and minus. Each of them have their own reason for using them.<\/p>\n

The three recovery models are Full, Bulk-Logged and Simple. The first one, Full, is set by default in SQL 2005. According to SQL-Recovery.com<\/a>, Full recovery model is:<\/p>\n

\n

This is your best guarantee for full data recovery. The SQL Server fully logs all operations, so every row inserted through a bulk copy program (bcp) or BULK INSERT operation is written in its entirety to the transaction log. When data files are lost because of media failure the transaction log can be backed up.<\/font><\/p>\n<\/blockquote>\n

Bulk Logged:<\/p>\n

\n

This model allows for recovery in case of media failure and gives you the best performance using the least log space for certain bulk operations, including BULK INSERT, bcp, CREATE INDEX, WRITETEXT, and UPDATETEXT.<\/font><\/p>\n<\/blockquote>\n

Simple:<\/p>\n

\n

It allows for the fastest bulk operations and the simplest backup-and-restore strategy. Under this model, SQL Server truncates the transaction log at regular intervals, removing committed transactions. Only full database backups and differential backups are allowed.<\/font><\/p>\n<\/blockquote>\n

Changing the recovery model is as simple as right clicking on the database in SQL 2005 management Studio, Clicking on Properties, Clicking on Options, and selecting your recovery model from the drop down menu. This can be tedious though if you have multiple databases. Some web servers have 50 plus databases. Do you want to manually change each one? I didn\u2019t think so!<\/p>\n

Below is a simple T-SQL<\/a> script you can run to change all of your databases to the the recovery model of choice!<\/p>\n

\n

USE master <\/strong><\/font><\/p>\n

GO <\/strong><\/font><\/p>\n

— Declare a variable to store the value [database name] returned by FETCH. <\/strong><\/font><\/p>\n

DECLARE @dbname sysname, @cmd varchar(1000) <\/strong><\/font><\/p>\n

— Declare a cursor to iterate through the list of databases <\/strong><\/font><\/p>\n

DECLARE db_recovery_cursor CURSOR FOR <\/strong><\/font><\/p>\n

SELECT name from sysdatabases <\/strong><\/font><\/p>\n

— Open the cursor <\/strong><\/font><\/p>\n

OPEN db_recovery_cursor <\/strong><\/font><\/p>\n

— Perform the first fetch and store the value in a variable. <\/strong><\/font><\/p>\n

FETCH NEXT FROM db_recovery_cursor INTO @dbname <\/strong><\/font><\/p>\n

— loop through cursor until no more records fetched <\/strong><\/font><\/p>\n

WHILE @@FETCH_STATUS = 0 <\/strong><\/font><\/p>\n

BEGIN <\/strong><\/font><\/p>\n

IF (SELECT DATABASEPROPERTYEX(@dbname,’RECOVERY’)) <> ‘<RECOVERYMODEL>’ and @dbName <> ‘tempdb’ BEGIN <\/strong><\/font><\/p>\n

— create the alter database command for each database <\/strong><\/font><\/p>\n

SET @cmd = ‘ALTER DATABASE "’ + @dbname + ‘" SET RECOVERY <RECOVERYMODEL>’ <\/strong><\/font><\/p>\n

— alter each dataabase setting the recovery model to <RECOVERYMODEL> <\/strong><\/font><\/p>\n

EXEC(@cmd) <\/strong><\/font><\/p>\n

PRINT @dbname <\/strong><\/font><\/p>\n

end <\/strong><\/font><\/p>\n

FETCH NEXT FROM db_recovery_cursor INTO @dbname <\/strong><\/font><\/p>\n

END <\/strong><\/font><\/p>\n

— close the cursor and deallocate memory used by cursor <\/strong><\/font><\/p>\n

CLOSE db_recovery_cursor <\/strong><\/font><\/p>\n

DEALLOCATE db_recovery_cursor<\/strong><\/font><\/p>\n<\/blockquote>\n

NOTE:<\/font><\/strong> Replace <RECOVERYMODEL><\/strong> with your model of choice (I.E. SIMPLE, BULK-LOGGED, FULL)<\/p>\n

What recovery model do you use on your servers in your environment? Why? Hit us up in the comments!<\/p>\n

Technorati Tags: sql 2005<\/a>,how to<\/a>,change<\/a>,recovery model<\/a>,multiple databases<\/a>,script<\/a><\/div>\n


\n\"Great<\/a><\/center><\/div>\n
\n<\/img><\/a> <\/img><\/a> <\/img><\/a> <\/img><\/a> <\/img><\/a> <\/img><\/a> <\/img><\/a> <\/img><\/a> <\/img><\/a> <\/img><\/a> <\/img><\/a> <\/img><\/a>\n<\/div>\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

In Microsoft SQL there are three ways to set your recovery model. Each method has their own pluses and minus. Each of them have their own reason for using them. The three recovery models are Full, Bulk-Logged and Simple. The first one, Full, is set by default in SQL 2005. According to SQL-Recovery.com, Full recovery […]<\/p>\n","protected":false},"author":1521,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-129612","post","type-post","status-publish","format-standard","hentry","category-news"],"_links":{"self":[{"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/posts\/129612","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/users\/1521"}],"replies":[{"embeddable":true,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/comments?post=129612"}],"version-history":[{"count":0,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/posts\/129612\/revisions"}],"wp:attachment":[{"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/media?parent=129612"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/categories?post=129612"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/tags?post=129612"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}