Error when trying to restore filelist only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Error when trying to restore filelist only

Author

Topic

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-20 : 09:51:37

Hello all, i am not very good with SQL but am trying, i have a database that was on a godaddy shared hosting server, i have downloaded the .bak file to try to restore to a server that i have built, when i run the restore filelistonly command i can see the mdf and the ldf files, but they are showing as being on the "d" drive, which i can only assume is where they were on the godaddy server, how do i get the files to restore to my server? here is an output when i run the follwoing sql query:
restore database adu1013805164646
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.BAK'
with move 'adu1013805164646' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\adu1013805164646.MDF',
move 'adu1013805164646_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\adu1013805164646_Log.LDF'

and here is the error message:

Msg 9953, Level 16, State 1, Line 1
The path 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\adu1013805164646' has invalid attributes. It needs to be a directory. It must not be hidden, read-only, or on a removable drive.
Msg 3156, Level 16, State 50, Line 1
File 'sysft_adu1013805164646' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\adu1013805164646'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

please help

thank you in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 10:25:07

Do it in SSMS-Dialog, don't execute it but click on create script and the see the differences to your statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2010-07-20 : 10:45:34

Looks like you have a full-text catalog in the backup.

Try adding this to your RESTORE statement:
MOVE 'sysft_adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData'

You may have to create the FTData folder, if it doesn't exist.

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-20 : 10:46:08

thank you for the quick response, not to sound dumb, but how would i do that? when i parse the query i get an error stating:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'adu1013805164646'.

my statement looks fine, i copied i from someone on this forum, all i did was change the db name and file locations.

also how can i change the logical name and physical names so that its not reading the files on the drive godaddy had them on?

thank you again for your help, it is much appreciated

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-20 : 10:50:20

Thanks again for the quick responses, here is my new statement with the added line:

RESTORE DATABASE adu1013805164646
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.BAK',
MOVE 'adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.MDF',
MOVE 'adu1013805164646_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646_log.LDF',
MOVE 'sysft_adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData';

but i am still getting the invalid syntax near adu1013805164646 error message

thank you again for the help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 11:32:21

I don't know to whom you have answered...
Have you tried my suggestion?

No, you're never too old to Yak'n'Roll if you're too young to die.

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-20 : 11:42:27

quote:
Originally posted by webfred

I don't know to whom you have answered...
Have you tried my suggestion?

No, you're never too old to Yak'n'Roll if you're too young to die.

hi, i kind of replied to both, but i am not sure how to do what you suggested

thanks again for the help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-20 : 12:17:29

This should give you an idea:
http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

No, you're never too old to Yak'n'Roll if you're too young to die.

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-20 : 22:23:58

no joy, i am still getting the error message about the invalid syntax

did you take a look at my sql statement? does it look like i have it written correctly?

thank you again for all help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-21 : 01:06:09

There should be no comma after the FROM DISK path:

RESTORE DATABASE adu1013805164646
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.BAK'
MOVE 'adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.MDF',
MOVE 'adu1013805164646_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646_log.LDF',
MOVE 'sysft_adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData';

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-21 : 01:17:22

thank you for the response, but now i get this error when parsing the statement:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MOVE'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-21 : 01:30:16

Damn!
Will it work if you do the restore in SSMS without scripting?

No, you're never too old to Yak'n'Roll if you're too young to die.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-21 : 01:37:19

Sorry you also need to remove the extra MOVEs:

RESTORE DATABASE adu1013805164646
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.BAK'
MOVE 'adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.MDF',
'adu1013805164646_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646_log.LDF',
'sysft_adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData';

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-21 : 08:55:22

Same error message when parsing the query

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MOVE'.

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-21 : 08:58:34

quote:
Originally posted by webfred

Damn!
Will it work if you do the restore in SSMS without scripting?

No, you're never too old to Yak'n'Roll if you're too young to die.

here is the error message i get using that method:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Restore failed for Server 'ADULTWEB'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\adu1013805164646.mdf" failed with the operating system error 21(The device is not ready.). (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-21 : 09:00:16

this backup was generated from the go daddy hosting control panel under databases, is there another way to generate a backup from the go daddy hosting control panel? i am positive that is why i am having so many problems

thank you again for all help

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2010-07-21 : 09:32:58

The statement from tkizer was only missing the WITH (from reading the error message, should be WITH MOVE instead of MOVE).
Try this:

RESTORE DATABASE adu1013805164646
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.BAK'
WITH MOVE 'adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.MDF',
'adu1013805164646_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646_log.LDF',
'sysft_adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData';

Does this work? If not, post the error message.

Kristen
Test

22859 Posts

Posted - 2010-07-21 : 09:39:56

If you are still stuck there is a guide to creating a restore command here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-21 : 10:01:19

quote:
Originally posted by YellowBug

The statement from tkizer was only missing the WITH (from reading the error message, should be WITH MOVE instead of MOVE).
Try this:

RESTORE DATABASE adu1013805164646
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.BAK'
WITH MOVE 'adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.MDF',
'adu1013805164646_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646_log.LDF',
'sysft_adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData';

Does this work? If not, post the error message.

here is the error message:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'adu1013805164646_log'.

and i would just like to thank everyone for their help

dzylildvl
Starting Member

12 Posts

Posted - 2010-07-21 : 10:09:27

quote:
Originally posted by Kristen

If you are still stuck there is a guide to creating a restore command here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

i will give this a try this afternoon when i have a few moments to work on it, i am going to call go daddy and see if they can get me a better backup as well.

thank you for your help

Kristen
Test

22859 Posts

Posted - 2010-07-21 : 13:04:46

Well ... substituting what you have above into what is in that link I get:


RESTORE DATABASE adu1013805164646
	FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.BAK'
	WITH
		REPLACE,	-- Overwrite DB - if one exists
		NORECOVERY,	-- Use if DIFFs / T/Logs to recover
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646.MDF', 
	MOVE 'adu1013805164646_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\adu1013805164646_log.LDF',
	MOVE 'sysft_adu1013805164646' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData'

looks like you need the extra "MOVE" command keywords

Note that the Backup is in program files, as are the database files that will be created. This may not be what you want?!

I don't know if that last MOVE is a file or folder (or which it needs to be ...)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources