InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 15:28:51 GMT20http://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxGuess who didn't read the article BEFORE posting comments....?Sorry Perry....Fri, 17 May 2013 23:25:29 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Perry Whittle (5/17/2013)[/b][hr][quote][b]kevaburg (5/17/2013)[/b][hr]Of course, in order to do this you Need to be using the Enterprise Version of the Software.....[/quote]On a slightly different plain to my article as this is moving a database across instances. The synchronous mirroring in standard edition will achieve the same results.[/quote]I was actually referring to use the Backup/Restore method over the Attach/Detach method. As has been pointed out to me, simply using the latter method can result in an inconsistent database because the Transaction log is missing. This means that when the instance/database starts up and recovery is performed, uncommitted Transactions cannot be rolled back. This is where the database becomes inconsistent and Problems start to occur.If the attach/detach method really must be used, then it should be as a last Resort or when the admin knows 100% that consistency can be guaranteed.Fri, 17 May 2013 23:23:36 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]kevaburg (5/17/2013)[/b][hr]Of course, in order to do this you Need to be using the Enterprise Version of the Software.....[/quote]On a slightly different plain to my article as this is moving a database across instances. The synchronous mirroring in standard edition will achieve the same results.Fri, 17 May 2013 23:12:10 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]kevaburg (5/17/2013)[/b][hr] Better to do a backup and restore.....[/quote]Hmm, no, as my article shows the best and supported way to move your data files is to use the alter database command. Doing this, the database is not detached from the server at all.Fri, 17 May 2013 23:09:31 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]karthiks.840 (2/19/2013)[/b][hr]Hi,How would you handle moving a big database(say 200GB) to another drive with minimal time on a production server??--Regards,Karthik[/quote]An option, should the SQL Server Versions be the same, is to mirror the database across to the new Server and once it is running as it should be, copy the users, Jobs, maintenance plans etc; across to the mirror from the principal. Once prepared, perform a switchover to the new machine and the move is complete with the very Minimum of downtime, measured in seconds. Users will be disconnected for the period of the switchover but that is easily planned for.Of course, in order to do this you Need to be using the Enterprise Version of the Software.....Fri, 17 May 2013 22:55:11 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]john.knight-557608 (5/17/2013)[/b][hr]Nice article Pezza.I went through a process just recently of renaming and moving a DB and used the old method of detach/attach, next time round I'll give this way a go.Good work on another fine article.Johnny[/quote]Be careful when you use this method. With a "simple" detach/attach you lose the Transaction log. Better to do a backup and restore.....Fri, 17 May 2013 22:50:47 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxNice article Pezza.I went through a process just recently of renaming and moving a DB and used the old method of detach/attach, next time round I'll give this way a go.Good work on another fine article.JohnnyFri, 17 May 2013 08:58:44 GMTjohn.knight-557608http://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxJust a thought... not sure if this would work..but is there any software that can create another copy of these db files irrespective of the handles present on it.. so that once it copies (say upto 95%), we can use the process you mentioned in the post, and copying the left over 5% shouldn't take much time.Can we do something like this?Am just curious if this can be done.[http://lockhunter.com/ : This completely unlocks and deletes the files]Is there anything which would copy the file even when there is a lock on it?--Regards,KarthikWed, 20 Feb 2013 00:16:29 GMTkarthiks.840http://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxWhatever happens you will need to release the sql server handles on the disk file(s), what capacity do you have to copy 200gb of database files across the drives? You need to factor this time into the equation.Tue, 19 Feb 2013 11:15:17 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxHi,How would you handle moving a big database(say 200GB) to another drive with minimal time on a production server??--Regards,KarthikTue, 19 Feb 2013 09:07:29 GMTkarthiks.840http://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxyou need to move the folder contents under the folder path that contains the file stream data. Check books online for more info on the alter database commandMon, 18 Feb 2013 08:04:26 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxHello, all!!! I liked the post. A lot. And I have a question regarding filestream. How do I move a filestream datafile ? Can I move only one datafile instead of all of them al once ?Thanks in advance from Argentina.Ariel.Mon, 18 Feb 2013 05:06:37 GMTabacrottohttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxanother pro alter database argument, if the dbid were to change that would affect the default database setting for logins (presuming the default was the user database)Thu, 14 Feb 2013 03:48:41 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxNo but since the database id may change history would be lost hereWed, 13 Feb 2013 16:21:45 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxpretty sure nothing in msdb is affected by a detach, it would be too time consuming to remove backup history for example.Wed, 13 Feb 2013 16:01:54 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]george sibbald (2/13/2013)[/b][hr]Jason, for an article on the benefits of alter database method and why it is the [i]preferred [/i]method for SQL2005 onwards see [url=http://www.sqlservercentral.com/articles/Administration/65896/]here[/url]I would add another reason, with detach\attach the owner of the database can be changed, using alter database that will not happen.In a nutshell any metadata about a database held in master is lost when it is detached, but not when alter database is used[/quote]Thanks for the good points...hadn't thought of metadata getting lost. I bet a few things in msdb get nuked too now that I think about it.Wed, 13 Feb 2013 15:25:34 GMTJason Shadonixhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Perry Whittle (2/13/2013)[/b][hr][quote][b]Jason Shadonix (2/13/2013)[/b][hr]Right...but the mere fact that you have to type in slightly different syntax to attach a database isn't by itself an argument to stop detatching and attaching databases. Not sure why you keep zeroing in on that. [/quote]You're the one who keeps zeroing in on syntax not me, you're missing the whole point of the article with your blind devotion to an ancient stored procedure. You dont have to detach the db or stop the sql server service to move the data files as i have seen suggested many times before on the forums.You cant just detach a mirrored or replicated db and as already mentioned or linked things like service broker, owner and backup history meta are lost.[/quote]You're missing the point of my question...I was trying to get you to tell me something besides the syntax change as a reason for using your method. You did (finally). So I'm good. Not sure why you think I'm "blindly devoted" to that stored procedure. I don't use it anymore and haven't for years. All I did was point out that the fact that we can't use it anymore was NOT an argument for changing methodology. The other points you later made are good points though, so I did get something out of it.Wed, 13 Feb 2013 15:22:14 GMTJason Shadonixhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Jason Shadonix (2/13/2013)[/b][hr]Right...but the mere fact that you have to type in slightly different syntax to attach a database isn't by itself an argument to stop detatching and attaching databases. Not sure why you keep zeroing in on that. [/quote]You're the one who keeps zeroing in on syntax not me, you're missing the whole point of the article with your blind devotion to an ancient stored procedure. You dont have to detach the db or stop the sql server service to move the data files as i have seen suggested many times before on the forums.You cant just detach a mirrored or replicated db and as already mentioned or linked things like service broker, owner and backup history meta are lost.Wed, 13 Feb 2013 15:13:39 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxJason, for an article on the benefits of alter database method and why it is the [i]preferred [/i]method for SQL2005 onwards see [url=http://www.sqlservercentral.com/articles/Administration/65896/]here[/url]I would add another reason, with detach\attach the owner of the database can be changed, using alter database that will not happen.In a nutshell any metadata about a database held in master is lost when it is detached, but not when alter database is usedWed, 13 Feb 2013 14:11:41 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Perry Whittle (2/13/2013)[/b][hr]I'm not being snarky that comment is a subtle hint.The old[code="sql"]EXEC sp_attach_db @dbname = N'AdventureWorks2012', @filename1 = N'C:\Somepath\AdventureWorks2012_Data.mdf', @filename2 = N'C:\Somepath\AdventureWorks2012_log.ldf';[/code]The new[code="sql"]CREATE DATABASE database_name ON PRIMARY (name=mydb_data, filename='d:\somepath\mydbdata.mdf')LOG ON (name=mydb_log, filename='e:\somepath\mydblog.ldf')FOR ATTACH[/code][/quote]Right...but the mere fact that you have to type in slightly different syntax to attach a database isn't by itself an argument to stop detatching and attaching databases. Not sure why you keep zeroing in on that. (for example: sp_adduser is depricated, and you now have to use CREATE USER...FOR LOGIN. But you don't go around telling people they shouldn't create users anymore just because sp_adduser is depricated.)All I was asking what the advantage to the method described in the article was over the detatch/attach method. You (sorta) listed some when you listed some (partially incorrect) cases where you couldn't detatch a database but could take it offline. That's the kind of thing I was asking.Wed, 13 Feb 2013 14:10:53 GMTJason Shadonixhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxI'm not being snarky that comment is a subtle hint.The old[code="sql"]EXEC sp_attach_db @dbname = N'AdventureWorks2012', @filename1 = N'C:\Somepath\AdventureWorks2012_Data.mdf', @filename2 = N'C:\Somepath\AdventureWorks2012_log.ldf';[/code]The new[code="sql"]CREATE DATABASE database_name ON PRIMARY (name=mydb_data, filename='d:\somepath\mydbdata.mdf')LOG ON (name=mydb_log, filename='e:\somepath\mydblog.ldf')FOR ATTACH[/code]Wed, 13 Feb 2013 14:01:03 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Perry Whittle (2/13/2013)[/b][hr][quote][b]Jason Shadonix (2/13/2013)[/b][hr]But that's just a minor syntax change in the process. [/Quote]You're joking right? Create and alter database have been enhanced extensively to include extra commands (file management, mirroring, etc). Previously, under 2000 detach\attach was the only way to move files around.[/Quote]No, I was not joking. The fact that you have to type out "CREATE DATABASE ... FOR ATTACH" instead of sp_attach_db to attach a database is a minor syntax change which alone isn't a reason to scrap the Detatch/Attach approach. [quote][b]Jason Shadonix (2/13/2013)[/b][hr]Between the two methods (which both accomplish the same thing as far as I can tell) - both will have essentially the same amount of down time as near as I can tell.What disadvantages are there in dropping a database from the system catalogs and adding them again for a detatch/attach? All of your security/permisisons should be unchanges as long as you are keeping the database on the same serverMirrored, snapshotted and replicated databases spring to mind here, they cant just be detached.[/quote]Ah ha! Finally something you mention that actually explains some cases where you would have an advantage by not detaching/attaching a database. That's all I was looking for. So if you are not dealing with a replicated database, it probably doesn't matter that much I guess. (But I don't think you can offline a database participating in mirroring or with snapshots can you?)[quote][b]Jason Shadonix (2/13/2013)[/b][hr]Also, when you say "it's the supported way of moving database files"... Are you saying detatch/attach is not supported? I don't see anything to indicate that in MSDN with some quck searches: http://msdn.microsoft.com/en-us/library/ms190794.aspxThis below from [url=http://msdn.microsoft.com/en-us/library/ms187858.aspx][b][u]MS books online[/u][/b][/url][quote][b]books online[/b][hr] However, we recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach. For more information, see Move User Databases.[/quote]You may want to read [url=http://weblogs.sqlteam.com/dang/archive/2009/01/18/Dont-Use-sp_attach_db.aspx][b][u]this[/u][/b][/url] and [url=http://msdn.microsoft.com/en-us/library/ms345483.aspx][b][u]this[/u][/b][/url].Phone MS support and tell them you're having trouble attaching a db using SP_ATTACH_DB, i guarantee I know their response ;-)[/quote]I never said I was still using sp_attach_db instead of CREATE DATABASE ... FOR ATTACH syntax. I was simply trying to get at what advantages the described method has over the Detatch/Attach method. No need to get snarky about it...I'm simply trying to learn what the differences are, not trying to question or be negative about your method.Wed, 13 Feb 2013 13:31:18 GMTJason Shadonixhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Jason Shadonix (2/13/2013)[/b][hr]But that's just a minor syntax change in the process. [/Quote]You're joking right? Create and alter database have been enhanced extensively to include extra commands (file management, mirroring, etc). Previously, under 2000 detach\attach was the only way to move files around.[quote][b]Jason Shadonix (2/13/2013)[/b][hr]Between the two methods (which both accomplish the same thing as far as I can tell) - both will have essentially the same amount of down time as near as I can tell.What disadvantages are there in dropping a database from the system catalogs and adding them again for a detatch/attach? All of your security/permisisons should be unchanges as long as you are keeping the database on the same server.[/quote]Mirrored, snapshotted and replicated databases spring to mind here, they cant just be detached.[quote][b]Jason Shadonix (2/13/2013)[/b][hr]Also, when you say "it's the supported way of moving database files"... Are you saying detatch/attach is not supported? I don't see anything to indicate that in MSDN with some quck searches: http://msdn.microsoft.com/en-us/library/ms190794.aspx[/quote]This below from [url=http://msdn.microsoft.com/en-us/library/ms187858.aspx][b][u]MS books online[/u][/b][/url][quote][b]books online[/b][hr] However, we recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach. For more information, see Move User Databases.[/quote]You may want to read [url=http://weblogs.sqlteam.com/dang/archive/2009/01/18/Dont-Use-sp_attach_db.aspx][b][u]this[/u][/b][/url] and [url=http://msdn.microsoft.com/en-us/library/ms345483.aspx][b][u]this[/u][/b][/url].Phone MS support and tell them you're having trouble attaching a db using SP_ATTACH_DB, i guarantee I know their response ;-)Wed, 13 Feb 2013 12:56:55 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote]It's the supported way of moving database files and doesn't involve dropping the database from the system catalogs.SP_ATTACH_DB is deprecated and will be removed in a future version of SQL Server, you should use CREATE DATABASE ... FOR ATTACH to attach database files to an instance of SQL Server. The ALTER DATABASE command is used to alter database configurations.[/quote]I understand the sp_attach_db stored procedure is deprecated and replaced with the CREATE DATABASE ... FOR ATTACH when it comes to attaching databases. But that's just a minor syntax change in the process. That alone isn't a reason to abandon the attach/detatch method of moving/renaming files in your database. I'm wondering if there are other reasons that I'm missing here is why I asked the question. Between the two methods (which both accomplish the same thing as far as I can tell) - both will have essentially the same amount of down time as near as I can tell.What disadvantages are there in dropping a database from the system catalogs and adding them again for a detatch/attach? All of your security/permisisons should be unchanges as long as you are keeping the database on the same server.Also, when you say "it's the supported way of moving database files"... Are you saying detatch/attach is not supported? I don't see anything to indicate that in MSDN with some quck searches: http://msdn.microsoft.com/en-us/library/ms190794.aspxI'm not saying your method is "bad" by any means. I just don't see why it's markedly better than the attach/detatch method, and am wondering if I am missing something.Wed, 13 Feb 2013 09:25:17 GMTJason Shadonixhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Jason Shadonix (2/12/2013)[/b][hr]Question - What advantages does this method have over the detatch/reattach method of moving files around?[/quote]It's the supported way of moving database files and doesn't involve dropping the database from the system catalogs.SP_ATTACH_DB is deprecated and will be removed in a future version of SQL Server, you should use CREATE DATABASE ... FOR ATTACH to attach database files to an instance of SQL Server. The ALTER DATABASE command is used to alter database configurations.[quote][b]Jason Shadonix (2/12/2013)[/b][hr]I've always just detatched/reattached whenever I move a database, and the time consuming part is always waiting for the file to transfer, not the detatch/reattach process, which has always been pretty fast for me. It seems like there are more ways to make a mistake using the method described in this article. Or am I missing something?[/quote]Yes, often the file copy process can take some time. If you're smart you'll copy the files first then and only then remove the originals once the database comes online successfully using the new paths.As long as you pay attention to what you're typing as part of the ALTER DATABASE ... MODIFY FILE command there's no reason why it should fail.Wed, 13 Feb 2013 09:07:25 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxQuestion - What advantages does this method have over the detatch/reattach method of moving files around?I've always just detatched/reattached whenever I move a database, and the time consuming part is always waiting for the file to transfer, not the detatch/reattach process, which has always been pretty fast for me. It seems like there are more ways to make a mistake using the method described in this article. Or am I missing something?Tue, 12 Feb 2013 13:12:21 GMTJason Shadonixhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]kevaburg (2/12/2013)[/b][hr]Although this is a very good article with regards to moving user databases[/quote]That is exactly what it is targeted at![quote][b]kevaburg (2/12/2013)[/b][hr]what happens if you want to move the master database somewhere else? [/quote]That warrants a whole separate article, the MS kb is at [url=http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx][u][b]this link[/b][/u][/url]Note that to change the location of the resource database still requires you to use the ALTER DATABASE ... MODIFY FILE command detailed in my articleTue, 12 Feb 2013 08:47:15 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxAlthough this is a very good article with regards to moving user databases, what happens if you want to move the master database somewhere else? This is something I have had to do often due to badly installed instances and it would certainly help the admins that realise perhaps the database files don't really belong alongside the operating system!Tue, 12 Feb 2013 08:02:03 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]sismb8 (2/12/2013)[/b][hr]Nice article. Thanks! Introduction of the move file technique has been a welcome advance in SQL Server. In the bad old days (SQLServer 6.5/7.0) I used to add a file to a filegroup on the new disk location, set the quota on the other files to zero, let the DB daemon siphon all the data to the new file and then drop the old ones when empty. Clunky but it worked. The move is miles better.[/quote]that is not what move file does!!You're referring to emptying data from a file, correct?Tue, 12 Feb 2013 03:40:18 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Yulingxu (2/11/2013)[/b][hr]Thanks Perry for this article.I see MS SQL Server is more like Oracle Database to handle those physical files.ThanksYuling[/quote]As a SQL Server\Oracle DBA i disagree. Oracle has no concept of filegroups and from what i have learned they are totally differentTue, 12 Feb 2013 03:39:16 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxNice article. Thanks! Introduction of the move file technique has been a welcome advance in SQL Server. In the bad old days (SQLServer 6.5/7.0) I used to add a file to a filegroup on the new disk location, set the quota on the other files to zero, let the DB daemon siphon all the data to the new file and then drop the old ones when empty. Clunky but it worked. The move is miles better.Tue, 12 Feb 2013 02:59:06 GMTsismb8http://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxThanks Perry for this article.I see MS SQL Server is more like Oracle Database to handle those physical files.ThanksYulingMon, 11 Feb 2013 16:02:12 GMTYulingxuhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Willem Gossink (2/11/2013)[/b][hr]Perhaps I did not explain my point clearly. Your article suggests using the "ALTER DATABASE ... " T-SQL command to bring the database back online. This is, of course, fine. However, the following screenshot is not the result of a T-SQL command. The screenshot is the result of the 'Bring Online' task in the GUI.[/quote]ok, i see. Thanks for pointing that outMon, 11 Feb 2013 04:34:15 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxOne of the most common errors we find on moving files is security. Admins quite often forget that SQL application service will require permissions to the new location. On start-up / bring on-line the error occurs and panic follows.... Yet all we need to ensure is the relevant permissions are in place at the alternate location.Mon, 11 Feb 2013 03:36:58 GMTDegradablehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxPerhaps I did not explain my point clearly. Your article suggests using the "ALTER DATABASE ... " T-SQL command to bring the database back online. This is, of course, fine. However, the following screenshot is not the result of a T-SQL command. The screenshot is the result of the 'Bring Online' task in the GUI.Mon, 11 Feb 2013 03:27:02 GMTWillem Gossinkhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]SQLDBA360 (2/11/2013)[/b][hr]Nice artical, I must admit i'm still a fan of detatching the databases to move files[/quote]No longer necessary to detach the database. The ALTER DATABASE ... MODIFY FILE command is the preferred way to perform these actions.[quote][b]SQLDBA360 (2/11/2013)[/b][hr] (plus i'm prone to the odd typ0). [/quote]Type slower and always check your work ;-)[quote][b]SQLDBA360 (2/11/2013)[/b][hr]One other thing, prob obvious to most readers, is that you can't do this method if your moiving the master or resource databases MS has an artical on this at [url]http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx[/url] this one is for 2005 but the other versions are available using the other version drop down.[/quote]In the article I make references to user databases only ;-)However, i appreciate your point and could have made it clearer.Note also that for the resource database you do still need to issue an [code="SQL"]ALTER DATABASE ... MODIFY FILE[/code]Mon, 11 Feb 2013 02:38:36 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Willem Gossink (2/11/2013)[/b][hr]Good article! One possible source of confusion: the screenshots showing 'completed with errors' and 'success' will not show after issuing the command "ALTER DATABASE [yourDB] SET ONLINE" but after performing 'Tasks - Bring Online' in the GUI.[/quote]Thanks for your feedback.The first 2 screenshots show a typical scenario where an operation to online the database fails, the last shows the dialog you will see when the online operation succeeds. The text does indicate this.Mon, 11 Feb 2013 02:32:22 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxNice artical, I must admit i'm still a fan of detatching the databases to move files as I tend to only do this on maintenance windows so the amount of time it takes me isn't an issue (plus i'm prone to the odd typ0). One other thing, prob obvious to most readers, is that you can't do this method if your moiving the master or resource databases MS has an artical on this at [url]http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx[/url] this one is for 2005 but the other versions are available using the other version drop down.Mon, 11 Feb 2013 02:12:28 GMTSQLDBA360http://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspxGood article! One possible source of confusion: the screenshots showing 'completed with errors' and 'success' will not show after issuing the command "ALTER DATABASE [yourDB] SET ONLINE" but after performing 'Tasks - Bring Online' in the GUI.Mon, 11 Feb 2013 02:06:07 GMTWillem Gossinkhttp://www.sqlservercentral.com/Forums/Topic1418218-1452-1.aspx[quote][b]Pradeep Hebballi (2/11/2013)[/b][hr]Hi Perry, the article was really helpful. Thanks![/quote]Thank you, i'm glad you found it useful\helpful[quote][b]Pradeep Hebballi (2/11/2013)[/b][hr]You mentioned that SP_ATTACH_DB is deprecated. Does that mean that we cannot use DETACH and ATTACH database methods to change/rename the database files in SQL Server 2008?ThanksPradeep[/quote]The whole point is you don't need to sp_attach_db, if all you're doing is renaming or moving database files use my article. To attach a database from raw files use[code="SQL"]CREATE DATABASE database_name FOR ATTACH[/code]Mon, 11 Feb 2013 01:15:20 GMTPerry Whittle