SQL Server 2000 database separation and Addition

Sp_detach_db
Detach the database from the server, and you can choose to run update statistics on all tables before the separation.

Syntax
Sp_detach_db [@ dbname =] 'dbname'
[, [@ Skipchecks =] 'skipcheck']

Parameters
[@ Dbname =] 'dbname'

Name of the database to be detached. The data type of dbname is sysname, and the default value is null.

[@ Skipchecks =] 'skipcheck'

The data type of skipchecks is nvarchar (10), and the default value is null. If the value is true, update statistics is skipped.
If it is false, run update statistics. This option is useful for databases to be moved to read-only media.

Return code value
0 (successful) or 1 (failed)

Result set
None

Note
Use sp_attach_db or sp_attach_single_file_db to retain and re-append the detached file. Files can also be moved and appended to other servers.

Permission
Only members of the SysAdmin fixed server role can execute sp_detach_db.

Example
The following example separates the pubs database and sets skipchecks to true.
Exec sp_detach_db 'pubs', 'true'

Sp_attach_db
Attach the database to the server.

Syntax
Sp_attach_db [@ dbname =] 'dbname'
, [@ Filename1 =] 'filename _ n' [,... 16]

Parameters
[@ Dbname =] 'dbname'

Name of the database to be appended to the server. The name must be unique. The data type of dbname is sysname, and the default value is null.

[@ Filename1 =] 'filename _ N'

The physical name of the database file, including the path. The data type of filename_n is nvarchar (260). The default value is null. A maximum of 16 file names can be specified. The parameter name starts with @ filename1 and increments to @ filename16. The file name list must contain at least the primary file. The primary file contains the system tables pointing to other files in the database. The list must also contain all the files that are moved after the database is detached.

Return code value
0 (successful) or 1 (failed)

Result set
None

Note
Execute sp_attach_db only for databases that previously separated from the database server using the explicit sp_detach_db operation. If you must specify more than 16 files, use the create database with the for attach clause.

If the server to which the database is appended is not the server from which the database is detached and the detached database is enabled for replication, run sp_removedbreplication to delete the replication from the database.

Permission
Only members of SysAdmin and dbcreator fixed server roles can perform this process.

Example
In the following example, two files in pubs are appended to the current server.

Exec sp_attach_db @ dbname = n 'pubs ',
@ Filename1 = n'c:/program files/Microsoft SQL Server/MSSQL/data/pubs. MDF ',
@ Filename2 = n'c:/program files/Microsoft SQL Server/MSSQL/data/pubs_log.ldf'

Sp_attach_single_file_db
Attaches a database with only one data file to the current server.

Syntax
Sp_attach_single_file_db [@ dbname =] 'dbname'
, [@ Physname =] 'physical _ name'

Parameters
[@ Dbname =] 'dbname'

Name of the database to be appended to the server. The data type of dbname is sysname, and the default value is null.

[@ Physname =] 'phsyical _ name'

The physical name of the database file, including the number path. The data type of physical_name is nvarchar (260), and the default value is null.

Return code value
0 (successful) or 1 (failed)

Result set
None

Note
When sp_attach_single_file_db is used to append the database to the server, it creates a new log file and performs additional cleanup operations to delete the copy from the attached database.

Execute sp_attach_single_file_db only for databases that previously separated from the server using the explicit sp_detach_db operation.

Permission
Only members of SysAdmin and dbcreator fixed server roles can perform this process.

Example
The following example separates pubs and attaches a file in pubs to the current server.

Exec sp_detach_db @ dbname = 'pubs'
Exec sp_attach_single_file_db @ dbname = 'pubs ',
@ Physname = 'C:/program files/Microsoft SQL Server/MSSQL/data/pubs. MDF'

Sp_helpfile
Returns the physical name and features of the file associated with the current database. Use this stored procedure to determine the name of the file attached to or detached from the server.

Syntax
Sp_helpfile [[@ filename =] 'name']

Parameters
[@ Filename =] 'name'

Is the logical name of any file in the current database. The data type of name is sysname, and the default value is null. If no name is specified, all files in the current database are listed.

Return code value
0 (successful) or 1 (failed)

Result set

Column name	Data Type	Description
Name	Sysname	Logical file name.
Fileid	Smallint	The numeric identifier of the file.
Filename	Nchar (260)	Physical file name.
Filegroup	Sysname	The group to which the file belongs. To facilitate allocation and management, you can divide database files into file groups. The log file does not belong to the file group.
Size	Nvarchar (18)	File size.
Maxsize	Nvarchar (18)	Maximum file size. The unlimited value in this field indicates that the file can be increased until the disk is full.
Growth	Nvarchar (18)	File increment. Indicates the size of the space added to the file each time a new space is required.
Usage	Varchar (9)	File usage. Data Files can only be usedData onlyThe log file can only be usedLog only.

Permission
The execution permission is granted to the public role by default.

Example
The following example returns information about the files in pubs.

Use pubs
Exec sp_helpfile