patternsqlModerate
SQL Server: why to use a backup device?
Viewed 0 times
whysqldeviceserverusebackup
Problem
When taking backups from databases, is it "good practice" to use logical backup device for backup location? What is the benefit of using backup devices?
Solution
I prefer one file per backup
I don't want to:
If you have TB size database, partitions, filegroups and want to do partial backups/restores then these problems are multiplied. In a DR situation, I want everything simple and clear.
Most (large and small) shops I've been in do this: it is simpler to deal with self describing files.
The "device" concept goes back to Sybase, SQL Server 6.5 and earlier. Here you had disk devices too Separate MDFs and LDFs were introduce with SQL Server 7
- DB_FULL_yyyymmddhhnn.bak
- DB_DIFF_yyyymmddhhnn.bak
- DB_LOG_yyyymmddhhnn.bak
I don't want to:
- query a backup device to see what is stored there
- copy an entire backup device to get one database
- FTP or otherwise shift more I need around the place
If you have TB size database, partitions, filegroups and want to do partial backups/restores then these problems are multiplied. In a DR situation, I want everything simple and clear.
Most (large and small) shops I've been in do this: it is simpler to deal with self describing files.
The "device" concept goes back to Sybase, SQL Server 6.5 and earlier. Here you had disk devices too Separate MDFs and LDFs were introduce with SQL Server 7
Context
StackExchange Database Administrators Q#7038, answer score: 10
Revisions (0)
No revisions yet.