Detach and Attach database in SQL Server by serverku

Recently, while working with the database move to another location, I used the tsql script to move them. There are some methods to move the database files to another like detach and attach files from the new location and the other one is worth modifying the database file location. So, lets see both of them as how can we move database files.

Option #1 : Using Modify file
Here we can use MODIFY clause to move database file after copying required place like as following,

USE master 
Go 
ALTER DATABASE TestDB SET offline 

ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB _Data, FILENAME = 'D:\TestDB _Data.mdf') 

ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB _Log, FILENAME = 'D:\TestDB _Log.ldf') 

ALTER DATABASE TestDB SET online 

This is one of the fist method to move databases to another location.

Option #2 : Using Detach and Attach
We can use detach and attach method to move database files also. Let’s see that also

ALTER DATABASE TestDB 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TestDB'
GO

This will detach database with drop existing connection. After detaching database, we can attach the database with or without log files like to mention as follows and can be applied any of following methods to attach the database again.
#1. Attaching database with both data and log files which will use existing mdf and ldf files,

EXEC sp_attach_db @dbname = N'TestDB', 
    @filename1 = N'D:\TestDB_Data.mdf',  
    @filename2 = N'D:\TestDB_Log.ldf';

#2. We can also generate or rebuild new log file with existing mdf file and attach like a database,

EXEC sp_attach_db @dbname = N'TestDB', 
 @filename1 = N'D:\TestDB_Data.mdf' 
 FOR ATTACH_REBUILD_LOG; 

#3. Creating a new database with same or different name using the same existing mdf file and rebuilding ldf file,

CREATE DATABASE TestDB ON
 (FILENAME =  N'D:\TestDB_Data.mdf') 
 FOR ATTACH_REBUILD_LOG

#4.  Creating a new database with same or different name using same existing mdf and ldf files,

CREATE DATABASE TestDB ON
 (FILENAME = 'D:\TestDB_Data.mdf')  ,
 (FILENAME = 'D:\TestDB_Log.ldf')
 FOR ATTACH;

These all are about to move tsql script, We can also perform the same thing with the UI,

These are various methods to move databases to another location. Hope you liked this post and also usable for you.

Restore Full Database from multiple backup files - SQL Server by serverku

As I know database restoration is a best practice on periodically to check and verify database backup copies and issues while restoration due to any stuffs. I have written for the database backups as how can we perform a full database backup completely and split into multiple files to reduce IO and time. As we have performed full database backups into split multiple files, Same way we will perform restoration from those complete and split multiple files. First we will see to restore full database complete backup and tried for from multiple files.

#1. Using TSQL : Let us run the below script first and get the logical names for database data and log files

RESTORE FILELISTONLY FROM 
DISK =  'D:\DBBackups\ReportServer\ReportServer.bak'
GO

After collection and putting logical file names in script below, it will restore the database from complete backup.

RESTORE DATABASE ReportServerComplteCopy FROM 
DISK = 'D:\DBBackups\ReportServer\ReportServer.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy_log.ldf'
GO

As above, we can restore database from multiple files as following,

RESTORE DATABASE ReportServerSplitCopy FROM 
 DISK = 'D:\DBBackups\ReportServer\ReportServer_Split1.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split2.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split3.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy_log.ldf'
GO

#2. From Management Studio : We can also perform restoration from SSMS by adding those multiple backups as per shot.

You can see over here after adding those files it will show as a single backup set.

Hope this help you.