Restore does not leave database in usable state......

Ben Taylor

So this is the c# code I'm using to do my restore from a backed up file, but after the restore is called the database cannot have sql statements executed against it. Any ideas are greatfully received

public static void RestoreDatabase(Database db, string fileName)

{

RecoveryModel recoverymod = db.DatabaseOptions.RecoveryModel;

DatabaseUserAccess originalUserAccess = db.DatabaseOptions.UserAccess;

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

if (db.ActiveConnections > 0)

{

MessageBox.Show(db.ActiveConnections + "connections");

return;

}

BackupDeviceItem theDevice = new BackupDeviceItem(fileName, DeviceType.File);

Restore r = new Restore();

r.Partial = false;

r.Action = RestoreActionType.Database;

r.NoRecovery = true;

r.ReplaceDatabase = true;

r.Database = db.Name;

r.Devices.Add(theDevice);

r.SqlRestore(db.Parent);

r.Wait();

r.Devices.Remove(theDevice);

db.DatabaseOptions.RecoveryModel = recoverymod;

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

}

When I exit this code at the end the db object looks like this and can't have SQL executed against it Note that the Status (in red below) says it is "restoring" and IsAccessible is false......

base {Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase}: {[q2]}

ActiveConnections: 0

ActiveDirectory: {[q2]}

ApplicationRoles: {Microsoft.SqlServer.Management.Smo.ApplicationRoleCollection}

Assemblies: {Microsoft.SqlServer.Management.Smo.SqlAssemblyCollection}

AsymmetricKeys: {Microsoft.SqlServer.Management.Smo.AsymmetricKeyCollection}

AutoCreateStatisticsEnabled: true

AutoUpdateStatisticsEnabled: true

CaseSensitive: false

Certificates: {Microsoft.SqlServer.Management.Smo.CertificateCollection}

Collation: "Latin1_General_CI_AS"

CompatibilityLevel: Version90

CreateDate: {21/11/2006 22:33:59}

DatabaseGuid: {a5b71ccb-9df8-4e9c-bec6-f0e59440bd37}

DatabaseOptions: {Microsoft.SqlServer.Management.Smo.DatabaseOptions}

DatabaseSnapshotBaseName: ""

DataSpaceUsage: 'theDatabase.DataSpaceUsage' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

DboLogin: 'theDatabase.DboLogin' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

DefaultFileGroup: "PRIMARY"

DefaultFullTextCatalog: ""

Defaults: {Microsoft.SqlServer.Management.Smo.DefaultCollection}

DefaultSchema: 'theDatabase.DefaultSchema' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

Events: {Microsoft.SqlServer.Management.Smo.DatabaseEvents}

ExtendedProperties: {Microsoft.SqlServer.Management.Smo.ExtendedPropertyCollection}

ExtendedStoredProcedures: {Microsoft.SqlServer.Management.Smo.ExtendedStoredProcedureCollection}

FileGroups: {Microsoft.SqlServer.Management.Smo.FileGroupCollection}

FullTextCatalogs: 'theDatabase.FullTextCatalogs' threw an exception of type 'Microsoft.SqlServer.Management.Smo.UnsupportedFeatureException'

ID: 6

IndexSpaceUsage: 'theDatabase.IndexSpaceUsage' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsAccessible: false

IsDatabaseSnapshot: false

IsDatabaseSnapshotBase: false

IsDbAccessAdmin: 'theDatabase.IsDbAccessAdmin' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsDbBackupOperator: 'theDatabase.IsDbBackupOperator' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsDbDatareader: 'theDatabase.IsDbDatareader' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsDbDatawriter: 'theDatabase.IsDbDatawriter' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsDbDdlAdmin: 'theDatabase.IsDbDdlAdmin' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsDbDenyDatareader: 'theDatabase.IsDbDenyDatareader' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsDbDenyDatawriter: 'theDatabase.IsDbDenyDatawriter' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsDbOwner: 'theDatabase.IsDbOwner' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsDbSecurityAdmin: 'theDatabase.IsDbSecurityAdmin' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsFullTextEnabled: true

IsMailHost: 'theDatabase.IsMailHost' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

IsMirroringEnabled: false

IsSystemObject: false

IsUpdateable: true

LastBackupDate: {21/11/2006 22:37:15}

LastLogBackupDate: {01/01/0001 00:00:00}

LogFiles: {Microsoft.SqlServer.Management.Smo.LogFileCollection}

LogReuseWaitStatus: Nothing

MasterKey: 'theDatabase.MasterKey' threw an exception of type 'Microsoft.SqlServer.Management.Smo.EnumeratorException'

MirroringFailoverLogSequenceNumber: 'theDatabase.MirroringFailoverLogSequenceNumber' threw an exception of type 'Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException'

