Unable to use RESTORE FILELISTONLY
-
Hi all,
I want to restore a database on machine 2, the back up of which is taken on say Machine 1 using TSQL. Both the machines are windows XP.
I have taken a backup and transferred the same to the following location C:\Database\Backup\TestBackup.bak
I then type the following command to check for the files in the Backup set.
RESTORE FILELISTONLY
FROM DISK = 'C:\Database\Backup\TestBackup.bak'
The server gives me the followign error.
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Projects\ASDOS\DataBase\CompleteBackup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
I am running the command under windows authentication mode having create DB and sysadmin permissions. THe Help also suggests that ay user can execute the above stmt.
Further the Restore DATABASE
RESTORE DATABASE TestBkup2
FROM DISK = 'C:\Projects\ASDOS\DataBase\TESTBackup.bak'
WITH MOVE 'TESTBACKUP' TO 'C:\Projects\ASDOS\DataBase\TestBsckup_Data.MDF',
MOVE 'TestBackup_log' TO 'C:\Projects\ASDOS\DataBase\TestBackup_Log.LDF',
REPLACE
gives the foll error. Cannot open backup device 'C:\Projects\ASDOS\DataBase\TEST.bak'. Device error or device off-line. See the SQL Server error log for more details.
The above stmts work if i create a databse on the same machine and then take a backup, delete the Database and then try to restore using TSQL
This seems to be soem permissions problem but unable to figure out what.Anybody having the sol please help.
reagrds
shweta
-
Don't have the solution, but I noticed that you specified 'C:\Database\Backup\TestBackup.bak' in your first "RESTORE FILELISTONLY" statement, then later 'C:\Projects\ASDOS\DataBase\TESTBackup.bak' and then finally 'C:\Projects\ASDOS\DataBase\TEST.bak' in the error message. Each of these has a slightly different path/filename!
Sounds like you're not being fully consistent--so SQL may be having trouble finding your backup file!
To simplify things, I would stick your backup file right on the root of "C:" and call it 'TEST.bak'. Then you should be able to refer to "C:\TEST.bak" consistently, everywhere. See if that helps...
- john
-
My mistake..I am sorry, have given the stmts frm the various examples i was trying.The problem persists even if i put the backup on the c:\root and use consistant file names (which i was using initially too..) .
However,If i am the db Owner of a given database, i am able to execute RESTORE FILELISTONLY '<path of BAK file>' and
RESTORE DATABASE <New Name>
FROM DISK <path of BAK file>
WITH MOVE '<Logical file name of mdf file>' TO '<path where of file where i want to store the mdf file>',
MOVE '<Logical file name of ldf file>' TO '<path where of file where i want to store the ldf file>'
I am not the owner of any particulat DB on the machine, but have the sys admin, sys Security administrators, and Database creater roles the above SQL stmts do not work
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply