We help IT Professionals succeed at work.

sql server 2005 restore database task 'restore as' under  options issue of reverting to wrong filename

MyDanes
MyDanes asked
on
430 Views
Last Modified: 2010-04-21
ok in sql server 2005 this would be restoring from a backup that is already present on the hard drive

In sql server management console - right mouse database name select tasks - restore - database

then choose from 'device'

navigate to the .bak file on the hard drive

Next place the check mark in the backup file being utilized

next click 'options'

next CHECK 'overwrite existing database'

next in the window where it says 'original file name' and restore as

change the restore as to the correct path/database files for .mdf and.ldf files

NOW - NORMALLY if you click back to 'GENERAL' and then click back AGAIN to OPTIONS  your update to the restore as will remain intact

On this server when I come back to options it reverts back to what it was before I changed it.  PLEASE HELP

this is sql server 2005 standard and it is patched to sp2

What can I do - i tested on another server and it behaves correctly it is only this 'production server' that is the problem.  
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jagdish DevakuSr DB Architect

Commented:
i think their might some issues with management studio...

try to reinstall the management studio...

check the below link for downloading management studio...

http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&displaylang=en

all the best....

Author

Commented:
Ok Chapmandew

restore database abc
from disk = 'D:\MSSQL\MSSQL.1\MSSQL\Backup\bp.bak'
with
move 'datafilename' to 'D:\MSSQL\MSSQL.1\MSSQL\Data\abc.mdf',
move 'logfilename' to 'D:\MSSQL\MSSQL.1\MSSQL\Data\abc_log.ldf',
replace

I get:

Msg 3234, Level 16, State 2, Line 1
Logical file 'datafilename' is not part of database 'abc'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
:) Those were only place holders.  You have to figure out what your data file and log file name is.  You can use restore filelistonly...look at the name column

restore filelistonly from disk = 'D:\MSSQL\MSSQL.1\MSSQL\Backup\bp.bak'

Author

Commented:
restore database abc
from disk = 'D:\MSSQL\MSSQL.1\MSSQL\Backup\bp.bak'
with
move 'abc' to 'D:\MSSQL\MSSQL.1\MSSQL\Data\abc.mdf',
move 'abc_log' to 'D:\MSSQL\MSSQL.1\MSSQL\Data\abc_log.ldf',
replace

it is running now will let you know what I get :)
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
Yeah, if it is a large db it will be slow....but, since it is running it is working. :)

Author

Commented:
what would the syntax be for a 'full' database backup? - by the way it is still running
 

Author

Commented:
I tried:

backup database Jessup
to disk = 'D:\MSSQL\MSSQL.1\MSSQL\Backup\Pilotrestore.bak'

and it ran but is that a 'FULL' backup?

Author

Commented:
Here is what my output read:
Processed 79368 pages for database 'Jessup', file 'Jessup' on file 1.
Processed 2 pages for database 'Jessup', file 'Jessup_log' on file 1.
BACKUP DATABASE successfully processed 79370 pages in 42.041 seconds (15.465 MB/sec).

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
Yes, that is a full backup...you successfully created a full backup.  :)

Is your restore done yet?

Author

Commented:
restore just finished :)

thanks chapmandew :) As usual GREAT help :)

Author

Commented:
Thanks again :0
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
You're welcome.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.