Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Script to Rename Many .ndf Physical Files

By Greg Milner,

At my job, I have a large database that's partitioned and has about 115 separate .ndf files. Recently, when I restored it on our DR site, I didn't do it correctly and so instead of .ndf files named like this:

MYDATABASE_Services_2008_01.ndf
MYDATABASE_Services_2008_02.ndf
MYDATABASE_Services_2008_03.ndf
...
...
MYDATABASE_Services_2010_02.ndf
MYDATABASE_Services_2010_03.ndf
MYDATABASE_Events_2008_01.ndf
MYDATABASE_Events_2008_02.ndf
...
...
MYDATABASE_Events_2008_01.ndf
MYDATABASE_Events_2008_02.ndf
MYDATABASE_Events_2008_03.ndf

... and so on.

What happened was that the files wound up named like this:

MY_DATABASE_16.ndf
MY_DATABASE_17.ndf
MY_DATABASE_18.ndf
MY_DATABASE_19.ndf
...
...
MY_DATABASE_33.ndf
MY_DATABASE_34.ndf
MY_DATABASE_35.ndf
...
...
MY_DATABASE_105.ndf
MY_DATABASE_106.ndf
MY_DATABASE_107.ndf

In addition to this, many of the files are on different drives. For example, the files for Services are on drive I: while the files for Events are on drive F:

Now, this database is well over 300GB in size and even with SQL 2008 backup compression the .BAK file is still 43GB. I was not looking forward to copying it across the country again to redo the restore so I could get the filenames right.

Forturnately, I am consistent in my naming of the filegroups, though. They are named thusly:

FG_Services_2008_01
FG_Services_2008_02
FG_Services_2008_03
...
...
FG_Services_2010_02
FG_Services_2010_03
FG_Events_2008_01
FG_Events_2008_02
...
...
FG_Events_2008_01
FG_Events_2008_02.ndf
FG_Events_2008_03.ndf

 

So I created a script that will take the filegroup names and rename the files for you. What it does is create 2 scripts you can run. The first one is a set of ALTER DATABASE commands to rename how the files will appear in the system tables. The second thing it spits out is a set of DOS commands you can run from a batch file to rename the physical files.

What you wind up with after all is said and done, are .ndf files named like this:

MYDATABASE_Services_2008_01.ndf
MYDATABASE_Services_2008_02.ndf
MYDATABASE_Services_2008_03.ndf

 

Total article views: 938 | Views in the last 30 days: 4
 
Related Articles
FORUM

Restore database from network drive

Restore database from network drive

FORUM

cluter server databases backup on local drive

cluter server databases backup on local drive

FORUM

error# 625

consistency errors in database 'MyDatabase'.

BLOG

Restoring databases to a set drive and directory

  Restoring databases to a set drive and directory Introduction Often people say that necessity i...

FORUM

Any known issues with SQl Database on Dynamic Disk drive configuration?

Ok to put Sql database on Dynamic disk drives

Tags
rename    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones