restore database.

I was attempting to restore a database and it hung up. Now when I try to
restore I get the message, re-issue restore statement using WITH REPLACE, but
not sure how to do this, is it a restore dbase option through the GUI, thanks.
Paul G
Software engineer.
I believe the option in the gui is something like Force Restore over existing
database or something similar. Also, make sure that on the first tab you
have the correct name to restore the database as. I think it defaults to
Master which is not what you want.
"Paul" wrote:

> I was attempting to restore a database and it hung up. Now when I try to
> restore I get the message, re-issue restore statement using WITH REPLACE, but
> not sure how to do this, is it a restore dbase option through the GUI, thanks.
> --
> Paul G
> Software engineer.
sql

restore database.

I was attempting to restore a database and it hung up. Now when I try to
restore I get the message, re-issue restore statement using WITH REPLACE, bu
t
not sure how to do this, is it a restore dbase option through the GUI, thank
s.
--
Paul G
Software engineer.I believe the option in the gui is something like Force Restore over existin
g
database or something similar. Also, make sure that on the first tab you
have the correct name to restore the database as. I think it defaults to
Master which is not what you want.
"Paul" wrote:

> I was attempting to restore a database and it hung up. Now when I try to
> restore I get the message, re-issue restore statement using WITH REPLACE,
but
> not sure how to do this, is it a restore dbase option through the GUI, tha
nks.
> --
> Paul G
> Software engineer.

restore database.

I was attempting to restore a database and it hung up. Now when I try to
restore I get the message, re-issue restore statement using WITH REPLACE, but
not sure how to do this, is it a restore dbase option through the GUI, thanks.
--
Paul G
Software engineer.I believe the option in the gui is something like Force Restore over existing
database or something similar. Also, make sure that on the first tab you
have the correct name to restore the database as. I think it defaults to
Master which is not what you want.
"Paul" wrote:
> I was attempting to restore a database and it hung up. Now when I try to
> restore I get the message, re-issue restore statement using WITH REPLACE, but
> not sure how to do this, is it a restore dbase option through the GUI, thanks.
> --
> Paul G
> Software engineer.

RESTORE DATABASE Timeout in SQL 2000 with large backup files

Hello,

I am attempting to restore the database from within VB.NET application I am making the following 3 calls:

RESTORE FileListOnly FROM DISK = 'C:\MyDatabase.dat'

USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\LDF\MyDatabase.ldf', REPLACE

RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat'

using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks

Eugene

Have you tried changing the timeout setting of the ADO.NET Command?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

As Wesley indicated you should change the query timeout.

SQL 2000 does not have fast file initialization so it can take awhile to create and initialize the files.

|||

Yes I forgot to mention. The object which executes query is SMO.Database and it runs

Smo.Database.ExecuteNonQuery(sSQL)

So I set the timeout on the Smo.Server.Connection = 0.

I've also tried to set it to Smo.Server.Connection=3600 but the timeout occure in about 12 min so

sorry It did not help...

|||

For those of you who might be looking for answer to the similar problem - I found the solution. SMO has StatementTimeout property. This is different from the one I was trying to change - which was a Connection timeout.

That is the one which needed to be increased.

I do appreciate the responses to my post though. It helped. Thanks

sql

RESTORE DATABASE Timeout in SQL 2000 with large backup files

Hello,

I am attempting to restore the database from within VB.NET application I am making the following 3 calls:

RESTORE FileListOnly FROM DISK = 'C:\MyDatabase.dat'

USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\LDF\MyDatabase.ldf', REPLACE

RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat'

using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks

Eugene

Have you tried changing the timeout setting of the ADO.NET Command?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

As Wesley indicated you should change the query timeout.

SQL 2000 does not have fast file initialization so it can take awhile to create and initialize the files.

|||

Yes I forgot to mention. The object which executes query is SMO.Database and it runs

Smo.Database.ExecuteNonQuery(sSQL)

So I set the timeout on the Smo.Server.Connection = 0.

I've also tried to set it to Smo.Server.Connection=3600 but the timeout occure in about 12 min so

sorry It did not help...

|||

For those of you who might be looking for answer to the similar problem - I found the solution. SMO has StatementTimeout property. This is different from the one I was trying to change - which was a Connection timeout.

That is the one which needed to be increased.

I do appreciate the responses to my post though. It helped. Thanks

Restore database fails because database is in use.

I'm a newbie so please be gentle.

In attempting to run a restore I get the following error message:
"Exclusive access could not be obtained because the database is in
use." However, it doesn't appear that there are any connections to the
database. At the suggestion of another thread, I ran sp_who2 and there
are no connections to the database in question. I've been running the
same restore for months and all has been well, until yesterday...

I wrote an .asp page (below) to test connectivity to this database.
The vb code ran successfully and soon thereafter, my nightly restore
failed. Task manager does no show that the asp page is active.

Lastly, I did add a user ("jbtest") as well as change the option to use
either Windows authentication or SQL Server authentication in
Enterprise Manager.

Any ideas would be greatly appreciated.

Thanks.

<%@. Language=VBScript %
<%
companyName = "agemni"

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.ConnectionString = "DRIVER=SQL
Server;SERVER=SHEELA-NA-GIG;UID=jbtest;PWD=test1;APP=Microsoft
Development Environment;WSID=SHEELA-NA-GIG;DATABASE=" & companyName &
";Trusted_Connection=No"

cnn.ConnectionTimeout = 300
cnn.CommandTimeout = 300
cnn.Open

Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Preferences"
RS.Open strSQL, cnn

Response.Write "connection established with the <b>" & RS("Company
Name") & "</b> database. They are a <b>" & RS("street") & "</b>
customer. "

RS.Close
Set RS = Nothing
%<jbmccluskey@.satcountry.com> wrote in message
news:1112117455.317127.10740@.g14g2000cwa.googlegro ups.com...
> I'm a newbie so please be gentle.
> In attempting to run a restore I get the following error message:
> "Exclusive access could not be obtained because the database is in
> use." However, it doesn't appear that there are any connections to the
> database. At the suggestion of another thread, I ran sp_who2 and there
> are no connections to the database in question. I've been running the
> same restore for months and all has been well, until yesterday...
> I wrote an .asp page (below) to test connectivity to this database.
> The vb code ran successfully and soon thereafter, my nightly restore
> failed. Task manager does no show that the asp page is active.
> Lastly, I did add a user ("jbtest") as well as change the option to use
> either Windows authentication or SQL Server authentication in
> Enterprise Manager.
> Any ideas would be greatly appreciated.
> Thanks.

<snip
Probably the most reliable solution is to use ALTER DATABASE to get rid of
any open connections, wherever they're from:

alter database MyDB set restricted_user with rollback immediate

See Books Online for the other state options (single_user, offline etc.) -
one of them might suit your needs better.

You don't mention how you're running the restore, but if it's via a
scheduled job, you should also check that the selected database for the
restore step is set to master, not to the database you're restoring,
otherwise the job can block itself.

Simon|||Thanks for the reply Simon. The problem was that I was trying to run
the RESTORE in the database itself as opposed to running it under
MASTER.

JB