Moving Database files from one location to another location

Question

Post reply

Moving Database files from one location to another location

deepak.a

SSCertifiable

Points: 5330
More actions
November 3, 2011 at 1:43 am

#259445

Hi All,
what are the best pratices for Moving Database files from one location to another location?
Thanks & Regards -Deepak
November 3, 2011 at 2:19 am

This was removed by the editor as SPAM
November 3, 2011 at 3:11 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

deepak.a SSCertifiable Points: 5330 More actions · Answer 1

Hi Stewart,

Thanks for your quick reply , i have few questions here ,

> If the database is invloved replication (in my case it is transactional replication and transactional replication with updatable subscription)

> Is there any impact on ownership changes by doing this

> What are the considerations or remarks for moving DB files from one location to another location ?

Thanks & Regards - Deepak

crazy4sql SSCoach Points: 19590 More actions · Answer 2

what are the best pratices for Moving Database files from one location to another location?

Points to be considered :-

1) Must have enough space at new location for files to grow in future.

2) Must have proper downtime acceptable by all party.

3) Detach and attach is best method for this.

4) Dont rely on cut/paste. Better use copy/paste for backout purpose.

5) As you not changing anything at object level, so as per my understanding it will not impact your existing replication.

----------
Ashish

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

crazy4sql (11/3/2011)
what are the best pratices for Moving Database files from one location to another location?
Points to be considered :-
1) Must have enough space at new location for files to grow in future.
2) Must have proper downtime acceptable by all party.
3) Detach and attach is best method for this.
4) Dont rely on cut/paste. Better use copy/paste for backout purpose.
5) As you not changing anything at object level, so as per my understanding it will not impact your existing replication.

I agree with the above points except #3

I recommend ALTER DATABASE method instead of DETACH/ATTACH.

M&M SSC-Insane Points: 21700 More actions · Answer 4

If replication is involved, do not use detach\attach method. You would have to drop replication entirely in order to detach whereas by using 'ALTER DATABASE MODIFY FILE' option you don't have to do it.

You could try this yourself on a test server with replication configured. It is a lot easier using 'ALTER DATABASE MODIFY FILE' option.

M&M

deepak.a SSCertifiable Points: 5330 More actions · Answer 5

Hi All,

Thanks for your valuable inputs

Regards - Deepak

deepak.a SSCertifiable Points: 5330 More actions · Answer 6

Suresh B. (11/3/2011)
crazy4sql (11/3/2011)
what are the best pratices for Moving Database files from one location to another location?
Points to be considered :-
1) Must have enough space at new location for files to grow in future.
2) Must have proper downtime acceptable by all party.
3) Detach and attach is best method for this.
4) Dont rely on cut/paste. Better use copy/paste for backout purpose.
5) As you not changing anything at object level, so as per my understanding it will not impact your existing replication.
I agree with the above points except #3
I recommend ALTER DATABASE method instead of DETACH/ATTACH.

Hi suresh ,

Is There any specific reason to avoid DETACH/ATTACH method ?

Regards - Deepak

Suresh B. SSC-Insane Points: 22986 More actions · Answer 7

Yes. There are reasons to avoid DETACH/ATTACH.

Mohammed Moinudheen has already mentioned a good reason.

If replication is involved, do not use detach\attach method. You would have to drop replication entirely in order to detach whereas by using 'ALTER DATABASE MODIFY FILE' option you don't have to do it.
You could try this yourself on a test server with replication configured. It is a lot easier using 'ALTER DATABASE MODIFY FILE' option.
Mohammed Moinudheen

When you detach a database, it's entry will be removed from sys.databases. When you reattach, a new entry will be made. So, it may get a new database id.

When you take the database OFFLINE/ONLINE using ALTER DATABASE, sys.database entry will not change.

Another reason is, taking OFFLINE/ONLINE is much easier and simpler.

crazy4sql SSCoach Points: 19590 More actions · Answer 8

I also agree with alter database recommendation and we should be aware that detach/attach is going to be depreciated in future SQL Versions. So be habitual to alter database 😀

Just one more comment on last post :-

When you detach a database, it's entry will be removed from sys.databases. When you reattach, a new entry will be made. So, it may get a new database id.

you will not get the different dbid for your database unless you do not create any new database in between of your detach and attach activity.

----------
Ashish

deepak.a SSCertifiable Points: 5330 More actions · Answer 9

Suresh B. (11/3/2011)
Yes. There are reasons to avoid DETACH/ATTACH.
Mohammed Moinudheen has already mentioned a good reason.
If replication is involved, do not use detach\attach method. You would have to drop replication entirely in order to detach whereas by using 'ALTER DATABASE MODIFY FILE' option you don't have to do it.
You could try this yourself on a test server with replication configured. It is a lot easier using 'ALTER DATABASE MODIFY FILE' option.
Mohammed Moinudheen
When you detach a database, it's entry will be removed from sys.databases. When you reattach, a new entry will be made. So, it may get a new database id.
When you take the database OFFLINE/ONLINE using ALTER DATABASE, sys.database entry will not change.
Another reason is, taking OFFLINE/ONLINE is much easier and simpler.

Thanks suresh for your explanation.

Regrads - Deepak