[RESOLVED] Database Restore Fails

[Serigraphie]

Hi Everyone,
Good day, I am trying to do a restore of my database and I am using this command :


RESTORE DATABASE TicketingRestored
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\ESSTicketingDEC022012.bak'


However, I am receiving the following errors :

Msg 3201, Level 16, State 2, Line 4
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\ESSTicketingDEC022012.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.


I tried to research for some answers but it is still giving me the above error.

The reason why I want to restore this database to "another newly created blank database" is because I want to view one table inside it that contains December data.

Thank you very much in advance for your help.

[techgnome]

Ok... couple things... first, the error is because it can't find the bak file. It cannot open it because it cannot find it. Are you *sure* that's where it is at?

Secondly, you need to change up the restore, to include MOVE commands to move the individual file(s) in the backup to the files of the database you're restoring (since you are not restoring to the original database).

It might also be that item 2 is the source of the error as well... so, after verifying that the backup is in the right place - and it must be ON THE SERVER and in a place where the server can find it - then proceed to step 2.

-tg

[Serigraphie]

Hello Tg,
Thank you for your response. In case I include the move command in the restore command, will it move the ldf and mdf files in a different location? Because if so, the other database (the original that I am going to restore last December) won't be able to find its own mdf and ldf files.

Please pardon my question if it confuses you.

Thank you again.

[techgnome]

Oh, I'm not confused... you are... which is fine, it can be a bit screwball. Inside the bak file there are copies of the ldf and mdf files... THOSE are what will be moved....


here's a segment of a restore I use.

RESTORE DATABASE [myDataBase] FROM  DISK = N'D:\Databases\TempStorage\PrimaryDatabase20120501.bak' WITH  FILE = 1,  
MOVE N'DB_PRIM' TO N'D:\Databases\MyDatabase\DATA\DB_PRIM.mdf',  
MOVE N'DB_DEF1' TO N'D:\Databases\MyDatabase\DATA\DB_DEF1.ndf',  
MOVE N'DB_DEF2' TO N'D:\Databases\MyDatabase\DATA\DB_DEF2.ndf',  
MOVE N'DB_DEF3' TO N'D:\Databases\MyDatabase\DATA\DB_DEF3.ndf',  
MOVE N'DB_DEF4' TO N'D:\Databases\MyDatabase\DATA\DB_DEF4.ndf',  
MOVE N'DB_IDX1' TO N'D:\Databases\MyDatabase\DATA\DB_IDX1.ndf',

The MOVE command moves the DB_* files in the backup to the location as specified in the second part of the TO .... so the DB_PRIM in the backup gets moved to the D:\Databases\MyDatabase\DATA\DB_PRIM.mdf location. where D:\Databases\MyDatabase\DATA\ points to my NEW EMPTY database.

If you don't use the MOVE command it will try to overwrite the files at their original location. That's not what you want.

-tg

[Serigraphie]

TG,
Thank you very much for your guidance! First of all, you're right when you told me to make sure it finds where the .BAK file is. What confused me is that the file seems to be in the path I indicated in the query. I noticed though that the file does not contain the .bak file type name so I went ahead and renamed it.

After that, I included the MOVE command to the query and looked like this:



RESTORE DATABASE TicketingRestored
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\ESSTicketingDEC022012.bak' WITH REPLACE,
MOVE 'TicketingRestored' TO
'D:\april.lee-dont_delete\TicketingRestored.mdf',
MOVE 'TicketingRestored_Log' TO
'D:\april.lee-dont_delete\TicketingRestored_log.ldf'

and then the restore was a SUCCESS!

Thank you TG, you helped me a lot