Sql-server – How to change DB file location with Replication

sql serversql-server-2008

I need to move few DB files to another location on same server due to low disk space. Replication is present with that DB and this scenario is on subscriber server. Is there any way to do this without doing reconfiguration?

Best Answer

Here's how I'd do it:

Schedule some time where the database can be down for the amount of time to move the files
Execute a series of 'alter database [foo] modify file (name = 'foo_1', filename = 'new location here') statements
alter database [foo] set offline (you'll have to kill any active spids in the db or wait for them to finish their business)
move your files
alter database [foo] set online

If you want to be really clean about this, you can stop the distribution agent while the database is offline and restart it when it's back online. But that's not necessary. Just so you know, I've also done this with a distribution database and would imagine that it would work fine with a publisher, too.

Related Solutions

Sql-server – How to change the location of the data files in SQL Server 2000

If you can plan some downtime, I would unattach the database from the server, move the physical files and then reattach the database.

Right click on the database --> all tasks --> detach database.
Move files
Right click on 'Databases' --> all tasks --> attach database.

The other option you have is to add another data file on the b: drive (right click on database --> properties -- > data files). Once you do that, you can move some of your tables over there. Easiest way to do that is to do 'select into' on the second drive, then do a rename of the first and second table.

Sql-server – How to move data from Primary Data File to other data file without destroying replication

SSMS is creating a copy of the table, populating it, dropping the original. hence the error.

I'd create a new filegroup, rather then adding files to the primary filegroup because of how the files are used within a file group

You can move a table "in-situ" to a new filegroup by rebuilding the clustered index using CREATE INDEX .. DROP_EXISTING

CREATE NONCLUSTERED INDEX PK_Mytable
    ON dbo.Mytable(PKColumn)
    WITH (DROP_EXISTING = ON)
    ON NewFileGroup

Best Answer

Related Solutions

Sql-server – How to change the location of the data files in SQL Server 2000

Sql-server – How to move data from Primary Data File to other data file without destroying replication

Related Question