MirroringID: 'theDatabase.MirroringID' threw an exception of type 'Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException'

MirroringPartner: 'theDatabase.MirroringPartner' threw an exception of type 'Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException'

MirroringRoleSequence: 'theDatabase.MirroringRoleSequence' threw an exception of type 'Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException'

MirroringSafetyLevel: None

MirroringSafetySequence: 'theDatabase.MirroringSafetySequence' threw an exception of type 'Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException'

MirroringStatus: None

MirroringWitness: 'theDatabase.MirroringWitness' threw an exception of type 'Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException'

MirroringWitnessStatus: None

Owner: "MBS"

Parent: {[REMOVED]}

PartitionFunctions: {Microsoft.SqlServer.Management.Smo.PartitionFunctionCollection}

PartitionSchemes: {Microsoft.SqlServer.Management.Smo.PartitionSchemeCollection}

PrimaryFilePath: 'theDatabase.PrimaryFilePath' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

RecoveryForkGuid: {3f49a263-0cb2-41c3-95f2-afed98132870}

ReplicationOptions: 0

Roles: {Microsoft.SqlServer.Management.Smo.DatabaseRoleCollection}

Rules: {Microsoft.SqlServer.Management.Smo.RuleCollection}

Schemas: {Microsoft.SqlServer.Management.Smo.SchemaCollection}

ServiceBroker: {Microsoft.SqlServer.Management.Smo.Broker.ServiceBroker}

ServiceBrokerGuid: {bc2d714a-78e8-4f57-89f0-1e3b9d1afce8}

Size: 'theDatabase.Size' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

SpaceAvailable: 'theDatabase.SpaceAvailable' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

Status: Restoring

StoredProcedures: {Microsoft.SqlServer.Management.Smo.StoredProcedureCollection}

SymmetricKeys: {Microsoft.SqlServer.Management.Smo.SymmetricKeyCollection}

Synonyms: {Microsoft.SqlServer.Management.Smo.SynonymCollection}

Tables: {Microsoft.SqlServer.Management.Smo.TableCollection}

Triggers: {Microsoft.SqlServer.Management.Smo.DatabaseDdlTriggerCollection}

UserDefinedAggregates: {Microsoft.SqlServer.Management.Smo.UserDefinedAggregateCollection}

UserDefinedDataTypes: {Microsoft.SqlServer.Management.Smo.UserDefinedDataTypeCollection}

UserDefinedFunctions: {Microsoft.SqlServer.Management.Smo.UserDefinedFunctionCollection}

UserDefinedTypes: {Microsoft.SqlServer.Management.Smo.UserDefinedTypeCollection}

UserName: 'theDatabase.UserName' threw an exception of type 'Microsoft.SqlServer.Management.Common.ExecutionFailureException'

Users: {Microsoft.SqlServer.Management.Smo.UserCollection}

Version: 611

Views: {Microsoft.SqlServer.Management.Smo.ViewCollection}

XmlSchemaCollections: {Microsoft.SqlServer.Management.Smo.XmlSchemaCollectionCollection}

Allen White

Ben, in your code you include the statement

r.NoRecovery = true;

This says to leave the database in a 'restoring' mode so you can potentially apply transaction log backups to the database after the restore you've started. Change this to false and that should fix your problem.

vinaypugalia

Allen,

I am also facing the same problem but in my case NoRecovery is set to False. This is my code snippet :

restore = new Restore();

restore.Action = RestoreActionType.Database;

restore.Database = dbName;

restore.Devices.AddDevice(sourceFileName, DeviceType.File);

restore.ReplaceDatabase = true;

restore.PercentCompleteNotification = 1;

restore.PercentComplete += new PercentCompleteEventHandler(UpdateDatabase_PercentComplete);

restore.Partial = false;

restore.NoRecovery = false;

...........

............

............................

restore.SqlRestore(server);

Can you please help me out !!!

-Vinay

Arjan Griffioen

Perhaps you forgot to restore the original useraccess:

db.DatabaseOptions.UserAccess = originalUserAccess;

vinaypugalia

Arjan,

I have tried restoring the user access rights but still am facing the same problem.

The following is the exception which is fired :

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Any hints

AVanWieren

I got the same error and found the only way around it was to put a while clause around it tied to a timer. Usually on the second pass it could read from the database.

Just to test it out, put a while statement with a condition that will allow two or three passes, if it fails have it go to a catch statement inside the loop, so that it will continue to try again. On the second pass it should have no problems. Not pretty, but worked for me.

This was the only way I found to work around this, I would love to know what else could be done.

Let me know if this helps.

Restore does not leave database in usable state......

Re: Restore does not leave database in usable state......

Re: Restore does not leave database in usable state......

Re: Restore does not leave database in usable state......

Re: Restore does not leave database in usable state......

Re: Restore does not leave database in usable state......