Prossima revisione | Revisione precedente |
hobby:development:sql:select:sys_0004 [2023/09/01 11:16] – creata mauro.cortese | hobby:development:sql:select:sys_0004 [2023/09/01 11:30] (versione attuale) – mauro.cortese |
---|
{{:wiki:icons:t-sql.png?nolink&90 |}} <fs xx-large>Query Db User</fs> | {{: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}}]]\\ | [[hobby:development:sql|{{ :wiki:icons:left_arrow.png?48|Vai alla mappa}}]]\\ |
//<fc #5F5F5F><fs large>(Elenca tutti i DB di un'istanza SQL Server)</fs></fc>// | //<fc #5F5F5F><fs large>(Esegue il backup di DB utente di un'istanza SQL Server)</fs></fc>// |
\\ | \\ |
\\ | \\ |
USE [master] | USE [master] |
| |
DECLARE @FolderBck NVARCHAR(255) = '\\FS001\ICT400$\PDM_Production\' -- Storage folder for SQL Backup | DECLARE @FolderBck NVARCHAR(255) = '' -- '\\FS001\ICT400$\PDM_Production\' -- Storage folder for SQL Backup |
DECLARE @Date DATETIME = GETDATE() | DECLARE @Date DATETIME = GETDATE() |
DECLARE @FileBck NVARCHAR(255); | DECLARE @FileBck NVARCHAR(255); |
DECLARE @SqlCmd NVARCHAR(MAX); | DECLARE @SqlCmd NVARCHAR(MAX); |
DECLARE @Database VARCHAR(255) | DECLARE @Database VARCHAR(255) |
| DECLARE @SubFolder NVARCHAR(255); |
| |
-- Declares and opens a cursor to list all non sytem database | -- Declares and opens a cursor to list all non sytem database |
WHILE @@FETCH_STATUS = 0 | WHILE @@FETCH_STATUS = 0 |
BEGIN | BEGIN |
| -- Create sub forlder for each DB |
SET @FileBck = @FolderBck + CONVERT(VARCHAR,@Date,112)+'_'+LEFT(REPLACE(CONVERT(VARCHAR,@Date,8),':',''),4)+'_' + @Database + '.bak' | 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' | SET @SqlCmd = 'BACKUP DATABASE [' + @Database + '] TO DISK = ''' + @FileBck + ''' WITH NOFORMAT, NOINIT, NAME = ''' + @Database + '_BackUp_Completo'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10' |
PRINT @SqlCmd | -- PRINT @SqlCmd |
-- Executes current data base backup | -- Executes current data base backup |
--EXEC (@SqlCmd) | 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 | FETCH NEXT FROM DatabaseCursor INTO @Database |
END | END |
</sxh> | </sxh> |
| |
<datatable> | |
^ ID ^ DBName ^ LogicalName ^ FileType ^ Path ^ Size ^ Collation ^ RecMode ^ | |
| 5 | Bom | Bom_log | LOG | P:\UserLog\LOG\Bom.ldf | 96 | Latin1_General_CI_AS | FULL | | |
| 5 | Bom | Bom_dat | ROWS | P:\UserDB\DATA\Bom.mdf | 88152 | Latin1_General_CI_AS | FULL | | |
| 6 | ErpLauncher | ErpLauncher_log | LOG | P:\UserLog\LOG\ErpLauncher.ldf | 3857 | Latin1_General_CI_AS | FULL | | |
| 6 | ErpLauncher | ErpLauncher_dat | ROWS | P:\UserDB\DATA\ErpLauncher.mdf | 112792 | Latin1_General_CI_AS | FULL | | |
| 7 | ErpTools | ErpTools_log | LOG | P:\UserLog\LOG\ErpTools.ldf | 640 | Latin1_General_CI_AS | FULL | | |
| 7 | ErpTools | ErpTools_dat | ROWS | P:\UserDB\DATA\ErpTools.mdf | 1280 | Latin1_General_CI_AS | FULL | | |
| 18 | Hrm | Hrm_log | LOG | P:\UserLog\LOG\Hrm.ldf | 776 | Latin1_General_CI_AS | FULL | | |
| 18 | Hrm | Hrm_dat | ROWS | P:\UserDB\DATA\Hrm.mdf | 1280 | Latin1_General_CI_AS | FULL | | |
| 8 | Ict | Ict_log | LOG | P:\UserLog\LOG\Ict.ldf | 3072 | Latin1_General_CI_AS | FULL | | |
| 8 | Ict | Ict | ROWS | P:\UserDB\DATA\Ict.mdf | 1024 | Latin1_General_CI_AS | FULL | | |
| 9 | LiveBom | LvtBom_log | LOG | P:\UserLog\LOG\LvtBom.ldf | 104 | SQL_Latin1_General_CP1_CI_AS | FULL | | |
| 9 | LiveBom | LvtBom | ROWS | P:\UserDB\DATA\LvtBom.mdf | 2584 | SQL_Latin1_General_CP1_CI_AS | FULL | | |
| 10 | LivePdm | LvtPdm_log | LOG | P:\UserLog\LOG\LvtPdm.ldf | 136 | SQL_Latin1_General_CP1_CI_AS | FULL | | |
| 10 | LivePdm | LvtPdm | ROWS | P:\UserDB\DATA\LvtPdm.mdf | 5960 | SQL_Latin1_General_CP1_CI_AS | FULL | | |
| 12 | LiveRights | LiveRights_log | LOG | P:\UserLog\LOG\LiveRights.ldf | 640 | Latin1_General_CI_AS | FULL | | |
| 12 | LiveRights | LiveRights_dat | ROWS | P:\UserDB\DATA\LiveRights.mdf | 1280 | Latin1_General_CI_AS | FULL | | |
| 13 | LiveSuite | LiveSuite_log | LOG | P:\UserLog\LOG\LiveSuite.ldf | 1024 | Latin1_General_CI_AS | FULL | | |
| 13 | LiveSuite | LiveSuite_dat | ROWS | P:\UserDB\DATA\LiveSuite.mdf | 7216 | Latin1_General_CI_AS | FULL | | |
| 14 | LiveSys | LiveSys_log | LOG | P:\UserLog\LOG\LiveSys.ldf | 640 | Latin1_General_CI_AS | FULL | | |
| 14 | LiveSys | LiveSys_dat | ROWS | P:\UserDB\DATA\LiveSys.mdf | 1280 | Latin1_General_CI_AS | FULL | | |
| 20 | LiveWatermark | LiveWatermark_log | LOG | P:\UserLog\LOG\LiveWatermark.ldf | 640 | Latin1_General_CI_AS | FULL | | |
| 20 | LiveWatermark | LiveWatermark_dat | ROWS | P:\UserDB\DATA\LiveWatermark.mdf | 1280 | Latin1_General_CI_AS | FULL | | |
| 17 | LvtPdmBase | LvtPdmBase_log | LOG | P:\UserLog\LOG\LvtPdmBase.ldf | 579072 | Latin1_General_CI_AS | FULL | | |
| 17 | LvtPdmBase | LvtPdmBase_dat | ROWS | P:\UserDB\DATA\LvtPdmBase.mdf | 251072 | Latin1_General_CI_AS | FULL | | |
| 16 | LvtPdmLibrary | Pdm_log | LOG | P:\UserLog\LOG\Pdm_Library.ldf | 114544 | Latin1_General_CI_AS | FULL | | |
| 16 | LvtPdmLibrary | Pdm_dat | ROWS | P:\UserDB\DATA\Pdm_Library.mdf | 251072 | Latin1_General_CI_AS | FULL | | |
| 11 | LvtPdmProd | Pdm_log | LOG | P:\UserLog\LOG\LvtPdmProd.ldf | 128 | Latin1_General_CI_AS | FULL | | |
| 11 | LvtPdmProd | Pdm_dat | ROWS | P:\UserDB\DATA\LvtPdmProd.mdf | 155112 | Latin1_General_CI_AS | FULL | | |
| 15 | Pdm | Pdm_log | LOG | P:\UserLog\LOG\Pdm.ldf | 128 | Latin1_General_CI_AS | FULL | | |
| 15 | Pdm | Pdm_dat | ROWS | P:\UserDB\DATA\Pdm.mdf | 193304 | Latin1_General_CI_AS | FULL | | |
| 24 | PdmGarbage | Pdm_log | LOG | P:\UserLog\LOG\PdmGarbage.ldf | 128 | Latin1_General_CI_AS | FULL | | |
| 24 | PdmGarbage | Pdm_dat | ROWS | P:\UserDB\DATA\PdmGarbage.mdf | 2416 | Latin1_General_CI_AS | FULL | | |
</datatable> | |