April 20, 2012 at 3:50 am
Hi I need to change the location of mdf and ldf files of existing sql server user databases.
is it possible through ??
ALTER DATABASE Sampledata
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE Sampledata
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO
I guess this is right... but will it work... please specify... I need to implement it...
Sagar Sonawane
** Every DBA has his day!!:cool:
April 20, 2012 at 3:51 am
For a user database it'll work if you change the file name to where you want the files to be. Once you've run that, take the database offline, move the files, then bring the DB back online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2012 at 4:14 am
GilaMonster (4/20/2012)
For a user database it'll work if you change the file name to where you want the files to be. Once you've run that, take the database offline, move the files, then bring the DB back online.
ALTER DATABASE [JAY] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Database offline successfully
ALTER DATABASE Jay
MODIFY FILE (NAME='Jay', FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Jay.ldf')
GO
ALTER DATABASE Jay
MODIFY FILE (NAME='Jay_log', FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LDF_Files\Jay_log.ldf')
GO
-- Successfully
ALTER DATABASE [JAY] SET ONLINE
GO
error :
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Jay.ldf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 945, Level 14, State 2, Line 1
Database 'JAY' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
please suggest
Sagar Sonawane
** Every DBA has his day!!:cool:
April 20, 2012 at 4:19 am
Read my instructions again.
GilaMonster (4/20/2012)
Once you've run that, take the database offline, move the files, then bring the DB back online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2012 at 4:25 am
1) Open SQL Server Management Studio or SQL Enterprise Manager (based on your SQL version/edition).
2) Connect to specific Instance of SQL.
3) Right click on database whose data files you want to move.
4) Select Task -> Offline.
5) Once database goes offline then again Right click on the database & Task -> Detach (this time).
Reason i asked to take DB offline first is so that database should be closed properly (it will be done during direct Detach as well but I always preffer to take to offline.. personal choice).
5) once detach happens, move the MDF or LDF files to a new location.
6) Attach database in SQL Server Management Studio/Enterprise Manager by right clicking on DATABASES in Object Explorer.
7) Provide location of MDF file & hit OK and you are done.
I did these steps.... it successfully done.... Thank you GilaMonster
Sagar Sonawane
** Every DBA has his day!!:cool:
April 20, 2012 at 4:38 am
That was completely unnecessary. All you had to do was move the files and bring the database back online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply