June 3, 2009 at 7:32 pm
Hi All,
I am a novice DBA and our company have a geodatabase appliaction that feeds data to SQL server. Unfortunately my mdf file located on C drive is increasing a lot. As a preventive measure, I added a second file (ndf) file and pointed to D drive that has more space. This is a dev server and we are not using any SAN or EMC 😉
Now if I want to move my database, is it good to use attach/detach method and move the data files (mdf and ndf) files to some other location?
using T SQL I have to use sp_attach_db and create database .. for attach ?
Can I use Alter database instead of attach/detach database as explained here: http://www.sqlservercentral.com/articles/Administration/65896/
what is the difference and which one is the best ?
will moving the data files affect my application and jobs ?
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
June 3, 2009 at 10:35 pm
Hi,
I think this document has explained things pretty well (diff between SP_ATTACH_DB & ALTER DATABASE). A small curx of it.
So why exactly is this important, and what difference does it really make? Well, there are a number of things that can be affected by the use of attach/detach that are not affected when using ALTER DATABASE. For example if your database uses Service Broker, by using detach/attach, Service Broker is disabled on the database, whereas when using ALTER DATABASE MODIFY FILE, Service Broker remains enabled. To re-enable Service Broker for the database requires exclusive access to the database, which means that you will have to kick any active connection out of the database to use ALTER DATABASE ENABLE BROKER, once you realize that there is a problem. In addition, if you have enable TRUSTWORTHY for the database for SQLCLR or cross database ownership chaining, this is disabled using attach/detach where it is not using ALTER DATABASE MODIFY FILE. The reason for this is security. When you attach a database, it may not be from a trusted source, and for this reason, TRUSTWORTHY is always disabled upon attaching the database making it necessary for a DBA to reset this flag marking the database as trusted.
While it is possible to still move a database to a different file system location using detach/attach, there are potential unplanned consequences to doing so. For expedience and stability of your application/database, ALTER DATABASE should be the preferred method of moving the database inside of the same SQL Instance.
So Yes even I will suggest to use ALTER DATABASE method rather than SP_ATTCH_DB method.
Regards
GURSETHI
June 4, 2009 at 9:28 am
GURSETHI (6/3/2009)
Hi,I think this document has explained things pretty well (diff between SP_ATTACH_DB & ALTER DATABASE). A small curx of it.
So why exactly is this important, and what difference does it really make? Well, there are a number of things that can be affected by the use of attach/detach that are not affected when using ALTER DATABASE. For example if your database uses Service Broker, by using detach/attach, Service Broker is disabled on the database, whereas when using ALTER DATABASE MODIFY FILE, Service Broker remains enabled. To re-enable Service Broker for the database requires exclusive access to the database, which means that you will have to kick any active connection out of the database to use ALTER DATABASE ENABLE BROKER, once you realize that there is a problem. In addition, if you have enable TRUSTWORTHY for the database for SQLCLR or cross database ownership chaining, this is disabled using attach/detach where it is not using ALTER DATABASE MODIFY FILE. The reason for this is security. When you attach a database, it may not be from a trusted source, and for this reason, TRUSTWORTHY is always disabled upon attaching the database making it necessary for a DBA to reset this flag marking the database as trusted.
While it is possible to still move a database to a different file system location using detach/attach, there are potential unplanned consequences to doing so. For expedience and stability of your application/database, ALTER DATABASE should be the preferred method of moving the database inside of the same SQL Instance.
So Yes even I will suggest to use ALTER DATABASE method rather than SP_ATTCH_DB method.
Regards
GURSETHI
Thanks a lot for the clarification. I was able to move my data file and the log file !! 🙂
One more question:
I have added an ndf file. How can I get rid of the ndf file as now the disk space is big enough for the mdf file to grow? Is it a good idea to get rid of ndf file, or it is good to keep it for future use.
my both ndf and mdf are marked as primary. Is there any way that I can mark the ndf as secondary ?
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply