RESTORE DATABASE FROM QUERY ANALYZER & ISQL OR OSQL

[triad1234]

RESTORE DATABASE TWO FROM DISK='C:\TWO.BAK' WITH REPLACE,NORECOVERY, MOVE 'GPSTWODat' to 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTDat.mdf', MOVE 'GPSTWOlog' TO 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTlog.ldf'

Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.

Received the above error message while trying to restore database through query analyzer. I want to restore to TEST database from TWO.bak file while nobody is using either TWO and TEST database. I know how to restore database through SQL Enterprise Manager, but I really want to find out a way to schedule isql or osql to restore database to a backup SQL server.

Will the following statement work?
isql -U sa -P PASSWORD -S SERVERNAME -Q "RESTORE DATABASE TWO FROM DISK='C:\TWO.BAK' WITH REPLACE,NORECOVERY, MOVE 'GPSTWODat' to 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTDat.mdf', MOVE 'GPSTWOlog' TO 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTlog.ldf'"

Thanks in advance for any help.

Rob

 

[zhavic]

Yes it should work.

But there must not be connected any user to the database "TWO"
you are want to restore

type EXEC sp_who in query analyzer to view who is connected to the database "TWO"

Also you can't be connected to the databse "TWO" in query analyzer,
you should connect to "master" database for example

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[triad1234]

Thanks for reply Zhavic,

Stopped SQL service and restarted, run the same statement against master in QA, getting the following error:

Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'GPSTWODat' is not part of database 'TWO'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I tried to restore an entire database and checked logical file name for databse TWO which are GPSTWODat for mdf and GPSTWOLog for ldf. Why it is not working?

ROb

 

[mrdenny]

WPSTWODat may be the physcial file name, but it's probably not the logical filename. You can get the logical filename by using the RESTORE FILELISTONLY as the error said.

RESTORE FILELISTONLY from disk='c:\TWO.BAK'

That will tell you both the logical and physical file names. You use the WITH MOVE (as you did above) to change the physical file name.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)