Attach & Detach all databases

While working in my current project, I faced many scenarios where I have to attach one or more Databases and then detaches these databases. Later I found one script here which can easily generate the “attach” code for each database, make any path or filename changes necessary, and re-attach all the databases with a single mouse click.

Refer the below script and test this before you run in Production environment:

USE [master];

DECLARE @database NVARCHAR(200),
@cmd NVARCHAR(1000),
@attach_cmd NVARCHAR(4000),
@file NVARCHAR(1000),
@i INT;

DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR
SELECT  RTRIM(LTRIM([name]))
FROM    sysdatabases
WHERE   [dbid] > 4  -- exclude system databases

OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;

-- Initial attach command stub
SET @attach_cmd = \'-- \' + QUOTENAME(@database) + CHAR(10)
+ \'EXEC sp_attach_db @dbname = \'\'\' + @database + \'\'\'\' + CHAR(10);

-- Get a list of files for this database
DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR
SELECT  physical_name
FROM    sys.master_files
WHERE   database_id = db_id(@database)
ORDER   BY [file_id];

OPEN dbfiles_cur
FETCH NEXT FROM dbfiles_cur INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN
SET @attach_cmd = @attach_cmd + \'    ,@filename\' + CAST(@i AS NVARCHAR(10)) + \' = \'\'\' + @file + \'\'\'\' + CHAR(10);
SET @i = @i + 1;
FETCH NEXT FROM dbfiles_cur INTO @file
END

CLOSE dbfiles_cur;
DEALLOCATE dbfiles_cur;

-- Output \"attach\" command.
PRINT @attach_cmd;

-- DETACH (uncomment the following line at your peril - IT WILL DETACH ALL USER DATABASES!)
/*EXEC sp_detach_db @dbname = @database, @skipchecks = \'true\';  -- change this to false if you want it to update stats before detaching*/

FETCH NEXT FROM dbname_cur INTO @database
END

CLOSE dbname_cur;
DEALLOCATE dbname_cur;

Full Backup script in SQL Server

The below script will generate FULL BACKUP Script. After running the below command, copy the output and run the copied script to create Full Database Backup.

Declare @Path VARCHAR(100)
SEt @Path=\'C:\\DatabaseBackup\\\'
Select \'BACKUP DATABASE [\'+name+\'] TO DISK = N\'\'\'+@Path+\'\'+name+\'.bak\'\'
WITH NOFORMAT, NOINIT, NAME = N\'\'\'+name+\'-Full Database Backup\'\', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10\'

from sys.databases
where database_id>4