top of page

How to take Transaction Log Backups For All User Databases?

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!

Result of backup
Files of backup transaction logs

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!

bottom of page