0

I'm trying to restore the database to SQL Server.

At first I got this error:

Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\myDataBase.mdf" failed with the operating system error 3(System can't find path).

File 'myDataBase' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\myDataBase.mdf'. Use WITH MOVE to identify a valid location for the file.

Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\myDataBase_log.ldf" failed with the operating system error 3(System can't find path).

File 'myDataBase' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\myDataBase_log.ldf'. Use WITH MOVE to identify a valid location for the file.

Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.

The problem is that my server is not running SQL Server Express. I have no idea where that part of the path comes from.

After I've created the folder, because It didn't exist on my pc, the error I got:

The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\myDataBase.mdf'. File 'myDataBase' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\myDataBase.mdf'. Use WITH MOVE to identify a valid location for the file. The operating system returned the error '5(Acccess is denied)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\myDataBase_log.ldf'. File 'myDataBase_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\myDataBase_log.ldf'. Use WITH MOVE to identify a valid location for the file. Problems were identified while planning for the RESTORE statement. Previous messages provide details. RESTORE DATABASE is terminating abnormally

I've tried to run Visual as admin and SQL Server as well.

Restoring databases from SQL Server works fine.

Here is my code:

public RestoredDB RestoreDB(string destPath, string dbName)
        {
            var myServer = new Server(@"LOCALHOST");
            var restoredDb = new RestoredDB(); 


            var res = new Restore();
            res.Database = dbName;

            res.Action = RestoreActionType.Database;
            res.Devices.AddDevice(destPath, DeviceType.File);
            res.PercentCompleteNotification = 10;
            res.ReplaceDatabase = true;

            try
            {
                res.SqlRestore(myServer);
                restoredDb.Name = dbName;
                restoredDb.isRestored = true;
                return restoredDb; 
            }
            catch(Exception e)
            {
                MessageBox.Show(dbName + " couldn't be restored");

                restoredDb.Name = dbName;
                restoredDb.isRestored = false;

                return restoredDb; 
            }
        }

It probably has something to do with permissions? How do I change them?

Edit: Is there any way to change the directory in the first error msg? Some myServer property or something? I can't find it anywhere

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alice
  • 173
  • 1
  • 15
  • Is this your local pc or you are running in a remote server ? – dim mik Dec 13 '18 at 10:27
  • it is a local pc – Alice Dec 13 '18 at 10:29
  • It is probably due to the database still being attached to the server. I have seen issues when I de-attached a database and then re-attach using c#. I usually have to stop my c# application and restart. When you de-attached the C# object doesn't get dispose. Also I seen when a database is not archived by an admin the permissions of the file get changed and then a normal user can't reattach. I a normal user is archiving the have them de-attach, make a copy in Windows of mdf and ldf, and then attach. – jdweng Dec 13 '18 at 10:48
  • We also had issues when archiving a database that was on the D: drive and the Server was on c: using SSMS. We found that we had to de-attach the database and copy to c: drive before archiving. These problems happened with old version of SSMS and do not know if any of these issues were fixed. We are still using Processes that are 5 years old. – jdweng Dec 13 '18 at 11:04
  • @jdweng Thank You for your answer. Can You check the edit I made? I've posted different error as well. Could you check if there can be something done with that previous error? If not, then I'll try to use your comments. – Alice Dec 13 '18 at 11:41
  • @Alice, files are restored to the original location the backup was taken from by default. See [this answer](https://stackoverflow.com/questions/14376615/smo-restore-database) for an example showing how to move files during the restore with SMO. Personally, I prefer to use [`T-SQL RESTORE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql) rather than SMO, which allows `MOVE` clauses to relocate files. – Dan Guzman Dec 13 '18 at 11:56
  • Always get any query working in SQL Server Management Studio before trying in c#. The error messages in SSMS are 1000 time better than the obtuse error messages in Net Library. Use are using Express and not all features are enabled in Express. SSMS will indicate that. You may need to de-attach database and attach on full version of SSMS with license to get query working. – jdweng Dec 13 '18 at 12:03

0 Answers0