8-3. mssql (detach & Attach vs backup &Restore의 차이)

• Detach후에 다른 드라이브 또는 다른 서버로 파일을 이동시에 파일이 조각난다면, 
• 그리고, 복사한 MdF파일이 없다면, 그 DB는 아주 위험한 상태가 된다. 
• DB도 이미 분리된 상태이기 때문에 Attach가 안된다면 DB를 복원 시키는것이 불가능 할 수 있다.
• 그러나 Backup은 Active상태에서도 동작 하고, 이동시에 파일이 조각나도
• 언제든 다시 Backup할 수 있다. 

Difference Between Attach vs Restore Database in SQL Server

AttributesAttach/Detach SQL Server DatabaseBackup & Restore SQL Server Database

Description	The operation involves simple attachment of secondary SQL server database file with primary one	The operation involves restoration of all items from the SQL server database backup file.
File Format of Source SQL Database File (파일 확장자)	Here, the source file for attachment is in MDF format.	The SQL server backup file from which items are to be restored is in BAK format.
Required Mode (요구 상태)	This activity is carried out in an offline mode. It means that the source database must be in inactive status.	It requires online or active mode for the successful finishing up of the task.
Total Time Required (필요한 총 시간)	The time taken in attaching the SQL database is as such not dependent on file size. Basically, it is same as the time required in copying data files and log from one server to the another.	Restoration time of database is equivalent to 3 times of the backup time. Talking about the migration from one server to another then, time will be overall combination of backup time, restoration time and then the time required in server migration.
SQL Server Log File Presence (로그 파일 존재)	If in case the size of server log file is huge then, users need to copy the all the records over the network. However, if you are not having the log file then also you can simply attach MDF file and continue with the server migration.	The backup file size does not include any kind of data log file size aspect.
Fragmented Data Files (조각난 Data 파일)	It would be of no worth, if you are carrying attach option on fragmented data files. It is so because this activity will carry unwanted additional data bytes, which are totally of no use.	The SQL BAK file comprises of the pages, which are already used in the server. So, here the size concern is quite close to the size of used data files.
Maintenance of the SQL Server Activity Record (작업기록의 유지보수)	There is no record maintained for the detach or attach operation in the SQL server. So, no procedure details like procedure time, where were the files attached, etc., are stored in MSDB database.	The procedure of backup and restoration in the SQL server is stored in MSDB database tables. This table comprises of information like date, size, backup / restore type, etc.
Equipped Additional Options (장착된 추가 옵션들)	No other additional options area available to move data from one server to another.	Users are provided with advance options like partial backup, mirrored backup, time recovery point, etc., in this migration measure.

Conclusion

All the possible differences are explained under different circumstances that will help you in easily choosing the best activity. Always read differences between attach and restore database in the Microsoft SQL server to properly understand them. After clearing all doubts, one can now decide with which they want to go for.

 

산업 모니터링에 관한 다른 Contents도 확인 하세요.