Moving Database Files Detach/Attach or ALTER DATABASE?

Question

Moving Database Files Detach/Attach or ALTER DATABASE?

Viewing 15 posts - 31 through 45 (of 58 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

GRE (5/27/2009)
I think there occaions when an deatach/attach is the best option especially when dealing with larger databases and you only have native SQL backup to use...Sometimes you don't have the neccessary disk space to take a backup and restore the database files (2x times the size of the database) expecially when migrating accross two servers where you can detach the db, copy the database files accorss to the new server and reattch there...If you are worried about corruption run a DBCC CHECKDB before you start.
SQL Won't restore the database unless it has exclusive access to it...so if users are connected the restore will fail.

Where are your normal FULL backups stored if you don't have space?

Using FULL Recovery, your normal FULL backup can be the starting point of an upgrade/migration to a new server while keeping your production system online and functioning. For large databases, restoring the last FULL with NORECOVERY and then applying TLOG backups to roll it forward is the only sensible way to migrate or perform upgrades, essentially manually log shipping your database to the new server until you hit the cutover point when you shut down the app, take a final TLOG backup, and shut down the old server. Your downtime is limited to the time it takes to copy a LOG Backup between servers, restore it with RECOVERY and reconfigure the application. This might be a bit more work, but it has much less downtime than using Attach/Detach to migrate the database.

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 2

Jonathan Kehayias (5/27/2009)
GRE (5/27/2009)
I think there occaions when an deatach/attach is the best option especially when dealing with larger databases and you only have native SQL backup to use...Sometimes you don't have the neccessary disk space to take a backup and restore the database files (2x times the size of the database) expecially when migrating accross two servers where you can detach the db, copy the database files accorss to the new server and reattch there...If you are worried about corruption run a DBCC CHECKDB before you start.
SQL Won't restore the database unless it has exclusive access to it...so if users are connected the restore will fail.
Where are your normal FULL backups stored if you don't have space?
Using FULL Recovery, your normal FULL backup can be the starting point of an upgrade/migration to a new server while keeping your production system online and functioning. For large databases, restoring the last FULL with NORECOVERY and then applying TLOG backups to roll it forward is the only sensible way to migrate or perform upgrades, essentially manually log shipping your database to the new server until you hit the cutover point when you shut down the app, take a final TLOG backup, and shut down the old server. Your downtime is limited to the time it takes to copy a LOG Backup between servers, restore it with RECOVERY and reconfigure the application. This might be a bit more work, but it has much less downtime than using Attach/Detach to migrate the database.

In a previuos job which involved managing quite large dbs, 100 - 500+ GBs. There was not always space for a native backup to disk. Instead they used a storage based backup solution i believe was called split mirror (i might have that wrong as this was operated by the storage team, although the DBA team did have overall responsibility for restores) which was used to take the full backups. T/LOG backup were taken regularly via native backups every 20 mins. This was a complex setup with DR sites and SAN replication used to replicate to replicate the data around and a complex non microsoft cluster setup

The manual log ship process you describe above is pretty much how much how migrations were carried out, with additonal storage being allocated just for the migration purposes...we only ever used detach/attach to change the files to the correct locations, if they got mistakenly restored to the wrong location. Which was actually important to fix for split mirror purposes. Anyway i digress..,I agree your method of migration.

I guess all i was trying to say, pretty badly maybe, is that the detach/attach did, maybe still does have its place...It must do or you would not have spent your time writing an article on how to reatach a database using new syntax...

Gethyn Elliswww.gethynellis.com

Ronzo Hall of Fame Points: 3917 More actions · Answer 3

I would have ranked this article higher if you hadn't included the "red herring" of mentioning Broker Services. I saw no mention that the CREATE DATABASE ... FOR ATTACH includes in the syntax a WITH , meaning that the Broker can be setup again as part of the attach process.

The lack of an explanation of how to detach (other than with the Mgmt. Studio) is also a problem. Since BOL is lacking in this info, maybe it is an "undocumented" procedure?

And to infer that there is no situation that could not be addressed without detach/attach ignores special circumstances. Detaching a database and moving the physical drive(s) to another machine is a case I've run into.

Same with the attaching and regen'ing the logs, there are cases this is appropriate. It does allow attaching to test environments with less disk space than production. As someone said earlier, the rules for test can be less stringent than production, because you can always get another copy from production as a "backup".

Otherwise this was a great article for highlighting the deprecation of the old detach/attach methods and discussing some of the alternatives available.

Have Fun!

Ron

Have Fun!
Ronzo

Bill Whitman SSC Rookie Points: 38 More actions · Answer 4

xp_cmdshell 'copy \\existingfilepath\dbname.mdf \ewfilepath\dbname.mdf'

go

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Here's another couple of reasons why backup/restore may be better than detach/attach:

(1) You cannot always detach the database. This may occur if, for instance, the database participates in replication.

(2) Your database may have a lot of free space in the data and/or log files. In this case, copying a backup file, which will be the same size as the data you have in your database, will be quicker than copying the data and log files, which will be the same size as the data plus the free space.

John

smurug SSC Enthusiast Points: 113 More actions · Answer 6

Hi..

If i have two mdf,ldf files path. and i modify the path from 1st path to 2nd path

Then i should be able to access only 2st path tables,sp's etc. but i am able to access both the path info...

Is it the way or some thing wrong i am doing?

this is the process i follow:

1./*

USE master;

GO

ALTER DATABASE mydb

MODIFY FILE (NAME = mydbdev, FILENAME = 'E:\SQLData\mydb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = mydblog, FILENAME = 'E:\SQLData\mydb_log.ldf');

GO

*/

2. Stop and restart the instance of SQL Server.

by this above steps i am able to change the path but i am able to see all the information from the old mdf file.

any one Please clarify my doubt

v.raj.smart Grasshopper Points: 10 More actions · Answer 7

Hi..

If i have two mdf,ldf files path. and i modify the path from 1st path to 2nd path

Then i should be able to access only 2st path tables,sp's etc. but i am able to access both the path info...

Is it the way or some thing wrong i am doing?

this is the process i follow:

1./*

USE master;

GO

ALTER DATABASE mydb

MODIFY FILE (NAME = mydbdev, FILENAME = 'E:\SQLData\mydb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = mydblog, FILENAME = 'E:\SQLData\mydb_log.ldf');

GO

*/

2. Stop and restart the instance of SQL Server.

by this above steps i am able to change the path but i am able to see all the information from the old mdf file.

any one Please clarify my doubt

v.raj.smart Grasshopper Points: 10 More actions · Answer 8

Hi..

If i have two mdf,ldf files path. and i modify the path from 1st path to 2nd path

Then i should be able to access only 2st path tables,sp's etc. but i am able to access both the path info...

Is it the way or some thing wrong i am doing?

this is the process i follow:

1.

USE master;

GO

ALTER DATABASE mydb

MODIFY FILE (NAME = mydbdev, FILENAME = 'E:\SQLData\mydb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = mydblog, FILENAME = 'E:\SQLData\mydb_log.ldf');

GO

2. Stop and restart the instance of SQL Server.

by this above steps i am able to change the path but i am able to see all the information from the old mdf file.

any one Please clarify my doubt

Jorge Segarra SSCrazy Points: 2571 More actions · Answer 9

GRE-452109 (5/27/2009)
don_goodman (5/27/2009)
It is a terrible practice to detach a database from a production server. If you had any corruption in any database file or in the server itself, you will never be able to re-attach it. I know from experience this is so.
The CORRECT way to migrate a database and its files to a new location is through a manual backup and restore.
Ensure you use INIT in the backup statement. Ensure you disconnect the users before restoring it.
In this way you avoid business exposure to corruption. If it is corrupt, it will not restore. But the database will remain online for the business to use it while you and your team figure out what to do about the corruption. Then in an orderly, non-destructive way, you can migrate the business to a new solution, whatever that is.
NEVER detach a production database unless you want to answer the business challenge with a job interview elsewhere.
I think there occaions when an deatach/attach is the best option especially when dealing with larger databases and you only have native SQL backup to use...Sometimes you don't have the neccessary disk space to take a backup and restore the database files (2x times the size of the database) expecially when migrating accross two servers where you can detach the db, copy the database files accorss to the new server and reattch there...If you are worried about corruption run a DBCC CHECKDB before you start.
SQL Won't restore the database unless it has exclusive access to it...so if users are connected the restore will fail.

Just as a word of warning for those who don't know, make sure you have enough disk space available on the drive to run DBCC Checkdb. On SQL 2005 and up the check is actually run against a snapshot that is created on the database. This snapshot does take up disk space. If it runs out of disk space it will rollback IIRC but just be aware of that caveat.

=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management

pcd_au Ten Centuries Points: 1289 More actions · Answer 10

pcd_au

Ten Centuries

Points: 1289

September 23, 2010 at 11:39 pm

#1226712

.

amarshall-568002 Right there with Babe Points: 785 More actions · Answer 11

I didn't think you could move the log file with alter... unless you used enterprise or developer version of SQL.

Amy

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

amarshall-568002 (9/24/2010)
I didn't think you could move the log file with alter... unless you used enterprise or developer version of SQL.
Amy

You can use ALTER DATABASE MODIFY FILE for log files in any edition of SQL Server including Express. Once the command has been executed, you take the database offline, move the file to the new location and then bring the database back online. This is not an Enterprise Only Feature.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 13

Jonathan Kehayias (5/27/2009)
When you attach without the log file, it is rebuilt, which can leave your database incosistent, not a good thing to do.

I don't even think you can attach a database without a transaction log if the database wasn't shut down in a clean manner, and by that I mean that there would be no need for rollback or roll forwards during startup.

So if you delete the transaction log file and the database was not shut down properly there is no chance in h*** you can attach the database again.

Or am I missing something? Not that I would ever do something like that.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 14

Nils Gustav Stråbø (9/24/2010)
Jonathan Kehayias (5/27/2009)
When you attach without the log file, it is rebuilt, which can leave your database incosistent, not a good thing to do.
I don't even think you can attach a database without a transaction log if the database wasn't shut down in a clean manner, and by that I mean that there would be no need for rollback or roll forwards during startup.
So if you delete the transaction log file and the database was not shut down properly there is no chance in h*** you can attach the database again.
Or am I missing something? Not that I would ever do something like that.

You can get it back into the server but as I said above, it won't be guaranteed to be consistent. You may have to hack around it to get it back in, but it can certainly be done. I wouldn't advocate deleting a log file ever, but people do it for some reason.

jason.devereaux Valued Member Points: 58 More actions · Answer 15

Thanks for the article! I used to be a fan of the detach/attach method of moving database files around... that is, until I brought down Service Broker for Operations Manager 2007 in the Production environment! Didn't realize it either. Only caught it because the application folks started receiving failed connection messages every second or two. Had to use the SET ENABLE_BROKER command to get it back up! Ahhh, my rookie days...

ALTER DATABASE is definitely my preference now!

Jason D.