CODEWITHSUNDEEP
sql-server
JD.
JD.

Reputation: 15541

Transaction log back up when recovery of data is not important

We have a database that has configuration data in. When the applications are run against it, they basically do lots of calculations and then write some data to file. Unlike normal installations, we do not really need the transaction log. What I mean here is that we could take a backup of the database and use it without applying transaction logs to it to get it up to date.

Since the transaction logs are not that important to us, what should be the best backup strategy? Currently the transaction log is enormous (10 GB where as the database is about 50 MB, this was over a few months).

Could we just do an initial backup of the database and then each few days or less backup the transaction log, overwriting the current one? Or could we just delete the transaction log all together and have a new one started?

JD.

Upvotes: 1

Views: 265

Answers (2)

John Sansom
John Sansom

Reputation: 41819

Ensure the database is running in the Simple Recovery Model.

Doing so negates the need for you to perform transaction log backups. This recovery model automatically ensures that the inactive portions of the transaction log can become immediately available for reuse.

No longer concerned with the Transaction Log management, you can focus your attention on your backup strategy.

A weekly FULL Database Backup, perhaps with daily Differential Backups may suit your requirements.

Recovery Model Overview

Upvotes: 6

Sergey Olontsev
Sergey Olontsev

Reputation: 1104

As I understand, you do not write any data to your database. And for this reason the best backup strategy for you will be: 1. Change recovery model to simple and shrink the transaction log, using DBCC SHRINKFILE. 2. Make one full backup of your database.

Upvotes: 0

Related Questions