automating data refresh - Backup, Recovery and Disaster Recovery

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Database Administration » Backup, Recovery and Disaster Recovery » automating data refresh

automating data refresh

Options

Previous Topic · Next Topic

pvsramu

Posted: Thursday, May 24, 2007 10:29:20 AM

Rank: Newbie

Joined: 1/11/2007
Posts: 8
Points: 30
Location: Atlanta

Frequently we get request of data refresh. Its curbersome process everytime to take backup from prod and restore in development/test region. I am thinking to automate this process using stored procedure. But I need to pass various parameters to SP to tell where is source, destination, domain, etc.,

Anyone has better ideas/suggestions.

Thanks,
Ramu

Scott Whigham

Posted: Friday, May 25, 2007 10:52:33 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Here's a few questions I have (and this serves to test out neven's new List hack to YAF):

Do you need to do a full "data refresh" at the test server? In other words, do you basically delete the old database and replace it with a new copy?
Can you change structure of the tables of the production database to add a "Status" type of column?

pvsramu

Posted: Friday, May 25, 2007 11:27:16 AM

Rank: Newbie

Joined: 1/11/2007
Posts: 8
Points: 30
Location: Atlanta

We usually take latest backup copy of production database and then restore it in development/test server, overwriting the existing one. After that, map the users/logins.

Scott Whigham

Posted: Friday, May 25, 2007 1:56:17 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Ahhhh - so you're having trouble with users not mapping up to logins, eh? Having to resort to using sp_changeuserslogin? Ugggh! There are better ways Smile

In your case, I think that the backup/restore is the perfect choice. Had you only needed certain parts of the database then I would've suggested SSIS and other techniques. Backup/restore is the fastest way and the easiest to manage. I have a few backup-related stored procedures that I use (in my SQL Server scripts) to do this very thing.

You'll also want to check out sp_helprevlogin for SQL Server - it may prevent you from needing to run sp_changeuserslogin ever again Smile

ashwin4all1@gmail.com

Posted: Monday, December 31, 2007 1:13:44 AM

Rank: Newbie

Joined: 12/28/2007
Posts: 0
Points: 6
Location: bangalore

hi man

If u configure a log shipping Option between u r online server and production server
every day u will getting a fresh data u better think about log shipping

regards

ashwin

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.