When working with production SQL Server 2008 R2 databases, we often need to create backups without disrupting service. The detach/attach method you mentioned requires taking the database offline, which isn't feasible for 24/7 operations.

SQL Server provides several built-in mechanisms for online backups:

-- Basic full backup command
BACKUP DATABASE [YourDatabase] 
TO DISK = 'C:\\Backups\\YourDatabase.bak'
WITH COMPRESSION, STATS = 10;

To get a physical file equivalent to MDF without downtime:

1. File Snapshot Backup (Enterprise Edition only):

BACKUP DATABASE [YourDatabase] 
TO DISK = 'C:\\Backups\\YourDatabase.snap'
WITH FILE_SNAPSHOT;

2. Copy-Only Backup + Restore:

-- Step 1: Create copy-only backup
BACKUP DATABASE [YourDatabase] 
TO DISK = 'C:\\Backups\\YourDatabase_COPY.bak'
WITH COPY_ONLY, COMPRESSION;

-- Step 2: Restore to new database
RESTORE DATABASE [YourDatabase_Copy] 
FROM DISK = 'C:\\Backups\\YourDatabase_COPY.bak'
WITH MOVE 'YourDatabase_Data' TO 'C:\\Data\\YourDatabase_Copy.mdf',
MOVE 'YourDatabase_Log' TO 'C:\\Data\\YourDatabase_Copy.ldf';

For point-in-time copies without full MDF files:

CREATE DATABASE [YourDatabase_Snapshot] ON
(NAME = 'YourDatabase_Data',
FILENAME = 'C:\\Snapshots\\YourDatabase.ss')
AS SNAPSHOT OF [YourDatabase];

Several commercial tools can create MDF-equivalent backups while the database remains online:

• Redgate SQL Backup Pro
• ApexSQL Backup
• Idera SQL Safe Backup

When implementing online backup solutions:

• Monitor disk I/O during backup operations
• Schedule backups during low-usage periods
• Verify backup integrity regularly
• Consider log file growth during backup operations

When working with production SQL Server 2008 R2 databases, taking the database offline for maintenance operations often isn't an option. The challenge lies in creating a usable MDF file copy while maintaining database availability. Let's explore the native SQL Server solutions for this scenario.

The most straightforward method is using SQL Server's native BACKUP command. While this creates a BAK file rather than direct MDF access, it's the safest online approach:

BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backups\YourDatabaseName.bak'
WITH COPY_ONLY, COMPRESSION;

If you specifically need the MDF file structure, restore the backup to a temporary location while keeping production online:

RESTORE DATABASE YourDatabaseName_Temp
FROM DISK = 'C:\Backups\YourDatabaseName.bak'
WITH 
    MOVE 'YourDatabaseName' TO 'C:\Temp\YourDatabaseName_Temp.mdf',
    MOVE 'YourDatabaseName_log' TO 'C:\Temp\YourDatabaseName_Temp.ldf',
    REPLACE, RECOVERY;

For SQL Server 2008 R2 Enterprise Edition, consider database snapshots:

CREATE DATABASE YourDatabaseName_Snapshot ON
(NAME = YourDatabaseName,
FILENAME = 'C:\Snapshots\YourDatabaseName_Snapshot.ss')
AS SNAPSHOT OF YourDatabaseName;

For advanced scenarios, tools like Redgate SQL Backup Pro or ApexSQL Backup offer additional features including:

• Compression and encryption options
• Differential backup capabilities
• Automated verification processes

Be aware that SQL Server 2008 R2 has specific constraints:

• Maximum database size of 524 PB (theoretical)
• Backup compression requires Enterprise Edition
• FILESTREAM data requires special handling

Here's a complete script that handles backup, verification, and cleanup:

DECLARE @DBName VARCHAR(255) = 'YourDatabaseName'
DECLARE @BackupPath VARCHAR(255) = 'C:\Backups\' + @DBName + '_' + 
    CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'

-- Perform the backup
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH COMPRESSION, STATS = 10, CHECKSUM

-- Verify the backup
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = 1, NOUNLOAD, NOREWIND

-- Log completion
PRINT 'Backup completed successfully: ' + @BackupPath

Use this query to monitor backup operations in progress:

SELECT 
    session_id AS SPID,
    command,
    start_time,
    percent_complete,
    estimated_completion_time/1000 AS seconds_remaining
FROM sys.dm_exec_requests
WHERE command LIKE '%BACKUP%'