May 17, 2004 at 5:53 am
I have been given the task of replicating our production database to a testing database at least nightly. The testing database exists on the same server (we are a small shop...). Our production database is 11gig and I will have a database dump including hourly transaction logs available. Since it is our production server as well I don't want to be too intrusive with this process.
Thanks in Advance
Doug
May 18, 2004 at 3:31 am
Out of curiosity, why do you need the test database to be so up to date?
If users are looking to work on real live records that they worked on the day before, I suggest you persuade them otherwise by pushing them towards setting up test scenarios to work on.
This will save you a lot of work transferring data around and ensure the tests are performed more thoroughly and entire processes are covered.
The only time I will ever refresh the test database is after an upgrade. Be more forceful with those users. The customer's not always right!
May 18, 2004 at 8:19 am
I have run into the same situation. I set up a job to copy the database backup to another server (which you will not have to do) and restore the database nightly, along with the proper security scripted. This worked very well. If your customers think they need hourly logs applied, I would agree with Jonathan and question why, as it is your job to keep an eye on resources also!!!
May 18, 2004 at 8:58 am
Most of our data is time sensitive and our systems are real time. The logic in several systems won't show the users data unless the current date time is within some bounds. That is the reason for nightly updates along with three tier testing environment.
I do agreee that the hourly transaction logs are not really necessary except in extreme circumstances. The users don't really require the data be that upto date, but the design of several systems does. Perhaps that is bad design, though in our case I feel it is not.
dbmark, would you mind posting some of your script so I can get an idea of exactly what is required to automate the restore?
Thank you in advance,
Doug
May 18, 2004 at 9:18 am
Assuming you have the backup on the same machine:::
The fisrt thing to do is kill the spid's in the database:
DECLARE @spid varchar (10)
DECLARE @spidconv varchar (4)
DECLARE @sql nvarchar(1000)
DECLARE cursTables CURSOR FAST_FORWARD FOR
SELECT CAST(spid AS char(3))
FROM sysprocesses
WHERE dbid in (7, 8, 9) **** use the dbid of the database you wish to restore ***
OPEN cursTables
FETCH NEXT FROM cursTables INTO @spid
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = 'KILL ' + @spid
PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM cursTables INTO @spid
END
CLOSE cursTables
DEALLOCATE cursTables
Then restore it :
restore database DB from disk = 'd:\dailydwh\dailydwh.BAK' with recovery,
move 'DailyDWH_Prime' to 'd:\data\DailyDWH_Data.MDF',
move 'DailyDWH_User' to 'd:\data\DailyDWH_Userdata_Data.NDF',
move 'DailyDWH_Log' to 'd:\data\DailyDWH_Log.LDF', replace
go
Then add the proper security:
use DB
go
sp_adduser 'agf\p3qsd'
go
sp_addrolemember 'db_owner', 'agf\p3qsd'
go
You can get a lot cooler on the SQL, but this is the basics!
May 18, 2004 at 10:56 am
Absoutely awsome, thank you.
Doug
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply