Restore Process - 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)

Restore Process

Author

Topic

kelvin.loh
Starting Member

3 Posts

Posted - 2002-07-30 : 21:48:53

Hi,

Actually, i have added this restoration as a job to be done every night. I put it as :-
restore database <database name> from disk= <filename>'.

But a few questions come into my mind, :-

Is this a force restoration? What are the things I should add in to make this a force restoration?

If there are still users in the database, how can I automate the killing process? What should i add into the command line

Usually, how do people have a automate restoration to be done every night on another server.

Do advise me. Thank you and best regards.

Kelvin

braddoro
Starting Member

7 Posts

Posted - 2002-07-30 : 21:59:50

Here is a script that I run before I perform my restore procedure (see DB Restore What do you think?). This procedure will kill any non-server users that are attached to the DB in question. I then create a job with 3 steps in it. First I run this script (below), then the one I posted in the other message, then I run a step that updates the stastics. It works pretty well, I do this so I can quickly create databases on a development server for my development team.

GL!

CREATE procedure usp_Process_KillAllUsers_CommonTables
as
declare @int_UserID integer
declare @str_KillString nvarchar(100)

set nocount on

create table #tmp_KillUsers (UserID integer)

insert into #tmp_KillUsers (userid)
select master..sysprocesses.spid
from master..sysprocesses
inner join CommonTables..sysusers on master..sysprocesses.uid = CommonTables..sysusers.uid
inner join master..sysdatabases on master..sysprocesses.dbid = master..sysdatabases.dbid
Where master..sysdatabases.name = 'CommonTables'

Start:

set @int_UserID = (Select top 1 UserID from #tmp_KillUsers)

set @str_KillString = (Select top 1 'Kill ' + rtrim(convert(char(3),UserID,0)) from #tmp_KillUsers)

EXECUTE sp_executesql @str_KillString
delete from #tmp_KillUsers where userid = @int_UserID
if (select count(*) from #tmp_KillUsers) > 0
begin
goto Start
end
drop table #tmp_KillUsers

GO

sumwanlah
Starting Member

43 Posts

Posted - 2002-07-31 : 02:14:20

you might wanna try this one instead. i had this piece of code refined to my setup, so that you don't have to create temp tables. as you can see, it uses a while loop. the reasons why the spid and dbid constraints are included are simple. you wanna skip killing processes that are accessing any of the 4 system databases (where their system dbids are 1 to 4, AND standard system process ids, which access those databases numbering 1 to 9)


DECLARE @spid int ,@sql varchar(1000) 
while exists(select * from master.dbo.sysprocesses WHERE spid > 9 and dbid > 4) 
	begin
		select @spid = min(spid) from master.dbo.sysprocesses WHERE spid > 9 and dbid > 4 
		select @sql = 'kill ' + convert(varchar(10),@spid)
		exec (@sql)
	end

also, if you wanna forcibly restore a database over an existing one, use the restore database command with the 'Replace' option.

remember to check for unresolved user ids.

sounds to me like you creating a form of backup server right? the crudest way that you are doing now, is to backup databases from a source and copy the backups to another server right? if this is the case, then schedule this process on a regular basis.

next comes the tricky part. on the other server, you have to schedule the restore job immediately after the backup file is copied. when to precisely schedule the restore? depends on how long the backup file copy from the source server to its destination takes. If timing is not a big issue, give at least a 15min time difference, from the estimated time the file copy completes till the time the restore schedule should run. you also have to be aware of the minutest of details including the difference in times on both servers.

------------------------
What goes around...? Is the belt around my waist!

braddoro
Starting Member

7 Posts

Posted - 2002-07-31 : 08:38:20

I agree that sumwanlah's code is more elegant. In fact I'll probably use his coode.

But if I am reading it right, his code is going to kill all users on the server, not just in one database. That may not be what you want to do.

solart
Posting Yak Master

148 Posts

Posted - 2002-07-31 : 11:51:56

If you are using SQL Server 2000, then:

alter database 'pubs'
set restricted_user with rollback immediate

This only kills, if necessary, the users in the defined database.

Refer to BOL

solart

Edited by - solart on 07/31/2002 11:53:19

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources