30 เมษายน 2555 22:22I 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!!
30 เมษายน 2555 23:16ผู้ดูแล
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'
1 พฤษภาคม 2555 1:22If 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.
1 พฤษภาคม 2555 3:45
You can configure replication for this table and schedule it according to your requirements..
1 พฤษภาคม 2555 16:57
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
14 มิถุนายน 2555 16:08
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.