• Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon
  • Grey Google+ Icon

© 2016 - 2019 All rights reserved. PigeonSQL.com | Filip Holub

How to take Transaction Log Backups For All User Databases?

February 26, 2017

This scritp you can use if you need to take transaction log backup for all user databases of instance. Script below exclude system database(Master, Model, MSDB and TempDB) and it includes only those databases that are in a recovery mode of "FULL" or "BULK LOGGED". Source for name of database is Select from sys.databases.

 

My script below:

 

 

Here is result!

 

 

However, there may be a problem with filegroup like in my case below:

 

Msg 3007, Level 16, State 1, Line 1
The backup of the file or filegroup "Pigeon_FG" is not permitted because it is not online. Container state: "Restoring" (8). Restore status: 8. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

 

 

Then I deleted this filegroup(I never used this FG) and the backup transaction log again and ran successfully.

 

Have a nice day!

 

 

 

Please reload

RECENT POST
Please reload

  • Facebook Social Icon
  • Google+ Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon