SQL Server 2008:Moving Database files from One location to another by Attach and Detach Method.

Change Database File Location.

 

1.Prepare for database backup script.Include All System Databases(master,model,msdb,tempdb).

Use this,Click

Connect with SA user to perform all operation.

2.Get the information of Current Databases file location.

Use this SQL

SELECT name,physical_name FROM master.sys.master_files

GO

Alternatively,

USE DBNAME

GO
 
sp_helpfile
 
Go

3.Detach All Databases excluding system databases.
(master,model,msdb,tempdb).

Use this script for all databases.

USE [master]

GO

DECLARE @name VARCHAR(50) -- database name

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name  not IN ('master','model','msdb','tempdb')

begin

--Backup master_files information to backup table.

 select * into master_files_bak from  (select distinct DB_NAME(a.database_id) dbname,case  when a.physical_name like '%.mdf' then a.physical_name end Datafile,
 case when b.physical_name like '%.ldf' then b.physical_name end logicalfile
 from
 master.sys.master_files a
 inner join
 master.sys.master_files b
 on DB_NAME(a.database_id)=DB_NAME(b.database_id)
 where a.name not in ('master','msdb','model','tempdb')
 ) c
 where Datafile is not null and logicalfile is not null
 and dbname not in ('master','model','msdb','tempdb')
  order by dbname

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN
       EXEC('sp_detach_db '+@name)  
       FETCH NEXT FROM db_cursor INTO @name 
END

CLOSE db_cursor

DEALLOCATE db_cursor

end


4.Copy All data files from current location(C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\) to new location(D:\MSSQL\DATA\).

 5.Attach databases.Use Windows authentication or Start SSMS by "Run as Administrator".



Use this script..

--For Attach

USE [master]

Go

DECLARE @name        VARCHAR(50)  -- database name
DECLARE @physicalfile    VARCHAR(500) -- database filename with location
DECLARE @logicalfile    VARCHAR(500) -- database filename with location
DECLARE @datafile    VARCHAR(500) -- mdf file without location
DECLARE @logfile    VARCHAR(500) -- ldf file without location
DECLARE @Command    VARCHAR(500) -- SQL Command

DECLARE db_cursor CURSOR FOR
SELECT dbname,Datafile,logicalfile
FROM master.dbo.master_files_bak

begin

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name,@physicalfile,@logicalfile

WHILE @@FETCH_STATUS = 0 
BEGIN 
     
select  @datafile=right((@physicalfile),charindex('\',reverse(@physicalfile), 1) - 1)

select @logfile=right((@logicalfile),charindex('\',reverse(@logicalfile), 1) - 1)
 
SET @Command= 'sp_attach_db '''+ @name+''','+'''D:\MSSQL\DATA\'+@datafile+''','+'''D:\MSSQL\DATA\'+@logfile+''''


print(@Command)


exec(@Command)
      
       FETCH NEXT FROM db_cursor INTO @name,@physicalfile,@logicalfile
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

DROP TABLE [dbo].[master_files_bak]

end

 
**Note :If error occur like 

Unable to open the physical file "D:\MSSQL\DATA\***.mdf". Operating system error 5: "5(Access is denied.)". 

Use Command which prints.Execute as Windows authentication.

sp_attach_db '****','D:\MSSQL\DATA\****.mdf','D:\MSSQL\DATA\*****_log.ldf'



6.For System Databases Data files movements.

Stop Services..

Open Command Prompt.

C:\Documents and Settings\Administrator>net stop "SQL Server (SQLEXPRESS)"

This is only for MODEL,MSDB and TEMPDB.(not for resource and master).


 Start SQL Server with this parameter.

C:\Documents and Settings\Administrator>net start "SQL Server (SQLEXPRESS)" /f /
T3608


Output

The SQL Server (SQLEXPRESS) service is starting.
The SQL Server (SQLEXPRESS) service was started successfully.



7.Connect SQL Server from SQLCMD. Connect with windows authentication.

Collect logical name of the all database files.

select name from master.dbo.master_files

Execute this command

  ALTER DATABASE model MODIFY FILE( NAME = 'modeldev' , FILENAME = 'D:\MSSQL\DATA\model.mdf')

Go

The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.




 8.For master data,

Go to SQL Server Configuration Manager.




 Right Click on SQL Services..





Go to Advance Tab






9.Change the new path.


 --Old Parameter value

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.ldf

--New Parameter value

-dD:\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG;-lD:\MSSQL\DATA\mastlog.ldf








Stop Services...




Copy Master file and log file to new location.

Start Service...


10.For tempdb,

USE master; GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\MSSQL\DATA\tempdb.mdf'); GO

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\MSSQL\DATA\templog.ldf'); GO


Restart Service.






No comments:

Post a Comment

Dear User,

Thank you very much for your kind response