TSQL restore script help

[Diepenheim]

Hello all,

I have want to restore a database from an other server to an other with SQL 7
I was trying a small test script sofar but I got this error.

Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013)

Can somebody help me with this.
Or is somebody owner of a very handy TSQL restore script.

Thanx.

Casper.

[rogerjh]

The problem is either you are still attached to the database, or other users are attached to the database.

1. Make sure you have no connections to the database by running sp_who2 and reviewing the results.

2. If you have no connections to the database (and still getting same error) create and run this procedure; retrieve the output and run against master database:

CREATE procedure cjsp_killconn
as
DECLARE @ids varchar(25)
DECLARE id_cursor CURSOR FOR
select spid from master.dbo.sysprocesses
where db_name(dbid) NOT in ('', 'master', 'msdb&#39

OPEN id_cursor
FETCH NEXT FROM id_cursor into @ids
WHILE (@@FETCH_STATUS=0)
BEGIN
print 'kill ' + @ids
FETCH NEXT FROM id_cursor into @ids
END
CLOSE id_cursor
DEALLOCATE id_cursor
GO

----

Will kill all other connections to all other databases on the server. The only problem is that if someone is still using the database, they will re-attach which leads to...

3. Disconnect the server from the network either by disabling the network adapter from Network/Properties on Win2k Server, or physically disconnecting the cable. Stop/Start MSSQLServer service, then place database to be recovered in single user mode; this should ensure no users can connect to the database. At this point, either re-connect the cable and restore, or leave the server disconnected and restore (just in case an outside user manages to take the database before you get to it).



------------
Diepenheim at 3/6/2002 2:54:05 AM

Hello all,

I have want to restore a database from an other server to an other with SQL 7
I was trying a small test script sofar but I got this error.

Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013)

Can somebody help me with this.
Or is somebody owner of a very handy TSQL restore script.

Thanx.

Casper.