Hướng dẫn attach database trong sql server 20234
Nhưng may mắn là tìm được cách xử lý đơn giản này mà lại thành công cao - dù không phải là cao siêu gì cả như nhiều cách khác theo hướng dẫn của Google mà vẫn thất bại. Cụ thể có một hướng dẫn như sau: Show
Shahab | May 20, 2012 at 11:17 am I had a situation where non of those methods work, bu this method worked for me fine:
USE [master] GO ALTER DATABASE [MyDatabase] SET EMERGENCY GO ALTER DATABASE [MyDatabase] SET SINGLE_USER GO DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS) GO ALTER DATABASE [MyDatabase] SET MULTI_USER GO ALTER DATABASE [MyDatabase] SET ONLINE GO Đại khái anh ấy bảo như sau:
Chú ý : việc chạy New Query này cần thực hiện câu lệnh chính xác để thành công ngay từ lần đầu tiên nhé. Khi thực hiện có thông báo thành công (tức không có thông báo lỗi) có nghĩa là bạn đã attach thành công file database SQL Server mà chỉ còn mỗi file .MDF rồi đấy. Tôi đã thực hiện theo hướng dẫn cách Attach database SQL Server khi không có file LDF mà chỉ có file MDF kết quả đã thành công và tôi tin bạn làm đúng thì cũng sẽ thành công giống như tôi vậy. Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Attach a Database
In this articleApplies to: This article describes how to attach a database in SQL Server with SQL Server Management Studio or Transact-SQL. You can use this feature to copy, move, or upgrade a SQL Server database. Limitations and restrictionsFor a list of limitations and restrictions, see Database Detach and Attach (SQL Server). PrerequisitesReview all of the following prerequisites before proceeding:
Is Attach the best choice?We recommend that you move databases within an instance with the It is not recommended to use detach and attach for Backup and Recovery. There are no transaction log backups or point-in-time recovery available when detaching files to be backed up externally from SQL Server. SecurityFile access permissions are set during many database operations, including when a database is detached and attached. When a database is detached or attached, the Database Engine tries to impersonate the Windows account of the connection performing the operation to guarantee that the account has permission to access the database and log files. For mixed security accounts that use SQL Server logins, the impersonation might fail. The following table shows the permissions set on the database and log files after an attach or detach operation is completed, and whether the connecting account can be impersonated by the Database Engine. Operation Connecting account can be impersonated Files permissions are granted to Detach Yes Only the account performing the operation. Additional accounts can be added by an operating system administrator, if they are needed after the database is detached. Detach No The SQL Server (MSSQLSERVER) service account and members of the local Windows Administrators group. Attach Yes The SQL Server (MSSQLSERVER) service account and members of the local Windows Administrators group. Attach No The SQL Server (MSSQLSERVER) service account. For more information on file system permissions granted to the per-service SIDs for the SQL Server service, see Configure File System Permissions for Database Engine Access. Caution We recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database. For more information about attaching databases and information about changes that are made to metadata when you attach a database, see Database Detach and Attach (SQL Server). PermissionsRequires Use SQL Server Management Studio (SSMS)Before moving a databaseIf you are moving a database, before you detach it from its existing SQL Server instance, use the Database properties page to review the files associated with the database and their current locations.
Be sure to account for all files associated with the database before you detach, move, and attach. Then, proceed with the detach, file copy, and attach database steps in the next section. For more information, see Detach a Database. Attach a database
Use Transact-SQLBefore moving a databaseIf you are moving a database, before it is detached from its existing SQL Server instance, use the
Be sure to account for all files associated with the database before you detach, move, and attach. Then, proceed with the detach, file copy, and attach database steps in the next section. For more information, see Detach a Database. To attach a database
After upgrading a SQL Server databaseDatabase compatibility levelAfter you upgrade a database by using the attach method, the database becomes available. The database will be automatically upgraded to the internal version level of the new instance. If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the Full-Text Upgrade Option server property. If the upgrade option is set to Import or Rebuild, the full-text indexes are unavailable during the upgrade. Depending on the amount of data being indexed, importing can take several hours, and rebuilding can take up to 10 times longer. Note also that when the upgrade option is set to Import, if a full-text catalog is not available, the associated full-text indexes are rebuilt. After the upgrade, the database compatibility level remains at the compatibility level before the upgrade, unless the previous compatibility level is not supported on the new version. In this case, the upgraded database compatibility level is set to the lowest supported compatibility level. For example, if you attach a database that was compatibility level 90 before attaching it to an instance of SQL Server 2019 (15.x), after the upgrade the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2019 (15.x). For more information, see ALTER DATABASE Compatibility Level (Transact-SQL). Change Data Capture (CDC)If you are attaching a database from an instance of SQL Server 2014 (12.x) or earlier, which had Change Data Capture (CDC) enabled, you must execute the following command to upgrade the Change Data Capture (CDC) metadata:
For more information, see Error when you attach a CDC-enabled database to an instance of SQL Server 2016 or SQL Server 2017 on Windows. |