Click here to Skip to main content
15,891,529 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm currently using SMO to create an SQL command (as a string) and then use that string with the "Restore" object to restore the database. So, the SQL I generate is starts off as follows:

SQL
USE master
ALTER DATABASE BackupRestoreTest 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO
ALTER DATABASE BackupRestorTest
SET READ_ONLY
But, I keep getting the following error message ...
SQL
Msg 5011, Level 14, State 5, Line 6
User does not have permission to alter database 'BackupRestorTest', the database does not exist, or the database is not in a state that allows access checks.

I'm logged in as the system administrator (sa) ... and tested it as such ...
SELECT IS_SRVROLEMEMBER('sysadmin') as I_am_SA, * FROM sys.databases WHERE name = 'BackupRestoreTest'
so I'm sure I have the required permissions so that leaves "the database is not in a state that allows access checks".

What I have tried:

The test database I'm working with is a brand new one and it does exist ... so the only state it should be in is a regular multi use situation. I researched Google for potential answers, worked through them but nothing stood out as a resolution to the problem.
Posted
Updated 28-Oct-19 9:34am

1 solution

Maybe you can try this: SQL Server 2008 - Backup and Restore Databases using SMO[^]
If you are using a newer version of SQL Server this can give problems as in my experience SMO and it's dll's can change between versions.
So the best advice I can give if you want your code to be futureproof is:
Don't use SMO !

Here is some code that shows how to do it without SMO:
// Make SQL Server backup.
// Use a longer connection timeout of 60 s for connecting instead of the default 15 s.
var connectionString = connectionStringWithoutCatalog;
connectionString = connectionString.Replace("Connection Timeout=" + SqlConnectionTimeout + ";", "Connection Timeout=60;");

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    if (connection.State == ConnectionState.Open)
    {
        SqlCommand command;
        if (connection.Database.ToLower().Equals("master"))
        {
            command = new SqlCommand(string.Format(@"BACKUP DATABASE [{0}] TO DISK = '{1}' WITH FORMAT, NAME = 'Full Backup of {0}';", catalog, backupFileName), connection);
        }
        else
        {
            command = new SqlCommand(string.Format(@"USE Master; BACKUP DATABASE [{0}] TO DISK = '{1}' WITH FORMAT, NAME = 'Full Backup of {0}';", catalog, backupFileName), connection);
        }

        command.CommandTimeout = 0;         // No Timeout: avoid "Timeout expired" error as large backup operations can take long to complete.
        command.ExecuteNonQuery();
    }
}


