2

I would say that I'm bridging the gap between the beginner and intermediate user in SQL Server. I've taught myself through google searches, but I can't find anything decent related to this question.

I have a DB that has gone through many changes and each change has added a full backup set and some size to my backup file. I'm seeing this as a burden as I'm pretty sure I don't need the older backup sets, but would like to keep them in case.

Can anyone point me to a good tutorial or best practices for keeping SQL Backups, or just offer some good advice?

John the Ripper
  • 2,389
  • 4
  • 35
  • 61

2 Answers2

1

Normally you would take a backup each (hour/day/week) depending on need, and make these overwrite each previous backup.

You'd then archive these individual files off to perhaps tape, offsite, another server etc etc, and choose how many you wanted to keep based on business/legal need.

So in answer to your question, to keep the backup set down to a sensible size, set it to overwrite existing backups in the file, and then archive the file off to a seperare location at whatever interval you feel is appropriate.

dougajmcdonald
  • 19,231
  • 12
  • 56
  • 89
  • Does this still apply if I'm still in development and I don't have any real data? For example I will test my application by entering false data then restoring sever times each week. – John the Ripper Sep 08 '11 at 12:17
  • If you are in development I would still use a similar method, but not as frequently. I would tend to ensure that changes to the DB are scripted so they can be undone/rolled back anyway, these scripts can then be source controlled too – dougajmcdonald Sep 08 '11 at 12:44
1

For the time when your app will be in use and has real data in the database, you need to take "real" backups as dougajmcdonald already explained in his answer.

However, as long as your project is still in the development phase, you are taking the backups basically because you want to keep track of things like table definition changes, correct?

If yes, how about storing your changes as T-SQL scripts in source control, together with your actual code that accesses the database?

There are tools that generate CREATE TABLE scripts and stuff like that for an existing database.
Here are a few links to related SO questions:

(search for "sql server source control" if you want to find more)

There are also open source projects like FluentMigrator that help you to track database changes in source code (.net in this case, but there are similar tools for other languages).
Here is a tutorial from the original author of Fluent Migrator, explaining what Fluent Migrator is, why you might need it and how it works.

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182