Restore problem - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

Restore problem

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2006-09-01 : 05:56:34

Hi Team,

In my server,

I have one database called "TESTDB"
i have taken the complete bkp using a cmd in "Query Analyzer" as follows:

BACKUP DATABASE northwind
TO DISK = 'd:\backups\testdb\complete20060901.bak'

After deleting some objects, i tried to restore the bkp saying

RESTORE DATABASE testdb
FROM DISK = 'd:\backups\testdb\complete20060901.bak'
WITH
MOVE 'testdb' TO 'C:\software\Microsoft SQL Server\MSSQL\data\TESTDB_Data.MDF'
MOVE 'testdb_log' TO 'C:\software\Microsoft SQL Server\MSSQL\data\TESTDB_Log.LDF'

But it showing ERROR as follows :
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'MOVE'.

Can anyone suggest what might be wrong.

Thanks in advance
Frank

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-01 : 06:04:27

missing comma before the 2nd MOVE

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2006-09-01 : 06:20:23

Hi man,

I have give comma this time. but even then i am getting the following error:

-- taken bkp as follows
BACKUP DATABASE testdb TO DISK = 'd:\BACKUPS\testdb.bak'

-- tried to restore as follows
RESTORE DATABASE testdb FROM DISK = 'd:\BACKUPS\testdb.bak'
WITH
MOVE 'testdb' TO 'C:\software\Microsoft SQL Server\MSSQL\data\TESTDB_Data.MDF',
MOVE 'testdb_log' TO 'C:\software\Microsoft SQL Server\MSSQL\data\TESTDB_Log.LDF'

Error is :

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

Regards,
frank

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-01 : 06:57:10

Do what it says to see what the logical filenames are.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2006-09-01 : 06:58:09

HiHi team finally i got it.

I must use the following cmd

restore database naveeng from disk='C:\SQLSERVERBKPS\testdb\fulldb1.bak'

Anyhow thanks for help.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-01 : 08:45:26

Not true. That will create the files at the same location as in the backup.
Your previous command would have worked if you had the correct logical file names (and destination). You could have verified this from the filelist mentioned in the error message.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources