0

I wrote my own little database library that fits our needs. Every steps that we defined necessary while restoring a database is coded to nothing needs to be executed manually. But the last two restores failed at 70 percent.

IMPORTANT: The same code below worked just fine on one of the fastest sql-servers that we have. It is bare-metal compared to the others that are virtualized! The database was over 100 gigabyte in size!

FOR SMALL DATABASES THE RESTORE WORKS ON EVERY SERVER!

To me it seems like it's an timeout problem or something like that. It loses connection and breaks.

The code for restoring the database:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections.Generic;

namespace BSH.Internal
{
    internal class DBRestoreBase
    {
        public void Restore(string databaseName, string databaseServer, string backupLocation, IList<RelocateFile> relocateFiles)
        {
            try
            {
                Restore sqlRestore = new Restore();

                BackupDeviceItem deviceItem = new BackupDeviceItem(backupLocation, DeviceType.File);
                sqlRestore.Devices.Add(deviceItem);
                sqlRestore.Database = databaseName;

                ServerConnection connection = new ServerConnection(databaseServer);
                Server sqlServer = new Server(connection);

                Microsoft.SqlServer.Management.Smo.Database db = sqlServer.Databases[databaseName];
                sqlRestore.Action = RestoreActionType.Database;
                db = sqlServer.Databases[databaseName];

                foreach (RelocateFile relocateFile in relocateFiles)
                {
                    sqlRestore.RelocateFiles.Add(relocateFile);
                }
                sqlRestore.ReplaceDatabase = true;
                sqlRestore.Complete += (obj, e) => { 
                    Console.WriteLine("Restore completed");
                    Console.WriteLine("-----------------------------------------------");
                };
                sqlRestore.PercentCompleteNotification = 10;
                sqlRestore.PercentComplete += (obj, e) => { Console.WriteLine("Percent completed: {0}%.", e.Percent); };

                sqlRestore.SqlRestore(sqlServer);

                db = sqlServer.Databases[databaseName];

                db.SetOnline();

                sqlServer.Refresh();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            };
        }
    }
}

I am totally clueless what the problem could be. I have to say that I'm new to C# with little knowledge!

  • What is the error that you get? – Mark Baijens Feb 07 '22 at 08:37
  • Does it work on the slow server if you restore the database from the command line? – Charles Feb 07 '22 at 08:38
  • The restore works with the SQL Server Management Studio its a problem with my code. @MarkBaijens I'm checking that right now. I inform you as soon as I get the error. Don't have it on me right now. – Silent3rror Feb 07 '22 at 08:54
  • @MarkBaijens the Error says: "Restore failed" , "The execution timeout expired. The timeout period expired before the operation completed, or the server is not responding.\r\nThe operation was canceled by the user." How do I set that to a greater number – Silent3rror Feb 07 '22 at 09:27
  • 3
    `ServerConnection.StatementTimeout`. Setting it to `0` should mean "indefinitely", per `SqlCommand.CommandTimeout`. (The default is 30 seconds.) – Jeroen Mostert Feb 07 '22 at 09:46
  • Thanks for the 'timeout'-answer. That helped. Everything works as expected! – Silent3rror Feb 11 '22 at 08:02

0 Answers0