February 10, 2013 at 11:15 pm
Comments posted to this topic are about the item Moving Database Files in SQL Server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 11, 2013 at 12:32 am
Hi Perry, the article was really helpful. Thanks!
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?
Thanks
Pradeep
February 11, 2013 at 1:15 am
Pradeep Hebballi (2/11/2013)
Hi Perry, the article was really helpful. Thanks!
Thank you, i'm glad you found it useful\helpful
Pradeep Hebballi (2/11/2013)
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?Thanks
Pradeep
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
CREATE DATABASE database_name FOR ATTACH
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 11, 2013 at 2:06 am
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.
February 11, 2013 at 2:12 am
Nice 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 http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx this one is for 2005 but the other versions are available using the other version drop down.
February 11, 2013 at 2:32 am
Willem Gossink (2/11/2013)
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.
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.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 11, 2013 at 2:38 am
SQLDBA360 (2/11/2013)
Nice artical, I must admit i'm still a fan of detatching the databases to move files
No longer necessary to detach the database. The ALTER DATABASE ... MODIFY FILE command is the preferred way to perform these actions.
SQLDBA360 (2/11/2013)
(plus i'm prone to the odd typ0).
Type slower and always check your work 😉
SQLDBA360 (2/11/2013)
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 http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx this one is for 2005 but the other versions are available using the other version drop down.
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
ALTER DATABASE ... MODIFY FILE
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 11, 2013 at 3:27 am
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.
February 11, 2013 at 3:36 am
One 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.
February 11, 2013 at 4:34 am
Willem Gossink (2/11/2013)
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.
ok, i see. Thanks for pointing that out
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 11, 2013 at 4:02 pm
Thanks Perry for this article.
I see MS SQL Server is more like Oracle Database to handle those physical files.
Thanks
Yuling
Yuling luke XU
February 12, 2013 at 2:59 am
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.
February 12, 2013 at 3:39 am
Yulingxu (2/11/2013)
Thanks Perry for this article.I see MS SQL Server is more like Oracle Database to handle those physical files.
Thanks
Yuling
As a SQL Server\Oracle DBA i disagree. Oracle has no concept of filegroups and from what i have learned they are totally different
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2013 at 3:40 am
sismb8 (2/12/2013)
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.
that is not what move file does!!
You're referring to emptying data from a file, correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2013 at 8:02 am
Although 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!
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply