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
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
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.
Comments
Post a Comment
Dear User,
Thank you very much for your kind response