Change SQL Database Recovery Model – 2 Easy Methods

This article explains how to change SQL database recovery model using SQL Server Management Studio (SSMS) and Transact-SQL.

According to Microsoft, a “recovery model” is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

With SSRS configured for SCCM or reporting in general, by default, the ReportServer database recovery model is enabled as FULL. This means that all activity in the ReportServer database will get logged to the transaction file. Most importantly, the transaction logs can grow with time.

When you want to reduce the size of the SQL Server-based SCCM reporting services log database, you might want to change the recovery model. SQL administrators will know of more ways to change the database recovery model like these.


https://remont.biz.pl/forum/thread-141126.html

http://famstock.co.uk/showthread.php?tid=12140

https://forum.cousinzins.fr/thread-2650.html

https://forum.bedwantsinfo.nl/thread-144841.html

https://1776freedomproject.com/forum/showthread.php?tid=1057

https://socalireefer.com/forum/showthread.php?tid=6060

https://oldschooldrp.com/showthread.php?tid=6003

https://whippedupgaming.com/showthread.php?tid=264603

https://csgamearena.net/showthread.php?tid=30795

https://australiantravelforum.com/travel/showthread.php?tid=139780

http://www.fischer-bayern.de/phpBB2/viewtopic.php?p=25420

http://www.fischer-bayern.de/phpBB2/viewtopic.php?p=22473

http://www.fischer-bayern.de/phpBB2/viewtopic.php?t=7140

http://www.fischer-bayern.de/phpBB2/viewtopic.php?t=7110

https://forum.iabi.or.id/discussion/204/welcome-bonus/

https://forum.zombvival.de/viewtopic.php?t=6121

https://forum.zombvival.de/viewtopic.php?t=6651

https://forum.zombvival.de/viewtopic.php?t=7051

https://forum.zombvival.de/viewtopic.php?t=239

https://nakenprater.com/viewtopic.php?t=13916


Recovery Models for SQL Databases

Three recovery models exist for SQL databases: simple, full, and bulk-logged. Typically, a database uses the full recovery model or the simple recovery model. A database can be switched to another recovery model at any time.

The model database sets the default recovery model of new databases. Before switching from the full recovery or bulk-logged recovery model, back up the transaction log.


  • Simple: Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. 
  • Full: No work is lost due to a lost or damaged data file.
  • Bulk-logged: Reduces log space usage by using minimal logging for most bulk operations.

https://nakenprater.com/viewtopic.php?t=19471

https://nakenprater.com/viewtopic.php?t=18761

https://nakenprater.com/viewtopic.php?t=15989

https://yomad.me/nomad/viewtopic.php?t=2731

https://yomad.me/nomad/viewtopic.php?t=11796

https://yomad.me/nomad/viewtopic.php?t=11914

https://yomad.me/nomad/viewtopic.php?t=11459

http://www.arcierimirasole.org/forum/viewtopic.php?f=14&t=18458

http://www.arcierimirasole.org/forum/viewtopic.php?f=14&t=18599

http://www.arcierimirasole.org/forum/viewtopic.php?f=14&t=13347

http://www.arcierimirasole.org/forum/viewtopic.php?f=14&t=9006

http://www.azs-bialystok.pl/forum/topic956.html

https://azs-bialystok.pl/forum/topic30208.html

http://azs-bialystok.pl/forum/post39749.html

http://www.mtbclubs.ru/viewtopic.php?t=7218

http://mtbclubs.ru/viewtopic.php?t=7434

http://mtbclubs.ru/viewtopic.php?t=25825

https://salvagecarforum.com/viewtopic.php?t=66933

https://salvagecarforum.com/viewtopic.php?t=60022

https://salvagecarforum.com/viewtopic.php?t=38020

https://salvagecarforum.com/viewtopic.php?t=72824

http://forum.inphtech.pt/viewtopic.php?t=178637


View Recovery Model of a MS SQL Database

To quickly view the recovery model of a MS SQL database, you can use these steps:

  • Launch the SQL Server Management Studio.
  • Connect to Microsoft SQL Server Database Engine.
  • Right-click any Database and select Properties which opens the Database Properties dialog box.
  • Under Select a page pane, click Options. You would see current recovery model displayed under Recovery model list box.


1. Change SQL Database Recovery Model using SSMS

Using SQL Server Management Studio, you can change the recovery model for a SQL database with the following steps:




Step 1: Launch the SQL Server Management Studio and connect to Database Instance.

Step 2: Once you are connected to Microsoft SQL Server Database Engine, on the left-hand side in Object Explorer pane, click the SQL server name to expand the server tree. Expand Databases and right-click on the database whose recovery model you wish to change and select Properties.

Step 3: Now you see the Database Properties dialog box. Under Select a page, click Options, and now you can set the recovery model for the selected database. By default, the recovery model for the SQL database is set to Full. From the Recovery model drop-down you can select Simple or Bulk-logged. Click OK to save the changes.


