Cách attach database sql 2012

     
Problem

How vày you attach and detach databases in Squốc lộ Server? What is the differencebetween a SQL Server backup và restore vs. detach & attach?

Solution

Generally, detaching and attaching a Squốc lộ Server database is used to move sầu database filesin the tệp tin system or between servers. If you wantlớn move a database lớn a different Squốc lộ Server instance, in some opinions the easiest option wouldbe detach the database, move sầu the database files khổng lồ the hệ thống và then attach thefiles. The other option is to lớn backup, copy the backup tệp tin and restore thedatabase to the new VPS. Depending on the file sizes và networkbandwidth dictates which option is the fasthử nghiệm. One option to lớn improve sầu thetime needed for the database detach attach would be to shrink the files, ifthey have a significant amount of free space.

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

Preliminary Squốc lộ Server 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 to the new locationafter detaching.

Get Database File Information Using Squốc lộ Server Management Studio

InSquốc lộ Server Management Studio, right cliông xã on the database & select Properties & go tothe Files page. You can see a các mục of database files andthe Path where the files currently exist.


*

Get Database File Information Using T-SQL

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


This will output path & tệp tin name information in the filename column for eachtệp tin used for the database.

Detach SQL Server Database

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

There are two options to detach a database: using Squốc lộ Server Management Studio(SSMS) or using T-SQL.

Detach a SQL Server Database Using SSMS

First, right click on the database in SSMS which you want khổng lồ detach và selectTasks > Detach...as shown in the below image.


*

*

In the above sầu screen, there are two kiểm tra boxes that you can use whendetaching a database:

Drop Connections - There might be existing connections to lớn the databasewhich needs khổng lồ be dropped before the database detach can occur. If there are anyopen connections to lớn 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 khổng lồ Success.


*

After the database is detached, it will be removed from the Databasesmenu in SSMS, but the data and log files for the database will remain in the sametệp tin 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 to lớn the database.


The following script detaches the AdventureWorks2017 database & first dropsexisting connections to lớn the database.


USE GOALTER DATABASE SET 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.

Xem thêm: Cách Vào Deep Web Và Thế Giới Dark Net (Phần 1), Hướng Dẫn Cách Vào Deep Web An Toàn


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 and log files for the database will remain in the same file system location.

Squốc lộ Server Attach Database with Log File

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

Attach a Squốc lộ Server Database Using SSMS

To attach the database, right cliông xã on Databases & selectAttach... on the SQL Server instance where you want to lớn 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 selecta different file.


When you have the correct files, cliông xã OK and you will see thescreen show a green checkmark và then the screen will cđại bại.


Attach a Squốc lộ Server Database Using T-SQL

This is the T-Squốc lộ script for the attach database with data và 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

Squốc lộ Server Attach Database without Log File

You don’t need a log tệp tin to lớn attach the database. In this scenario, thelog file will be recreated. If you are planning khổng lồ attach the database without copyingthe log file, make sure you exexinh đẹp a CHECKPOINT on the database before detachingthe database.

SQL Server Attach Database Using SSMS

The process is similar to above sầu, but you will see the message "Transactionlog file not found. A new empty log tệp tin will be created". You can thenclick OK and the database will be attached & a new log tệp tin will be created.


Squốc lộ 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 & empty log file is created.


USE GOCREATE DATABASE ON ( FILENAME = N'D:AdventureWorks2017.mdf' ) FOR ATTACHGO
File activation failure. The physical file name "D:AdventureWorks2017_log.ldf" may be incorrect.New log file "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 and Restore is the much cleaneroption than the Attach & Detach. However, a backup & 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 & detachoption there won't be any record of the sự kiện.

Difference between Squốc lộ Server Detach and Offline

Taking a database offline leaves the database registration lớn the SQL Serverinstance intact, so all you need to do is set it online to make it operational again.Basically, detaching and attaching is used lớn move sầu database files between SQL Serverinstances whereas with the Offline option you are unable lớn move the files.

Important Notes

*
Dinesh Asanka is a 10 time Data Platform MVPhường and frequent speaker at local và international conferences with more than 12 years of database experience.View all my tips

Thursday, January 30, 2020 - 10:40:trăng tròn AM - Greg RobidouxBaông xã To Top (84051)

Thanks Scott, this has been updated.

Xem thêm: Các Bài Văn Nghị Luận Hay Lớp 8 Hay Nhất 2020, Tuyển Tập Những Bài Văn Nghị Luận

Thursday, January 30, 20trăng tròn - 6:01:47 AM - ScottBack To Top (84048)

I know this is picky, but "..." is an ellipsis, not an ellipse. And I"m picky.


Chuyên mục: