How to take the snap shot of a table in sql server 2008

Question

I need your advice regarding taking snapshot of a table. I have a table named 'Balances'. I need to take the snapshot of this table('Balances') till may 7 2012 data, I need to use this snapshot table to create a report. and again on june 8 2012 i need to take another snapshot of this table and use it to create june report. I want to confirm with you that "CAN WE TAKE THE SNAPSHOT OF A SINGLE TABLE IN SQL SERVER??" I know we can take the snapshot of entire database but not a single table!! Correct me if i am  wrong!!


Thanks for your time!!

Raj Vardhan 

Answer 1

Unfortunately, you cannot take a snapshot of a table in SQL Server using native tools. There are a couple of ways to do this

1) Create another database and import the data from the source table to the destination table using either SSIS or bcp

2) Use 3rd party tools like LiteSpeed for SQL Server so you can perform object-level restore from backups

If your purpose is to simply create reports, why not create a TSQL query that will return data based on a specified datetime value, for example, SELECT cols from Balances WHERE datetime_column <= '2012-05-12'

---

Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn

Answer 2

If DML operation on the table is purely timestamp related. You may be able to create the view based on timestamp field of the table as report source.

Answer 3

You can configure replication for this table and schedule it according to your requirements.. 

Answer 4

Hi,

MS-SQL doesn't provide single-table backup/restore operations since its long gone (for good...) 6.5 release.

You'll have to export its data to an external container (CSV, MS-Access, SQL Express, another SQL Instance, whatever) and keep it properly identified.

I'd recommend you to script your table too, just in case its metadata changes in the middle (indexes, constraints, field definition, etc).

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 5

Hi,

As above said, we cannot take snap shot for a single table in SQL SERVER..

Click on these links for more information regarding snapshot of a table.

http://msdn.microsoft.com/en-us/library/ms175876.aspx

http://msdn.microsoft.com/en-us/library/ms175158.aspx

http://blogs.msdn.com/b/sqlcat/archive/2008/08/05/microsoft-sql-server-database-snapshots-and-synonyms.aspx

---

Sivamanikanta. P