Cách Attach Database Sql 2012

Problem

How do you attach and detach databases in SQL Server? What is the differencebetween a SQL hệ thống backup and restore vs. Detach & attach?

Solution

Generally, detaching & attaching a SQL vps database is used lớn move database filesin the file system or between servers. If you wantto move a database to lớn a different SQL hệ thống instance, in some opinions the easiest option wouldbe detach the database, move the database files lớn the server & then attach thefiles. The other option is to backup, copy the backup file and restore thedatabase lớn the new server. Depending on the tệp tin sizes và networkbandwidth dictates which option is the fastest. One option to improve thetime needed for the database detach attach would be khổng lồ shrink the files, ifthey have a significant amount of không tính phí space.

Bạn đang xem: Cách attach database sql 2012

Preliminary SQL vps Database Information

Before detaching a database, you should get information about the underlying filessupporting the database. This will be helpful when you need to copy the files lớn the new locationafter detaching.

Get Database tệp tin Information Using SQL hệ thống Management Studio

InSQL hệ thống Management Studio, right click on the database & select Properties and go tothe Files page. You can see a danh sách of database files andthe Path where the files currently exist.

Get Database tệp tin Information Using T-SQL

To get this information using T-SQL you can run the following command for the databaseyou want khổng lồ detach, in this case AdvnetureWorks2017.

This will đầu ra path và file name information in the filename column for eachfile used for the database.

Detach SQL hệ thống Database

A SQL server database consists of at least two files: one data tệp tin (mdf) andone log file (ldf), but in some cases there can be multiple data files. When thesefiles need to be moved to another SQL server instance, you are unable khổng lồ just copythese files since they are attached lớn a SQL hệ thống instance and the file systemmarks the files as open. Therefore, before moving these files, you can detach therelevant database (files) from the SQL hệ thống instance.

There are two options lớn detach a database: using SQL server Management Studio(SSMS) or using T-SQL.

Detach a SQL vps Database Using SSMS

First, right click on the database in SSMS which you want lớn detach & selectTasks > Detach...as shown in the below image.

In the above screen, there are two check boxes that you can use whendetaching a database:

Drop Connections - There might be existing connections khổng lồ the databasewhich needs lớn be dropped before the database detach can occur. If there are anyopen connections to the database the detach will fail.Update Statistics - This will refresh any out of date statistics beforedetaching the database. If this option is unchecked, statistics will remainas they were at the time you detached the database.

In this case we will select both options as shown below and select OK.After the detach occurs, the Status changes to Success.

After the database is detached, it will be removed from the Databaseslist in SSMS, but the data and log files for the database will remain in the samefile system location.

Detach a SQL server Database Using T-SQL

The following script detaches the AdventureWorks2017 database with the defaultoptions. This does not update the statistics nor does it drop any existingconnections khổng lồ the database.

The following script detaches the AdventureWorks2017 database và first dropsexisting connections khổng lồ the database.

Xem thêm: Tổng Hợp Những Cuốn Sách Học Tốt Tiếng Anh Lớp 10 Mới Hay Nhất

USE GOALTER DATABASE mix SINGLE_USER WITH ROLLBACK IMMEDIATEGOUSE GOEXEC master.dbo.sp_detach_db
The following script detaches the database first dropping any connections andalso updates the statistics.

USE GOALTER DATABASE set SINGLE_USER WITH ROLLBACK IMMEDIATEGOUSE GOEXEC master.dbo.sp_detach_db
After detaching the database, the database is not available for users, but thedata & log files for the database will remain in the same file system location.

SQL vps Attach Database with Log File

After detaching the database using either of the methods above, the mdf, ldfand ndf (if exists, these are additional data files) need to be copied to the newlocation where you want lớn attach the database.

Attach a SQL hệ thống Database Using SSMS

To attach the database, right click on Databases và selectAttach... on the SQL vps instance where you want to attach thedatabase.

SSMS fill then show the associated files for the database as shown below.If this is not correct, you can use the ellipsis next khổng lồ each file name to lớn selecta different file.

When you have the correct files, click OK and you will see thescreen show a green checkmark và then the screen will close.

Attach a SQL server Database Using T-SQL

This is the T-SQL script for the attach database with data and log files. Inthis example we are attaching the database to lớn a different path.

USE GOCREATE DATABASE ON ( FILENAME = N'D:AdventureWorks2017.mdf' ),( FILENAME = N'D:AdventureWorks2017_log.ldf' ) FOR ATTACHGO

SQL hệ thống Attach Database without Log File

You don’t need a log tệp tin to attach the database. In this scenario, thelog tệp tin will be recreated. If you are planning to attach the database without copyingthe log file, make sure you execute a CHECKPOINT on the database before detachingthe database.

SQL hệ thống Attach Database Using SSMS

The process is similar khổng lồ above, but you will see the message "Transactionlog file not found. A new empty log tệp tin will be created". You can thenclick OK và the database will be attached and a new log tệp tin will be created.

SQL server Attach Database Using T-SQL

You can use this script khổng lồ attach the database without a log file. When thisis used, a new and empty log file is created.

USE GOCREATE DATABASE ON ( FILENAME = N'D:AdventureWorks2017.mdf' ) FOR ATTACHGO
File activation failure. The physical tệp tin name "D:AdventureWorks2017_log.ldf" may be incorrect.New log tệp tin "D:AdventureWorks2017_log.ldf" was created.

Difference between SQL server Backup and Restore vs. Attach và Detach

This is a very common interview question. Backup & Restore is the much cleaneroption than the Attach and Detach. However, a backup và restore may take more timethan using the attach và detach option. When a Backup and Restore is done, thereis a record created in the MSDB for the operations whereas for the attach and detachoption there won't be any record of the event.

Xem thêm: Cách Chế Biến Món Ăn Từ Thịt Lợn (Heo) Cực Ngon, 30+ Món Ngon Từ Thịt Lợn (Heo) Cực Ngon

Difference between SQL hệ thống Detach và Offline

Taking a database offline leaves the database registration lớn the SQL Serverinstance intact, so all you need to bởi is phối it online khổng lồ make it operational again.Basically, detaching và attaching is used lớn move database files between SQL Serverinstances whereas with the Offline option you are unable to move the files.

Important Notes

Attach và Detach option works only in SQL server editions, not in AzureSQL Database, Azure SQL Data Warehouse version of SQL Server.Detach cannot be done when the database is set khổng lồ Replication and Mirroring.When the database is in suspect mode, you cannot detach the database.You can only detach user databases, but not system databases such as master,msdb, tempdb & model.When a database is attached to lớn a different version of SQL Server, the previous compatibility will be maintained và you can change this afterattach is done.Next StepsCheck out these related tips: