This is a SQL script of get all Databases Backup in particular folder.
backup file name will be "DataBaseName_{Month}_{Date}_{Year}.bak"

Notes:

  1. Current user have full access of backup database.
  2. Remote drive setup is extra step you have to perform in sql server in order to backup your dbs to remote drive.
  3. You have to change you sql server account to a network account and add that user to have full access to the network drive you are backing up to.

SQL Script

  1. DECLARE @DBName VARCHAR(MAX)
  2. DECLARE @FileName VARCHAR(MAX)
  3. DECLARE @FileDate VARCHAR(20)
  4. DECLARE @Path VARCHAR(MAX)
  5. SET @Path='C:\DBBackup\'
  6. SET @FileDate=REPLACE((SELECT CONVERT(VARCHAR(20),GETDATE(),101)),'/','_')
  7. DECLARE db_cursor CURSOR FOR
  8. SELECT name
  9. FROM master.dbo.sysdatabases
  10. WHERE name NOT IN ('master','tempdb','model','msdb')
  11. OPEN db_cursor
  12. FETCH NEXT FROM db_cursor into @DBName
  13. WHILE @@FETCH_STATUS = 0
  14. BEGIN
  15. SET @FileName= @Path + @DBName + '_' + @FileDate + '.BAK'
  16. BACKUP DATABASE @DBName TO DISK =@FileName
  17. FETCH NEXT FROM db_cursor into @DBName
  18. END
  19. CLOSE db_cursor
  20. DEALLOCATE db_cursor