December 28, 2009 at 1:49 am
as w e r experiencing space crunch we need to move on the database files in to the another drive on the same server to ensure the space on the drive as a production dba how to do this by using attach and detach. what are the preventive steps to be taken before moving the files .
December 28, 2009 at 2:03 am
Set production database in single user mode
Take full back of production database
Following is example for Detach the Database
EXEC sp_detach_db 'AdventureWorks', 'true';
Following is the example for Attach the Database
EXEC sp_attach_db @dbname = N'AdventureWorks',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server
\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf' ;
Set production database in single user mode false
Refer to BOL given with examples
December 28, 2009 at 8:31 am
EXEC sp_attach_db @dbname = N'AdventureWorks',
--From BOL
Important:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 28, 2009 at 8:51 am
ramyours2003 (12/28/2009)
as w e r experiencing space crunch we need to move on the database files in to the another drive on the same server to ensure the space on the drive as a production dba how to do this by using attach and detach. what are the preventive steps to be taken before moving the files .
Y u go for attach/detach?
First u have to plan application downtime.
I'd suggested use Alter database method instead of attach/detach
Read the Moving Database Files Detach/Attach or ALTER DATABASE?
By Jonathan Kehayias[/url]
Edit:For Added more info
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 28, 2009 at 1:14 pm
Yet another way to do it is to take a backup of the database then do a restore using the "move" datafile option. You could restore the database to the new drive (using a different name) and then do a drop <original database> followed by a sp_renamedb <oldname>, <newname>
Carlos
December 30, 2009 at 4:43 pm
As of SQL Server 2005, you can use ALTER DATABASE and just take the database OFFLINE during the move. See BOL under "Moving Database Files." This section has separate instructions for User and System databases.
This way, SQL Server doesn't lose track of the database. For example, I believe (haven't tried it) that if the database has a database master encryption key which is encrypted by the service master key, it will be retained in the master database. If you detach the database this should be removed, and you would need to do an OPEN MASTER KEY and ALTER MASTER KEY after you re-attach the database. There are probably other gotcha's that will also be avoided.
David Lathrop
DBA
WA Dept of Health
December 30, 2009 at 5:40 pm
preventative steps - ensure no connections to database then backup the database
Use the alter database modify file method to move the file, preserves dbowner, dbid and default database settings for logins.
---------------------------------------------------------------------
December 30, 2009 at 6:30 pm
Yes, the database owner is one of the more important things to watch out for.
If you do detach and reattach databases (especially system databases) to move them, make sure you do a "EXECUTE AS LOGIN = 'SA' ;" before the attach, or they will belong to you. [On the system databases you can't just change the owner to correct the mistake. Been there, done that...]
David Lathrop
DBA
WA Dept of Health
December 30, 2009 at 8:00 pm
Both ALTER DATABASE and Detach/Attach demand a downtime.
If you are using ALTER make sure to take your database offline before moving/copying mdf/ldf. Same thing for Detach/Attach. Detach and then copy the file. Reason is if you manage to copy the mdf and ldf when database is ONLINE then the after the attach the database will be corrupted. Open files shd never be copied.
ALTER Database is a cleaner option for the reasons provided by Jonathan.
Regards,
Raj
December 31, 2009 at 3:33 am
arr.nagaraj (12/30/2009)
ALTER Database is a cleaner option for the reasons provided by Jonathan.
gotta ask........who's jonathan?
---------------------------------------------------------------------
January 1, 2010 at 3:59 am
Jonathan Kehayias , the noted SQL Server MVP, whose article's link on the topic discussed has been posted above.
Regards,
Raj
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply