Move database log files massively

Question

Post reply

Move database log files massively

WhiteLotus

SSChampion

Points: 12122
More actions
September 14, 2015 at 7:43 pm

#307795

Hi All ,
I want to to move all database log files from drive E to F .
There are more than 10 databases so I don’t wanna move them 1 by 1 .
At the moment I use detach – attach method .
-Detach db
-Move log file
-Attach db
How do I do this massively in one go ? Any script ?
Much appreciate on any feedback .

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

In SSMS:

ALTER DATABASE [dbname] MODIFY FILE (Name = Logfilename, FILENAME = N'F:\....\')

GO

ALTER DATABASE [dbname] SET OFFLINE

GO

*Outside SSMS* Move/copy files to new location on F:

*In SSMS, after copy's complete* ALTER DATABASE [dbname] SET ONLINE

Code for each DB, or write a cursor/loop with dynamic SQL to pick up the DB & file names, Filepaths etc.

You could also do it in a single cmd batch file or PoSh script, combining SqlCmd for the db operations with the file system copy operations.

To avoid downtime, it could be possible to add a new logfile on F to each DB , then issue a shrinkfile with emptyfile on the old one; and drop it once empty.

Haven't tried that myself though.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Gazareth (9/14/2015)
To avoid downtime, it could be possible to add a new logfile on F to each DB , then issue a shrinkfile with emptyfile on the old one; and drop it once empty.

Nope.

https://msdn.microsoft.com/en-us/library/ms189493.aspx

EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.

Emphasis mine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

GilaMonster (9/15/2015)
Gazareth (9/14/2015)
To avoid downtime, it could be possible to add a new logfile on F to each DB , then issue a shrinkfile with emptyfile on the old one; and drop it once empty.
Nope.
https://msdn.microsoft.com/en-us/library/ms189493.aspx
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
Emphasis mine.

Thanks Gail, I was a little dubious as to if that was a valid option or not.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The 'create a new log file' and 'drop the old one once empty' are fine and will work. It's the ShrinkFile portion that won't, because EmptyFile only applies to data files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

Umer Akram

SSC-Insane

Points: 22209

September 15, 2015 at 4:25 am

#1826682

Check this Moving User Databases

hope it helps