Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dj463
Starting Member
11 Posts |
 Posted - 2012-04-16 : 16:33:00
|
| Our production database is growing large and we decieded to archive the production database to a different server in the same domain. So that our production database holds the data of last two months. This process has to be performed every two months so that we archive the historical data and delete it from our production server. What is the best possible way to do so?Do i need to create a linked server and write a query (Insert into Select *) for each and every table in the database. This would take a lot of time as there are more than 100 tables in the database.Plz advice |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-16 : 16:49:00
|
| are you apply partitioning on tables which needs to be archived? Also are you trying to achieve all tables or only rapidly growing fact tables?If partitioning is applied based on date, its easier to do archiving of old data as data will be organised into partitions based on date values.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Dj463
Starting Member
11 Posts |
Posted - 2012-04-16 : 17:13:05
|
| @visakh16: I want to archive all the tables in the database, not specific tables. Also i need to archive the production database to another server. Does partitioning tables work in this scenario? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Dj463
Starting Member
11 Posts |
Posted - 2012-04-19 : 16:52:25
|
quote:
Originally posted by visakh16
seehttp://vyaskn.tripod.com/sql_archive_data.htm------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
As i need to archive the data from production database on Server A to Archive database on Server B, do i need to use linked servers with the stored procedure in the above link |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-20 : 15:36:20
|
| yep...you've to use linked server if you want archive db to be in a different server altogether. For automating this, you can create sql agent jobs to run archive scripts based on a set schedule------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-04-21 : 06:25:26
|
| Do you have queries that require access to the archived data ? You'll need to analyse impact of performance to access data on a separate database (separate server)Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
Archiving database and move to another Server