To restore:
            // SQL Server, use a longer connection timeout of 60 s for connecting instead of the default 15 s.
            var connectionString = connectionStringWithoutCatalog;
            connectionString = connectionString.Replace("Connection Timeout=" + SqlConnectionTimeout + ";", "Connection Timeout=60;");

            // First check if file exists in the default backup location, e.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
            var backupPath = GetDatabaseDefaultBackupPath(connectionString);
            var errorMessage = string.Empty;

            if (!File.Exists(Path.Combine(backupPath, fullBackupFileName)))
            {
                ErrorMessageEvent.Raise(EventSource, null, "DatabaseRestore() " + fullBackupFileName + " backup file not found.");
                return false;
            }

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                if (connection.State == ConnectionState.Open)
                {
                    string sqlMaster = string.Empty;
                    string sqlString;

                    try
                    {
                        if (!connection.Database.ToLower().Equals("master"))
                        {
                            sqlMaster = @"USE Master; ";
                        }

                        if (DatabaseExists(connectionString, catalog))
                        {
                            sqlString = string.Format(@"{0} ALTER DATABASE [{1}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;", sqlMaster, catalog);
                            var commandAlter = new SqlCommand(sqlString, connection);
                            commandAlter.CommandTimeout = SqlCommandTimeout;
                            commandAlter.ExecuteNonQuery();
                        }

                        if (GetSqlServerVersionYear(connectionString) < 2008)
                        {
                            string formatString = @"RESTORE DATABASE [{0}] FROM DISK = '{1}'; ";
                            formatString += @"ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE;";

                            sqlString = string.Format(formatString, catalog, fullBackupFileName);
                        }
                        else
                        {
                            // From SQL Server 2008 the RESTORE command is different.
                            // Retrieve the logical file names of the database from backup file first 
                            // with the new DatabaseRestoreFilelistonly() method.
                            string logicalName;
                            string logicalNameLog;

                            if (DatabaseRestoreFilelistonly(connectionString, fullBackupFileName, out logicalName, out logicalNameLog))
                            {
                                // Now construct the RESTORE command with the correct logical file names.
                                string formatString = @"RESTORE DATABASE [{0}] FROM DISK = N'{1}' ";
                                formatString += @"WITH  FILE = 1,  MOVE N'{3}' TO N'{2}{0}.mdf', ";
                                formatString += @"MOVE N'{4}' TO N'{2}{0}_log.LDF',  NOUNLOAD,  STATS = 5; ";
                                formatString += @"ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE;";

                                sqlString = string.Format(formatString, catalog, fullBackupFileName, backupPath.Replace(@"\Backup", @"\DATA\"), logicalName, logicalNameLog);
                            }
                            else
                            {
                                return false;
                            }
                        }

                        sqlString = sqlMaster + sqlString;
                        Debug.Print(sqlString);
                        var command = new SqlCommand(sqlString, connection);
                        command.CommandTimeout = 0;         // No Timeout: avoid "Timeout expired" error.
                        command.ExecuteNonQuery();
                        WarningMessageEvent.Raise(EventSource, null, "DatabaseRestore() " + fullBackupFileName + " succes.");
                        return true;
                    }
                    catch (Exception ex)
                    {
                        Debug.Print(EventSource + " " + ex.Message);

                        if (ex.Message.Contains("media family"))
                        {
                            // Give a more user friendly message than "The media family on device ... is incorrectly formed".
                            errorMessage = @"The backup was probably made with a higher SQL Server version, and can not be restored.";
                        }
                        else
                        {
                            errorMessage = @"It might be needed to set Server Properties - Connections - Remote query timeout to 0 in SSMS.";
                        }

                        ErrorMessageEvent.Raise(EventSource, ex, errorMessage);
                    }
                }
}

/// <summary>
/// Retrieve the logical file name of the database from backup file with RESTORE FILELISTONLY FROM DISK.
/// This is needed for SQL Server 2008 and higher.
/// </summary>
private static bool DatabaseRestoreFilelistonly(string connectionString, string backupFileName, out string logicalName, out string logicalNameLog)
{
    logicalName = string.Empty;
    logicalNameLog = string.Empty;

    using (var connection = new SqlConnection(connectionString))
    {
        // Open the database connection
        connection.Open();

        // Create a temporary table to buffer the output from RESTORE FILELISTONLY FROM DISK.
        string formatString =
        @"DECLARE @Table TABLE (LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar,
        [FileGroupName] varchar(128), [Size] varchar(128), [MaxSize] varchar(128),
        [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128),
        [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128),
        [BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128),
        [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128),
        [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128)) ";

        formatString += @"DECLARE @Path varchar(1000)='{0}'
        DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128)
        INSERT INTO @table EXEC('RESTORE FILELISTONLY FROM DISK=''' +@Path+ '''')
        SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
        SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')
        SELECT @LogicalNameData,@LogicalNameLog";

        // SQL query
        string sqlGetLogicalName = string.Format(formatString, backupFileName);

        // Get the logicalNames from the database
        var command = connection.CreateCommand();
        command.CommandText = sqlGetLogicalName;
        var sqlDataReader = command.ExecuteReader();

        if (sqlDataReader.HasRows)
        {
            sqlDataReader.Read();
            logicalName = sqlDataReader.GetString(0);
            logicalNameLog = sqlDataReader.GetString(1);
        }

        // Close database connection
        connection.Close();
    }

    if (string.IsNullOrEmpty(logicalName) || string.IsNullOrEmpty(logicalNameLog))
    {
        ErrorMessageEvent.Raise(EventSource, null, "DatabaseRestoreFilelistonly() " + backupFileName + " failed.");
        return false;
    }

    return true;
}

And now you may understand why we don't use SQL Server anymore and switched to PostgreSQL :)
 
Share this answer
 
v3
Comments
Charles T. Blankenship 30-Oct-19 10:17am    
LOL ... Rick I think you have the solution there! But, what is the alternative?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900