Backup by specified date interval

Question

Backup by specified date interval

lucasomoura

Valued Member

Points: 74
More actions
May 26, 2022 at 4:53 pm

#4039785

Good afternoon,
Consulting the SQL Server documentation I saw that there are some types of backup, but none of them answer my question.
I would like to know if it is possible to backup database in a certain interval. Ex.: I want a backup of data from year x to year y.
It's possible?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

Not sure what your goal is... Table specific ?

You could write a script to export the data from your tables based on date. Then export into a different database for each year, and save those databases.

Or you could take yearly backups, and save them, then restore to a different database name, and extract the differences.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

No, not directly from SQL Server anyway. SQL has no mechanism to allow only certain parts of data in a table to be backed up.

You back up databases (or filegroups, theoretically), not at the individual table level, and you can't specify data conditions on the backup.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

lucasomoura Valued Member Points: 74 More actions · Answer 3

thanks homebrew01 for answering,

in my case, it would not be a specific table but the entire database.

for example, I have 10 years of data, ok?

I want to take the first two years and do a restore.

Is there such a possibility?

homebrew01 SSC Guru Points: 55539 More actions · Answer 4

You can take a full backup at Year End every year. Not sure what you do with them though.

ratbak Ten Centuries Points: 1328 More actions · Answer 5

Backups store state at the point of backup.

Are you trying to implement a data retention policy that removes older data? If so, you'd need to implement processes to do that using periodic deletes, or possibly using table paritions (e.g., partitioned by year), which would allow you to truncate partitions that contain older data -- more sophisticated, more efficient for removing old data, but more complex to implement, & having potential impacts to query performance... Partitioning can be time-consuming on a large table. If you tried this, it might be best to start after existing old data was already deleted.

You'd probably want to use scheduled SQL Server Agent jobs to run the processes in either case. And consider archiving old data to different tables and different filegroup or database if you periodically need to use data older than two years.

lucasomoura Valued Member Points: 74 More actions · Answer 6

homebrew01 wrote:

You can take a full backup at Year End every year. Not sure what you do with them though.

Okay, I'll follow the strategy of deleting everything leaving only the last 2 years.

From that point on, backup annually.

That type of backup would be the Differential type, right?

Thanks to everyone who replied!!!

This reply was modified 1 year, 10 months ago by lucasomoura.
This reply was modified 1 year, 10 months ago by lucasomoura.

homebrew01 SSC Guru Points: 55539 More actions · Answer 7

A Differential captures the Changes to a database since the last Full backup. It has no value by itself. It is used to restore a database back to that point

I suggest reading about SQL backups, and sketch out what you're trying to accomplish. Backups save whatever is in the database at the time. They are not selective about which data to save from which year. You need to separate the data that you want to save.