Restore Fail

  • Thread starter Thread starter ad
  • Start date Start date
A

ad

I use the SQL:
"use master; restore database myBase from disk = 'c:\MyBackup.bak' with
Replace " in program
to restore myBase form the disk file MyBackup.bak, the disk file
MyBackup.bak is a Sql2005 backup file.
But it result a error:
Exclusive access could not be obtained because the database is in use.

I think there must have some connection still exist.

How can I disconnect all connections to myBase before execute the restore
command?
 
From http://www.dbmaint.com/download/util_proc/sp_dbm_kill_users.sql
you can use the following Stored Procedure:


USE master
GO
CREATE PROC sp_dbm_kill_users @dbname sysname, @delay DATETIME = '00:00' AS
/*******************************************************************************/
/* This procedure executes KILL for all connections in the specified
database. */
/* The procedure can be mofified so it kills all connections for a
server. */
/* The procedure takes the following parameters:
*/
/* @dbname SYSNAME (required): Database name.
*/
/* @delay DATETIME (optional) [0] : Optional delay (s) before resume.
*/
/* Written by Tibor Karaszi and Börje Carlsson 1999. www.dbmaint.com
*/
/* Tested on verion 6.5, 7.0 and 8.0.
*/
/*******************************************************************************/
SET NOCOUNT ON
--Get version number and verify supported version
DECLARE @ver VARCHAR(7)
SELECT @ver = CASE
WHEN CHARINDEX('6.50', @@VERSION) > 0 THEN '6.50'
WHEN CHARINDEX('7.00', @@VERSION) > 0 THEN '7.00'
WHEN CHARINDEX('8.00', @@VERSION) > 0 THEN '8.00'
ELSE 'Unknown'
END
IF @ver = 'Unknown'
BEGIN
RAISERROR('Unsupported version of SQL Server.',16,1)
RETURN -101
END

DECLARE loop_name INSENSITIVE CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)

OPEN loop_name
DECLARE @conn_id SMALLINT
DECLARE @exec_str VARCHAR(255)
FETCH NEXT FROM loop_name INTO @conn_id
WHILE (@@fetch_status = 0)
BEGIN
SELECT @exec_str = 'KILL ' + CONVERT(VARCHAR(7), @conn_id)
EXEC( @exec_str )
FETCH NEXT FROM loop_name INTO @conn_id
END
DEALLOCATE loop_name

WAITFOR DELAY @delay
GO

/* Sample Execution:
EXEC sp_dbm_kill_users pubs
EXEC sp_dbm_kill_users @dbname = pubs, @delay = '00:00:05'
*/
 
Thanks,
But I can't create stored procedure in the server.
Can I disconnect all connections by program?


luxspes said:
From http://www.dbmaint.com/download/util_proc/sp_dbm_kill_users.sql
you can use the following Stored Procedure:


USE master
GO
CREATE PROC sp_dbm_kill_users @dbname sysname, @delay DATETIME = '00:00'
AS
/*******************************************************************************/
/* This procedure executes KILL for all connections in the specified
database. */
/* The procedure can be mofified so it kills all connections for a server.
*/
/* The procedure takes the following parameters: */
/* @dbname SYSNAME (required): Database name. */
/* @delay DATETIME (optional) [0] : Optional delay (s) before resume. */
/* Written by Tibor Karaszi and Börje Carlsson 1999. www.dbmaint.com */
/* Tested on verion 6.5, 7.0 and 8.0. */
/*******************************************************************************/
SET NOCOUNT ON
--Get version number and verify supported version
DECLARE @ver VARCHAR(7)
SELECT @ver = CASE
WHEN CHARINDEX('6.50', @@VERSION) > 0 THEN '6.50'
WHEN CHARINDEX('7.00', @@VERSION) > 0 THEN '7.00'
WHEN CHARINDEX('8.00', @@VERSION) > 0 THEN '8.00'
ELSE 'Unknown'
END
IF @ver = 'Unknown'
BEGIN
RAISERROR('Unsupported version of SQL Server.',16,1)
RETURN -101
END

DECLARE loop_name INSENSITIVE CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)

OPEN loop_name
DECLARE @conn_id SMALLINT
DECLARE @exec_str VARCHAR(255)
FETCH NEXT FROM loop_name INTO @conn_id
WHILE (@@fetch_status = 0)
BEGIN
SELECT @exec_str = 'KILL ' + CONVERT(VARCHAR(7), @conn_id)
EXEC( @exec_str )
FETCH NEXT FROM loop_name INTO @conn_id
END
DEALLOCATE loop_name

WAITFOR DELAY @delay
GO

/* Sample Execution:
EXEC sp_dbm_kill_users pubs
EXEC sp_dbm_kill_users @dbname = pubs, @delay = '00:00:05'
*/



I use the SQL:
"use master; restore database myBase from disk = 'c:\MyBackup.bak' with
Replace " in program
to restore myBase form the disk file MyBackup.bak, the disk file
MyBackup.bak is a Sql2005 backup file.
But it result a error:
Exclusive access could not be obtained because the database is in use.

I think there must have some connection still exist.

How can I disconnect all connections to myBase before execute the restore
command?
Click to expand...
Click to expand...
 
In SQLServer 2000 With enterprise manager, All Tasks -> Detach Database
-> Dialg, that dialog allows you to disconnect everyone... (just
remember to only use the "disconnect" feature, or will have to re-Attach
de database)
Thanks,
But I can't create stored procedure in the server.
Can I disconnect all connections by program?


From http://www.dbmaint.com/download/util_proc/sp_dbm_kill_users.sql
you can use the following Stored Procedure:


