Professional Web Applications Themes

programming restore database with "share deny" poblem - Microsoft SQL / MS SQL Server

I write a program for SQL Server 2000 backup and restore, the backup is ok but the restore can't work because share deny, my SQL sytanx: restore database mydb from disk='c:\myback.dat' this program is a part of my ASP Web application, the ASP connection is: connstr="driver={SQL Server};server=(local);uid=sa;pwd=sa;database=mydb " Set conn = Server.CreateObject("ADODB.Connection") conn.Open connstr why I can't run the "restore database" command? please help me...

  1. #1

    Default programming restore database with "share deny" poblem

    I write a program for SQL Server 2000 backup and restore,
    the backup is ok
    but the restore can't work because share deny,

    my SQL sytanx:

    restore database mydb from disk='c:\myback.dat'

    this program is a part of my ASP Web application,

    the ASP connection is:

    connstr="driver={SQL Server};server=(local);uid=sa;pwd=sa;database=mydb "
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open connstr

    why I can't run the "restore database" command?

    please help me


    jiatiejun Guest

  2. #2

    Default Re: programming restore database with "share deny" poblem

    thanks, but I run follow SQL:

    ALTER DATABASE mydb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
    restore database mydb from disk = 'c:\myback.dat'

    the problem still exists.

    seem SQL server think the SA user's connect is useing database,
    but I can't run SQL without ADO.Connection,


    thank very much

    jiatiejun Guest

  3. #3

    Default Re: programming restore database with "share deny" poblem

    The database has to be in single user mode for a restore, restricted user is not good enough.

    ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    restore database mydb from disk = 'c:\myback.dat'

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "jiatiejun" <xaonline.com> wrote in message news:phx.gbl...
    thanks, but I run follow SQL:

    ALTER DATABASE mydb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
    restore database mydb from disk = 'c:\myback.dat'

    the problem still exists.

    seem SQL server think the SA user's connect is useing database,
    but I can't run SQL without ADO.Connection,


    thank very much


    Jacco Guest

  4. #4

    Default Re: programming restore database with "share deny" poblem

    Hi Amy,
     

    Single_user will prevent a normal user that is disconnected by ALTER
    DATABASE from reconnecting to the database, because there is still a user in
    that database, namely the one that issued the ALTER DATABASE statement.

    You are right that the only requirement for a restore is that there have to
    be no other users connected to the database and it doesn't have to be in
    single_user mode. But the only way to 100% enforce the requirement of no
    other users in the database is to set the database in single_user mode, as
    restricted_user mode still allows other users in, even if they have to be a
    member of the sysadmin, db_creator or db_owner roles. This is the problem
    jiatiejun ran into, there was probably still another connection open that
    used sa as login.

    I hope this clarifies my previous post.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Amy" <com> wrote in message
    news:phx.gbl... [/ref]
    user 


    Jacco Guest

  5. #5

    Default programming restore database with "share deny" poblem


    Instead of connecting to myDB connect to master, run the
    restore command, close the connection and reconnect to
    myDB.

     
    (local);uid=sa;pwd=sa;database=mydb" 
    rc Guest

  6. #6

    Default Re: programming restore database with "share deny" poblem

    Hi Linda,

    You are right, i'm sorry.
    I was using another computer at work this week and I did not check it's time
    settings.

    I will remember it next time :-)

    Thank you.

    "lindawie" <com> wrote in message
    news:phx.gbl... 


    Amy Guest

  7. #7

    Default Re: programming restore database with "share deny" poblem

    > "The database has to be in single user mode for a restore, restricted user
    is not good enough."

    AFAIK the only requirement is that no users are connected to it.
    From BOL:
    "When SINGLE_USER is specified, only one user at a time can access the
    database. When RESTRICTED_USER is specified, only members of the db_owner,
    dbcreator, or sysadmin roles can use the database. MULTI_USER returns the
    database to its normal operating state."

    So setting to SINGLE_USER will not prevent a 'normal' user that was
    disconnected by the ALTER DATABASE statement from reconnecting before you
    start the restore process which might again fail your restore.

    Try:

    USE master

    EXEC sp_addumpdevice 'disk', 'mydev', 'c:\mydev.bak'

    BACKUP DATABASE pubs TO mydev

    SELECT DATABASEPROPERTYEX ('pubs', 'useraccess')

    --returns 'multi_user'.
    --but since there are no users connected....

    RESTORE DATABASE pubs FROM mydev

    -- no problemo


    Amy Guest

  8. #8

    Default Re: programming restore database with "share deny" poblem

    Hi Jacco,
     
    in 

    BOL recommends issuing the ALTER DATABASE statement from 'master'.
    All the examples in BOL issue a 'USE master' before the ALTER DATABASE
    statement.
    although this is not a requirement, it makes sense logically and i usually
    follow it.

    If jiatiejun uses the sa login for his database users, i think that's a
    practice he should seriously reconsider.
    MS strongly discourages it.

    I hope that clarifies my previous post ;-) But I accept your argument as
    well.

    I think it would be nice if MS added another DB option like
    'SINGLE_RESTRICTED_USER'.
    I think it was possible in SQL 7 but i don't remember for sure.




    Amy Guest

Similar Threads

  1. Any thought on "Perl Database" based on "Tie:File"?
    By Public in forum PERL Miscellaneous
    Replies: 8
    Last Post: October 20th, 04:38 PM
  2. Replies: 1
    Last Post: September 5th, 04:16 PM
  3. Replies: 6
    Last Post: September 4th, 05:36 AM
  4. Somehow, the user was placed on the "Deny Logon Locally" option.
    By Ian in forum Windows Setup, Administration & Security
    Replies: 2
    Last Post: July 25th, 01:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139