Changing Extensions

Question

Post reply

Changing Extensions

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
January 20, 2018 at 1:46 pm

#100966

Comments posted to this topic are about the item Changing Extensions

Viewing 15 posts - 1 through 15 (of 28 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

The most correct answer is actually missing a step.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christoph Muthmann Ten Centuries Points: 1127 More actions · Answer 2

Setting the database offline as the first step and bringing it back online at the end would be a good idea!

Have a nice day,Christoph

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

Yes, I chose option 4 for that reason. It's correct, and although it entails more steps, it's less disruptive since you don't have to take the database offline.

John

ildjarn.is.dead Ten Centuries Points: 1159 More actions · Answer 4

The question isn't "What is the easiest way" but "what should I do". And adding an extra file and migrating the data works.

ferasdeeb SSC Veteran Points: 281 More actions · Answer 5

You can also argue that the first option is also correct if you take the database Offline.
Steps would be:
1- Take the DB Offline
2- Rename the file in the OS with SQL Server stopped. Use an UPDATE statement to alter sys.database_files.physical_name.
3- Bring the DB back Online.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

ildjarn.is.dead - Monday, January 22, 2018 3:47 AM
The question isn't "What is the easiest way" but "what should I do". And adding an extra file and migrating the data works.

Try your solution with a 2TB database file. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

ferasdeeb - Monday, January 22, 2018 5:20 AM
You can also argue that the first option is also correct if you take the database Offline.
Steps would be:
1- Take the DB Offline
2- Rename the file in the OS with SQL Server stopped. Use an UPDATE statement to alter sys.database_files.physical_name.
3- Bring the DB back Online.

I don't think you can update the system catalogs directly - not since 2000, anyway. That's why I discounted that option.

John

ildjarn.is.dead Ten Centuries Points: 1159 More actions · Answer 8

Jeff Moden - Monday, January 22, 2018 5:44 AM
ildjarn.is.dead - Monday, January 22, 2018 3:47 AM
The question isn't "What is the easiest way" but "what should I do". And adding an extra file and migrating the data works.
Try your solution with a 2TB database file. 😉

Does that make the answer less correct? Again: Nowhere in the question is 'easiest' mentioned. Answer 4 is a valid answer, although most of the times not the smartest. But answer 3 (the 'correct' answer) is definitely incomplete (you need to offline the database before renaming the datafile), so it's actually less correct than answer #4.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

ildjarn.is.dead - Monday, January 22, 2018 6:48 AM
Jeff Moden - Monday, January 22, 2018 5:44 AM
ildjarn.is.dead - Monday, January 22, 2018 3:47 AM
The question isn't "What is the easiest way" but "what should I do". And adding an extra file and migrating the data works.
Try your solution with a 2TB database file. 😉
Does that make the answer less correct? Again: Nowhere in the question is 'easiest' mentioned. Answer 4 is a valid answer, although most of the times not the smartest. But answer 3 (the 'correct' answer) is definitely incomplete (you need to offline the database before renaming the datafile), so it's actually less correct than answer #4.

Possibly. What are you using to do the migration of the data to the new file?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

George Vobr SSCrazy Eights Points: 9915 More actions · Answer 10

ildjarn.is.dead - Monday, January 22, 2018 6:48 AM
Does that make the answer less correct? Again: Nowhere in the question is 'easiest' mentioned. Answer 4 is a valid answer, although most of the times not the smartest. But answer 3 (the 'correct' answer) is definitely incomplete (you need to offline the database before renaming the datafile), so it's actually less correct than answer #4.

+1
I fully agree that answer #4 is also correct. Sometimes may be the use of migration more practical, see e.g.in "Move Database Files Without Taking the Database Offline".

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

George Vobr - Monday, January 22, 2018 7:59 AM
ildjarn.is.dead - Monday, January 22, 2018 6:48 AM
Does that make the answer less correct? Again: Nowhere in the question is 'easiest' mentioned. Answer 4 is a valid answer, although most of the times not the smartest. But answer 3 (the 'correct' answer) is definitely incomplete (you need to offline the database before renaming the datafile), so it's actually less correct than answer #4.
+1
I fully agree that answer #4 is also correct. Sometimes may be the use of migration more practical, see e.g.in "Move Database Files Without Taking the Database Offline".

I'll fully agree that "It Depends" on the method used to do the migration. If you use DBCC SHRINKFILE with the "empty" option to do the migration, then you're in deep Kimchie when it's done, especially if the file is of any size that could be considered "large".
.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 12

Christoph Muthmann - Monday, January 22, 2018 2:49 AM
Setting the database offline as the first step and bringing it back online at the end would be a good idea!

Although it is true that you need to set the database offline at certain point to remove the lock SQL Server holds on the database files, the steps to minimise downtime are
- ALTER DATABASE MODIFY FILE to change the file name
- SET DATABASE OFFLINE --> this is actually missing in the "correct answer"
- Change the actual file --> only possible if the db is offline
- SET DATABASE ONLINE

Obviously you can set offline first, but you are adding down time, it possibly is negligible for a single file, specially if you have everything scripted out, but I'd say its better practice have the database offline as less as possible.

Cheers.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 13

I would argue that this sentence, "What should I do to rename this file?", means adding a new file is incorrect.

I didn't add the verbiage for offline/online, or detach/attach as it makes a cumbersome set of answers here.

You cannot update the system tables anymore.

Bobby Russell SSCrazy Points: 2801 More actions · Answer 14

Item 4 was my choice, which IS correct whether you think so or not, and the best way to perform this operation. Why would you consider this incorrect? It is the most correct of your options available.