Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to use SQL statements to separate and attach databases". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

You can deal with it by watching the interface operation with Manage Studio.

Detach the database: for detaching the database with stored procedures, if you find that the user link cannot be terminated, you can use the ALTER DATABASE command and use a termination option that can break the existing link to set the database to SINGLE_USER mode. The SIGLE_USER code is as follows: ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE below is the CMD command EXEC sp_detach_db DatabaseName for separating the database once a database is separated successfully From a SQL Server point of view, there is no difference between deleting this database.

Additional databases: for additional databases, you can use sp_attach_db stored procedures, or the CREATE DATABASE command with the FOR ATTACH option, which is recommended in SQL Server2005 or later, which is phasing out for forward compatibility, while the latter provides more control over files. CREATE DATABASE databasename ON (FILENAME ='D:\ Database\ dbname.mdf') FOR ATTACH | FOR ATTACH_REBUILD_LOG, however, for such attachments, we need to pay attention to a few things. Because it involves rebuilding the log. 1. For a read / write database, if there is an available log file, whether using FOR ATTACH or FOR ATTACH_REBULD_LOG, the log file will not be rebuilt for this database. If the log file is not available or is not physically available, using FOR ATTACH or FOR ATTACH_REBULID_LOG rebuilds the log file, so if we copy a database with a large number of log files to another server, we can just copy the .MDF file instead of copying the log file, and then use the FOR ATTACH_REBULD_LOG option to rebuild the log. The condition is that the server will mainly use or only change the copy of the database for read operations. two。 For a read-only database, there is a difference. If the log file is not available, the master file cannot be updated, so the log cannot be rebuilt, so when we attach a read-only database, we must specify the log file in the FOR ATTACH clause. If the log file is rebuilt using an additional database. Using FOR ATTACH_REBUILD_ log breaks the log backup chain, and it's best to make a full backup of the database before doing so. One of the advantages of using sp_detach_db stored procedures is that you can ensure that a database is cleanly closed, so log files are not necessary for additional databases. We can use the FOR ATTACH_REBUILD_LOG command to rebuild the log to get a minimum log file. It's also a way to quickly shrink a large log file.

This is the end of the content of "how to use SQL statements to separate and attach databases". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.