Move DB

Question

Move DB

water490

SSC Eights!

Points: 918
More actions
September 8, 2023 at 6:09 pm

#4282625

Hi everyone
My SSD is starting to fill up so I am looking at buying a new drive and moving the DB instance to that new drive. Setting up SS2019, SSIS, and WinSCP was not easy to do so ideally I do not want to move/touch/reinstall them. I just want to move the actual DB I am using to the new drive. How can I do this? I have never done this before so I need help from experts. Any help you can give is much appreciated.
Thank you

Viewing 14 posts - 1 through 13 (of 13 total)

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

frederico_fonseca SSCoach Points: 16088 More actions · Answer 1

and what have you googleed and read on the documentation about moving datafiles? there are plenty of examples and documentation is rather clear on what to do.

water490 SSC Eights! Points: 918 More actions · Answer 2

frederico_fonseca wrote:

and what have you googleed and read on the documentation about moving datafiles? there are plenty of examples and documentation is rather clear on what to do.

Internet has a lot of garbage advice so I try to stay away from it. If there is a reliable resource that has answers to my question then I am totally open to it.

frederico_fonseca SSCoach Points: 16088 More actions · Answer 3

well.. on that case you should not be trusting this site either - garbage can be anywhere.

your solution is to turn, which it seems you haven't so far, to microsoft documentation - which happens to be one of the links google returns to you on this subject - unless you also consider that as garbage.

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 4

@Frederico,

As you know, MS doesn't make it easy to find the correct documentation for doing certain things. Do you have a link that might help the OP out?

--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)

Jo Pattyn SSC-Dedicated Points: 32253 More actions · Answer 5

Link https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-database-files?view=sql-server-ver16

Jonathan AC Roberts SSCoach Points: 18302 More actions · Answer 6

If you are only moving the application databases (not the system databases). You can just detach the databases, move the database files to a different location then just Attach to the files in the new location.

Make sure you backup the database before performing this type of operation.

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 7

Jonathan AC Roberts wrote:

If you are only moving the application databases (not the system databases). You can just detach the databases, move the database files to a different location then just Attach to the files in the new location.
Make sure you backup the database before performing this type of operation.

Perhaps and perhaps not. If you've done it (encryption) correctly, you probably won't be able to access anything that's been encrypted by doing that. You also have to cover system logins, etc.

--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)

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 8

Jo Pattyn wrote:

Link https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-database-files?view=sql-server-ver16

I've found out the hard way in my early days that "Moving the Files" <> "Successfully moving the database".

--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)

Jonathan AC Roberts SSCoach Points: 18302 More actions · Answer 9

Jeff Moden wrote:

Jonathan AC Roberts wrote:
If you are only moving the application databases (not the system databases). You can just detach the databases, move the database files to a different location then just Attach to the files in the new location.
Make sure you backup the database before performing this type of operation.
Perhaps and perhaps not. If you've done it (encryption) correctly, you probably won't be able to access anything that's been encrypted by doing that. You also have to cover system logins, etc.

Yes, you'll have to give SQL Server permissions to the new file locations. But you can usually work that out as you go along.

I assumed that the database was not encrypted (it probably isn't). But if it is encrypted and just needs it to be moved to a different drive on the same server then it still should be a case of just detaching the database, moving the files then attaching them. If it is moving the database to a different server then obviously the certificate and private key would need to be backed up and restored on the new server.

Jeffrey Williams SSC Guru Points: 90279 More actions · Answer 10

I find it to be much easier t0 update the file locations directly - take the database offline - move the files and then bring the database online. Detaching the database often resets permissions and it sometimes doesn't get reset appropriately when moved - causing further issues when trying to attach the files.

When moving to a new drive - make sure the destination folder(s) have the same permissions set as the source folders. One way to do that is to use Robocopy - which can be configured to copy security as well as the files.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jonathan AC Roberts SSCoach Points: 18302 More actions · Answer 11

Jeffrey Williams wrote:

I find it to be much easier t0 update the file locations directly - take the database offline - move the files and then bring the database online. Detaching the database often resets permissions and it sometimes doesn't get reset appropriately when moved - causing further issues when trying to attach the files.
When moving to a new drive - make sure the destination folder(s) have the same permissions set as the source folders. One way to do that is to use Robocopy - which can be configured to copy security as well as the files.

Yes maybe, also make sure you move the files and don't just copy them leaving the old ones where they were so you are sure that it is using the new file locations.

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 12

Jonathan AC Roberts wrote:

Jeff Moden wrote:
Jonathan AC Roberts wrote:
If you are only moving the application databases (not the system databases). You can just detach the databases, move the database files to a different location then just Attach to the files in the new location.
Make sure you backup the database before performing this type of operation.
Perhaps and perhaps not. If you've done it (encryption) correctly, you probably won't be able to access anything that's been encrypted by doing that. You also have to cover system logins, etc.
Yes, you'll have to give SQL Server permissions to the new file locations. But you can usually work that out as you go along.
I assumed that the database was not encrypted (it probably isn't). But if it is encrypted and just needs it to be moved to a different drive on the same server then it still should be a case of just detaching the database, moving the files then attaching them. If it is moving the database to a different server then obviously the certificate and private key would need to be backed up and restored on the new server.

I'm also talking about application and AD logins if things have been moved to a new server.

--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)

Jonathan AC Roberts SSCoach Points: 18302 More actions · Answer 13

Jeff Moden wrote:

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Jonathan AC Roberts wrote:
If you are only moving the application databases (not the system databases). You can just detach the databases, move the database files to a different location then just Attach to the files in the new location.
Make sure you backup the database before performing this type of operation.
Perhaps and perhaps not. If you've done it (encryption) correctly, you probably won't be able to access anything that's been encrypted by doing that. You also have to cover system logins, etc.
Yes, you'll have to give SQL Server permissions to the new file locations. But you can usually work that out as you go along.
I assumed that the database was not encrypted (it probably isn't). But if it is encrypted and just needs it to be moved to a different drive on the same server then it still should be a case of just detaching the database, moving the files then attaching them. If it is moving the database to a different server then obviously the certificate and private key would need to be backed up and restored on the new server.
I'm also talking about application and AD logins if things have been moved to a new server.

I read the question as they wanted to move the database for their application not the entire instance to a new drive.