DB Backup from 1 PC & Restore to diff. PC

Friends,

I have taken backup of a database, of which Data (MDF) & Log (LDF) files are located on "E:\...." directory.

Now, I am using the same backup file to restore on another PC which is having no partitions at all. I mean now I have to restore the database using same backup file, of which Data & Log files should be located on "C:\...." directory only, as it has no partitions.

This restore process gives me error :

Restore failed for Server 'HSVM\MICROFIT'.
Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

What is to be done, in order to restore the database from the same back up file successfully, regardless whether the original physical location of the MDF & LDF file, is existing on current PC ?

Please try to solve the same for me.... W8ing...

Whe u move database from one machine to another , u can either do it by Detach/attach or backup /resotre method. U mentioned that u have backup of MDF and LDF. How have u taken this backup. Did u detached the database first and then copied or u stoped the services and then copied. Please let us know the process u carried out to take backup of MDF and LDF

If u have taken backup of database using Backup Database statement, then u must restore the database from this backup using With MOVE Option . Read about Restore Database with MOVE Option in BOL

From BOL

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

Madhu

|||
I have taken Backup from PC(1) without stopping any services, from SQL Management Studio 2005 directly, using UI.
Database files were on "E:\...." directory.

Copied that "xyz.bak" file into other PC(2), which is having only "C:\..." Drive.|||

Now u follow these steps

(a) RESTORE FILELISTONLY FROM DISK = 'c:\xyz.bak' :- This command will give u the logical file names in the backup set

(b) RESTORE DATABASE YourNewDatabaseName
FROM DISK = 'c:\xyz.bak'
WITH MOVE 'LogicaldataFilename got from Step (a) ' TO 'c:\yourfolder\YourNewDatabaseName.mdf',
MOVE 'LogicalLogfile name got from Step(a)' TO 'c:\yourfolder\YourNewDatabaseName.ldf'

Above statement will restore the database to new location.

Madhu

|||
Practically I just tried with your suggession.... and ofcourse it worked. Thank you for that.

Are you aware of VB.NET ? and if yes, then MicroSoft.SqlServer.Management.Smo Object Model ?

Bcoz I have taken backup file manually, but restoring from .NET implementation.

If you aware of same, then plz guide me to use Restore Class of SMO Object Model.|||

check this

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx

Madhu

|||
still i am not satisfied.

while restoring DB it is giving me the error :

Exception >> Restore failed for Server 'HSVM\MICROFIT'.
Inner Exception >> Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

where HSVM is computer name & MICROFIT is instance on SQL server.