Home > SQL Server > SQL data transfer

SQL data transfer


MOVE SQL DATABASE TO NEW LOCATION

The following example moves a database that is named OnePoint. This database contains one data file, EeaData.mdf.mdf, and one log file, EeaData.ldf. If the database that you are moving has more data files or log files, specify the files in a comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db procedure does not change regardless of how many files the database contains because the sp_detach_db procedure does not list the files.

* Offline your database that you want to move.

Run query as:

ALTER
DATABASE Onepoint SET OFFLINE

* After some time it will show query completed successfully and your data now switched to master.

* Detach the database as follows:

go

Sp_detach_db
‘OnePoint’

go

* Manually move database to the drive where you want to move.

* Now attach your database, run query as:

use
master
go

sp_attach_db’OnePoint’,’E:\Microsoft
SQL Server\MSSQL.1\MASQL\Data\EeaData.mdf’, E:\Microsoft SQL
Server\MSSQL.1\MASQL\Data\EeaLog.ldf’

go

* Verify the change in file locations by using the sp_helpfile stored procedure:

use
OnePoint
go
sp_helpfile
go

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: