Backup Db User
(Esegue il backup di DB utente di un'istanza SQL Server)

USE [master]

DECLARE @FolderBck NVARCHAR(255) = '' -- '\\FS001\ICT400$\PDM_Production\' -- Storage folder for SQL Backup
DECLARE @Date DATETIME = GETDATE()
DECLARE @FileBck NVARCHAR(255);
DECLARE @SqlCmd NVARCHAR(MAX); 
DECLARE @Database VARCHAR(255)  
DECLARE @SubFolder NVARCHAR(255);
 
-- Declares and opens a cursor to list all non sytem database
-- ======================================================================
DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  
OPEN DatabaseCursor  
 
FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  
 	-- Create sub forlder for each DB
	SET @SubFolder = @FolderBck + @Database +'\'
	EXECUTE master.dbo.xp_create_subdir @SubFolder

    SET @FileBck = @SubFolder + CONVERT(VARCHAR,@Date,112)+'_'+LEFT(REPLACE(CONVERT(VARCHAR,@Date,8),':',''),4)+'_' + @Database + '.bak'
    SET @SqlCmd = 'BACKUP DATABASE [' + @Database + '] TO DISK = ''' + @FileBck + ''' WITH NOFORMAT, NOINIT, NAME = ''' + @Database + '_BackUp_Completo'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
    -- PRINT @SqlCmd
	-- Executes current data base backup
    EXEC (@SqlCmd)
 	
    SET @FileBck = @SubFolder + CONVERT(VARCHAR,@Date,112)+'_'+LEFT(REPLACE(CONVERT(VARCHAR,@Date,8),':',''),4)+'_' + @Database + '.trn'
	SET @SqlCmd = 'BACKUP LOG [' + @Database + '] TO DISK = ''' + @FileBck + ''' WITH NOFORMAT, NOINIT,  NAME = ''' + @Database + '_BackUp_Tansation_Log'', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10'
    -- PRINT @SqlCmd
	-- Executes current data base backup
    EXEC (@SqlCmd)


    FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor