patternsqlMinor
SQL Server: How much space needed to restore?
Viewed 0 times
spacemuchneededsqlhowserverrestore
Problem
SQL Server 2016 Std
Our DB is 1TB of MDF, but there are 500GB free space in that MDF.
When we do a restore on a secondary (log shipping target), will the MDF there be 1TB, or ~500GB ?
Our DB is 1TB of MDF, but there are 500GB free space in that MDF.
When we do a restore on a secondary (log shipping target), will the MDF there be 1TB, or ~500GB ?
Solution
The size of the data and log files as seen by the operating system when the database is backed up, is what will be restored, regardless of how much free space is in a file. You can see exactly how large, in bytes, the data file will be when restored by executing this:
The [Size] column, 5th column along, has the value you can use to check whether you'll have enough drive space to restore a database.
restore filelistonly from disk = 'location_of_backup_file.bak';The [Size] column, 5th column along, has the value you can use to check whether you'll have enough drive space to restore a database.
Code Snippets
restore filelistonly from disk = 'location_of_backup_file.bak';Context
StackExchange Database Administrators Q#202244, answer score: 7
Revisions (0)
No revisions yet.