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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-22 : 12:14:05
|
Ron G writes "Help,
When I run the following command through the SQL agent to restore a database, the Job occasionally fails after appearing to run to completion. (It runs for 1:55:10 then Fails. It then restarts and always finishes the second time in about 10 seconds longer 1:55:18)
We are running SQLServer 7.0 sp3
The output from the first step when it fails is.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Killing 21 dbo [SQLSTATE 01000] (Message 0) Killing 15 public [SQLSTATE 01000] (Message 0) Killing 15 public [SQLSTATE 01000] (Message 0) Process ID 15 is not an active process ID. [SQLSTATE 42000] (Error 6106). The step failed.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help would be greatly appreciated.
----------------------------------------------------------------
SELECT getdate()
GO
--
-- Script to kill all user connections in a database before restoring it
--
declare @spid varchar(20),
@user varchar(80),
@cmdstring varchar(200)
-- Change pubs to the database name to used
while exists ( select spid from sysprocesses where db_name(dbid) = 'cc3_report')
begin
select @spid=convert(varchar(20),spid),
@user=user_name(uid)
from
sysprocesses
where
db_name(dbid) = 'cc3_report'
print 'Killing ' + @spid + ' ' + @user
set @cmdstring = 'Kill ' + @spid
exec (@cmdstring)
end
RESTORE DATABASE cc3_report
FROM DISK='h:\DB_Backups\cc3_full_db.bkp'
WITH
REPLACE,
MOVE 'cc3_Data' to 'i:\sqldata\Data\cc3_Data.MDF',
MOVE 'cc3_Log' to 'i:\sqldata\Data\cc3_Log.LDF'
GO
SELECT getdate()
GO
use cc3_report
UPDATE globals
set system_nam = 'cCURA3 - OASIS cc3_report'
GO
sp_dboption @dbname='cc3_report',
@optname='trunc. log on chkpt.',
@optvalue='true'
GO
sp_grantdbaccess @loginame='dblink2'
GO
sp_addrolemember @membername='dblink2', @rolename ='db_owner'
GO
sp_revokedbaccess @name_in_db = 'auto_reporting'
GO
sp_grantdbaccess @loginame='auto_reporting'
GO
sp_addrolemember 'db_owner','auto_reporting'
GO
sp_addrolemember 'batch_reader','Darena'
GO
" |
|
|
|
|
|
|
|
Restore of DB randomly fails - first time