Getting DB to test server - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Getting DB to test server

Author

Topic

Sully
Starting Member

30 Posts

Posted - 2005-04-11 : 11:51:22

Hi All,

We have a test server, each night a job runs which drops a specific DB on the test server, runs a DTS package to copy the same DB from the live server back to the test server, and then does a backup of the DB on the test server. The problem is, the copy does not work if anything is accessing the live DB, and the drop has already taken place. I can recreate the DB on test, but this seems more difficult than it should be. I'm thinking I could have a backup of the live DB executed, placing the file on the test server, then run the restore to the test DB. But, I don't know if I can do that in one job, so I can check the success/failure of the previous step. Also, is there a better way to accomplish this? Any help would be appreciated.

Sully

jason
Posting Yak Master

164 Posts

Posted - 2005-04-11 : 12:16:41

Search for log shipping on this site. If you have Enterprise, then SQL server supports this natively. If not, there are custom solutions available. Note that log shipping requires that your database be in full recovery mode.

Sully
Starting Member

30 Posts

Posted - 2005-04-11 : 12:56:14

Thanks for responding Jason...I did look at log shipping a bit before posting...it seems to be more than what I need, but I could be wrong. We don't want the two DB's to be totally in sync, the testing is done against the previous days data...I will look at it some more though.

Sully

jason
Posting Yak Master

164 Posts

Posted - 2005-04-11 : 13:09:16

Then I would suggest the custom solution. Should work great.

nishithrn
Yak Posting Veteran

58 Posts

Posted - 2005-04-12 : 04:44:03

Hi Sully,
You hv actually given a reply to your problem in ur question itself.

You can very well take a backup of the production database, move the backup of the production database to the test server and then do a restore on the same. You can schedule the above as mentioned below.

A. Take the backup of production database
B. Copy the production backup file to test server
C. Restore the production backup on test database

A can be scheduled on Production server
B and C can be scheduled on test server in two different steps.

Just make sure that there is some time difference between A(Prod Server) and 2 (test Server) for backup to take place.

kish
Starting Member

45 Posts

Posted - 2005-04-12 : 07:27:17

Nishit's advice is good. One more thing you need to take care of id that you should first break all the connections on the testserver before you perform a restore on that database.

Sully
Starting Member

30 Posts

Posted - 2005-04-12 : 10:28:29

Thanks Guys...in fact that is what I have done...I'm doing a backup of the live DB to the test box over night as a scheduled job, then the restore is done on the test box about a half hour later. It worked fine last night. There shouldn't be any users on at the time of the restore, but is there a way for me to check that or break the connections via a step in the job I set up. The restore failing isn't a huge deal, because I can do that myself if necessary...except when I'm on vacay, which is where I wish I was right now...lol.

jason
Posting Yak Master

164 Posts

Posted - 2005-04-12 : 10:39:28

I execute this script as preceding step on some scheduled jobs. Works pretty well, but I would be interested if there were a better way.

Dim dbobj, dbconn, qry, rs, dbid, fso, cl_log

'create a log file
Set fso = CreateObject("Scripting.FileSystemObject")
Set cl_log = fso.CreateTextFile("<path>",true)

'set the dbid to process here
dbid = 19

dbconn = "uid=<sysadmin account>;pwd=<password>;driver={SQL Server};server=MyServer;database=MASTER;dsn="

qry = "Use Master EXEC sp_lock"

On Error Resume Next
Set dbobj = CreateObject("ADODB.Connection")
CheckError "1"
dbobj.Open dbconn
CheckError "2"

Set rs = CreateObject("ADODB.Recordset")
CheckError "3"
rs.Open qry, dbobj
CheckError "4"

If Not rs.EOF then
While NOT rs.EOF
If rs("dbid") = dbid then
spid = rs("spid")
qry = "KILL " & spid
dbobj.Execute qry
CheckError "5"
cl_log.writeline("Process ID " & spid & " was terminated from database ID " & dbid & "." & vbcrlf)
End If
rs.MoveNext
Wend
Else
err.Raise 9000,"Check Locks on 19","No data returned by stored procedure."
CheckError "6"
End If

rs.Close
Set rs = Nothing
dbobj.Close
Set dbobj = Nothing

cl_log.writeline("Script executed successfully.")
cl_log.close
Set cl_log = Nothing
Set fso = Nothing

Public Sub CheckError(intLine)
If err.number <> 0 then
cl_log.writeline("Error Number: " & err.number & vbcrlf & "Error Description: " & err.Description & _
vbcrlf & "Line No.: " & intline)
cl_log.close
Wscript.quit
End If
End Sub

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-12 : 10:40:57

[code]
-- Kill Connections to a Database

declare @cmd varchar(4000)
declare @dbname sysname
declare @spid int

select @dbname = 'MYDBName'

declare curKill cursor forward_only
for
select
spid
from
master.dbo.sysprocesses a (nolock)
where
a.dbid = db_id(@dbname)
order by
a.spid

open curKill

fetch next from curKill into @spid

while (@@fetch_status = 0)
begin
select @cmd = 'Kill ' + str(@spid)
exec (@cmd)
fetch next from curKill into @spid
end

close curKill

deallocate curKill[/code]

CODO ERGO SUM

Sully
Starting Member

30 Posts

Posted - 2005-04-12 : 13:04:01

jason and Michael...thanks, I just tested your script Michael, worked like a charm...

Stuck in neutral in a pedal to the metal world,
Sully

jason
Posting Yak Master

164 Posts

Posted - 2005-04-12 : 16:08:53

You might also consider putting the DB in single user or restricted user mode to prevent updates during the operation. It's possible a user or automated process would reestablish the connection.

Sully
Starting Member

30 Posts

Posted - 2005-04-13 : 10:02:02

Thanks jason, I'll look into that as well.

Stuck in neutral in a pedal to the metal world,
Sully

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources