restore problem

hi
I have a problem with restore db (can not obtain exclusice access to db)
the question is:
how to obtain exclusive access to database using jdbc driver ?
how to log off all users and prerform restore ?
thanks for help
Artur
| From: "Monika" <monika@.o2.pl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: restore problem
| Date: Fri, 22 Oct 2004 09:57:42 +0200
| Organization: tp.internet - http://www.tpi.pl/
| Lines: 12
| Message-ID: <claeo7$80f$1@.nemesis.news.tpi.pl>
| NNTP-Posting-Host: acn10.neoplus.adsl.tpnet.pl
| X-Trace: nemesis.news.tpi.pl 1098432071 8207 83.25.65.10 (22 Oct 2004
08:01:11 GMT)
| X-Complaints-To: usenet@.tpi.pl
| NNTP-Posting-Date: Fri, 22 Oct 2004 08:01:11 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
ail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6416
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| hi
|
| I have a problem with restore db (can not obtain exclusice access to db)
| the question is:
| how to obtain exclusive access to database using jdbc driver ?
| how to log off all users and prerform restore ?
|
| thanks for help
|
| Artur
|
|
|
Hello Artur,
I'm not clear on what your end goal is. Are you asking how to restore a
database, or are you asking how to restrict database access to a single
user?
In SQL Server, you can issue an ALTER DATABASE command to set the
SINGLE_USER state option for a given database. This effectively allows
only one user to access the database at a time. This is a Transact-SQL
command, and it is not specific to JDBC. The syntax for this operation
would be:
ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
So, you could connect to your database using JDBC code and execute the
statement above. This will rollback any pending transactions and then
close all other connections to the same database.
===========
If you are trying to restore a database, then you should use the RESTORE
DATABASE Transact-SQL command:
USE master
GO
RESTORE DATABASE pubs FROM DISK='c:\pubs.bak' WITH REPLACE
You cannot be using the database that you intend to restore. This is why
the command above changes the context to Master first. If you are finding
yourself contending with access to the database for the restore operation,
you may consider taking the database offline first and then restoring a new
copy.
I recommend consulting the SQL Server 2000 Books Online for further
information.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Hello
my goal is to write a code (java), which will restore a database from file
I've tried to do it by executing sql statement
conn.createStatement().executeUpdate("RESTORE DATABASE pubs FROM
DISK='c:\pubs.bak' WITH REPLACE")
but I've got the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Exclusive access could not be obtained because the database
is in use.
my question is: how to restore a database from java (assuming that some
clients may be connected to database from another pc's) ?
(my first idea was to force exclusive access - but how to do it ?)
thanks again
Artur
Uytkownik ""Carb Simien [MSFT]"" <CarbinoS@.online.microsoft.com> napisa w
wiadomoci news:ZKRQvy4uEHA.3436@.cpmsftngxa10.phx.gbl...
> --
> | From: "Monika" <monika@.o2.pl>
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | Subject: restore problem
> | Date: Fri, 22 Oct 2004 09:57:42 +0200
> | Organization: tp.internet - http://www.tpi.pl/
> | Lines: 12
> | Message-ID: <claeo7$80f$1@.nemesis.news.tpi.pl>
> | NNTP-Posting-Host: acn10.neoplus.adsl.tpnet.pl
> | X-Trace: nemesis.news.tpi.pl 1098432071 8207 83.25.65.10 (22 Oct 2004
> 08:01:11 GMT)
> | X-Complaints-To: usenet@.tpi.pl
> | NNTP-Posting-Date: Fri, 22 Oct 2004 08:01:11 +0000 (UTC)
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> | Path:
>
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
>
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
>
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
> ail
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6416
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | hi
> |
> | I have a problem with restore db (can not obtain exclusice access to db)
> | the question is:
> | how to obtain exclusive access to database using jdbc driver ?
> | how to log off all users and prerform restore ?
> |
> | thanks for help
> |
> | Artur
> |
> |
> |
> Hello Artur,
> I'm not clear on what your end goal is. Are you asking how to restore a
> database, or are you asking how to restrict database access to a single
> user?
> In SQL Server, you can issue an ALTER DATABASE command to set the
> SINGLE_USER state option for a given database. This effectively allows
> only one user to access the database at a time. This is a Transact-SQL
> command, and it is not specific to JDBC. The syntax for this operation
> would be:
> ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> So, you could connect to your database using JDBC code and execute the
> statement above. This will rollback any pending transactions and then
> close all other connections to the same database.
> ===========
> If you are trying to restore a database, then you should use the RESTORE
> DATABASE Transact-SQL command:
> USE master
> GO
> RESTORE DATABASE pubs FROM DISK='c:\pubs.bak' WITH REPLACE
> You cannot be using the database that you intend to restore. This is why
> the command above changes the context to Master first. If you are finding
> yourself contending with access to the database for the restore operation,
> you may consider taking the database offline first and then restoring a
new
> copy.
> I recommend consulting the SQL Server 2000 Books Online for further
> information.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||| From: "Artur" <monika@.o2.pl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Re: restore problem
| Date: Wed, 3 Nov 2004 22:18:59 +0100
| Organization: tp.internet - http://www.tpi.pl/
| Lines: 116
| Message-ID: <cmbi6u$nm2$1@.nemesis.news.tpi.pl>
| References: <claeo7$80f$1@.nemesis.news.tpi.pl>
<ZKRQvy4uEHA.3436@.cpmsftngxa10.phx.gbl>
| NNTP-Posting-Host: acs36.neoplus.adsl.tpnet.pl
| X-Trace: nemesis.news.tpi.pl 1099516958 24258 83.25.70.36 (3 Nov 2004
21:22:38 GMT)
| X-Complaints-To: usenet@.tpi.pl
| NNTP-Posting-Date: Wed, 3 Nov 2004 21:22:38 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
ail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6443
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hello
|
| my goal is to write a code (java), which will restore a database from file
| I've tried to do it by executing sql statement
| conn.createStatement().executeUpdate("RESTORE DATABASE pubs FROM
| DISK='c:\pubs.bak' WITH REPLACE")
| but I've got the following exception:
| java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
| JDBC][SQLServer]Exclusive access could not be obtained because the
database
| is in use.
|
| my question is: how to restore a database from java (assuming that some
| clients may be connected to database from another pc's) ?
|
| (my first idea was to force exclusive access - but how to do it ?)
|
| thanks again
|
| Artur
|
|
| Uytkownik ""Carb Simien [MSFT]"" <CarbinoS@.online.microsoft.com> napisa
w
| wiadomoci news:ZKRQvy4uEHA.3436@.cpmsftngxa10.phx.gbl...
| >
| > --
| > | From: "Monika" <monika@.o2.pl>
| > | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > | Subject: restore problem
| > | Date: Fri, 22 Oct 2004 09:57:42 +0200
| > | Organization: tp.internet - http://www.tpi.pl/
| > | Lines: 12
| > | Message-ID: <claeo7$80f$1@.nemesis.news.tpi.pl>
| > | NNTP-Posting-Host: acn10.neoplus.adsl.tpnet.pl
| > | X-Trace: nemesis.news.tpi.pl 1098432071 8207 83.25.65.10 (22 Oct 2004
| > 08:01:11 GMT)
| > | X-Complaints-To: usenet@.tpi.pl
| > | NNTP-Posting-Date: Fri, 22 Oct 2004 08:01:11 +0000 (UTC)
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| > | Path:
| >
|
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
| >
|
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
| >
|
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
| > ail
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6416
| > | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > |
| > | hi
| > |
| > | I have a problem with restore db (can not obtain exclusice access to
db)
| > | the question is:
| > | how to obtain exclusive access to database using jdbc driver ?
| > | how to log off all users and prerform restore ?
| > |
| > | thanks for help
| > |
| > | Artur
| > |
| > |
| > |
| >
| > Hello Artur,
| >
| > I'm not clear on what your end goal is. Are you asking how to restore a
| > database, or are you asking how to restrict database access to a single
| > user?
| >
| > In SQL Server, you can issue an ALTER DATABASE command to set the
| > SINGLE_USER state option for a given database. This effectively allows
| > only one user to access the database at a time. This is a Transact-SQL
| > command, and it is not specific to JDBC. The syntax for this operation
| > would be:
| >
| > ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
| >
| > So, you could connect to your database using JDBC code and execute the
| > statement above. This will rollback any pending transactions and then
| > close all other connections to the same database.
| >
| > ===========
| >
| > If you are trying to restore a database, then you should use the RESTORE
| > DATABASE Transact-SQL command:
| >
| > USE master
| > GO
| > RESTORE DATABASE pubs FROM DISK='c:\pubs.bak' WITH REPLACE
| >
| > You cannot be using the database that you intend to restore. This is
why
| > the command above changes the context to Master first. If you are
finding
| > yourself contending with access to the database for the restore
operation,
| > you may consider taking the database offline first and then restoring a
| new
| > copy.
| >
| > I recommend consulting the SQL Server 2000 Books Online for further
| > information.
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| >
| > Are you secure? For information about the Strategic Technology
Protection
| > Program and to order your FREE Security Tool Kit, please visit
| > http://www.microsoft.com/security.
| >
|
|
|
Hello,
Thanks for the clarification. As I stated before, you can take the
database offline and forcibly kick other users out of the database. Any
pending transactions will be rolled back to maintain proper consistency.
Once this is done, you can restore the database as suggested. Below are
the queries you can run from your code to accomplish this task:
ALTER DATABASE pubs SET offline WITH rollback immediate
RESTORE DATABASE pubs FROM DISK='c:\pubsbak.bak' WITH REPLACE
These are all SQL Server concepts, so they are not specific to Java or
JDBC. Please consult the SQL Server 2000 Books Online for further
information.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.