Generate Full Backup Scripts for all User Database in SQL Server

The advantage of utilising T-SQL to handle repetitive chores is that often things that seem hard are considerably simpler than you realise. The requirement to backup every database on your server can be one of these jobs. If you only have a few databases, this is not a major concern, but what if you need to back up every single database? It takes time and work to carry out the same procedure one at a time, especially if your SQL Server has a lot of databases. You might wish to back them all up at once in this situation.

Here is script that can be used to generate Full Backup Scripts for all the user databases in SQL Server.

Declare @BackupPath VARCHAR(100)

Set @BackupPath='c:\Backup\'

Select 'BACKUP DATABASE ['+name+'] TO  DISK = N'''+@BackupPath+''+name+'.bak''

WITH NOFORMAT, NOINIT,  NAME = N'''+name+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,

COMPRESSION,  STATS = 10'

from sys.databases

where database_id>4