azharmateen
asked on
BackUp/Restore Sql Server Database
Hi Dears.
I have facing a problem i am using VB 6.0 and using Sql Server2000. I want to backup my database and restore it from specific location. i am using ADO 2.6.
For Back up i used the following string.
conn.Execute _
"BACKUP DATABASE accounts" & _
" TO DISK='C:\Backups\Accounts.
" WITH INIT"
It works perfect.
And For Restore i used the following string.
conn.Execute " RESTORE DATABASE accounts FROM DISK='c:\BackUps\accounts.
but it cotained the following error" Exclusive Access Could Not be contained because the database is in use"
please any body give me any idea for the following problem or any substitute method for doing my BackUp/Restore.
Thanks.
I have facing a problem i am using VB 6.0 and using Sql Server2000. I want to backup my database and restore it from specific location. i am using ADO 2.6.
For Back up i used the following string.
conn.Execute _
"BACKUP DATABASE accounts" & _
" TO DISK='C:\Backups\Accounts.
" WITH INIT"
It works perfect.
And For Restore i used the following string.
conn.Execute " RESTORE DATABASE accounts FROM DISK='c:\BackUps\accounts.
but it cotained the following error" Exclusive Access Could Not be contained because the database is in use"
please any body give me any idea for the following problem or any substitute method for doing my BackUp/Restore.
Thanks.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
while aneeshattingal is perfectly right, here the probable reason:
if you want to restore the database, you have NOT to connect to that database itself (but connect to the master database instead).
if you want to restore the database, you have NOT to connect to that database itself (but connect to the master database instead).
in order to run the sql server in single user mode,
in
start->run type
sqlservr.exe -m
in
start->run type
sqlservr.exe -m
... to run the server in single user mode is ONLY needed to restore the master database.
ASKER
My problem is still there and connect to the master database and disconnect my current database but no vain my current database show in Loading Mode.
I on my single user but in this mode my vb connection has not been established.
so please tell me any other method.
Thanks.
I on my single user but in this mode my vb connection has not been established.
so please tell me any other method.
Thanks.
remove the keyword "norecovery" from the restore syntax
ping
Firstly establish a connection to the master database, using ADO:
'...
Set oConn = CreateObject("ADODB.Connec
oConn.Open "Provider=...REST OF CONNECTION STRING...Initial Catalog=master"
'...
Now execute the following SQL against that master connection (from http:/Q_10230761.html):
DECLARE @pid SMALLINT
DECLARE @db_name VARCHAR(255)
DECLARE @kill VARCHAR(255)
DECLARE c_users CURSOR FOR
SELECT DISTINCT db.name, p.spid
FROM sysprocesses p,sysdatabases db
WHERE p.dbid = db.dbid
AND hostname <> ""
AND UPPER(db.name) LIKE UPPER('YOUR_DATABASE_NAME'
OPEN c_users
FETCH c_users INTO @db_name, @pid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @kill = 'KILL '+ CAST(@pid AS VARCHAR)
EXECUTE (@kill)
END
CLOSE c_users
DEALLOCATE c_users
This will forcibly disconnect all users from the database in question. Now run your restore query.
HTH
J.
'...
Set oConn = CreateObject("ADODB.Connec
oConn.Open "Provider=...REST OF CONNECTION STRING...Initial Catalog=master"
'...
Now execute the following SQL against that master connection (from http:/Q_10230761.html):
DECLARE @pid SMALLINT
DECLARE @db_name VARCHAR(255)
DECLARE @kill VARCHAR(255)
DECLARE c_users CURSOR FOR
SELECT DISTINCT db.name, p.spid
FROM sysprocesses p,sysdatabases db
WHERE p.dbid = db.dbid
AND hostname <> ""
AND UPPER(db.name) LIKE UPPER('YOUR_DATABASE_NAME'
OPEN c_users
FETCH c_users INTO @db_name, @pid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @kill = 'KILL '+ CAST(@pid AS VARCHAR)
EXECUTE (@kill)
END
CLOSE c_users
DEALLOCATE c_users
This will forcibly disconnect all users from the database in question. Now run your restore query.
HTH
J.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is not the problem with your Statements,
This is because, the database you are trying to restore is already in use,
In order to overcome these messages you need to opt any of the following
1. Wait till all the connections to the database closes
2. Kill all the processes
3. Start sql server in single user mode
in order to the processes running on sql serevr, run the following in Query Analyser
sp_who or sp_who2
the above will list the process id s also
In order to kill one proccess rujn this in qa
KILL processid
Cheers!