2

I have a database, in SQL Server 2008, with only temporary data, but it has tables with a certain structure which I need. Should I back it up?

And if I should, should I make one copy of it or should I include it in the rest of the backup routine?

  • Unless the database is very large, consider following the same backup/recovery plan as other databases to simplify administration. Otherwise, you will need a process to create a new backup after schema changes or recreate the database from script. – Dan Guzman Sep 28 '15 at 12:32
  • Well If you ask my personal choice I would necessarily take full backup atleast thrice in a week. To add further backup policy should be as per RPO and RTO. You should ask question to yourself what if I loose data or table structure when database gets corrupt, would there be escalations ? – Shanky Sep 28 '15 at 12:54
  • It all depends on how long "temporary" means in your situation. However long that is will determine the recovery model as well as whether transaction log backups are necessary and how frequently they should occur. Or maybe you just need a couple differentials a day. If space is an issue, you can truncate your tables once the data is expired and then perform a backup. – Queue Mann Sep 28 '15 at 13:25
  • 1
    It's really this simple: If the DB corrupted or somehow vanished, would anyone miss it? If you answered "yes" or "i dont know" to that question, then back it up. – Kris Gruttemeyer Sep 28 '15 at 13:46
  • 1
    After all your answer, and looking online and talking to colleagues, I have decided to back it up every day since it didn't effect the performance. Better safe then sorry... Thx for your input :) – Erik Dahlen Sep 28 '15 at 13:50
1

If there is a database with data on it, someone, at some point, will consider it important. So best approach would be run a full backup to avoid any hassle. Maybe weekly with couple of daily differentials. If there are frequent changes then maybe a daily full backup and one differential in the middle of the day.

If the data is temporary and can be recreated somehow, then I would configure the recovery model to SIMPLE and take a FULL backup daily. And to avoid spaces issues, if the data is not so important and can be recreated afterwards, I would add a process to empty all tables before running the backup. Then configure another step after backup process in order to add fresh data once again so anyone using the database has data to continue working.

And last, but not least, you could just script the database and recreate it from those scripts. Do you have your database code under source control? That would help a lot on this case. We have couple of databases with similar characteristics and we only save the recreation scripts, data can be easily recreated with couple of scripts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.