Sql Server: How to remove existing data files
- Posted by Sqltimes
- On April 19, 2014
- 0 Comments
Quick one today:
In the last few weeks, there have been multiple incidents where there was a need to remove/add/modify data files on a Sql Server databaes. So, it is time to add it as a new post.
Altering data files to a database is made easier in newer versions of Sql Server.
Add a new data file to DBADB database
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
---- Add a new FileGroup for new data file--ALTER DATABASE DBADBADD FILEGROUP nCI_01GO---- Add new file DBADB_Data3--ALTER DATABASE DBADBADD FILE( Name = N'DBADB_Data3.NDF' , FileName = N'G:\Data\DBADB_Data3.ndf' , SIZE = 10 GB , FILEGROWTH = 10%)TO FILEGROUP [nCI_01]GO |
Remove existing file
Existing data files could be removed after making them empty. This process was covered in previous post. Once a data file is empty, then it could be removed using the command below.
|
1
2
3
4
5
6
|
---- Remove data file--ALTER DATABASE DBADBREMOVE FILE DBADB_Data3GO |
Modifying an existing file
Increasing the data file size to 20 GB (from 10 GB)
|
1
2
3
4
5
6
7
8
9
10
|
---- Modify a data file--ALTER DATABASE DBADBMODIFY FILE( NAME = DBADB_Data3 , SIZE = 20 GB)GO |
Hope this helps,
_Sqltimes
0 Comments