SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Avi Grinberg / move MDF or LDF files to new location / Latest Posts

RE: move MDF or LDF files to new location

Mad-Dog — Sun, 20 Dec 2009 03:32:37 GMT

no.it's design for sql 2005 and above.sry.

RE: move MDF or LDF files to new location

sivark1 — Thu, 17 Dec 2009 05:34:40 GMT

Hi i executed the above script found errorfor MDF Server: Msg 208, Level 16, State 1, Procedure MoveAllDatabaseFile, Line 34Invalid object name 'sys.master_files'.Server: Msg 208, Level 16, State 1, Procedure MoveAllDatabaseFile, Line 34Invalid object name 'sys.sysdatabases'for LDFServer: Msg 208, Level 16, State 1, Procedure MoveAllDatabaseFile, Line 24Invalid object name 'sys.master_files'.Server: Msg 208, Level 16, State 1, Procedure MoveAllDatabaseFile, Line 24Invalid 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 filespls updateThanks in advance

RE: move MDF or LDF files to new location

george sibbald — Wed, 02 Sep 2009 04:23:08 GMT

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.cheersgeorge

RE: move MDF or LDF files to new location

Mad-Dog — Thu, 23 Jul 2009 04:18:23 GMT

you can use this improved script.--This script create a store procedure that Move All MDF - LDF Files--Wrote by Avi Grinbergcreate procedure MoveAllDatabaseFile@FileType char (3),@fullpath varchar(100)asif @FileType not in ('MDF','LDF','mdf','ldf')beginprint '@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\'''returnendif @fullpath not like '%\'beginprint 'The @fullpath must end with a ''\'' ,this script is terminated!. 'returnenddeclare @dbname as varchar (100)declare @LogicalFileName as varchar (100)declare @PhysicalFileName as varchar (100)declare @runalter as varchar(500)--Case LDFif @FileType = 'LDF' or @FileType = 'ldf'beginDECLARE 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 sysdbwhere masterfiles.database_id = sysdb.dbidand masterfiles.type_desc = 'LOG' and masterfiles.database_id > 4 end--Case MDFelsebeginDECLARE 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 sysdbwhere masterfiles.database_id = sysdb.dbidand 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

RE: move MDF or LDF files to new location

tim tapping — Wed, 22 Jul 2009 13:48:14 GMT

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.....

RE: move MDF or LDF files to new location

Mad-Dog — Thu, 30 Oct 2008 23:34:15 GMT

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 isexample 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 Grinbergcreate procedure MoveAllDatabaseFile@FileType char (3),@fullpath varchar(500)asif @FileType not in ('MDF','LDF')beginSelect '@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'returnenddeclare @dbname as varchar (100)declare @dbfilename as varchar (100)declare @runalter as varchar(500)--Case LDFif @FileType = 'LDF'beginDECLARE CRS_db CURSOR LOCAL FAST_FORWARD FORselect sysdb.name as 'DBName',masterfiles.name as 'FileName'from sys.master_files masterfiles,sys.sysdatabases sysdbwhere masterfiles.database_id = sysdb.dbidand masterfiles.type_desc = 'LOG' and masterfiles.database_id > 4 --not take system DB'send--Case MDFelsebeginDECLARE CRS_db CURSOR LOCAL FAST_FORWARD FORselect sysdb.name as 'DBName',masterfiles.name as 'FileName'from sys.master_files masterfiles,sys.sysdatabases sysdbwhere masterfiles.database_id = sysdb.dbidand masterfiles.type_desc = 'ROWS' and masterfiles.database_id > 4 --not take system DB'send--Start execute MDF or LDFOPEN CRS_dbFETCH NEXT FROM CRS_db INTO @dbname,@dbfilename WHILE @@FETCH_STATUS = 0BEGIN Set @runalter = 'Alter database ' + @dbname + ' modify file (name = ' + @dbfilename + ' , filename = N'''+ @fullpath + @dbfilename + '.' + @FileType + ''')' exec(@runalter) FETCH NEXT FROM CRS_db INTO @dbname,@dbfilenameENDCLOSE CRS_dbDEALLOCATE CRS_db

RE: move MDF or LDF files to new location

ashah-926655 — Thu, 30 Oct 2008 19:03:32 GMT

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

move MDF or LDF files to new location

Mad-Dog — Fri, 25 Jul 2008 11:28:31 GMT

Comments posted to this topic are about the item [B]move MDF or LDF files to new location[/B]