This is a SQL script of get all Databases Backup in particular folder.
backup file name will be "DataBaseName_{Month}_{Date}_{Year}.bak"
Notes:
- Current user have full access of backup database.
- Remote drive setup is extra step you have to perform in sql server in order to backup your dbs to remote drive.
- 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
- DECLARE @DBName VARCHAR(MAX)
- DECLARE @FileName VARCHAR(MAX)
- DECLARE @FileDate VARCHAR(20)
- DECLARE @Path VARCHAR(MAX)
- SET @Path='C:\DBBackup\'
- SET @FileDate=REPLACE((SELECT CONVERT(VARCHAR(20),GETDATE(),101)),'/','_')
- DECLARE db_cursor CURSOR FOR
- SELECT name
- FROM master.dbo.sysdatabases
- WHERE name NOT IN ('master','tempdb','model','msdb')
- OPEN db_cursor
- FETCH NEXT FROM db_cursor into @DBName
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET @FileName= @Path + @DBName + '_' + @FileDate + '.BAK'
- BACKUP DATABASE @DBName TO DISK =@FileName
- FETCH NEXT FROM db_cursor into @DBName
- END
- CLOSE db_cursor
- DEALLOCATE db_cursor