?
Solved

Changing recoverymode on MSSQL

Posted on 2012-09-21
6
Medium Priority
?
429 Views
Last Modified: 2012-10-06
Hi,

I have plans for changing a database recovery mode from Simple to Full because T-Logs backups are demanded

I can't find docs about it, the question is simple, can you change the recovery mode on the fly?
What are the suggested checks to do after?
(i will run a bak file anyway at first) and there is a backup of the server itself.
0
Comment
Question by:Oldiesel
  • 3
5 Comments
 
LVL 81

Assisted Solution

by:arnold
arnold earned 200 total points
ID: 38421804
Yes, using ssms, properties of the database, options.
Make sure you set growth and size limit on the Log file.
The most important part is to configure log backups.  This is the way you would limit/control the size of the transaction log.
If either/ both options not used, your log file could grow and might lead to system running out of space on the drive.
http://msdn.microsoft.com/en-us/library/ms189272.aspx
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 600 total points
ID: 38423064
Yes, you can do the switch with one simple command (or the GUI, although I greatly prefer commands to the GUI).

But after this switch, from SIMPLE to FULL, you need do an immediate full db backup.


ALTER DATABASE <db_name> SET RECOVERY FULL

BACKUP DATABASE <db_name>
TO DISK = 'x:\full\path\to\backup\file\backup.bak'
WITH FORMAT
0
 

Author Comment

by:Oldiesel
ID: 38423743
Scott

I presume the backup part after the switch is not needed, i wil kick off the T-log backup by Netbackup right away.
It is configured to backup T-logs every hour.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38423788
>> I presume the backup part after the switch is not needed <<

??  I emphasized (underlined) it; let me be even clearer:

YOU NEED TO DO A FULL DATABASE BACK AFTER THE SWITCH.

SQL will not let you start taking log backups until the full backup is done.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38428761
The switch from SIMPLE to FULL is trivial anyway, so you do NOT need a db backup BEFORE the switch.

But to get to FULL recovery from SIMPLE, SQL basically requires you to take a full backup AFTER the switch.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question