Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

RESTORE DATABASE FROM QUERY ANALYZER & ISQL OR OSQL

triad1234 (MIS)
6 Jul 05 9:29
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 (Programmer)
6 Jul 05 9:36
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 (MIS)
6 Jul 05 10:26
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 (Programmer)
7 Jul 05 11:43
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.

CODE

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.)

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close