Nostalgia 01 (Fastest way to copy a database)

I can still remember, when I was a beginner, I used to take backup of my databases in most unconventional way, probably the fastest way to copy an image of a database. This is a big confession; still I like to share this to novice DBAs. Sometimes it can be a lifesaver. So, this is how I used to do it.

Step 01: Shutdown the Database

Offline

Step 02: DETACH DATABASE

Detach

Step 03: Copy the Database

Step 04: Paste the Database

Step 05: Rename the Database

Step 06: Attach Database

Attach

 

Get the total script here.

 

USE [MASTER]
GO
ALTER DATABASE TSQL2012
SET OFFLINE WITH ROLLBACK IMMEDIATE

USE [MASTER]
GO
EXEC master.DBO.SP_DETACH_DB  @DBNAME = ‘TSQL2012’
GO

USE [MASTER]
GO
CREATE DATABASE [TSQL2012] ON
(FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.NIRVANA\MSSQL\DATA\TSQL2012_COPY.MDF’),
(FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.NIRVANA\MSSQL\DATA\TSQL2012_LOG_COPY.LDF’)
FOR ATTACH
GO

 

Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s