Friday, April 10, 2009

How To Use Detach and Attach Method In SQL Server

Moving database file from one drive to another drive In SQL Server 2000 using detach and attach.

Note: Take a complete backup before continuing. If you don't you could lose data.

Once backup was done now time to move your database files. There are 3 different files *.mdf , *.ldf and *.ndf files that are stored somewhere such as D:\Mssql1\Data. The files are named for your database.

For example, Advent_dat.mdf and Advent_log.ldf  is the files for the database named Adventureworks

Step 1: Run below query
use master
GO
sp_detach_db 'Adventureworks' GO 


Step 2: Now copy the data files and the log files from the current location (D:\Mssql1\Data) to the new location (E:\Sqldata).

Step 3: Re-attach the database. Point to the files in the new location as follows:
use master 
GO 
sp_attach_db 'Adventureworks','Advent_dat.mdf','Advent_log.ldf'
GO 

Step 4: Now verify the change in file locations by using the sp_helpfile stored procedure: 
use Adventureworks
GO
SP_helpfile
GO

SQL server database move. database file move. moving file from one location to another location. moving file. moving file from one drive to another drive. database file move from one location to another location. how to move mdf files. how to move log files from one location to another location. how to move data files. how to move log files. how to move tempdb. how to move master db. how to move model db. how to move msdb. how to move distibution db. sql server 2005 moving files. sql server 2008 r2. sql server 2012. sql server 2012 mocing file. denali. How to use Detach and Attach functions to move SQL Server. Move Database Files in SQL Server: Detach Attach. SQL Server backup restore detach attach. Attaching and Detaching Databases on SQL Server. How to Detach and Attach a SQL Server FILESTREAM Enabled. how to attach and Detach database in SQL server 2005.

0 comments:

Post a Comment