SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


move MDF or LDF files to new location


move MDF or LDF files to new location

Author
Message
Mad-Dog
Mad-Dog
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 1197
Comments posted to this topic are about the item move MDF or LDF files to new location
ashah-926655
ashah-926655
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 2
Can you give me more details.
Do you stop sql service before creaing or executing the script?
what are the modification you have to make?
Will you please give me some examples?
Thanks
Mad-Dog
Mad-Dog
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 1197
Hi,

every thing that you need to know is writing in the script details.
but for you i'll write it again.
this stored procedure move the LDF or MDF files to a new location.
the syntax to use it is after that you run the script that create the stored procedure is
example MDF: exec MoveAllDatabaseFile 'MDF','C:\NewMDFLocation\'
example LDF: exec MoveAllDatabaseFile 'LDF','C:\NewMDFLocation\'
after that you stop SQL Server Services,move those MDF or LDF files that you just run the script on them and then start the SQL Server Services back.

The Script

--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 + ''')'

exec(@runalter)

FETCH NEXT FROM CRS_db INTO @dbname,@dbfilename
END
CLOSE CRS_db
DEALLOCATE CRS_db
tim tapping
tim tapping
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 3
Followed the directions but this did not work for me. I had to drop every db and use sp_attach_db after moving the mdf and ldf files. Oh well.....
Mad-Dog
Mad-Dog
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 1197
you can use this improved script.

--This script create a store procedure that Move All MDF - LDF Files
--Wrote by Avi Grinberg
create procedure MoveAllDatabaseFile
@FileType char (3),@fullpath varchar(100)
as
if @FileType not in ('MDF','LDF','mdf','ldf')
begin
print '@FileType must be MDF or LDF (or lower case) and @fullpath should be like N''C:\DatabaseFiles\'' the script was terminated!.'
print 'example of right syntax - exec MoveAllDatabaseFile ''MDF'',N''C:\DatabaseFiles\'''
return
end
if @fullpath not like '%\'
begin
print 'The @fullpath must end with a ''\'' ,this script is terminated!. '
return
end
declare @dbname as varchar (100)
declare @LogicalFileName as varchar (100)
declare @PhysicalFileName as varchar (100)
declare @runalter as varchar(500)
--Case LDF
if @FileType = 'LDF' or @FileType = 'ldf'
begin
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
select sysdb.name as 'DBName',masterfiles.name as 'LogicalFileName',substring(right(physical_name,charindex('\',reverse(physical_name))),2,100) as 'PhysicalFileName'
from sys.master_files masterfiles,sys.sysdatabases sysdb
where masterfiles.database_id = sysdb.dbid
and masterfiles.type_desc = 'LOG' and masterfiles.database_id > 4
end
--Case MDF
else
begin
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
select sysdb.name as 'DBName',masterfiles.name as 'LogicalFileName',substring(right(physical_name,charindex('\',reverse(physical_name))),2,100) as 'PhysicalFileName'
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,@LogicalFileName,@PhysicalFileName

WHILE @@FETCH_STATUS = 0
BEGIN

Set @runalter = 'Alter database ' + @dbname + ' modify file (name = ' + @LogicalFileName + ' , filename = N'''+ @fullpath + @PhysicalFileName + ''')'

select (@runalter)

FETCH NEXT FROM CRS_db INTO @dbname,@LogicalFileName,@PhysicalFileName
END
CLOSE CRS_db
DEALLOCATE CRS_db
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10486 Visits: 13687
Avi,

just want to say thanks for posting the script, I have over 300 databases to move and this script will be most useful. ( I have done a successful trial run on a test bed server)

Only change I would make is use 'print (@runalter)' rather than 'select (@runalter)' so only the commands you actually want are reversed engineered out, also all the databases could be offlined, files moved, then bought on-line again if you want to avoid a service restart.

cheers

george

---------------------------------------------------------------------
sivark1
sivark1
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 1010
Hi i executed the above script found error


for MDF

Server: Msg 208, Level 16, State 1, Procedure MoveAllDatabaseFile, Line 34
Invalid object name 'sys.master_files'.
Server: Msg 208, Level 16, State 1, Procedure MoveAllDatabaseFile, Line 34
Invalid object name 'sys.sysdatabases'

for LDF

Server: Msg 208, Level 16, State 1, Procedure MoveAllDatabaseFile, Line 24
Invalid object name 'sys.master_files'.
Server: Msg 208, Level 16, State 1, Procedure MoveAllDatabaseFile, Line 24
Invalid object name 'sys.sysdatabases'.

Will it work for sqlserver 2000?????

iam using

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.2 (Build 3790: )


please help i need to move 20+ 20 Dbs mdf & ldf files


pls update

Thanks in advance
Mad-Dog
Mad-Dog
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 1197
no.
it's design for sql 2005 and above.
sry.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search