March 22, 2012 at 2:50 pm
Hi all,
I have SQL Server 2000 running on W2K. I have a batch file that executes a stored proc to restore the database. The following is my stored proc:
CREATE PROCEDURE restoredb
AS
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
SET @spidstr = ''
IF (db_id('SampleDB1') < 4) AND (db_id('SampleDB2') < 4)
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid in (db_id('SampleDB1'), db_id('SampleDB2'))
IF LEN(@spidstr) > 0
BEGIN
exec (@spidstr)
END
alter database SampleDB1 set single_user with rollback immediate
alter database SampleDB2 set single_user with rollback immediate
restore database SampleDB1 from disk='d:\msserver\MSSQL\Backup\SampleDB1_db.bak' with move 'SampleDB1_log' to 'd:\msserver\SampleDB1\SampleDB1_log.ldf', move 'SampleDB1_data' to 'd:\msserver\SampleDB2\SampleDB1_data.mdf'
restore database SampleDB2 from disk='d:\msserver\MSSQL\Backup\SampleDB2_db.bak' with move 'SampleDB2_log' to 'd:\msserver\SampleDB2\SampleDB2_log.ldf', move 'SampleDB2_data' to 'd:\msserver\SampleDB2\SampleDB2_data.mdf'
alter database SampleDB1 set multi_user
alter database SampleDB2set multi_user
GO
So basically what the code does is to kill all the processes before doing the restore. Everything works fine only that I found the following in the Event Viewer before the log that said the database is restored:
[p]
Event Type:Information
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17055
Description:
8128 :
Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_regread'.
[/p]
I just wonder why it needs to read the registry. Am I doing anything wrong here?
March 22, 2012 at 3:46 pm
Arsenal (3/22/2012)
Hi all,I have SQL Server 2000 running on W2K. I have a batch file that executes a stored proc to restore the database. The following is my stored proc:
CREATE PROCEDURE restoredb
AS
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
SET @spidstr = ''
IF (db_id('SampleDB1') < 4) AND (db_id('SampleDB2') < 4)
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid in (db_id('SampleDB1'), db_id('SampleDB2'))
IF LEN(@spidstr) > 0
BEGIN
exec (@spidstr)
END
alter database SampleDB1 set single_user with rollback immediate
alter database SampleDB2 set single_user with rollback immediate
restore database SampleDB1 from disk='d:\msserver\MSSQL\Backup\SampleDB1_db.bak' with move 'SampleDB1_log' to 'd:\msserver\SampleDB1\SampleDB1_log.ldf', move 'SampleDB1_data' to 'd:\msserver\SampleDB2\SampleDB1_data.mdf'
restore database SampleDB2 from disk='d:\msserver\MSSQL\Backup\SampleDB2_db.bak' with move 'SampleDB2_log' to 'd:\msserver\SampleDB2\SampleDB2_log.ldf', move 'SampleDB2_data' to 'd:\msserver\SampleDB2\SampleDB2_data.mdf'
alter database SampleDB1 set multi_user
alter database SampleDB2set multi_user
GO
So basically what the code does is to kill all the processes before doing the restore. Everything works fine only that I found the following in the Event Viewer before the log that said the database is restored:
[p]
Event Type:Information
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17055
Description:
8128 :
Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_regread'.
[/p]
I just wonder why it needs to read the registry. Am I doing anything wrong here?
Why are you manually killing the SPIDs when you you do this before the restore:
alter database SampleDB1 set single_user with rollback immediate
alter database SampleDB2 set single_user with rollback immediate
This will kill any connections to these databases without you having to do it manually.
March 22, 2012 at 3:51 pm
Thanks for the reply Lynn! So what you suggested is to remove this:
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid in (db_id('SampleDB1'), db_id('SampleDB2'))
and leave this in the stored proc?
alter database SampleDB1 set single_user with rollback immediate
alter database SampleDB2 set single_user with rollback immediate
is this why we have the log in the event viewer?
March 22, 2012 at 3:54 pm
I have no idea why your getting the message in your log.
This is what I would have in your procedure:
CREATE PROCEDURE restoredb
AS
BEGIN
SET NOCOUNT ON
alter database SampleDB1 set single_user with rollback immediate
alter database SampleDB2 set single_user with rollback immediate
restore database SampleDB1 from disk='d:\msserver\MSSQL\Backup\SampleDB1_db.bak' with move 'SampleDB1_log' to 'd:\msserver\SampleDB1\SampleDB1_log.ldf', move 'SampleDB1_data' to 'd:\msserver\SampleDB2\SampleDB1_data.mdf'
restore database SampleDB2 from disk='d:\msserver\MSSQL\Backup\SampleDB2_db.bak' with move 'SampleDB2_log' to 'd:\msserver\SampleDB2\SampleDB2_log.ldf', move 'SampleDB2_data' to 'd:\msserver\SampleDB2\SampleDB2_data.mdf'
alter database SampleDB1 set multi_user
alter database SampleDB2set multi_user
END
GO
Possibly even pull the SET NOCOUNT ON out.
March 22, 2012 at 4:08 pm
Thanks Lynn, I will try it out.
I still hope someone can give me some clues on the xp_regread thing.
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply