{{:wiki:icons:t-sql.png?nolink&90 |}} <fs xx-large>Backup Db User</fs>
[[hobby:development:sql|{{ :wiki:icons:left_arrow.png?48|Vai alla mappa}}]]\\
//<fc #5F5F5F><fs large>(Esegue il backup di DB utente di un'istanza SQL Server)</fs></fc>//
\\
\\
<sxh sql>
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
</sxh>