May 26, 2022 at 4:53 pm
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?
May 26, 2022 at 5:19 pm
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.
May 26, 2022 at 5:26 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 26, 2022 at 5:27 pm
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?
May 26, 2022 at 6:18 pm
You can take a full backup at Year End every year. Not sure what you do with them though.
May 26, 2022 at 7:59 pm
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.
May 27, 2022 at 11:57 am
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!!!
May 27, 2022 at 1:09 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply