smo restore database

Question

I use SQL Server SMO to restore a .bak to a new database, but failed to work.

sql server is 2012 and smo object version is from the latest sdk version 11.0

file .bak was created using sql management studio 2012, same local pc, on the same coding pc as well.

The error message I get is:

Restore failed for Server 'SERVER'.

What's wrong with my code?

string dbPath = Path.Combine(@"d:\my data", dbName + "_db" + ".mdf");
string logPath = Path.Combine(@"d:\my data", dbName + "_db" + "_Log.ldf");

Restore restore = new Restore();

BackupDeviceItem deviceItem = new BackupDeviceItem("d:\template.BAK", DeviceType.File);
restore.Devices.Add(deviceItem);
restore.Database = dbName + "_db";

RelocateFile relocateDataFile = new RelocateFile("Data", dbPath);
RelocateFile relocateLogFile = new RelocateFile("Log", logPath);

restore.RelocateFiles.Add(relocateDataFile);
restore.RelocateFiles.Add(relocateLogFile);

restore.Action = RestoreActionType.Database;
restore.ReplaceDatabase = true;
restore.SqlRestore(server);

UPDATED: I surrended SMO solutions, and tried

 using (SqlConnection connection = new SqlConnection("Data Source=server;user id=sa;password=xxxxx;"))
        {

            using (SqlCommand command = new SqlCommand(@"RESTORE DATABASE beauty01 FROM DISK = 'd:\template.bak' WITH RECOVERY, MOVE 'beauty1' TO 'D:\MyData\beauty01_Data.mdf', MOVE 'beauty1_log' TO 'd:\Mydata\beauty01_Log.ldf', REPLACE", connection))
            {
                connection.Open();
                // Add the parameters for the SelectCommand.


                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }

        }  >> work good.

Thanks all.

Habanera

Is there an inner exception? Please check in debug, that'll probably give you the real reason. – Irresolute

Also, are you sure you're not trying to overwrite files that already exist? If you use the same dbName, you could have data and log files with the same name - try checking to see if the file exists first, and if it does, don't try creating it again. – Irresolute

Cannot open backup device 'd:\template.BAK'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.). >> the .bak was created by sql management studio 2012 and smo is the correct version (version 11). – Habanera

Does d:\template.BAK exist, and do you have access to it? Is D a network drive? If so, try moving it to a local disk. – Irresolute

yes, template.bak does exist. it's on my local drive. and i got it restored manually on SSMS just fine. it's strange. – Habanera

Remove the RelocateFile stuff and it should work just fine ;p (that is if you dont need it) – Mena

no i need them, to restore to the different db. and even without these lines, still cannot open backup device, Operating system error 123 – Habanera

@namvo Please post how you resolved it as an answer, and accept it. It'll help people experiencing the same problem find an answer by searching, and it'll stop potential answerers coming here to try and solve your problem when it's already resolved! – Irresolute

Answer 1

I successfully used SMO to restore the database. I'll share my code. Hope it helps. This solution has one caveat though, it considers that you have only one primary data file. Getting to match up the log and data files is really tricky and something can go wrong in many ways. Anyway try and let me know it this helps.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Text;
using System.Threading;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.Win32;

namespace DatabaseUtility
{
    public class BackupRestore
    {
        static Server srv;
        static ServerConnection conn;

        public static void BackupDatabase(string serverName, string databaseName, string filePath)
        {
            conn = new ServerConnection();
            conn.ServerInstance = serverName;
            srv = new Server(conn);

            try
            {
                Backup bkp = new Backup();

                bkp.Action = BackupActionType.Database;
                bkp.Database = databaseName;

                bkp.Devices.AddDevice(filePath, DeviceType.File);
                bkp.Incremental = false;

                bkp.SqlBackup(srv);

                conn.Disconnect();
                conn = null;
                srv = null;
            }

            catch (SmoException ex)
            {
                throw new SmoException(ex.Message, ex.InnerException);
            }
            catch (IOException ex)
            {
                throw new IOException(ex.Message, ex.InnerException);
            }
        }

        public static void RestoreDatabase(string serverName, string databaseName, string filePath)
        {

            conn = new ServerConnection();
            conn.ServerInstance = serverName;
            srv = new Server(conn);

            try
            {
                Restore res = new Restore();

                res.Devices.AddDevice(filePath, DeviceType.File);

                RelocateFile DataFile = new RelocateFile();
                string MDF = res.ReadFileList(srv).Rows[0][1].ToString();
                DataFile.LogicalFileName = res.ReadFileList(srv).Rows[0][0].ToString();
                DataFile.PhysicalFileName = srv.Databases[databaseName].FileGroups[0].Files[0].FileName;

                RelocateFile LogFile = new RelocateFile();
                string LDF = res.ReadFileList(srv).Rows[1][1].ToString();
                LogFile.LogicalFileName = res.ReadFileList(srv).Rows[1][0].ToString();
                LogFile.PhysicalFileName = srv.Databases[databaseName].LogFiles[0].FileName;

                res.RelocateFiles.Add(DataFile);
                res.RelocateFiles.Add(LogFile);

                res.Database = databaseName;
                res.NoRecovery = false;
                res.ReplaceDatabase = true;
                res.SqlRestore(srv);
                conn.Disconnect();
            }
            catch (SmoException ex)
            {
                throw new SmoException(ex.Message, ex.InnerException);
            }
            catch (IOException ex)
            {
                throw new IOException(ex.Message, ex.InnerException);
            }
        }

        public static Server Getdatabases(string serverName)
        {
            conn = new ServerConnection();
            conn.ServerInstance = serverName;

            srv = new Server(conn);
            conn.Disconnect();
            return srv;

        }
    }
}

Stover

This code really helped me, with a few modifications. I needed to restore a DB to a different location and that RelocateFiles stuff, complex as it is, worked well. – Ferretti

Hi, what's the purpose of the relocating files in the database restore operation? – Vedette

@RustyWizard Sometimes the database files are not present in the default location. In that case we need to find the file and use it in the restore command so that there are no errors while restoring. – Stover

variables MDF & LDF are never used! – Idiopathy

You should never catch exceptions and rethrow the way you show here. Instead just use "throw;" - this way you wont lose stack information – Skaw

It does not work for me. When the database is being used by SQL Server, the Restore function will raise an exception. – Understructure