Automate BACKUP DATABASE script.

The below script will generate backups for all the databases in a single instance.

SET NOCOUNT ON
DECLARE @servername varchar(50)
DECLARE @databasename varchar(50)
DECLARE @path VARCHAR(50)
DECLARE @stmt varchar(4000)
DECLARE @I INT
DECLARE @Count INT
DECLARE @Databases table
(Sno INT IDENTITY(1,1),[Name] varchar(50))
INSERT INTO @Databases
([Name])
SELECT Name FROM sys.databases
WHERE State=0 and database_id not in(2)
SET @I=0
SELECT @Count=COUNT(*) FROM @Databases
SET @servername=HOST_NAME()
--Provide path name here dont give \ in the end.
SET @path='\\ramesh\LS'
WHILE(@I<@Count)
BEGIN
SET @I=@I+1
SELECT @databasename=[Name] 
FROM @Databases
WHERE Sno=@I 
SET @stmt='BACKUP DATABASE '+'['+@databasename+']'+' TO DISK= '+''''+@path+'\'+@servername+'_'+@databasename+'_'+REPLACE(convert(nvarchar(20),GetDate(),120),':','-')+'.BAK'+''''+' WITH COMPRESSION,STATS=2;
GO'
PRINT @stmt
--EXEC(@stmt)
END