move MDF or LDF files to new location

,

This script create a stored procedure that called MoveAllDatabaseFile.you can move MDF or LDF to a new location.

the syntax for use the script after that you create the stored procedure is:

exec MoveAllDatabaseFile 'MDF','C:\NewLocation\'

the stored procedure not move any system databases.

run the stored procedure for 'MDF' file and run it again for 'LDF' file(with the appropriate location).

you can adjust the script to include or exclude any other databases.

after that you run the script you need to stop the SQL Server Service move the MDF or LDF files that you just run the script on them to the new location and then Start the SQL Server Service.

--Move All MDF - LDF Files to a new location
--write by Avi Grinberg
create procedure MoveAllDatabaseFile
@FileType char (3),@fullpath varchar(500)
as
if @FileType not in ('MDF','LDF')
begin
Select '@FileType must be MDF or LDF and @fullpath should be like N''C:\DatabaseFiles\'' the script was terminated' as 
'ERROR'
select 'example of right syntax - exec MoveAllDatabaseFile ''MDF'',N''C:\DatabaseFiles\''' as 'Example Script'
return
end
declare @dbname as varchar (100)
declare @dbfilename as varchar (100)
declare @runalter as varchar(500)
--Case LDF
if @FileType = 'LDF'
begin
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD 
FOR
select sysdb.name as 'DBName',masterfiles.name as 'FileName'
from sys.master_files masterfiles,sys.sysdatabases sysdb
where masterfiles.database_id = sysdb.dbid
and masterfiles.type_desc = 'LOG' and masterfiles.database_id > 4 --not take system DB's
end
--Case MDF
else
begin
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD 
FOR
select sysdb.name as 'DBName',masterfiles.name as 'FileName'
from sys.master_files masterfiles,sys.sysdatabases sysdb
where masterfiles.database_id = sysdb.dbid
and masterfiles.type_desc = 'ROWS' and masterfiles.database_id > 4 --not take system DB's
end
--Start execute MDF or LDF
OPEN CRS_db
FETCH NEXT FROM CRS_db INTO @dbname,@dbfilename
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
 Set @runalter = 'Alter database ' + @dbname + ' modify file (name = ' + @dbfilename + ' , filename = N'''+ @fullpath + 
@dbfilename + '.' + @FileType + ''')' 
 
 select (@runalter)
 
 FETCH NEXT FROM CRS_db INTO @dbname,@dbfilename
END
CLOSE CRS_db
DEALLOCATE CRS_db

Rate

3.11 (9)

Share

Share

Rate

3.11 (9)