http://forum.inphtech.pt/viewtopic.php?t=178775

http://forum.inphtech.pt/viewtopic.php?t=193457

http://forum.inphtech.pt/viewtopic.php?t=195957

http://www.ethiocist.org/for-news-page-articles/1446-2020-11-16-09-03-13

http://biowong.freehostia.com/phpbb2/viewtopic.php?p=1544009

http://biowong.freehostia.com/phpbb2/viewtopic.php?t=985594

http://biowong.freehostia.com/phpbb2/viewtopic.php?p=1583785

http://biowong.freehostia.com/phpbb2/viewtopic.php?p=1583800

https://www.darkquarterer.it/forum/viewtopic.php?t=236102

https://www.darkquarterer.it/forum/viewtopic.php?t=151825

https://www.darkquarterer.it/forum/viewtopic.php?t=237878

https://www.darkquarterer.it/forum/viewtopic.php?t=237087

https://tnfnorth.com/viewtopic.php?t=249

https://tnfnorth.com/viewtopic.php?t=22347


2. Change Database Recovery Model using Transact-SQL

Another easy way to change the recovery model of a database is by using Transact SQL. T-SQL or Transact SQL is the query language specific to the Microsoft SQL Server product. It can help perform operations like retrieving the data from a single row, inserting new rows, and retrieving multiple rows.

Step 1: Launch the SQL Server Management Studio and connect to Database Engine.

Step 2: Once you are connected to Microsoft SQL Server Database Engine, on the left-hand side in Object Explorer pane, click the SQL server name to expand the server tree. Expand Databases and right-click on the database whose recovery model you wish to change and select New Query.

Step 3: In the Query window, enter the below query and click Execute to change the recovery model of the database. Before running the command make sure you replace “DBNAME” with the correct database name.

The commands appear to have completed successfully after running the aforementioned T-SQL command. Using Transact SQL, we were able to change the database’s recovery model.


Read Next

  • SCCM SQL Query to Find Last Restart Time of Devices
  • 2 Ways to Repair SQL Server Management Studio (SSMS)
  • SCCM SQL Server Upgrade from 2014 to 2017
  • How to Install SCCM Reporting Services Point | ConfigMgr SSRS

Comments

Post a Comment

Popular posts from this blog

The 10 Best Apartment Websites of 2019

Condo discoverer sites are prospering, with most destinations joined by Android and iOS applications for in a hurry get to. To abstain from passing up key highlights, be that as it may, it's ideal to concentrate on the sites themselves. Here's are the ten best sites to discover lofts, regardless of whether its a studio or a shabby 2 room. 1.Hotpads: Most Feature-Packed Apartment Finder  Naturally, this component rich site gives you a guide of the zone, with a rundown see close by. In the guide see, you select symbols of structures to see subtleties and photographs of explicit loft postings. Photographs additionally incorporate Google Street View. Worked in inquiry channels incorporate condo, value run, number of office setup showers, rental sorts, and property types. You can choose from rental kinds, for example, customary, sublet, corporate, and space for lease, just as from property types, for example, loft, apartment suite, duplex, house, and townhouse. On the of
Read more

The 10 Best Sites for Free Stock Images

On the off chance that you need to download free stock pictures to add to your site, blog, social profiles or whatever else, you need to realize where to search for them on the web. The best destinations have enormous libraries of astounding pictures in each classification conceivable that you can use for essentially anything by any stretch of the imagination—without expecting to give attribution on the off chance that you'd favor not. We scoured the web to locate the most elite. Examine the accompanying rundown with the expectation of complimentary stock photographs that will overwhelm you. 1.Unsplash: Crowdsourced Photos from Photograpehrs Around the World  Unsplash is a notable top choice, highlighting probably the most expert looking photography you'll discover anyplace on the web. The majority of its photographs originate from the liberal picture takers who give them to the network to use for nothing, no attribution fundamental (however valued). You can make your
Read more

ConfigMgr 2212 Technical Preview Update Details

There will be no ConfigMgr 2212 Technical Preview release. Yes, you heard it right: Microsoft skipped the 2212 technical preview release, and the next TP release will be version 2301. Typically, Microsoft releases a technical preview version every single month of the year. In the past, we also saw two technical preview releases in a month. But for the first time ever, there won’t be a technical preview release. On this blog, I have been covered the installation and new features all the versions of technical preview that have been released so far. Did you know that there have been 97 technical preview versions released by Microsoft so far? The two previous technical preview releases were TP 2211 and TP 2210 and version 2212 was expected in the month of December 2022. Here is an updated guide consisting of all the SCCM technical preview releases. https://www.tnfnorth.com/viewtopic.php?t=2830 http://tnfnorth.com/viewtopic.php?t=7189 http://tnfnorth.com/viewtopic.php?t=155931 https://tnfno
Read more