Script to backup all database in a Server

Use below script to take backup of all Database in a SQL Server:



Set Nocount on
Declare @Database_Name varchar(100)
Declare @Server_Name varchar(100)
declare @d varchar(30)


Declare Cur_DB Cursor For
        -- ALL USER DATABASES
        Select name from master.dbo.sysdatabases
        where dbid > 4 


Declare @osql varchar(1000)


select @Server_Name = @@Server_Name
select @d = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 120), '-', ''), ':', ''), ' ', '_')


Open Cur_DB
Fetch Next from Cur_DB into @Database_Name
While @@Fetch_status=0
    Begin
        Set @osql='EXEC master.dbo.xp_cmdshell '+''''+'sqlcmd -E -S '+@Server_Name+' -Q"BACKUP DATABASE ['+@Database_Name+'] TO disk = ''''\\filer\SQL_BACKUPS_SHARE\myserver\UserDBs\'+@Database_Name+'_' + @d + '.bak'''' " -o"C:\SQLLogs\Agent Jobs\Backup User Databases - '+@Database_Name+'.log"'+''''
        EXEC (@osql) --Execute the osql statement
        Fetch Next from Cur_DB into @Database_Name
    End
Close Cur_DB


Deallocate Cur_DB