USE master
GO
CREATE PROC sp_dbm_kill_users @dbname sysname, @delay DATETIME = '00:00'
AS
/*******************************************************************************/
/* This procedure executes KILL for all connections in the specified
database. */
/* The procedure can be mofified so it kills all connections for a server.
*/
/* The procedure takes the following parameters: */
/* @dbname SYSNAME (required): Database name. */
/* @delay DATETIME (optional) [0] : Optional delay (s) before resume. */
/* Written by Tibor Karaszi and Börje Carlsson 1999. www.dbmaint.com */
/* Tested on verion 6.5, 7.0 and 8.0. */
/*******************************************************************************/
SET NOCOUNT ON
--Get version number and verify supported version
DECLARE @ver VARCHAR(7)
SELECT @ver = CASE
WHEN CHARINDEX('6.50', @@VERSION) > 0 THEN '6.50'
WHEN CHARINDEX('7.00', @@VERSION) > 0 THEN '7.00'
WHEN CHARINDEX('8.00', @@VERSION) > 0 THEN '8.00'
ELSE 'Unknown'
END
IF @ver = 'Unknown'
BEGIN
RAISERROR('Unsupported version of SQL Server.',16,1)
RETURN -101
END

DECLARE loop_name INSENSITIVE CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)

OPEN loop_name
DECLARE @conn_id SMALLINT
DECLARE @exec_str VARCHAR(255)
FETCH NEXT FROM loop_name INTO @conn_id
WHILE (@@fetch_status = 0)
BEGIN
SELECT @exec_str = 'KILL ' + CONVERT(VARCHAR(7), @conn_id)
EXEC( @exec_str )
FETCH NEXT FROM loop_name INTO @conn_id
END
DEALLOCATE loop_name

WAITFOR DELAY @delay
GO

/* Sample Execution:
EXEC sp_dbm_kill_users pubs
EXEC sp_dbm_kill_users @dbname = pubs, @delay = '00:00:05'
*/



I use the SQL:
"use master; restore database myBase from disk = 'c:\MyBackup.bak' with
Replace " in program
to restore myBase form the disk file MyBackup.bak, the disk file
MyBackup.bak is a Sql2005 backup file.
But it result a error:
Exclusive access could not be obtained because the database is in use.

I think there must have some connection still exist.

How can I disconnect all connections to myBase before execute the restore
command?
Click to expand...
Click to expand...
Click to expand...
 
But I am use Sql2005 Express to develop my application.
The user can't use these tools.
How can I disconnect all connections by program?



luxspes said:
In SQLServer 2000 With enterprise manager, All Tasks -> Detach Database ->
Dialg, that dialog allows you to disconnect everyone... (just remember to
only use the "disconnect" feature, or will have to re-Attach de database)
Thanks,
But I can't create stored procedure in the server.
Can I disconnect all connections by program?


From http://www.dbmaint.com/download/util_proc/sp_dbm_kill_users.sql
you can use the following Stored Procedure:


USE master
GO
CREATE PROC sp_dbm_kill_users @dbname sysname, @delay DATETIME = '00:00'
AS
/*******************************************************************************/
/* This procedure executes KILL for all connections in the specified
database. */
/* The procedure can be mofified so it kills all connections for a
server. */
/* The procedure takes the following parameters: */
/* @dbname SYSNAME (required): Database name. */
/* @delay DATETIME (optional) [0] : Optional delay (s) before resume.
*/
/* Written by Tibor Karaszi and Börje Carlsson 1999. www.dbmaint.com */
/* Tested on verion 6.5, 7.0 and 8.0. */
/*******************************************************************************/
SET NOCOUNT ON
--Get version number and verify supported version
DECLARE @ver VARCHAR(7)
SELECT @ver = CASE
WHEN CHARINDEX('6.50', @@VERSION) > 0 THEN '6.50'
WHEN CHARINDEX('7.00', @@VERSION) > 0 THEN '7.00'
WHEN CHARINDEX('8.00', @@VERSION) > 0 THEN '8.00'
ELSE 'Unknown'
END
IF @ver = 'Unknown'
BEGIN
RAISERROR('Unsupported version of SQL Server.',16,1)
RETURN -101
END

DECLARE loop_name INSENSITIVE CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)

OPEN loop_name
DECLARE @conn_id SMALLINT
DECLARE @exec_str VARCHAR(255)
FETCH NEXT FROM loop_name INTO @conn_id
WHILE (@@fetch_status = 0)
BEGIN
SELECT @exec_str = 'KILL ' + CONVERT(VARCHAR(7), @conn_id)
EXEC( @exec_str )
FETCH NEXT FROM loop_name INTO @conn_id
END
DEALLOCATE loop_name

WAITFOR DELAY @delay
GO

/* Sample Execution:
EXEC sp_dbm_kill_users pubs
EXEC sp_dbm_kill_users @dbname = pubs, @delay = '00:00:05'
*/




ad wrote:

I use the SQL:
"use master; restore database myBase from disk = 'c:\MyBackup.bak' with
Replace " in program
to restore myBase form the disk file MyBackup.bak, the disk file
MyBackup.bak is a Sql2005 backup file.
But it result a error:
Exclusive access could not be obtained because the database is in use.

I think there must have some connection still exist.

How can I disconnect all connections to myBase before execute the
restore command?
Click to expand...
Click to expand...
Click to expand...
 
ad said:
But I am use Sql2005 Express to develop my application.
The user can't use these tools.
How can I disconnect all connections by program?
Click to expand...
I am starting to think you need to explain exactly what do you mean
by "disconnect all connections by program" :S
 
Back
Top