When restoring SQL Server databases from compressed backups, we often face storage allocation mismatches. The backup contains only actual data (10GB in our case), but SQL Server recreates the original file structure (100GB) during restoration. This becomes problematic when:

• Target servers have limited disk space
• Multiple restores are needed in development environments
• Storage costs are a concern in cloud deployments

The most efficient method combines RESTORE WITH MOVE and explicit file sizing:

RESTORE DATABASE [NewDB] 
FROM DISK = 'C:\Backups\OriginalDB.bak'
WITH 
    MOVE 'OriginalDB_Data' TO 'D:\Data\NewDB.mdf',
    MOVE 'OriginalDB_Log' TO 'L:\Logs\NewDB.ldf',
    RECOVERY,
    STATS = 5,
    FILEGROWTH = 512MB,
    MAXSIZE = 20GB;  -- Limits total allocation

For SQL Server 2016+ with instant file initialization enabled:

-- Step 1: Restore with NORECOVERY to prevent immediate growth
RESTORE DATABASE [NewDB] FROM DISK = 'C:\Backups\OriginalDB.bak'
WITH NORECOVERY, MOVE...;

-- Step 2: Shrink before bringing online
ALTER DATABASE [NewDB] SET SINGLE_USER;
DBCC SHRINKFILE (N'NewDB_Data' , 0, TRUNCATEONLY);
DBCC SHRINKFILE (N'NewDB_Log' , 0, TRUNCATEONLY);
ALTER DATABASE [NewDB] SET MULTI_USER;
ALTER DATABASE [NewDB] SET RECOVERY;

For regular restores in dev environments:

$backupPath = "\\NAS\SQLBackups\ProdDB.bak"
$newDBName = "DevDB_$(Get-Date -Format 'yyyyMMdd')"

$restoreCmd = @"
RESTORE DATABASE [$newDBName] 
FROM DISK = N'$backupPath'
WITH 
    MOVE 'ProdDB_Data' TO 'E:\SQLData\$newDBName.mdf',
    MOVE 'ProdDB_Log' TO 'F:\SQLLogs\$newDBName.ldf',
    RECOVERY, REPLACE,
    FILEGROWTH = 10%;
"@

Invoke-SqlCmd -Query $restoreCmd -ServerInstance "SQLDEV01"

# Auto-shrink if over 20GB
$sizeCheck = Invoke-SqlCmd -Query "SELECT size/128.0 AS SizeMB FROM sys.master_files WHERE database_id = DB_ID('$newDBName')" 
if ($sizeCheck.SizeMB -gt 20480) {
    Invoke-SqlCmd -Query "DBCC SHRINKDATABASE(['$newDBName'], 10)"
}

For Azure SQL Managed Instance:

-- Use COPY_ONLY backups for cloud restores
BACKUP DATABASE [OriginalDB] 
TO URL = 'https://storageaccount.blob.core.windows.net/container/OriginalDB.bak'
WITH COPY_ONLY, COMPRESSION;

-- Restore with auto-shrink pattern
CREATE DATABASE [CloudDevDB] AS COPY OF [OriginalDB];
ALTER DATABASE [CloudDevDB] MODIFY FILE (NAME = 'OriginalDB_Data', SIZE = 10GB);

When restoring SQL Server databases, the engine recreates data files with their original allocated sizes - a behavior that's particularly problematic when dealing with databases that have significant unused space. Let's examine why this happens:

-- Typical restore command that maintains original file sizes
RESTORE DATABASE [NewDB] 
FROM DISK = 'C:\Backups\OriginalDB.bak'
WITH MOVE 'OriginalDB_Data' TO 'C:\Data\NewDB.mdf',
MOVE 'OriginalDB_Log' TO 'C:\Logs\NewDB.ldf';

The most efficient method combines restoration with file shrinking in a single operation. Here's the technical approach:

-- Step 1: Restore with STANDBY to allow shrinking
RESTORE DATABASE [NewDB] 
FROM DISK = 'C:\Backups\OriginalDB.bak'
WITH 
    STANDBY = 'C:\Temp\UndoFile.ldf',
    MOVE 'OriginalDB_Data' TO 'C:\Data\NewDB.mdf',
    MOVE 'OriginalDB_Log' TO 'C:\Logs\NewDB.ldf';

-- Step 2: Shrink files while in standby mode
DBCC SHRINKFILE (N'NewDB_Data', 0, TRUNCATEONLY);
DBCC SHRINKFILE (N'NewDB_Log', 0, TRUNCATEONLY);

-- Step 3: Complete the restore
RESTORE DATABASE [NewDB] WITH RECOVERY;

For more precise control, extract file information first and specify exact sizes:

-- Get file layout from backup
RESTORE FILELISTONLY 
FROM DISK = 'C:\Backups\OriginalDB.bak';

-- Restore with custom sizes (example for 15GB initial size)
RESTORE DATABASE [NewDB] 
FROM DISK = 'C:\Backups\OriginalDB.bak'
WITH 
    MOVE 'OriginalDB_Data' TO 'C:\Data\NewDB.mdf',
    MOVE 'OriginalDB_Log' TO 'C:\Logs\NewDB.ldf',
    FILEGROWTH = 10%,
    SIZE = 15GB;

When implementing this solution, keep in mind:

• Performance impact during the shrink operation
• Transaction log requirements during the process
• File fragmentation considerations
• Compatibility with different SQL Server versions

For repeated operations, consider this PowerShell script:

# PowerShell script for automated restore-and-shrink
$backupPath = "C:\Backups\OriginalDB.bak"
$newDBName = "NewDB"
$dataPath = "C:\Data\$newDBName.mdf"
$logPath = "C:\Logs\$newDBName.ldf"

# Execute restore
Invoke-Sqlcmd -Query "
    RESTORE DATABASE [$newDBName] 
    FROM DISK = '$backupPath'
    WITH STANDBY = 'C:\Temp\UndoFile.ldf',
    MOVE 'OriginalDB_Data' TO '$dataPath',
    MOVE 'OriginalDB_Log' TO '$logPath';"

# Shrink files
Invoke-Sqlcmd -Query "
    USE [$newDBName];
    DBCC SHRINKFILE (N'${newDBName}_Data', 0, TRUNCATEONLY);
    DBCC SHRINKFILE (N'${newDBName}_Log', 0, TRUNCATEONLY);"

# Complete recovery
Invoke-Sqlcmd -Query "RESTORE DATABASE [$newDBName] WITH RECOVERY;"