One, foreword
Today, the company wants to switch the database server. The database file is larger than 2G, and the result is attached to the database of another server, which causes a problem. As follows:
Microsoft SQL Server Management Studio
——————————
Data could not be retrieved for this request. (Microsoft. Essentially. SmoEnum)
——————————
Other information:
An exception occurred while executing transact-SQL statements or batch processing. (Microsoft. Essentially. ConnectionInfo)
——————————
E:\DATA\ database name. MDF is not the primary database file. (Microsoft SQL Server, error :5171)
Second, solutions
I searched for many methods on the Internet, and finally found a relevant solution on CSDN, which can be used after testing.
use master
go
Sp_detach_db ‘database name’ — Detach the database
go
Sp_attach_db ‘database name’, ‘E: \ database name. MDF’, ‘E: \ database name. LDF – additional database files
go
I sorted out what I saw on the Internet.
In SQL Server 7.0, Microsoft introduced sp_ATTACH_DB and SP_ATTACH_singLE_FILe_DB system stored procedures. It is very convenient for SQL Server database administrators to perform the following tasks:
· SP_attach_DB: Directly attach to MDF and.LDF files are attached to the server.
· SP_attach_SINGLE_FILE_DB: attach only. MDF file.
· SP_DETach_DB: Detach the database from a server. Copy the.MDF files to another server, and then re-attach them to both servers using the SP_attach_DB system stored procedure.
Although it is useful for SQL Server database administrators, there are some limitations when using these two stored procedures. The restrictions are as follows:
· Cannot attach multiple log files.
· No more than 16 files can be attached.
In SQL Server 2008, Microsoft announced that the above system stored procedures would be deprecated in future releases. They added a clause “For Attach” to the “Create Database” SQL statement. Here are several ways to use the “For Attach” clause to overcome the limitations of using SP_ATTACH_DB and SP_ATTACH_singLE_FILe_DB.
Three cases,
Create test database
use master
go
CREATE DATABASE TestDB
ON
(
NAME = TestDB,
FILENAME = ‘D:\TestDB.mdf’,
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5,
)
LOG ON
(
NAME = TestDB_log,
FILENAME = ‘D”\TestDB_log.ldf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
GO
Now let’s detach the database and try attaching it again using SP_DETach_DB and SP_attach_DB.
Execute the following transaction SQL statement.
use master
go
sp_detach_db ‘TestDB’
go
sp_attach_db ‘TestDB’, ‘D:\TestDB.mdf’, ‘D:\TestDB_log.ldf’
go
You can also Attach the same database file using the “Create database” command with the “For Attach” clause, as shown below.
use master
go
sp_detach_db ‘TestDB’
go
CREATE DATABASE TestDB
ON
(FILENAME = ‘D:\TestDB.mdf’),
(FILENAME = ‘D:\TestDB_log.ldf’)
For Attach
go
Now let’s detach database TestDB, then delete the.ldF file, and then use SP_attach_singLE_file_DB
The system stored procedure passes, execute the following T-sql command to attach it again.
use master
go
sp_detach_db ‘TestDB’
go
exec master.. xp_cmdshell ‘del “D:\TestDB_log.ldf”‘
go
You can activate the Xp_cmdshell using the following transaction SQL statement
use master
go
sp_configure ‘show advanced options’,1
go
reconfigure with override
go
sp_configure ‘xp_cmdshell’,1
go
reconfigure with override
go
Alternatively, you can use Windows Explorer’s “Del” command at the MS-DOS command prompt to delete. LDF files.
For now, let’s just attach using SP_attach_singLE_file_db. MDF file. Execute the commands shown below.
use master
go
sp_attach_single_file_db ‘TestDB’,’D:\TestDB.mdf’
go
You can attach it simply by using the “Create database” command with the “For ATTACH_REBUILD_LOG” clause
— Same database. MDF file, as shown below.
use master
go
sp_detach_db ‘TestDB’
go
exec master.. xp_cmdshell ‘del “D:\TestDB_log.ldf”‘
go
— Note: When the log file is recreated, SQL Server automatically suffixes the log file name with “_log”.
CREATE DATABASE TestDB
ON
(
FILENAME = ‘D:\TestDB.mdf’
)