Solved

Changing log file location

Posted on 2007-04-05
4
294 Views
Last Modified: 2010-05-19
ALTER DATABASE  databaseName
MODIFY FILE (NAME = log_name, FILENAME = 'F:\logs\database_log.ldf');
GO

I tried the obove statement and get the following error:
MODIFY FILE failed. Do not specify physical name

Any suggestions
0
Comment
Question by:yanci1179
4 Comments
 
LVL 11

Accepted Solution

by:
dready earned 400 total points
ID: 18861534
I copied the relevant part from this link:
http://support.microsoft.com/kb/224071

Hope it helps

Moving user databases
The following example moves a database that is named mydb. This database contains one data file, Mydb.mdf, and one log file, Mydblog.ldf. If the database that you are moving has more data files or log files, specify the files in a comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db procedure does not change regardless of how many files the database contains because the sp_detach_db procedure does not list the files. 1. Detach the database as follows:use master
   go
   sp_detach_db 'mydb'
   go
 
2. Next, copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
3. Re-attach the database. Point to the files in the new location as follows:use master
  go
  sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
  go
Verify the change in file locations by using the sp_helpfile stored procedure:use mydb
   go
   sp_helpfile
   go
The filename column values should reflect the new locations.
Note Microsoft Knowledge Base article 922804 describes an issue for SQL Server 2005 databases on a network-attached storage. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
922804 (http://support.microsoft.com/kb/922804/) FIX: After you detach a Microsoft SQL Server 2005 database that resides on network-attached storage, you cannot reattach the SQL Server database
Consider this issue. Additionally, consider the permissions that are applied to a database when it is detached in SQL Server 2005. For more information, see the "Detaching and Attaching a Database" section of the "Securing Data and Log Files" topic in SQL Server Books Online. To view this topic, visit the following Microsoft Developer Network (MSDN) Web site:
0
 
LVL 10

Expert Comment

by:TAB8
ID: 18861690
The only lof file you can move online like this is the log file for tempdb ... follow dready's steps
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 18863870
I have created the following procedure to ease things up...works on 1MDF/1LDF databases (NDF non handled yet)...Please use with caution (offline operation)...

--1) First Create the procedure...

create procedure sp_move_db_offline(
@dbnamer varchar(50),
@target_data_path_drive varchar(3000),
@target_log_path_drive varchar(3000)
)
as
begin
      declare @string_execute1 varchar(1000)
      declare @source_primary_data_path_file varchar(3000), @source_log_path_file varchar(3000)
      declare @final_primary_data_path_file varchar(3000), @final_log_path_file varchar(3000)

      set @source_primary_data_path_file =
      (
      select sa.[filename] from master..sysaltfiles sa
            inner join master..sysdatabases sd
                  on sa.dbid = sd.dbid
      where sd.name = @dbnamer and
      upper(right(sa.[filename], 3)) = 'MDF'
      )

      set @source_log_path_file =
      (
      select sa.[filename] from master..sysaltfiles sa
            inner join master..sysdatabases sd
                  on sa.dbid = sd.dbid
      where sd.name = @dbnamer and
      upper(right(sa.[filename], 3)) = 'LDF'
      )


      set @final_primary_data_path_file = @target_data_path_drive + '\' + @dbnamer + '.MDF'      
      set @final_log_path_file = @target_log_path_drive + '\' + @dbnamer + '.LDF'
      set @string_execute1 = 'exec sp_detach_db ' + char(39) + @dbnamer + char(39)
      exec(@string_execute1)

      set @string_execute1 = 'copy ' + @source_primary_data_path_file + char(32) + @final_primary_data_path_file
      exec xp_cmdshell @string_execute1

      set @string_execute1 = 'copy ' + @source_log_path_file + char(32) + @final_log_path_file
      select @string_execute1
      exec xp_cmdshell @string_execute1

      waitfor delay '000:00:02'
      set      @string_execute1 = 'sp_attach_db ' + char(39) + @dbnamer + char(39) + ', ' + char(39) + @final_primary_data_path_file + char(39) + ', ' + char(39) + @final_log_path_file + char(39)
      exec(@string_execute1)
end


--2) Then use it as follows...
--> Make sure folders are created before you run the script...The script does not handle Folder existence check...
--> Won't work on system databases (master, msdb, model, tempdb)


use master
go
exec sp_move_db_offline 'yourdb', 'target_datafolder', 'target_logfolder'

Ex:
exec sp_move_db_offline 'Database1', 'D:\SQL2005\DATAFILE', 'D:\SQL2005\LOGFILE'
exec sp_move_db_offline 'Database2', 'D:\SQL2005\DATAFILE', 'D:\SQL2005\LOGFILE'

Hope this helps...

0
 

Author Comment

by:yanci1179
ID: 18875703
thank you Racimo.  We ended up deattaching databases.  I would've tried yours but didn't see it at the time we were working on changing the file names.  thanks again.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now