Wednesday, March 28, 2012

Problem restoring a "*.bak" file

I have a Demo db in a MSDE instance called "Instance1"
I used the "RESTORE Database..." statement to back it up into a file called
"demo.bak"
I moved that file onto another machine which is MSDE with an instance
called "MyInstance" and I tried restoring with this line:
RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
It gives the error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I tried with both a newly created blank database called demo in the
destination server and without. It says the same thing.
I seriously don't understand anything of this Errormessage, what should I do
to be able to restore this "demo.bak" onto another machine?
I was looking a little bit into the WITH MOVE thing, but I could figure it
out.
(so you know.. I''m a .NET programmer and haven't messed around with these
backup/restore thingies before)
/Lars
Get Books Online, read Restore database and with move option and use it to
set correct destination of files - path to files is kept in the backup but
on this computer there are no such folder.
Bojidar Alexandrov
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file
called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I
do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>
|||By default SQL Server will try to restore to the same location. If the path
does not exist then it will fail. Use the MOVE option to move the files.
Something like this will do it.
RESTORE DATABASE XXXXX
FROM DISK = 'C:\....'
WITH MOVE 'demo_dat' TO 'C:\newlocation',
MOVE 'demo_log' TO 'C:\newlocation'
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file
called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I
do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>
|||Lars,
Yes, you need to specify the MOVE parameter for each logical filename to specify where these files should be
created. Use RESTORE HEADERONLY and then RESTORE FILELISTONLY to examine the backup file, including getting
the logical file names for each database files. Then use the MOVE option to specify where these files are to
be created. And don't create the destination database first, it will be created for you when you do the
RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>
|||Thank you! It works now with those "With MOVE, MOVE". thankx!
/Lars
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> skrev i meddelandet
news:uUaxok0MEHA.2628@.TK2MSFTNGP12.phx.gbl...
> By default SQL Server will try to restore to the same location. If the
path[vbcol=seagreen]
> does not exist then it will fail. Use the MOVE option to move the files.
> Something like this will do it.
> RESTORE DATABASE XXXXX
> FROM DISK = 'C:\....'
> WITH MOVE 'demo_dat' TO 'C:\newlocation',
> MOVE 'demo_log' TO 'C:\newlocation'
> --
> Barry McAuslin
> Look inside your SQL Server files with SQL File Explorer.
> Go to http://www.sqlfe.com for more information.
> "Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
> news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> called
> Files\Microsoft
> Files\Microsoft
I[vbcol=seagreen]
> do
it[vbcol=seagreen]
these
>

No comments:

Post a Comment