MyDanes
asked on
sql server 2005 restore database task 'restore as' under options issue of reverting to wrong filename
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok Chapmandew
restore database abc
from disk = 'D:\MSSQL\MSSQL.1\MSSQL\Ba
with
move 'datafilename' to 'D:\MSSQL\MSSQL.1\MSSQL\Da
move 'logfilename' to 'D:\MSSQL\MSSQL.1\MSSQL\Da
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.
restore database abc
from disk = 'D:\MSSQL\MSSQL.1\MSSQL\Ba
with
move 'datafilename' to 'D:\MSSQL\MSSQL.1\MSSQL\Da
move 'logfilename' to 'D:\MSSQL\MSSQL.1\MSSQL\Da
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.
:) 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\Ba
restore filelistonly from disk = 'D:\MSSQL\MSSQL.1\MSSQL\Ba
ASKER
restore database abc
from disk = 'D:\MSSQL\MSSQL.1\MSSQL\Ba
with
move 'abc' to 'D:\MSSQL\MSSQL.1\MSSQL\Da
move 'abc_log' to 'D:\MSSQL\MSSQL.1\MSSQL\Da
replace
it is running now will let you know what I get :)
from disk = 'D:\MSSQL\MSSQL.1\MSSQL\Ba
with
move 'abc' to 'D:\MSSQL\MSSQL.1\MSSQL\Da
move 'abc_log' to 'D:\MSSQL\MSSQL.1\MSSQL\Da
replace
it is running now will let you know what I get :)
Yeah, if it is a large db it will be slow....but, since it is running it is working. :)
ASKER
what would the syntax be for a 'full' database backup? - by the way it is still running
ASKER
I tried:
backup database Jessup
to disk = 'D:\MSSQL\MSSQL.1\MSSQL\Ba
and it ran but is that a 'FULL' backup?
backup database Jessup
to disk = 'D:\MSSQL\MSSQL.1\MSSQL\Ba
and it ran but is that a 'FULL' backup?
ASKER
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).
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).
Yes, that is a full backup...you successfully created a full backup. :)
Is your restore done yet?
Is your restore done yet?
ASKER
restore just finished :)
thanks chapmandew :) As usual GREAT help :)
thanks chapmandew :) As usual GREAT help :)
ASKER
Thanks again :0
You're welcome.
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....