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.

Is there an inner exception? Please check in debug, that'll probably give you the real reason. — Bridge
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. — Bridge
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). — nam vo
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. — Bridge
Remove the RelocateFile stuff and it should work just fine ;p (that is if you dont need it) — leppie

Soham Dasgupta Soham Dasgupta · Accepted Answer · 2013-07-09T12:03:48

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;

        }
    }
}

smo restore database

1 Answers