I was wondering if it was possible to remove internet access from a PC but,
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you want
> to prevent users from using browsers or email? In order to communicate with
> the offsite SQL Server you'll need "internet access" but you may be able to
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>
|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
Showing posts with label program. Show all posts
Showing posts with label program. Show all posts
Friday, March 30, 2012
Restrict internet access using ODBC?
I was wondering if it was possible to remove internet access from a PC but,
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you wan
t
> to prevent users from using browsers or email? In order to communicate wit
h
> the offsite SQL Server you'll need "internet access" but you may be able t
o
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
>
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you wan
t
> to prevent users from using browsers or email? In order to communicate wit
h
> the offsite SQL Server you'll need "internet access" but you may be able t
o
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
>
Wednesday, March 28, 2012
Restrict Database Viewing
One of our programmers is going to write a program that involves some
sensitive data stored in the SQL server, using visual basic. Is there a way
to restrict him from viewing the data in the database, yet allowing him to d
o
his programming and debugging?Rather than developing against the live production database, consider
developing the application against a test database with identical schema.
You can use test data or obfuscated production data for testing.
Hope this helps.
Dan Guzman
SQL Server MVP
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:5118B0AD-451A-406E-81F9-F6EFA426B00E@.microsoft.com...
> One of our programmers is going to write a program that involves some
> sensitive data stored in the SQL server, using visual basic. Is there a
> way
> to restrict him from viewing the data in the database, yet allowing him to
> do
> his programming and debugging?|||I spend a lot of time writing apps against sensitive data, and often have to
explain to clients that valid data is needed to test against.
If you want to limit access to certain tables just create a user with
disabled access to sensitive table and allow only access to tables they will
use.
If the tables that they need, have actual sensitive data in them, then
create a duplicate database with different user and change some data.
i.e.. if its salary then directly edit the duplicate database and change all
the names and passwords to random data.
My suggestion is to have a third reliable party to backup database change
the sensitive data and return with another database name.
Then just hand that database on cd to your programmer.
As a second thought if its only reading i.e.. reports then create required
tables and fields as views.
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:5118B0AD-451A-406E-81F9-F6EFA426B00E@.microsoft.com...
> One of our programmers is going to write a program that involves some
> sensitive data stored in the SQL server, using visual basic. Is there a
> way
> to restrict him from viewing the data in the database, yet allowing him to
> do
> his programming and debugging?
sensitive data stored in the SQL server, using visual basic. Is there a way
to restrict him from viewing the data in the database, yet allowing him to d
o
his programming and debugging?Rather than developing against the live production database, consider
developing the application against a test database with identical schema.
You can use test data or obfuscated production data for testing.
Hope this helps.
Dan Guzman
SQL Server MVP
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:5118B0AD-451A-406E-81F9-F6EFA426B00E@.microsoft.com...
> One of our programmers is going to write a program that involves some
> sensitive data stored in the SQL server, using visual basic. Is there a
> way
> to restrict him from viewing the data in the database, yet allowing him to
> do
> his programming and debugging?|||I spend a lot of time writing apps against sensitive data, and often have to
explain to clients that valid data is needed to test against.
If you want to limit access to certain tables just create a user with
disabled access to sensitive table and allow only access to tables they will
use.
If the tables that they need, have actual sensitive data in them, then
create a duplicate database with different user and change some data.
i.e.. if its salary then directly edit the duplicate database and change all
the names and passwords to random data.
My suggestion is to have a third reliable party to backup database change
the sensitive data and return with another database name.
Then just hand that database on cd to your programmer.
As a second thought if its only reading i.e.. reports then create required
tables and fields as views.
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:5118B0AD-451A-406E-81F9-F6EFA426B00E@.microsoft.com...
> One of our programmers is going to write a program that involves some
> sensitive data stored in the SQL server, using visual basic. Is there a
> way
> to restrict him from viewing the data in the database, yet allowing him to
> do
> his programming and debugging?
Monday, March 26, 2012
restoring the database has failed
hi,
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'bark
I think the error messages is pretty clear. Someone is using the database, so you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users you have in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>
|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius
>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message[vbcol=seagreen]
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
database is in use.
>
>.
>
|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>
|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:
|||A better suggestion would be to take the database offline. If you are restoring it, who cares if it is offline or not.
I prefer this because of Web Server connection pooling and, in some situations, those connections have high privilege. Because of this, SINGLE_USER and RESTRICTED_USER may not successfully lock out users long enough to start up the restore operation. If you use the OFFLINE setting, then no one, not even you, can access that database until restored or brought back online.
ALTER DATABASE MyDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE
Sincerely,
Anthony Thomas
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message news:OYrgy%23t0EHA.1392@.TK2MSFTNGP14.phx.gbl...
thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...
[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
>
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> M'bark BOULOUIRD wrote:
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'bark
I think the error messages is pretty clear. Someone is using the database, so you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users you have in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>
|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius
>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message[vbcol=seagreen]
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
database is in use.
>
>.
>
|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>
|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:
|||A better suggestion would be to take the database offline. If you are restoring it, who cares if it is offline or not.
I prefer this because of Web Server connection pooling and, in some situations, those connections have high privilege. Because of this, SINGLE_USER and RESTRICTED_USER may not successfully lock out users long enough to start up the restore operation. If you use the OFFLINE setting, then no one, not even you, can access that database until restored or brought back online.
ALTER DATABASE MyDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE
Sincerely,
Anthony Thomas
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message news:OYrgy%23t0EHA.1392@.TK2MSFTNGP14.phx.gbl...
thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...
[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
>
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> M'bark BOULOUIRD wrote:
restoring the database has failed
hi,
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'barkI think the error messages is pretty clear. Someone is using the database, so you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users you have in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius
>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
>> hi,
>> i try to restore a database with this script
>> RESTORE DATABASE logship
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
>> WITH -- Norecovery,
>> restricted_user,
>> STANDBY = 'c:\undo.ldf',
>> REPLACE,
>> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_data.mdf',
>> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_log.ldf'
>> but i receive a error
>> Server: Msg 3101, Level 16, State 2, Line 1
>> Exclusive access could not be obtained because the
database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> i don't understand why it tells this?
>> thanks for your help.
>> M'bark
>>
>
>.
>|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a écrit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:
>> hi,
>> i try to restore a database with this script
>> RESTORE DATABASE logship
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
>> WITH -- Norecovery,
>> restricted_user,
>> STANDBY = 'c:\undo.ldf',
>> REPLACE,
>> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_data.mdf',
>> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_log.ldf'
>> but i receive a error
>> Server: Msg 3101, Level 16, State 2, Line 1
>> Exclusive access could not be obtained because the database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> i don't understand why it tells this?
>> thanks for your help.
>> M'bark
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'barkI think the error messages is pretty clear. Someone is using the database, so you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users you have in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius
>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
>> hi,
>> i try to restore a database with this script
>> RESTORE DATABASE logship
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
>> WITH -- Norecovery,
>> restricted_user,
>> STANDBY = 'c:\undo.ldf',
>> REPLACE,
>> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_data.mdf',
>> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_log.ldf'
>> but i receive a error
>> Server: Msg 3101, Level 16, State 2, Line 1
>> Exclusive access could not be obtained because the
database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> i don't understand why it tells this?
>> thanks for your help.
>> M'bark
>>
>
>.
>|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a écrit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:
>> hi,
>> i try to restore a database with this script
>> RESTORE DATABASE logship
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
>> WITH -- Norecovery,
>> restricted_user,
>> STANDBY = 'c:\undo.ldf',
>> REPLACE,
>> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_data.mdf',
>> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_log.ldf'
>> but i receive a error
>> Server: Msg 3101, Level 16, State 2, Line 1
>> Exclusive access could not be obtained because the database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> i don't understand why it tells this?
>> thanks for your help.
>> M'bark
restoring the database has failed
hi,
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'barkI think the error messages is pretty clear. Someone is using the database, s
o you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users y
ou have in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius
>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
database is in use.[vbcol=seagreen]
>
>.
>|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:|||A better suggestion would be to take the database offline. If you are resto
ring it, who cares if it is offline or not.
I prefer this because of Web Server connection pooling and, in some situatio
ns, those connections have high privilege. Because of this, SINGLE_USER and
RESTRICTED_USER may not successfully lock out users long enough to start up
the restore operation. If you use the OFFLINE setting, then no one, not ev
en you, can access that database until restored or brought back online.
ALTER DATABASE MyDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE
Sincerely,
Anthony Thomas
--
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message new
s:OYrgy%23t0EHA.1392@.TK2MSFTNGP14.phx.gbl...
thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
>
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> M'bark BOULOUIRD wrote:
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'barkI think the error messages is pretty clear. Someone is using the database, s
o you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users y
ou have in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius
>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
database is in use.[vbcol=seagreen]
>
>.
>|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:|||A better suggestion would be to take the database offline. If you are resto
ring it, who cares if it is offline or not.
I prefer this because of Web Server connection pooling and, in some situatio
ns, those connections have high privilege. Because of this, SINGLE_USER and
RESTRICTED_USER may not successfully lock out users long enough to start up
the restore operation. If you use the OFFLINE setting, then no one, not ev
en you, can access that database until restored or brought back online.
ALTER DATABASE MyDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE
Sincerely,
Anthony Thomas
--
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message new
s:OYrgy%23t0EHA.1392@.TK2MSFTNGP14.phx.gbl...
thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
>
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> M'bark BOULOUIRD wrote:
Friday, March 23, 2012
Restoring SQL2000 DB to SQLExpress on different machine
Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.
Restoring SQL2000 DB to SQLExpress on different machine
Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY |
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVERY |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restored
> (the roll forward set) is consistent with the database. If the roll forward
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.sql
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY |
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVERY |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restored
> (the roll forward set) is consistent with the database. If the roll forward
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.sql
Saturday, February 25, 2012
Restoring databases
Hi all,
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please helpWhat errors are you getting? I haven't used backupexec before, but have you
tried doing a restore using SQL Server's native RESTORE command? You can
find more information and examples on RESTORE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:071c01c3da4c$f2428040$a301280a@.phx.gbl...
Hi all,
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please help
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please helpWhat errors are you getting? I haven't used backupexec before, but have you
tried doing a restore using SQL Server's native RESTORE command? You can
find more information and examples on RESTORE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:071c01c3da4c$f2428040$a301280a@.phx.gbl...
Hi all,
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please help
Restoring databases
Hi all,
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please helpWhat errors are you getting? I haven't used backupexec before, but have you
tried doing a restore using SQL Server's native RESTORE command? You can
find more information and examples on RESTORE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:071c01c3da4c$f2428040$a301280a@.phx.gbl...
Hi all,
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please help
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please helpWhat errors are you getting? I haven't used backupexec before, but have you
tried doing a restore using SQL Server's native RESTORE command? You can
find more information and examples on RESTORE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:071c01c3da4c$f2428040$a301280a@.phx.gbl...
Hi all,
I'm trying to find out how to restall databases for
backupexec program located on another server. Every time
i try to re-store the data it comes up with errors. Does
any one or has anyone done this before
Please help
Subscribe to:
Posts (Atom)