CODEWITHSUNDEEP
sql-serverdatabase
Potatan
Potatan

Reputation: 29

Re-attach SQL database to replace the original (instead of backup/restore)

We have a SQL 2012 database on Windows Server 2008R2 that has been detached by our infrastructure team as a form of backup. We now need to restore the database, and there are no "proper" backups of the database. I imagine we would need to do something like this:

  1. Take the current Live DB offline
  2. Delete the current DB from the file system
  3. File copy the previously detached DB from its backed up location, into the \Data folder
  4. Re-attach the DB

When copying, we would copy both the .MDF and .LDF files into the original \Data location.

Am I missing anything?

(Comments are also welcome on the advisability or otherwise of using "detach" as a backup method.)

Upvotes: 1

Views: 4166

Answers (2)

Nichole
Nichole

Reputation: 11

you can detach your database using these T-SQL sentences:

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'stellar'
GO

In order to attach, you can use the following T-SQL sentences:

USE [master]
GO
CREATE DATABASE [stellar] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\stellar.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\stellar_log.ldf' )
FOR ATTACH
GO

Another way to attach a database is to use the create database

CREATE DATABASE Stellar  
ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\stellar.mdf')  
FOR ATTACH ;

If you do not have a log file, you can use the following T-SQL commands to generate a new log file:

CREATE DATABASE Stellar
ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\stellar.mdf')
FOR ATTACH_REBUILD_LOG ;

Check reference here.

Upvotes: 1

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6566

If you are going to attach a database you need to consider the following prerequisites:

  1. The database must first be detached. Attempting to attach a database that has not been detached will return an error
  2. When you attach a database, all data files (MDF and LDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file
  3. When you attach a database, if MDF and LDF files are located in different directories and one of the paths includes \?\GlobalRoot, the operation will fail.

If there is nothing important to consider in prerequesties, then you can detach/attach the databases using following steps:

  1. Detach the current database
USE master;   
GO   
EXEC sp_detach_db @dbname = N'AdventureWorks2012'; 
GO

  1. Copy the desired database files to their location

  2. Attach the database

USE master;  
GO  
CREATE DATABASE YourDBName
    ON (FILENAME = 'location to mdf file\YourDBName.mdf'),  
    (FILENAME = 'location to ldf file\YourDBName.ldf')  
    FOR ATTACH;  
GO

Note that you might need to include your .ndf files in the above query, if they also exists.

You can do the following steps using SQL Server Management Studio detach/atach wizards too.

Finally using detach/attach is not a good solution for backup and recovery. You might read more about it here

Upvotes: 1

Related Questions