November 28, 2007 at 3:44 am
Hi Guys I hope you will be able to help me with a challenge I have been set.
We are running Sage1000 as our finance package, it sits on our SQL 2005 server.
We then have a Test environment running alongside it.
In total there are around 12 DB's running on the server, when we built the Test Environment we simply restored from the previous nights backups from Live.
The challenge is to somehow automate the "data refresh" procedure, IE, i would like an easier way to update data than restoring from the backups.
I have looked at writing a script but not sure where to start when it comes to selecting the most upto date .bak filename to restore from etc.
Any advice would be greatly appreciated
thanks
Andy
November 28, 2007 at 4:41 am
Well, if you are taking about "real-time" data refresh then you can look more into replication, mirroring and snapshots, otherwise you can hit a search on this site to find a lot of automated scripts....
--Ramesh
November 28, 2007 at 7:42 am
You can certainly write a scipt and schedule it.
MSDB database has details of all the backups done.
Check the following tables:
select * from msdb.dbo.backupfile
select * from msdb.dbo.backupset
select * from msdb.dbo.backupmediaset
select * from msdb.dbo.backupmediafamily
November 29, 2007 at 10:09 pm
Hi,
If you are not looking for most real time updates then you have following options
1. could create a package, which will overwrite the existing objects in target DB and schedule to run at prefered time
2. Write a script to transfer the daily *.bak file from source DB server to Target DB server and user SQLCMD and RESTORE command to restore in target server.
Both the above methods will replace existing objects/tables with the latest data.
with rgds,
M
November 29, 2007 at 11:30 pm
The best option that i would say is go for snapshot replication in non-production night hours to you rtest machine. configure to replicate data and objects.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 30, 2007 at 9:21 am
I like automated solutions. As much as I think Sugesh' is the simplest solution, if something doesn't work, then replication gets frustrating to work with.
Or it has in the past. Not enough SS2K5 experience with this to really know how easy/hard it can be.
I prefer the backup/restore procedure, but there are good reasons not to do this, especially from a financial system. I guess I'd ask what types of "updates" do you want to do. What makes the most sense? If you can easily develop filters (horizontal or vertical), then maybe Sugesh' solution is the best. Otherwise it might make sense to build some SSIS packages, string them together, and let them run. Nice thing is they're very easy to "pause" or disable" for a few days if you don't want the dev system changing.
Which is a very likely scenario.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 3, 2007 at 8:59 am
Thanks for everyones responses, i have return this morning from a few day break.
I will digest all of your suggestions and see if i can decipher which solution fits us best.
Thanks again
December 4, 2007 at 4:26 am
We actually have a T-SQL script which uses XPCmdShell to read a list of the backup files from a specific directory. Then we parse the name down to pull the datetime portion of the name (our backups are saved via a Litespeed maintenance plan with this info). This is how we find the most recent backup file. Then we run a cursor which does the restore.
After that, we have steps to fix orphaned users and assign specific database permissions for our test servers.
I know a lot of people would say that's not the best way to do it, but we don't use replication at work and this has worked out for us splendidly without a lot of problems. Biggest problem we've ever had with it is when the backup from the night before didn't run properly.
December 10, 2007 at 8:33 am
Ok here is where i am up to with this.
As we needed to individually update certain databases and not schedule them i have gone ahead and created a maintenance plan.
Each database having its own Subplan, within the plan i script a restore the db from dbname.bak then fix orphaned users.
My problem now is the files coming out of the Prod server backups are named bsCRM_backup_200712072100.bak for example.
How can i best copy the latest file from the DIR, copy and rename it so it is on my test server with a filename of bsCRM.bak
Thanks in advance guys.
December 10, 2007 at 8:41 am
There are a couple of ways you can do it.
A) Write a script task (C# .Net or VB .Net) on SSIS and run that job on a scheduled basis.
B) Enable XP_CmdShell and create a T-SQL task that copies & renames the file (most people don't recommend this option due to the security risk.
C) Create a batch file that does the move / rename for you and then schedule it in the OS with Windows Scheduler.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply