May 16, 2011 at 11:24 pm
Hi Folks,
I've a test database which is having the .mdf and .ldf files in D:\Testdatabase\ and now the case is i've added a secondary file called test_1.ndf to test database. At that time unfortunately i've given the path of .ndf file in D:\. So, now i want to move that .ndf file to D:\Testdatabase\.
so kindly anyone help me with this process.
TIA.
May 16, 2011 at 11:37 pm
That requires downtime.
1.Detach database
2.Move the file
3.Attach database, make sure to specify the new path for the secondary file.
Can do it from SSMS or through scripts.
Check BOL for sp_attach_db, sp_detach_db
May 16, 2011 at 11:46 pm
Use Alter database Modify file to change the file name
Take the DB offline
Move the file
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
May 17, 2011 at 12:08 am
Hi Gail,
Any thoughts about pro et contra for both solutions?
Detach/attach seemed more ‘natural’ to me, but would like to understand why you advised different?
Believe that there was a reason.
Respect your posts (and not only posts) very much and taking every opportunity to learn something.
Thanks
Vera
May 17, 2011 at 12:19 am
Thanks allot for your kind co-operation,
I tried to move the secondary file through ALTER DATABASE MODIFY command, after copying the .ndf file to the destination folder am not able to bring back the database online.
It is throwing an error Access Denied.Alter database statement failed.
May 17, 2011 at 2:09 am
srinath.vanama (5/17/2011)
Thanks allot for your kind co-operation,I tried to move the secondary file through ALTER DATABASE MODIFY command, after copying the .ndf file to the destination folder am not able to bring back the database online.
It is throwing an error Access Denied.Alter database statement failed.
Most likely the SQL Server service account does not have full permission on the new folder.
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
May 17, 2011 at 2:10 am
ModestyBlaise84 (5/17/2011)
Detach/attach seemed more ‘natural’ to me, but would like to understand why you advised different?
Doesn't change the DB id, doesn't potentially mess up service broker, CDC or any of the other server-level tools. Can be done on a database involved in replication.
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
May 17, 2011 at 2:40 am
ModestyBlaise84 (5/17/2011)
Hi Gail,Any thoughts about pro et contra for both solutions?
Detach/attach seemed more ‘natural’ to me, but would like to understand why you advised different?
Believe that there was a reason.
Respect your posts (and not only posts) very much and taking every opportunity to learn something.
Thanks
Vera
Pls refer this link.
http://www.sqlservercentral.com/articles/Administration/65896/
M&M
May 17, 2011 at 3:20 am
Thank you 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply