How to Attach and Detach Database for SQL Server.

sp_attach_single_file_db
Attaches a database having only one data file to the current server.

Syntax
sp_attach_single_file_db [ @dbname = ] 'dbname'
, [ @physname = ] 'physical_name'

Arguments
[@dbname =] 'dbname'

Is the name of the database to be attached to the server. dbname is sysname, with a default of NULL.

[@physname =] 'phsyical_name'

Is the physical name, including path, of the database file. physical_name is nvarchar(260), with a default of NULL.

Return Code Values
0 (success) or 1 (failure)

Result Sets
None

Remarks
When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.

Use sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

Use sp_attach_single_file_db only on databases that have a single log file. Do not use this stored procedure on databases that have multiple log files.

Permissions
Only members of the sysadmin and dbcreator fixed server roles can execute this procedure.

Examples
This example detaches pubs and then attaches one file from 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'

Generate SQL Insert statements using Store Procedure

--EXEC sp_generate_inserts TABELNAME 
==========================================================
SET NOCOUNT ON 
PRINT 'Checking for the existence of this procedure' 
IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL 
BEGIN 
PRINT 'Procedure already exists. So, dropping it' 
DROP PROC sp_generate_inserts 
END 
GO 
CREATE PROC sp_generate_inserts 
(
@table_name varchar(776), 
@target_table varchar(776) = NULL, 
@include_column_list bit = 1, 
@from varchar(800) = NULL, 
@include_timestamp bit = 0, 
@debug_mode bit = 0, 
@owner varchar(64) = NULL, 
@ommit_images bit = 0, 
@ommit_identity bit = 0, 
@top int = NULL, 
@cols_to_include varchar(8000) = NULL, 
@cols_to_exclude varchar(8000) = NULL 
) 
AS 
BEGIN
SET NOCOUNT ON 
IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL)) 
BEGIN 
RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not specify both',16,1) 
RETURN -1 
END 
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0)) 
BEGIN 
RAISERROR('Invalid use of @cols_to_include property',16,1) 
PRINT 'Specify column names surrounded by single quotes and separated by commas' 
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"' 
RETURN -1 
END
IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0)) 
BEGIN 
RAISERROR('Invalid use of @cols_to_exclude property',16,1) 
PRINT 'Specify column names surrounded by single quotes and separated by commas' 
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"' 
RETURN -1 
END 
IF (parsename(@table_name,3)) IS NOT NULL 
BEGIN 
RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1) 
RETURN -1 
END 
IF @owner IS NULL 
BEGIN 
IF (OBJECT_ID(@table_name,'U') IS NULL) 
BEGIN 
RAISERROR('User table not found.',16,1) 
PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.' 
PRINT 'Make sure you have SELECT permission on that table.' 
RETURN -1 
END 
END 
ELSE 
BEGIN 
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = @owner) 
BEGIN 
RAISERROR('User table not found.',16,1) 
PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.' 
PRINT 'Make sure you have SELECT permission on that table.' 
RETURN -1 --Failure. Reason: There is no user table with this name 
END 
END 
--Variable declarations 
DECLARE @Column_ID int, 
@Column_List varchar(8000), 
@Column_Name varchar(128), 
@Start_Insert varchar(786), 
@Data_Type varchar(128), 
@Actual_Values varchar(8000), 
@IDN varchar(128) 
--Variable Initialization 
SET @IDN = '' 
SET @Column_ID = 0 
SET @Column_Name = 0 
SET @Column_List = '' 
SET @Actual_Values = '' 
IF @owner IS NULL 
BEGIN 
SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' 
END 
ELSE 
BEGIN 
SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' 
END 
--To get the first column's ID 
IF @owner IS NULL 
BEGIN 
SELECT @Column_ID = MIN(ORDINAL_POSITION) 
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
WHERE TABLE_NAME = @table_name 
END 
ELSE 
BEGIN 
SELECT @Column_ID = MIN(ORDINAL_POSITION) 
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
WHERE TABLE_NAME = @table_name AND 
TABLE_SCHEMA = @owner 
END 
--Loop through all the columns of the table, to get the column names and their data types 
WHILE @Column_ID IS NOT NULL 
BEGIN 
IF @owner IS NULL 
BEGIN 
SELECT @Column_Name = '[' + COLUMN_NAME + ']', 
@Data_Type = DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
WHERE ORDINAL_POSITION = @Column_ID AND 
TABLE_NAME = @table_name 
END 
ELSE 
BEGIN 
SELECT @Column_Name = '[' + COLUMN_NAME + ']', 
@Data_Type = DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
WHERE ORDINAL_POSITION = @Column_ID AND 
TABLE_NAME = @table_name AND 
TABLE_SCHEMA = @owner 
END 
IF @cols_to_include IS NOT NULL --Selecting only user specified columns 
BEGIN 
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0 
BEGIN 
GOTO SKIP_LOOP 
END 
END 
IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns 
BEGIN 
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0 
BEGIN 
GOTO SKIP_LOOP 
END 
END 
--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column 
IF (SELECT COLUMNPROPERTY( OBJECT_ID(@table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1 
BEGIN 
IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column 
SET @IDN = @Column_Name 
ELSE 
GOTO SKIP_LOOP 
END 
--Tables with columns of IMAGE data type are not supported for obvious reasons 
IF(@Data_Type in ('image')) 
BEGIN 
IF (@ommit_images = 0) 
BEGIN 
RAISERROR('Tables with image columns are not supported.',16,1) 
PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.' 
PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.' 
RETURN -1 --Failure. Reason: There is a column with image data type 
END 
ELSE 
BEGIN 
GOTO SKIP_LOOP 
END 
END 
--making sure, not to lose any data from flot, real, money, smallmomey, datetime columns 
SET @Actual_Values = @Actual_Values + 
CASE 
WHEN @Data_Type IN ('char','varchar','nchar','nvarchar') 
THEN 
''''''''' + '+'COALESCE(REPLACE(RTRIM(' + @Column_Name + '),'''''''',''''''''''''),''nvkon©'')' + ' + ''''''''' 
WHEN @Data_Type IN ('datetime','smalldatetime') 
THEN 
''''''''' + '+'COALESCE(RTRIM(CONVERT(char,' + @Column_Name + ',109)),''nvkon©'')' + ' + ''''''''' 
WHEN @Data_Type IN ('uniqueidentifier') 
THEN 
''''''''' + '+'COALESCE(REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''',''''''''''''),''NULL'')' + ' + ''''''''' 
WHEN @Data_Type IN ('text','ntext') 
THEN 
''''''''' + '+'COALESCE(REPLACE(CONVERT(char,' + @Column_Name + '),'''''''',''''''''''''),''NULL'')' + ' + ''''''''' 
WHEN @Data_Type IN ('binary','varbinary') 
THEN 
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')' 
WHEN @Data_Type IN ('timestamp','rowversion') 
THEN 
CASE 
WHEN @include_timestamp = 0 
THEN 
'''DEFAULT''' 
ELSE 
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')' 
END 
WHEN @Data_Type IN ('float','real','money','smallmoney') 
THEN 
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')' 
ELSE 
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')' 
END + '+' + ''',''' + ' + ' 
--Generating the column list for the INSERT statement 
SET @Column_List = @Column_List + @Column_Name + ',' 
SKIP_LOOP: --The label used in GOTO 
IF @owner IS NULL 
BEGIN 
SELECT @Column_ID = MIN(ORDINAL_POSITION) 
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
WHERE TABLE_NAME = @table_name AND 
ORDINAL_POSITION > @Column_ID 
END 
ELSE 
BEGIN 
SELECT @Column_ID = MIN(ORDINAL_POSITION) 
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
WHERE TABLE_NAME = @table_name AND 
ORDINAL_POSITION > @Column_ID AND 
TABLE_SCHEMA = @owner 
END 
--Loop ends here! 
END 
--To get rid of the extra characters that got concatened during the last run through the loop 
SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1) 
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6) 
--Forming the final string that will be executed, to output the INSERT statements 
IF (@include_column_list <> 0) 
BEGIN 
SET @Actual_Values = 
'SELECT ' + 
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + 
'''' + RTRIM(@Start_Insert) + 
' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' + 
' +''VALUES(''+ ' + 'REPLACE(' + @Actual_Values + ',''''''nvkon©'''''',''NULL'')' + '+'')''' + ' ' + 
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)') 
END 
ELSE IF (@include_column_list = 0) 
BEGIN 
SET @Actual_Values = 
'SELECT ' + 
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + 
'''' + RTRIM(@Start_Insert) + 
' '' +''VALUES(''+ ' + 'REPLACE(' + @Actual_Values + ',''''''nvkon©'''''',''NULL'')' + '+'')''' + ' ' + 
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)') 
END 
--Determining whether to ouput any debug information 
IF @debug_mode =1 
BEGIN 
PRINT '/*****START OF DEBUG INFORMATION*****' 
PRINT 'Beginning of the INSERT statement:' 
PRINT @Start_Insert 
PRINT '' 
PRINT 'The column list:' 
PRINT @Column_List 
PRINT '' 
PRINT 'The SELECT statement executed to generate the INSERTs' 
PRINT @Actual_Values 
PRINT '' 
PRINT '*****END OF DEBUG INFORMATION*****/' 
PRINT '' 
END 
PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas' 
PRINT '--Problems/Suggestions? Contact Vyas @ answer_me@hotmail.com' 
PRINT '--http://vyaskn.tripod.com' 
PRINT '' 
PRINT 'SET NOCOUNT ON' 
--Determining whether to print IDENTITY_INSERT or not 
IF (@IDN <> '') 
BEGIN 
PRINT 'SET IDENTITY_INSERT ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ' ON' 
PRINT 'GO' 
END 
PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + '''' 
--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes! 
EXEC (@Actual_Values) 
PRINT 'PRINT ''Done''' 
IF (@IDN <> '') 
BEGIN 
PRINT 'SET IDENTITY_INSERT ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ' OFF' 
PRINT 'GO' 
END 
PRINT 'SET NOCOUNT OFF' 
SET NOCOUNT OFF 
RETURN 0 --Success. We are done! 
END 
GO 
PRINT 'Created the procedure' 
GO 
SET NOCOUNT OFF 
GO 
PRINT 'Done'