Archive

Archive for the ‘SQL Server’ Category

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

Categories: SQL Server