DTS restore problem in SQL Server 2000

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 2005 Forums

SQL Server Administration (2005)

DTS restore problem in SQL Server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-20 : 09:52:56

scott writes "Hi!

I have been asked to write a DTS package that copies a database (call it DBOLD) to another database (call it DBNEW) every night. I do the following:

BACKUP DATABASE DBOLD_App
TO DISK = 'C:\backup\DBOLD.bak'
WITH INIT

GO

Then I do the following:

RESTORE DATABASE DBNEW FROM DISK = 'C:\backup\DBOLD.bak' WITH MOVE 'DBOLD_App_Data'
TO '... the new one .MDF', REPLACE,
MOVE 'DBOLD_App_Log' TO '... the new one.LDF', REPLACE

The RESTORE often (but not always) fails, particularly if 2005 Reporting Services has queried the data in DBNEW during the day. There should be no-one using the DBNEW overnight when the DTS package runs, so I have permission to "toss then overboard" if they are (note - DBNEW is effectively read-only, for reporting purposes, and is completely refreshed each night, so lost updates etc are not an issue).

How can I force the restore to proceed? I've tried dropping, detaching/attaching but the existing connection(s) apparently prevent this. I dont care about the contents of DBNEW as the data is conmpletely refreshed each night.

If you have any ideas I would be pleased to hear them...

Thanks in advance...Scott"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-20 : 10:07:38

Why use DTS for this?
You need to kill all users in the database you are overwriting.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-03-20 : 12:24:55

Hi Scott
couple of things, I know and have used the fact that SQL 2005 lets you kill destination database connection for log shipping. if not, i have a script called sp_kill_users, you can create this stored procedure in MSDB and you can just run it as the last statement before you run the log restores.

-------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/* and here's the sp to kill them all */

Alter procedure sp_KillUsers
@dbName varchar(32)

as
declare @spid smallint
declare @cmd varchar(32)

create table #tmp (
spid smallint,
ecid smallint,
status varchar(30),
loginame nchar(128),
hostname nchar(128),
blk char(5),
dbname nchar(128) NULL,
cmd varchar(255),
request_id int)
declare cLogin cursor for select spid from #tmp where dbname = @dbName

insert into #tmp exec sp_who

open cLogin
fetch cLogin into @spid

while @@fetch_status = 0
begin
select @cmd = 'kill ' + CONVERT(char, @spid)
print @cmd
execute( @cmd )
fetch cLogin into @spid
end

close cLogin
deallocate cLogin

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources