USE master
GO
SET NOCOUNT ON
DECLARE @BackupPath nvarchar(500)
DECLARE @BackupDBFile nvarchar(500)
DECLARE @BackupLogFile nvarchar(500)
DECLARE @FolderName nvarchar(25)
DECLARE @ShellPath nvarchar(500)
DECLARE @DatabaseName nvarchar(25)
DECLARE @BackupDBDevice nvarchar(25)
DECLARE @BackupLogDevice nvarchar(25)
SET @BackupPath = 'C:\'
-- Create datetime folder for backup files
SELECT @FolderName = CONVERT( nvarchar(8),GETDATE(),112)+'_'+REPLACE(CONVERT( nvarchar(8),GETDATE(),8),':','')
SET @ShellPath = 'md ' + @BackupPath + @FolderName
SET @BackupPath = @BackupPath + @FolderName
exec master..xp_cmdshell @ShellPath , NO_OUTPUT
-- Declare Cursor
DECLARE CUR_BackupDB CURSOR FOR
select name from sysdatabases
--where name = 'master'
where name not in ('tempdb','pubs','Northwind')
-- Open Cursor
OPEN CUR_BackupDB
-- Fetch the db names from Cursor
FETCH NEXT FROM CUR_BackupDB INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BackupDBDevice = @DatabaseName + '_data_dev'
SET @BackupLogDevice = @DatabaseName + '_log_dev'
SET @BackupDBFile = @BackupPath + '\' + @DatabaseName + '_data.dat'
SET @BackupLogFile = @BackupPath + '\' + @DatabaseName + '_log.dat'
-- Create the backup device for the full db backup.
EXEC sp_addumpdevice 'disk', @BackupDBDevice, @BackupDBFile
--Create the log backup device.
EXEC sp_addumpdevice 'disk', @BackupLogDevice, @BackupLogFile
-- Back up the full database.
BACKUP DATABASE @DatabaseName TO @BackupDBDevice
IF( @DatabaseName != 'master' )
-- Back up the log of the database.
BACKUP LOG @DatabaseName TO @BackupLogDevice
-- Drop dump device
EXEC sp_dropdevice @BackupDBDevice
EXEC sp_dropdevice @BackupLogDevice
-- Fetch the db names from Cursor
FETCH NEXT FROM CUR_BackupDB INTO @DatabaseName
END
-- Distroy Cursor
CLOSE CUR_BackupDB
DEALLOCATE CUR_BackupDB
SET NOCOUNT OFF
