I have an archiving question.

We have a History DB used for archiving old records from our main system and the application knows to query it

However, it becomes too big.

I would like to reinitialize the History DB. My idea was - in a nutshell:

  • Close down this DB,

  • Make it READ-ONLY,

  • Rename it to *old

  • Create a similar history DB (same structure/tables).

  • With the help of views/synonyms, the application will be able to query the data for both DBs without a code change.

My question is about the last part

Will that work ?

Can I create view/synonym (the view will query both of table DBs with UNION ALL) that will be the default go-to object name instead of the actual original table name ?

Our main problem is the backups, as the Archiving is done daily, we need also to back up this DB, and as it's huge DB, it's a waist of time and resources.

Thanks, Roni.

  • What is the issue with the size of the database? Your proposed solution doesn't seem to alter the fact that the DB is still queried and is going to remain its current size.
    – HandyD
    Apr 8, 2019 at 7:44
  • Thanks for the comment, The issue is mainly the backup. I want to close it and make it read-only and this way we won't backup this DB anymore
    – Roni Vered
    Apr 8, 2019 at 7:51
  • Are you taking FULL backups every day? Have you considered differentials? Apr 8, 2019 at 8:06
  • @RoniVered, I believe you are using compression for taking the backups?
    – Biju jose
    Apr 8, 2019 at 8:44
  • We're using diff backups, we're using data compression and backup compression
    – Roni Vered
    Apr 8, 2019 at 8:55

2 Answers 2


The built-in way to reduce backup size for VLDBs with read-only data is to parition your large tables, placing older partitions on read-only filegroups, and then take infrequent backups of the read-only filegroups (or whole database), combined with regular backups of the read-write filegroups. Then you can perform a Piecemeal Restore.

Will [using UNION ALL views over multiple databases] work ?

Yes, you can typically replace a table with a view without changing the application, unless the application does something unusual. The risk is in the performance, as the UNION ALL view across multiple databases doesn't really perform the same.

You might just try using columnstore compression on the large tables in the archive database.

  • Thanks, I think partitions with read-only FG and FG backups will be our solution.
    – Roni Vered
    Apr 10, 2019 at 6:57

Can I create view/synonym (the view will query both of table DBs with UNION ALL) that will be the default go-to object name instead of the actual original table name ?

Yes, you can create a view / synonym for cross database queries.

here are some side-effects that come to mind when implementing this:

  • Your application might rely on expecting a table and being handed a view. (there should be a way to mask this but I cannot find it at this moment)
  • Renaming your table(s) might break scripts / other views / procedures / ...
  • Collation mismatch between databases (if different)
  • Extra possible overhead due to two queries against two databases instead of one query against one.
  • There are a lot of extra considerations when using Read Only databases, such as statistics not being updated, not being auto created, .... More on that here.
  • Constraints / relationships between the two tables in the two databases will not work. This could result in duplicate primary keys, foreign key references not working, ....

  • ...

An example of a view over two tables in two databases

CREATE VIEW [dbo].[maintable]
SELECT id,val FROM [dbo].[maintable_v2] --new table
SELECT id,val FROM [my_test_old].[dbo].[maintable_old]; --old table

Querying this newly created view on the new database

SELECT id,val
FROM [dbo].[maintable]


id  val
1   oldvalue --old table 
2   NewValue --new table

An alternative

The best alternative would be David Browne - Microsoft's answer.

If that is not an option, here is a less optimal solution:

  1. Move the data in the table to a history table that is created on a read only filegroup in the same database.
  2. Create the new table
  3. Create the view on the history table and the new table
  4. Backup the read only filegroup separately, backup the database without the read only filegroup on separate occasions.

To backup the read_write filegroups:

TO DISK = N'C:\SQLBackups\DatabaseBackup.bak';
  1. If you are on enterprise you could do online filegroup (piecemeal) restores, on standard you cannot do this, meaning that you will have to wait on the restore of the read only filegroup.

This results in the only real advantage of splitting it into multiple databases when on standard edition would be restoring the non-history database first, making it accessible for queries while the history database is restoring.

  • Another option, (Not for your version, starting from SQL Server 2016): [SQL Server Stretch database][3. If the cold history data is not queried that much, store it in azure. This can be done without any query or application changes.
  • Thanks for your elaborate answer, you gave me things to think on :)
    – Roni Vered
    Apr 10, 2019 at 6:56
  • @RoniVered good to hear & I am glad it helped! Good luck! Apr 10, 2019 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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