?
Solved

BackUp/Restore Sql Server Database

Posted on 2006-05-16
9
Medium Priority
?
211 Views
Last Modified: 2010-05-01
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.bak'" & _
    " WITH INIT"
It works perfect.

And For Restore i used the following string.
conn.Execute " RESTORE DATABASE accounts FROM DISK='c:\BackUps\accounts.bak' With Move 'accounts_data' TO 'D:\accoutns.mdf',Move 'accounts_Log' to 'd:\accounts.ldf',Replace,nounload,stats=10,norecovery"

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.
0
Comment
Question by:azharmateen
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16688767
Hi azharmateen,
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!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16688778
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).

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16688783
in order to run the sql server in single user mode,
in
start->run          type
sqlservr.exe -m
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16688795
... to run the server in single user mode is ONLY needed to restore the master database.
0
 

Author Comment

by:azharmateen
ID: 16690646
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16690722
remove the keyword "norecovery" from the restore syntax
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 16697432
ping
0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 16698784
Firstly establish a connection to the master database, using ADO:

    '...
    Set oConn = CreateObject("ADODB.Connection")
    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.
0
 
LVL 16

Accepted Solution

by:
jimbobmcgee earned 750 total points
ID: 16698800
Sorry, the FETCH block needs to read:

    OPEN c_users
        FETCH c_users INTO @db_name, @pid
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @kill = 'KILL '+ CAST(@pid AS VARCHAR)
            EXECUTE (@kill)
            FETCH c_users INTO @db_name, @pid
        END
    CLOSE c_users
    DEALLOCATE c_users

J.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question