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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

If You Need To Fix Database Filename Extensions

In a recent post I showed how the file extension for a database doesn’t matter. It can be confusing, however, and you might wish to “fix” the filenames to conform to the proper extension. How can you do this?

Well, to change a file name, or location, you need to take the database offline. This is noted in the Books Online Move Database procedure. Why? Well, the files need to be physically changed in the file system (either a rename or copy), so there is downtime here. Locations are one thing, but what about renames?

The rename is simpler, and if you script this, downtime is minimal. The procedure is the same as listed in BOL:

  • set the database offline
  • rename the file
  • run the ALTER DATABASE command
  • set the database online

This is pretty simple. We want to run this code:

ALTER DATABASE [NameTest2] SET OFFLINE
GO
ALTER DATABASE [NameTest2]
 MODIFY FILE ( NAME = NameTest2
             , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.mdf' )
GO
ALTER DATABASE [NameTest2] SET ONLINE
GO

However that code misses item #2 from above. I can manually perform that step, which is pretty easy, or I can script it if I allow xp_cmdshell changes. I know this is a security risk, but I can enable it and disable it all in the script:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO 
ALTER DATABASE [NameTest2] SET OFFLINE
GO
EXEC xp_cmdshell 'rename C:\"Program Files"\"Microsoft SQL Server"\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.ldf nametest2.mdf'
GO
;
ALTER DATABASE [NameTest2]
 MODIFY FILE ( NAME = NameTest2
             , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.mdf' )
GO
ALTER DATABASE [NameTest2] SET ONLINE
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO 

Note in here that I need some quotes in the RENAME command inside the shell so that Windows handles the spaces correctly in the path.


Filed under: Blog Tagged: administration, sql server, syndicated

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...