Backup by specified date interval

  • 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?

  • 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.

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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?


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

  • 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.

  • 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 month, 1 week ago by  lucasomoura.
    • This reply was modified 1 month, 1 week ago by  lucasomoura.
  • 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 8 (of 8 total)

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