February 6, 2008 at 9:46 am
Hello,
I am trying to come up with a solution to create a development server that mimics our production databases, but only houses 60 days worth of data. In other words, a 'sliding window'.
Our corporate IT dept will not support the servers if I use replication, so I cannot use that. How about log shipping? If I manually create the databases on the dev server and restore only 60 days worth of data then start using log shipping, will it work? If there are transactions that effect data older than 60 days, will they just be ignored? Or applied, but nothing will happen?
SQL 2005 SP2 on W2K3 Enterprise SP1
DB's range in size up to 150GB
Majority transactions are overnight, logs are in the 10's of GBs by week end
-John
P.S. Should I just give up and write SSIS packages to do it all?
February 6, 2008 at 12:18 pm
Log shipping will not be the solution for your case you need to go for snapshot replication with your conditions met using tsql.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 6, 2008 at 8:34 pm
Actually you could use log shipping.
What I'd think about doing is restoring the production db, then set up log shipping. You might want to pause the jobs for log shipping or have them only run during off hours. Be aware that you could have issues if you're changing data on Dev and the logs play a transaction that requires that data to be another way.
What you'd want to do after the restore is delete the data that's older than 60 days. Assuming you can do this, you could run that same delete everyday after the logs are restored. That would keep you with 60 days of data.
the other alternative is to use snapshot replication, as Sugesh suggested, to move only 60 days worth of data in the various articles to the second server, but you'd be reinitializing the subscriptions everyday. Not sure if that's a problem either.
Might need to test both ways.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply