Move the User define Database

Move the User define Database

This article contains "To move the User define database in new location by attach and detach functions in SQL Server"   

Sometimes it is necessary to move the database and transaction log in different locations to increase the performance. As transaction log is growing quickly we have to make some strategy to get the solutions by moving it in a separate drive or separate disk.

One of my readers gives me a note related to it, and I want to share it with all of you.

"Shrinking of a database or log file causes file-fragmentation on the hard disk, this leads to poor performance. Therefore:

1. The transaction log should consist of two files: One which cannot expand itself and has the size you normally need between two full backups (plus some safety).
2. You create a second file which expands automatically.
If a transaction log grows too much only the second file will grow. Next day after full backup you just delete the second file and create a new one."

To move the existing database to new Location

Important

Before moving any database we must take the backup of the database.

Syntax

sp_detach_db [ @dbname= ] 'database_name'

            [ , [ @skipchecks= ] 'skipchecks' ]

            [ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

sp_attach_db [ @dbname = ] 'dbname' 
                     , [ @filename1 = ] 'filename_n' [ ,...16 ]

How to Move

Step-1 [ Detach the Database ]

USE MASTER

GO

SP_DETACH_DB 'MY_DATABASE'

GO

Step-2 [ Copy the Data File and Log File from Current Location to New Location ]

Just copy the File in New Location. For Example Copy the file from E:\myData\SQLData to F:\MyData\NewLocation

Step-3 [Re-Attach the Database from New Location ]

USE MASTER

GO

SP_ATTACH_DB 'MY_DATABASE',

             'F:\MYDATA\NEWLOCATION\MY_DATABASE.MDF',

             'F:\MYDATA\NEWLOCATION\MY_DATABASE.LDF'

GO

Step-4 [Verify the Changes ]

USE MY_DATABASE

GO

SP_HELPFILE

GO