error in restore

[NarendraSinghJTV]

i m using this stored procedure CREATE PROCEDURE [dbo].[sp_db_restore] @DBName varchar(60), @BackName varchar(120), @DataName varchar(60), @DataFileName varchar(120), @LogName varchar(60), @LogFileName varchar(120) AS RESTORE DATABASE @DBName FROM DISK = @BackName WITH MOVE @DataName TO @DataFileName , MOVE @LogName TO @LogFileName, REPLACE it gives the following error "RESTORE cannot process database 'databasename' because it is in use by this session. It is recommended that the master database be used when performing this operation. RESTORE DATABASE is terminating abnormally." plz help with any solution

Regards Narendra Singh (Jindal Tech Ventures)

[Ashfield]

I think the error says it all. It seems you are trying to run the stored proc in the database you are restoring, which obviously you can't

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[NarendraSinghJTV]

hey ashfield thanks for reply i found the answer before running restore command i have to use any other databse to make the restoring database free. After that when we run restore commands it works great. Thanks for all ur replies

Regards Narendra Singh (Jindal Tech Ventures)

[Vimalsoft Pty Ltd]

i usually have to Sp that Closes the Connection before i do a Back or Restore.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_ClearDatabaseConnections]

@DBNAME VARCHAR(255)

AS

SET NOCOUNT ON

DECLARE @SPID INT, @STR VARCHAR(255)

DECLARE USERS CURSOR FOR
SELECT SPID
FROM MASTER..SYSPROCESSES
WHERE DB_NAME(DBID) = @DBNAME

OPEN USERS
FETCH NEXT FROM USERS INTO @SPID

WHILE @@FETCH_STATUS <> -1
BEGIN
IF @@FETCH_STATUS = 0
BEGIN
SET @STR = 'KILL ' + CONVERT(VARCHAR, @SPID)
EXEC (@STR)
END
FETCH NEXT FROM USERS INTO @SPID
END

DEALLOCATE USERS

as you can see it accept the name of the DB that you want to Restore.

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[NarendraSinghJTV]

thanks for your reply good code

Regards Narendra Singh (Jindal Tech Ventures)

[Vimalsoft Pty Ltd]

You are Welcome :)

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/