如何在Entity Framework中还原SQL Server数据库（附备份实现代码）

Since you already have the backup logic working, let's tackle the restore functionality. Restoring a database has a few critical requirements we need to address first:

• No active connections: SQL Server won't allow a restore if there are open connections to the target database. We'll need to switch the database to single-user mode temporarily to kick out existing connections.
• Use the right EF method: For non-query SQL commands (like restore or altering database settings), ExecuteSqlCommand is more appropriate than SqlQuery (which is meant for returning result sets).

Step-by-Step Restore Command Implementation #

Here's how to build your RestoreCommand with proper error handling and connection management:

RestoreCommand = new RelayCommand(() => {
    try
    {
        // 1. Switch database to single-user mode to drop all active connections
        var setSingleUserCmd = @"ALTER DATABASE Winding SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
        _db.Database.ExecuteSqlCommand(setSingleUserCmd);

        // 2. Execute the restore command
        // Ensure FilePath points to a backup file accessible by the SQL Server service account
        var restoreCmd = @"RESTORE DATABASE Winding FROM DISK = '" + FilePath + @"' WITH REPLACE";
        _db.Database.ExecuteSqlCommand(restoreCmd);

        // 3. Switch back to multi-user mode to allow normal access
        var setMultiUserCmd = @"ALTER DATABASE Winding SET MULTI_USER";
        _db.Database.ExecuteSqlCommand(setMultiUserCmd);

        // Optional: Add a success notification for the user
        // MessageBox.Show("Database restored successfully!");
    }
    catch (Exception ex)
    {
        // Critical: Always revert to multi-user mode even if restore fails
        try
        {
            _db.Database.ExecuteSqlCommand(@"ALTER DATABASE Winding SET MULTI_USER");
        }
        catch { } // Ignore this error to avoid masking the original restore failure

        // Handle the error (log it, show user-friendly message, etc.)
        // MessageBox.Show($"Restore failed: {ex.Message}");
        throw; // Re-throw if you want upstream error handling to take over
    }
});

Key Notes for EF & SQL Server Restore #

• File Path Permissions: The SQL Server service account (not your application's user) needs read access to the backup file path. For local paths, grant permissions to the service account; for network shares, use a UNC path (\\server\share\backup.bak) and ensure the service account has access.
• WITH REPLACE: This clause tells SQL Server to overwrite the existing database with the backup. It's essential for most restore scenarios targeting the original database.
• Refresh EF Context: After a successful restore, dispose your current _db instance and create a new one to ensure you're working with the freshly restored data.
• Advanced Restore Scenarios: If you need to restore to a different database or move data/log files to new locations, use the WITH MOVE clause:

  RESTORE DATABASE Winding 
  FROM DISK = 'C:\Backups\Winding.bak'
  WITH REPLACE,
       MOVE 'Winding' TO 'C:\Data\Winding.mdf',
       MOVE 'Winding_Log' TO 'C:\Logs\Winding.ldf';
  

Why ExecuteSqlCommand Instead of SqlQuery? #

Your backup code uses SqlQuery<List<string>>, but for commands that don't return a result set (like restore or altering database settings), ExecuteSqlCommand is the correct EF method—it's purpose-built for non-query operations and returns the number of rows affected (though this metric is irrelevant for restore commands).

内容的提问来源于stack exchange，提问作者ar.gorgin