Removing an MSSQL file that was accidently or incorrectly added to a database

Recently, I have had a number of occasions where a new file was accidently added to the SQL Server database; for example, to the wrong file group.

The problem is slightly compounded if there is Transaction Logging in place.

There are number of steps to follow to correct the error

1 – Force the file to be emptied of any data

USE [InsertDatabaseNameHere]
GO
DBCC SHRINKFILE(‘InsertLogicalFileNameHere’, EMPTYFILE);

2 – Remove the file from the database

USE [master]
GO
ALTER DATABASE InsertDatabaseNameHere REMOVE FILE InsertLogicalFileNameHere;

3- If there is Transaction Logging or Log Shipping for this database, run an extra log backup. One cannot remove a file and then create it again within the same transaction log.

4- Add the file again with the correct settings. Change the values below to suit your configuration,

USE [master]
GO

ALTER DATABASE [InsertDatabaseNameHere]
ADD FILE
(
    NAME = N’InsertLogicalFileNameHere’,
    FILENAME = N’Z:\InsertDirectoryPathHere\InsertPhysicalFileNameHere.ndf’,
    SIZE = 1024MB , 
    MAXSIZE = 10240MB ,
    FILEGROWTH = 1024MB
) TO FILEGROUP InsertFileGroupNameHere;
GO

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: