Emigar - Contributi dell'utente [it]

Mysql Tuning

2026-07-07T21:08:56Z

WikiSysop: /* retrocompatibilità sql */

==innodb (8.0+)==
Impostazioni per avere un database che usa solo InnoDB:

parametri di startup linea di comando (controllare sulla documentazione cosa fanno e come si usano):
--innodb-dedicated-server
--memlock

====Modificabili solo nel file di configurazione, serve poi riavvio istanza:====

set persist_only disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE";

Con linux serve configurazione hugepages:
set persist_only large_pages=ON;

con 8.4 dovrebbe essere già default:
set persist_only innodb_flush_method=O_DIRECT;

set persist_only global_connection_memory_limit=536870912;
set persist_only global-connection-memory-tracking=TRUE;

====Modificabili a caldo:====
set persist innodb_buffer_pool_size=536870912;
set persist innodb_redo_log_capacity = 8589934592;

====uso memoria====

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

====retrocompatibilità sql====
select @@sql_mode;

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

set persist sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

====comportamenti strani====
se si configurano hugepages (ma forse anche se non), alcune operazioni fanno allocare memoria, che non viene rilasciata.
l'operazione "add constrain foreign key" su tabella grande produce questo effetto

forse usando libjemalloc2 il problema si risolve

[[Categoria:Database]]
[[Categoria:Mysql]]
[[Categoria:OpenSource]]

Mysql Tuning

2026-07-07T21:06:09Z

WikiSysop:

==innodb (8.0+)==
Impostazioni per avere un database che usa solo InnoDB:

parametri di startup linea di comando (controllare sulla documentazione cosa fanno e come si usano):
--innodb-dedicated-server
--memlock

====Modificabili solo nel file di configurazione, serve poi riavvio istanza:====

set persist_only disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE";

Con linux serve configurazione hugepages:
set persist_only large_pages=ON;

con 8.4 dovrebbe essere già default:
set persist_only innodb_flush_method=O_DIRECT;

set persist_only global_connection_memory_limit=536870912;
set persist_only global-connection-memory-tracking=TRUE;

====Modificabili a caldo:====
set persist innodb_buffer_pool_size=536870912;
set persist innodb_redo_log_capacity = 8589934592;

====uso memoria====

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

====retrocompatibilità sql====
select @@sql_mode;

set persist sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

====comportamenti strani====
se si configurano hugepages (ma forse anche se non), alcune operazioni fanno allocare memoria, che non viene rilasciata.
l'operazione "add constrain foreign key" su tabella grande produce questo effetto

forse usando libjemalloc2 il problema si risolve

[[Categoria:Database]]
[[Categoria:Mysql]]
[[Categoria:OpenSource]]

Oracle Query Varie

2026-06-29T07:00:39Z

WikiSysop: /* Autoextend datafiles */

=Tracciare operazioni nell'alert log=
EXEC SYS.DBMS_SYSTEM.kfnwrt(1, 'NOTA OPERATORE: Inizio manutenzione di Mario Rossi');

=ACL=
==creazione acl==

==interrogazioni acl==
col host for a40
col acl for a45
col acl_owner for a10
set pagesize 200 linesize 200
SELECT * FROM DBA_NETWORK_ACLS order by host,lower_port;

col principal for a25
col privilege for a20
col start_date for a4
col end_date for a4
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES order by principal,privilege;

select a.host,a.lower_port,p.acl,p.principal,p.privilege,p.is_grant
from DBA_NETWORK_ACLS a inner join DBA_NETWORK_ACL_PRIVILEGES p on (a.aclid=p.aclid)
order by p.acl,p.principal,a.host,a.lower_port;

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,
DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,
DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

=Job=
==Elenco Job==

col job_name for a40
col owner for a15
col last_start_date for a30
col next_run_date for a30
col start_date for a30
alter session set nls_timestamp_tz_format='DD/MM/YYYY HH24:MI:SS.FF TZH';
select job_name, owner, enabled, state, failure_count, LAST_START_DATE, NEXT_RUN_DATE, START_DATE from dba_scheduler_jobs order by last_start_date;

col job_name for a50
col owner for a20
select job_name, owner, enabled from dba_scheduler_jobs;

==abilitare e disabilitare job==

BEGIN
DBMS_SCHEDULER.ENABLE(name => 'nome_schema.nome_job' );
END;
/

==Job Immediato==

Per creare un indice:
<pre>
begin
dbms_scheduler.create_job(
job_name => 'il_mio_job',
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ''create index owner.index_name on owner.table(col01) online nologging''; end;',
enabled => TRUE,
auto_drop => TRUE,
comments => 'crea indice'
);
end;
/
</pre>

Per analizzare uno schema:
<pre>
begin
dbms_scheduler.create_job(
job_name => 'analyze_schema',
job_type => 'PLSQL_BLOCK',
job_action => 'begin dbms_stats.gather_schema_stats(ownname=>''SCHEMA'',cascade=>true); end;',
enabled => TRUE,
auto_drop => TRUE,
comments => 'analyze'
);
end;
/
</pre>

=Indici=
==Elenco Indici==
set pagesize 200
set linesize 140
col column_name for a30
col pos for 99
col table_name for a30
col index_name for a35
select table_owner || '.' || table_name table_name, index_owner || '.' || index_name index_name, column_name, column_position pos
from dba_ind_columns
where table_name=upper('&&1')
order by 1,2,4;

<pre>
set pagesize 200
set linesize 140
set autotrace off
col column_name for a30
col pos for 99
col table_name for a38
col index_name for a43
col tbs for a20
select c.table_owner || '.' || c.table_name table_name, c.index_owner || '.' || c.index_name || ' ' || i.generated index_name,
substr(i.uniqueness,1,1) U, c.column_name, c.column_position pos, i.tablespace_name tbs
from dba_ind_columns c
inner join dba_indexes i on
(c.table_owner=i.table_owner and c.table_name=i.table_name and
c.index_owner=i.owner and c.index_name=i.index_name)
where c.table_name=upper('&&1')
order by 1,2,4;

col owner for a40
col tablespace_name for a40
select owner,tablespace_name,bytes/(1024*1024) mb from dba_segments where segment_name=upper('&&1');

</pre>

==Indici con proprietario diverso==
select owner,index_name,table_owner,table_name from dba_indexes where table_owner!=owner;

=Processi e sessioni=
==verifica valori limite==
col resource_name for a40
select resource_name,current_utilization,max_utilization,limit_value from v$resource_limit;

==Elenco processi di background==

select s.sid, s.process, s.program, p.name from v$session s join v$bgprocess p using (paddr) ;

==sessioni==

===Sessioni Attive===

set linesize 220
set pagesize 200
set autotrace off
col machine for a35
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,
s.username,s.machine,s.sql_id,s.prev_sql_id,
s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw
from v$session s
left outer join v$process p on (p.addr=s.paddr)
where s.type='USER' and s.status='ACTIVE'
order by s.logon_time;

====versione RAC====
set linesize 220
set pagesize 200
set autotrace off
col machine for a35
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial#||'@'||s.inst_id as sid_ser,p.spid,
s.username,s.machine,s.sql_id,s.prev_sql_id,
s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw
from gv$session s
left outer join gv$process p on (p.addr=s.paddr and s.inst_id=p.inst_id)
where s.type='USER' and s.status='ACTIVE'
order by s.logon_time;

====con eventi====

set linesize 230
set pagesize 300
set autotrace off
col machine for a21
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
col sql_id for a14
col prev_sql_id for a14
col siw for 9999999
col status for a15
col event for a32
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,s.status,
s.username,substr(s.machine,1,20) as machine,s.sql_id,s.prev_sql_id,
s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw,s.event
from v$session s
left outer join v$process p on (p.addr=s.paddr)
where s.type='USER'
--and s.status='ACTIVE'
-- and s.username not in ('SYS','DBSNMP')
order by s.status desc,s.logon_time;

===Sessioni ordinate per tipo===

set linesize 200
set pagesize 200
set autotrace off
col machine for a21
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,s.status,
s.username,substr(s.machine,1,20) as machine,s.sql_id,s.prev_sql_id,
s.logon_time,substr(s.program,1,20) as program,s.seconds_in_wait as siw
from v$session s
left outer join v$process p on (p.addr=s.paddr)
where s.type='USER'
-- and s.username not in ('SYS','DBSNMP')
order by s.status,s.logon_time;

--

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,
s.username,substr(s.machine,1,20) as machine,s.sql_id,s.prev_sql_id,
s.logon_time,substr(s.program,1,20) as program,s.seconds_in_wait as siw
from v$session s
left outer join v$process p on (p.addr=s.paddr)
where s.type='USER'
and s.status='ACTIVE'
and s.username not in ('SYS','DBSNMP')
order by s.logon_time;

==long operations==

<pre>
SET LINESize 200
set pagesize 200
set autotrace off
COL sid FORMAT 9999
COL serial# FORMAT 9999999
col sid_ser for a12
COL username FORMAT A30
COL machine FORMAT A50
COL prog% FORMAT 999.00
COL elapsed FORMAT A10
COL remaining FORMAT A10
col message for a80
spool longops2.log
SELECT s.sid || ',' || s.serial# sid_ser,
s.username,
-- s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) "prog%",
s.sql_id,
replace(sl.message,'Blocks done','') as message
FROM v$session s
inner join v$session_longops sl on ( s.sid = sl.sid AND s.serial# = sl.serial# AND sl.time_remaining > 0 )
order by s.sql_id;
spool off

</pre>



==Rollback==
select state,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL, UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100 from gv$fast_start_transactions;

=Piano della query per sql_id=

--
set autotrace off
set pagesize 200
set linesize 200
select DBMS_LOB.substr(sql_fulltext, 3000) from v$sql where sql_id = '&1';
-- select plan_table_output from table(dbms_xplan.display_cursor('&1',null,'basic'));
-- select plan_table_output from table(dbms_xplan.display_cursor('&1'));
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&1',cursor_child_no=>&2));

=Storico query che hanno usato più TEMP=
Richiede AWR.

select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time > sysdate-2
and TEMP_SPACE_ALLOCATED > (500*1024*1024)
group by sql_id order by 2;

Versione per statspack:

=Segment Advisor=

--
set pagesize 200
set linesize 200
set autotrace off
-- select tablespace_name, segment_name, segment_type, partition_name,
-- recommendations, c1 from
-- table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
spool segadv.txt
select '/*' || recommendations || ' */ ' || c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) where c1 is not null order by segment_type,reclaimable_space;
spool off

=Logging=

-- tabelle
set pagesize 0 linesize 200 trim on trims on feedback off echo off timing off
select 'alter table ' || owner || '.' || table_name || ' logging;' from dba_tables where owner!='SYS' and logging='NO';
select 'alter index ' || owner || '.' || index_name || ' logging;' from dba_indexes where owner!='SYS' and logging='NO';
select 'alter table ' || owner || '.' || table_name || ' modify lob (' || column_name || ') (cache logging);' from dba_lobs where owner!='SYS' and logging='NO';

-- tabelle partizionate
select 'alter table ' || owner || '.' || table_name || ' modify default attributes logging; ' from dba_part_tables where owner!='SYS' and def_logging='NO';
select 'alter index ' || owner || '.' || index_name || ' modify default attributes logging; ' from dba_part_indexes where owner!='SYS' and def_logging='NO';
select 'alter table ' || table_owner || '.' || table_name || ' modify default attributes lob (' || column_name || ') (cache logging);' from dba_part_lobs where table_owner!='SYS' and def_logging='NO';

-- partizioni
select 'alter table ' || table_owner || '.' || table_name || ' modify partition ' || partition_name || ' logging;' from dba_tab_partitions where table_owner!='SYS' and logging='NO';
select 'alter index ' || index_owner || '.' || index_name || ' modify partition ' || partition_name || ' logging;' from dba_ind_partitions where index_owner!='SYS' and logging='NO' and status='USABLE';
select 'alter table ' || table_owner || '.' || table_name || ' modify partition ' || partition_name || ' lob (' || column_name || ') (cache logging);' from dba_lob_partitions where table_owner!='SYS' and logging='NO';

=Controllo Allocazione Spazio=
Questa query controlla lo spazio allocato, e lo confronta con lo spazio di massima crescita dei datafiles.
Utile per evidenziare casi in cui è necessario aggiungere altri datafiles.

<pre>
--

set pagesize 200
set linesize 200

with
segs as (select tablespace_name,sum(bytes)/(1024*1024) mbs, count(*) nsegs from dba_segments group by tablespace_name),
files as (select tablespace_name,count(file_name) nfiles,sum(case when maxbytes=0 then bytes else maxbytes end)/(1024*1024) max,sum(bytes)/(1024*1024) mba
from dba_data_files group by tablespace_name)
select t.tablespace_name,
t.block_size,
f.nfiles,
s.nsegs,
round(f.max) max,
round(s.mbs) mbs,
round(f.max-s.mbs) sdiff,
round(((f.max-s.mbs)/f.max)*100) pcts,
round(f.mba) mba,
round(f.mba-s.mbs) shrinkable,
round(((f.mba-s.mbs)/f.mba)*100) pctsrnk
from dba_tablespaces t
inner join files f on (t.tablespace_name=f.tablespace_name)
left outer join segs s on (t.tablespace_name=s.tablespace_name)
order by 10;

with
tf as (select tablespace_name,count(*) nf,round(sum(bytes)/(1024*1024)) mba,round(sum(case when maxbytes=0 then bytes else maxbytes end)/(1024*1024)) max from dba_temp_files group by tablespace_name)
select g.group_name,
t.tablespace_name,
f.nf nfiles,
f.mba,
f.max,
round(s.allocated_space/(1024*1024)) mballoc,
round(s.free_space/(1024*1024)) mbfree
from tf f
left outer join dba_tablespace_groups g on (g.tablespace_name=f.tablespace_name)
left outer join dba_temp_free_space s on (s.tablespace_name=f.tablespace_name)
right outer join dba_tablespaces t on (t.tablespace_name=f.tablespace_name)
where t.contents='TEMPORARY'
order by 1,2,3;

</pre>

===Per 10.2===
<pre>
with
tf as (select tablespace_name,count(*) nf,round(sum(bytes)/(1024*1024)) mba,round(sum(case when maxbytes=0 then bytes else maxbytes end)/(1024*1024)) max from dba_temp_files group by tablespace_name),
tsh as (select tablespace_name,round(sum(bytes_used)/(1024*1024)) used,round(sum(bytes_free)/(1024*1024)) free from v$temp_space_header group by tablespace_name)
select
t.tablespace_name,
f.nf nfiles,
f.mba,
f.max,
tsh.used mballoc,
tsh.free mbfree
from tf f
right outer join dba_tablespaces t on (t.tablespace_name=f.tablespace_name)
right outer join tsh on (tsh.tablespace_name=f.tablespace_name)
where t.contents='TEMPORARY'
order by 1,2,3;
</pre>

Sort Segments

<pre>
SELECT tf.tablespace_name,
ROUND(tf.total_space_mb) AS total_mb,
ROUND(NVL(tu.used_space_mb, 0)) AS used_mb,
ROUND(tf.total_space_mb - NVL(tu.used_space_mb, 0)) AS free_mb
FROM (SELECT tablespace_name, SUM(bytes)/(1024*1024) AS total_space_mb
FROM dba_temp_files GROUP BY tablespace_name) tf
LEFT JOIN (SELECT tablespace_name, SUM(used_blocks)*(SELECT value FROM v$parameter WHERE name='db_block_size')/(1024*1024) AS used_space_mb
FROM v$sort_segment GROUP BY tablespace_name) tu
ON tf.tablespace_name = tu.tablespace_name;
</pre>



==Autoextend datafiles==

Verificare se autoextend:
select TABLESPACE_NAME,FILE_ID,AUTOEXTENSIBLE,MAXBYTES/(1024*1024) MAX_MB,INCREMENT_BY from dba_data_files;

Autoextend datafiles:

begin
for c1 in (select file_id from dba_data_files)
loop
execute immediate 'alter database datafile ' || c1.file_id || ' autoextend on next 128M maxsize unlimited';
end loop;
end;
/

Su un solo tablespace

begin
for c1 in (select file_id from dba_data_files where tablespace_name='&1')
loop
execute immediate 'alter database datafile ' || c1.file_id || ' autoextend on next 128M maxsize unlimited';
end loop;
end;
/

Mette in autoextend i files non in autoextend, ma con maxsize uguale alla grandezza reale

begin
for c1 in (select file_id, bytes from dba_data_files where autoextensible='NO')
loop
execute immediate 'alter database datafile ' || c1.file_id || ' autoextend on next 128M maxsize ' || c1.bytes;
end loop;
end;
/

Autoextend tempfiles:

begin
for c1 in (select file_id from dba_temp_files)
loop
execute immediate 'alter database tempfile ' || c1.file_id || ' autoextend on next 128M maxsize unlimited';
end loop;
end;
/

==Shrink datafiles==
<pre>
set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024)
hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)
||'M after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

</pre>

==Schedula pulizia recycle bin==
Cancella dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni
<pre>
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'purge_recyclebin_3days',
job_type => 'PLSQL_BLOCK',
job_action => '
begin
for c1 in (select owner,type,OBJECT_NAME from dba_recyclebin where to_date(DROPTIME,''YYYY-MM-DD:HH24:MI:SS'') < sysdate-3 and CAN_PURGE=''YES'')
loop
begin
execute immediate ''purge '' || c1.type || '' '' || c1.owner || ''."'' || c1.object_name || ''"'';
exception
when others then
null;
end;
end loop;
end;
',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
enabled => TRUE);
END;
/

</pre>

=Ricerca files orfani ASM=

http://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/

<pre>
DEFINE ASMGROUP="DGDATA"

set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'),
v_parentindex AS (SELECT parent_index
FROM v$asm_alias
WHERE group_number = (SELECT group_number FROM v_asmgroup)
AND alias_index=0),
v_asmfiles AS (SELECT file_number, type
FROM v$asm_file
WHERE group_number = (SELECT group_number FROM v_asmgroup)),
v_dbname AS (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database)
SELECT 'rm '|| files files FROM -- this line show the delete command
(
SELECT '+&ASMGROUP'||files files, type
FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type
FROM (SELECT file_number,alias_directory,name, reference_index, parent_index
FROM v$asm_alias) aa,
(SELECT parent_index FROM v_parentindex) a,
(SELECT file_number, type FROM v_asmfiles) b
WHERE aa.file_number=b.file_number(+)
AND aa.alias_directory='N'
-- missing PARAMETERFILE, DATAGUARDCONFIG
AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')
START WITH aa.PARENT_INDEX=a.parent_index
CONNECT BY PRIOR aa.reference_index=aa.parent_index)
WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM v_dbname)
MINUS (
SELECT upper(name) files, 'DATAFILE' type FROM v$datafile
UNION ALL
SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile
UNION ALL
SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'
UNION ALL
SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);

</pre>

=constraints=

Per vedere le colonne. Va raffinata:

select c.constraint_name || ' ' || c.table_name || '(' || u.column_name || ')'
from dba_constraints c
inner join dba_cons_columns u on (u.owner=c.owner and u.constraint_name=c.constraint_name)
where c.constraint_type='R';

Per vedere le referenze tra tabelle:

select c.constraint_name || ' ' || c.table_name || ' -> ' || u.table_name
from dba_constraints c
inner join dba_constraints u on (u.owner=c.r_owner and u.constraint_name=c.r_constraint_name)
where c.constraint_type='R'
and (c.table_name = '&&1' or u.table_name='&&1');

select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,VALIDATED from dba_constraints where table_name='&&1';

=Get DDL=
set long 2000000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PARTITIONING',true);
end;
/

==Tabelle==
select dbms_metadata.get_ddl(object_type=>'TABLE',schema=>'SCHEMA_NAME',name=>'TABLE_NAME') from dual;

==Reverse Grants==

Elenco delle grants concesse all'utente. Utile quando l'utente va ricreato, ad esempio in caso di import.

Vecchio metodo (si può migliorare gestendo la grant option):
<pre>
--
set pagesize 0 linesize 200 trim on trims on tab off feedback off
spool privilegi_esportati.sql
select 'grant ' || privilege || ' to ' || grantee || ';' from dba_sys_privs where grantee in ('USER1');
select 'grant ' || GRANTED_ROLE || ' to ' || grantee || ';' from dba_role_privs where grantee in ('USER1');
select 'grant ' || privilege || ' on ' || owner || '."' || table_name || '" to ' || grantee || ';' from dba_tab_privs where grantee in ('USER1');
spool off
set pagesize 200 feedback on
</pre>

Metodo più efficace:
<pre>

set long 2000000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

variable v_username VARCHAR2(30);

exec:v_username := upper('UTENTE01');

spool grants_UTENTE01.sql

SELECT dbms_metadata.get_ddl('USER','UTENTE01') FROM dual;

select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_username
and rownum = 1
/

spool off;

</pre>

<pre>
clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt ' Output filename : '

spool &&outfile..gen

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off
</pre>

==Reverse Grants 2==

Elenco delle grants concesse all'utente. Utile quando l'utente va ricreato, ad esempio in caso di import.
<pre>

set long 2000000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

variable v_username VARCHAR2(30);

exec:v_username := upper('UTENTE01');

spool grants_UTENTE01.sql

SELECT dbms_metadata.get_ddl('USER','UTENTE01') FROM dual;

select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_username
and rownum = 1
/

spool off;

</pre>

<pre>
clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt ' Output filename : '

spool &&outfile..gen

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off
</pre>

=Locked Objects=

<pre>
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

col object_name for a30
col machine for a30

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, s.inst_id, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE, S.PORT,
S.LOGON_TIME, SQ.SQL_FULLTEXT
FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S, GV$PROCESS P, GV$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND (L.SESSION_ID = S.SID and l.inst_id=s.inst_id)
AND (S.PADDR = P.ADDR and s.inst_id=p.inst_id)
AND (S.SQL_ADDRESS = SQ.ADDRESS and s.inst_id=sq.inst_id)
;

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE, S.PORT,
S.LOGON_TIME, SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND (L.SESSION_ID = S.SID)
AND (S.PADDR = P.ADDR)
AND (S.SQL_ADDRESS = SQ.ADDRESS)
;
</pre>

==lock==

<pre>
--

set linesize 190
set pagesize 200
col blocker for a30
col sid_serial for a15
col blockee for a30

col object_name for a30
col machine for a45
col osuser for a15

select c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c,
v$lock l
where b.sid = a.session_id
and a.object_id = c.object_id
and l.sid=b.sid
and (l.block=1 or l.request > 0 )
;

select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

</pre>

<pre>
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) '
AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
set heading off
select 'SQL STATEMENT BLOCCANTI:' as SQL_STATEMENT_BLOCCANTI from dual;
set heading on
select a.username,a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address and a.sid in ( select
s1.sid
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2) ;

</pre>

==Tablespaces Read Only==

select 'alter tablespace ' || tablespace_name || ' read only;' from dba_tablespaces where contents='PERMANENT'
and status='ONLINE' and tablespace_name not in ('SYSTEM','SYSAUX');

select 'alter tablespace ' || tablespace_name || ' read write;' from dba_tablespaces where contents='PERMANENT'
and status='READ ONLY' and tablespace_name not in ('SYSTEM','SYSAUX');

=Character Set=

col PROPERTY_NAME for a30
col PROPERTY_VALUE for a40
col DESCRIPTION for a60
select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

==Gestione stringhe con caratteri unicode==

<pre>
SQL> select asciistr('Fernando, tre caffè! Olé!') as fernando from dual;

FERNANDO
--------------------------------------------------------------------------------
Fernando, tre caff\00E8! Ol\00E9!

SQL> select unistr('Fernando, tre caff\00E8! Ol\00E9!') as fernando from dual;

FERNANDO
---------------------------------------------------------------------------
Fernando, tre caffè! Olé!

SQL> select asciistr('èéà°òç½€') from dual;

ASCIISTR('ÈÉÀ°ÒÇ½€')
--------------------------------------------------------------------------------
\00E8\00E9\00E0\00B0\00F2\00E7\00BD\20AC

SQL>

</pre>

=Redefine Online=
<pre>
--
set timing on
drop table USER.EESKFILECOMUNICAZIONE_CP;
purge dba_recyclebin;

-- verifica se è utilizzabile
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('USER','EESKFILECOMUNICAZIONE',DBMS_REDEFINITION.CONS_USE_PK);
END;
/

-- organization index tablespace ODSSOLTBS overflow tablespace ODSSOLTBS
create table USER.EESKFILECOMUNICAZIONE_CP (IDSKFILE NUMBER(18),FBODY BLOB)
lob(FBODY) store as securefile (enable storage in row nocache nologging deduplicate compress high tablespace ODSLOBTBS)
partition by range (IDSKFILE) interval (5000000) ( partition values less than (5000000) )
compress for oltp tablespace ODSLOBTBS nologging;

-- insert /*+ append */ into USER.EESKFILECOMUNICAZIONE_CP
-- select * from USER.EESKFILECOMUNICAZIONE;

alter table USER.EESKFILECOMUNICAZIONE_CP enable row movement;
alter session force parallel dml parallel 8;
alter session force parallel query parallel 8;

EXEC DBMS_REDEFINITION.start_redef_table(uname=>'USER', orig_table=>'EESKFILECOMUNICAZIONE', int_table=>'EESKFILECOMUNICAZIONE_CP');

EXEC DBMS_REDEFINITION.sync_interim_table(uname=>'USER', orig_table=>'EESKFILECOMUNICAZIONE', int_table=>'EESKFILECOMUNICAZIONE_CP');

-- Copy dependents.
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'USER',
orig_table => 'EESKFILECOMUNICAZIONE',
int_table => 'EESKFILECOMUNICAZIONE_CP',
copy_indexes => 1, -- Default
copy_triggers => TRUE, -- Default
copy_constraints => TRUE, -- Default
copy_privileges => TRUE, -- Default
ignore_errors => FALSE, -- Default
num_errors => l_num_errors,
copy_statistics => FALSE, -- Default
copy_mvlog => FALSE); -- Default

DBMS_OUTPUT.put_line('num_errors=' || l_num_errors);
END;
/

EXEC DBMS_REDEFINITION.finish_redef_table(uname=>'USER', orig_table=>'EESKFILECOMUNICAZIONE', int_table=>'EESKFILECOMUNICAZIONE_CP');

</pre>

[[Categoria:Database]]
[[Categoria:Oracle]]

Postgres Dump

2026-06-22T12:22:53Z

WikiSysop: /* dump e import in un solo passaggio */

=dump singolo db=
pg_dump -U owner_db -d db01 --compress=gzip -f dump_db01.sql.gz

=dump e import in un solo passaggio=
pg_dump -h server01.sorgente.db -U owner_database01 -d database01 | psql -U owner_database01 -h localhost -p 5432 -d database01 -b -o imp_database01.log
con parallelismo
pg_dump -Fc -h server01.sorgente.db -U owner_database01 -d database01 | pg_restore -j 4 -U owner_database01 -h localhost -p 5432 -d database01 -b -o imp_database01.log

=autovacuum=
durante la import, sul database di destinazione si attivano uno o più processi di autovacuum, per ricalcolare le statistiche. Per evitarlo si può disattivare l'autovacuum sul database, e riattivarlo alla fine della import.
[[Categoria:Database]]
[[Categoria:Postgres]]

Postgres Dump

2026-06-22T09:54:53Z

WikiSysop:

=dump singolo db=
pg_dump -U owner_db -d db01 --compress=gzip -f dump_db01.sql.gz

=dump e import in un solo passaggio=
pg_dump -h server01.sorgente.db -U owner_database01 -d database01 | psql -U owner_database01 -h localhost -p 5432 -d database01 -b -o imp_database01.log

=autovacuum=
durante la import, sul database di destinazione si attivano uno o più processi di autovacuum, per ricalcolare le statistiche. Per evitarlo si può disattivare l'autovacuum sul database, e riattivarlo alla fine della import.
[[Categoria:Database]]
[[Categoria:Postgres]]

Mysql Password

2026-06-22T07:01:50Z

WikiSysop:

=Creazione utente=
create user pincopallino identified with caching_sha2_password by "password";
==Memorizzazione Password==
Per memorizzare le credenziali di connessione ad un database:

mysql_config_editor set --login-path=local_root --host=localhost --port=3306 --user=root --password

il comando genera un file <code>.mylogin.cnf</code> nella $HOME
se come login-path si utilizza la stringa <code>client</code> tale connessione viene utilizzata per default.

Per elencare tutte le connessioni memorizzate:
mysql_config_editor print --all

Per leggere un file diverso dal default:
mysql_config_editor print --defaults-file=/home/user/.mylogin.cnf -all

Quando si utilizzano le credenziali salvate:
mysql --login-path=local_root

mysql --defaults-file=/home/user/.mylogin.cnf --login-path=db_remoto

[[Categoria:Database]]
[[Categoria:Mysql]]
[[Categoria:OpenSource]]

MSSQL varie

2026-06-18T12:14:42Z

WikiSysop:

==ricalcolo statistiche per database==
use MioDB
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
===metodo detach attach===
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

===metodo rename file===
* elencare files e percorsi:
SELECT name,physical_name,state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MioDB');
* Mettere offline il database:
ALTER DATABASE MioDb SET OFFLINE;
GO
Se serve forzare transazioni in corso:
ALTER DATABASE MioDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
* Spostare o copiare i datafile
mv /vecchio_percorso/miodb01.mdf /nuovo_percorso/
mv /vecchio_percorso/miodb01_log.ldf /nuovo_percorso/
* rinominare i files nel DB
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_DATA, FILENAME = '/nuovo_percorso/miodb01.mdf');
GO
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_LOG, FILENAME = '/nuovo_percorso/miodb01_log.ldf');
GO
* rimettere online il DB
ALTER DATABASE MioDB SET ONLINE;
GO
===metodo online===
* aggiungi un nuovo file
ALTER DATABASE NomeDatabase
ADD FILE (
NAME = 'NomeNuovoFile',
FILENAME = 'D:\NuovoPercorso\NomeNuovoFile.ndf',
SIZE = 100MB,
FILEGROWTH = 100MB
);
* sposta i dati tutti nel nuovo file
DBCC SHRINKFILE ('NomeVecchioFile', EMPTYFILE);
* rimuovi il vecchio file
ALTER DATABASE NomeDatabase REMOVE FILE NomeVecchioFile;

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

==sessione che usa il tempdb==
<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
r.status,
r.command,
(su.user_objects_alloc_page_count
+ su.internal_objects_alloc_page_count) * 8 / 1024 AS TempDB_MB,
st.text
FROM sys.dm_db_session_space_usage su
JOIN sys.dm_exec_sessions s
ON su.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE s.session_id > 50
ORDER BY TempDB_MB DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

Oracle RMAN

2026-06-18T10:45:10Z

WikiSysop:

==Query per job di backup==

<pre>
set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from v$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
v$BACKUP_SET_DETAILS d
join v$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from v$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time
/

</pre>

==Duplicate Database==

[[Oracle_Duplicate_Database|Duplicate Database]]

==Restore Point in time==
Restore point in time utilizando il catalogo, spfile presente, valorizzato <code>db_create_file_dest</code> diverso dal database sorgente:

startup nomount force;
run {
set dbid XXXXXXXXXX;
allocate channel c1 type disk;
set until time "to_date('2021-06-15:01:00:00','YYYY-MM-DD:hh24:mi:ss')";
restore controlfile from autobackup;
sql 'alter database mount';
sql 'alter database disable block change tracking';
set newname for database to new;
restore database;
switch datafile all;
recover database;
}

==Recupero schema da backup==
Istanza avviata con init o spfile:
processes=200
sga_target=10G
pga_aggregate_target=2G
db_block_size=8192
compatible="12.2.0"
log_archive_dest_1="location=+DG_ARCH"
db_files=1000
db_create_file_dest="+DG_DATA"
_system_trig_enabled=false
db_name="DBORA01"
db_unique_name="RECO01"
diagnostic_dest="/u01/app/oracle"
control_files="+DG_DATA/RECO01/controlfile/current.786.1075850995"

Da rman:

sql 'alter database mount clone database';
run {
set until time "to_date('2021-06-20:03:20:00','YYYY-MM-DD:HH24:MI:SS')";
set newname for database to new;
restore database skip forever tablespace USERS,TBS01,TBS02 ;
switch datafile all;
recover database skip forever tablespace USERS,TBS01,TBS02 ;
}

==backup archive==
per salvare gli ultimi archive generati, ma evitare salvataggi doppi e cancellazione degli archive dopo 1 giorno dalle generazione:

Sintassi testata su rman 10.2

backup archivelog all not backed up 1 times to destination "sbt_tape";
backup as backupset device type sbt archivelog all not backed up 1 times;
delete noprompt archivelog all backed up 1 times to device type sbt;
delete noprompt archivelog all completed before 'sysdate-1' backed up 1 times to device type sbt;

=Rman script windows=
==Template per multi retention==
<pre>
@echo off

for /f "skip=2" %%x in ('wmic Path Win32_LocalTime get Day^,DayOfWeek /Format:List') do set %%x >NUL

rem echo %Day% %DayOfWeek%

rem Sovrascrivi per prove
rem set Day=1
rem set DayOfWeek=1

rem echo %Day% %DayOfWeek%

set BCK=ORD
if %DayOfWeek% EQU 0 set BCK=LT180
if %DayOfWeek% EQU 0 if %Day% GTR 7 set BCK=LT35

echo %BCK%
(
echo spool log to %BCK%.log
echo list expired backup;
echo spool log off;
) > %BCK%.rman

set ORACLE_SID=XXXXXXX
set NLS_LANG=ITALIAN_ITALY.WE8MSWIN1252
set NLS_DATE_FORMAT=DD-MM-YYYY HH24:MI:SS

rman target / nocatalog cmdfile %BCK%.rman

</pre>

=Rman script linux=
==Versione multi retention==
<pre>
#!/bin/sh
# versione 12/09/2014 EG

DAYW=$( date +%w )
case $DAYW in
0)
LEVEL=" incremental level 0 "
DAY=$( date +%d )
if [ $DAY -gt 7 ]
then
TAG="LT35LEV0"
KEEP=" keep until time 'sysdate+35' "
else
TAG="LT180LEV0"
KEEP=" keep until time 'sysdate+180' "
fi
;;
*)
LEVEL=" incremental level 1 cumulative "
TAG="LEV1"
KEEP=""
;;
esac

NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'; export NLS_DATE_FORMAT
NLS_LANG='ITALIAN_ITALY.WE8ISO8859P15' ; export NLS_LANG

rman target / nocatalog log /tmp/rman_${ORACLE_SID}.log << EOF
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_${ORACLE_SID}.f';
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/asigra/libdsc_ora_sbt_Linux_x86_64.so,ENV=(CONNECT_INFO=/asigra/parms.txt)';
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 22 DAYS;

crosscheck archivelog all;
crosscheck backup device type sbt;
backup as compressed backupset
archivelog all not backed up 1 times
format 'ora.%d.%s.%p.%t.%u.arc.rman'
filesperset 20 delete all input;

delete noprompt archivelog all backed up 1 times to device type sbt;

backup as compressed backupset
${LEVEL} tag ${TAG} ${KEEP}
database
format 'ora.%d.%s.%p.%t.%u.rman'
filesperset 20
plus archivelog not backed up 1 times
format 'ora.%d.%s.%p.%t.%u.arc.rman'
filesperset 20 delete all input;

#delete noprompt archivelog until time 'sysdate -1' backed up 1 times to device type sbt;

delete noprompt obsolete device type sbt;
delete noprompt expired backup device type sbt;

ALLOCATE CHANNEL FOR MAINTENANCE TYPE DISK;
CROSSCHECK BACKUP DEVICE TYPE DISK;
#CROSSCHECK DATAFILECOPY ALL;
#DELETE NOPROMPT EXPIRED DATAFILECOPY ALL;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;
RELEASE CHANNEL;

EOF

</pre>

==Versione semplice con recovery window==
<pre>
#!/bin/bash
echo $PATH

PATH=$PATH:/usr/local/bin
export PATH

#(( LEV = $( date +%w ) > 0 ? 1 : 0 ))

DAYW=$( date +%w )
DAY=$( date +%d )
H=$( date +%H )
if [ $DAYW = 0 -a $H -le 3 ]
then
LEV=0
else
LEV=1
fi
NLS_LANG=ITALIAN_ITALY.UTF8 ; export NLS_LANG
NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'

DST=/tmp
LEVEL="incremental level ${LEV}"
TAG='tag TAG00'

ORACLE_SID=$1 ; export ORACLE_SID
ORAENV_ASK=NO ; export ORAENV_ASK
. oraenv
unset ORAENV_ASK

## 6 ore 600 * 72

if lockfile -60 -r 72 /tmp/backup_rman_${ORACLE_SID}.LCK
then

rman target / nocatalog trace ${DST}/${ORACLE_SID}_rman_${DAY}_${H}.log << EOF
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${DST}/snapcf_${ORACLE_SID}.f';
CONFIGURE DEFAULT DEVICE TYPE TO SBT;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/asigra/libdsc_ora_sbt_Linux_x86_64.so,ENV=(CONNECT_INFO=/asigra/parms_${ORACLE_SID}.txt)';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 21 DAYS;

crosscheck archivelog all;
crosscheck backup device type sbt;
backup as compressed backupset device type sbt
${LEVEL} ${TAG}
database
format 'ora.%d.%s.%p.%t.%u.db.rman'
filesperset 20
include current controlfile
spfile
format 'ora.%d.%s.%p.%t.%u.spfile.rman'
plus archivelog not backed up 1 times
format 'ora.%d.%s.%p.%t.%u.arc.rman'
filesperset 20 delete all input;

delete noprompt archivelog all backed up 1 times to device type sbt;

delete noprompt expired backup;
delete noprompt obsolete;

EOF

rm -f /tmp/backup_rman_${ORACLE_SID}.LCK
fi

</pre>

[[Categoria:Database]]
[[Categoria:Oracle]]

MSSQL backup e restore

2026-06-17T12:19:49Z

WikiSysop: /* salvataggio */

==salvataggio==
Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Eventualmente modificarlo:
ALTER DATABASE <database_name> SET RECOVERY FULL
go

Salvataggio
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Per per salvare solo il transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, FORMAT, COMPRESSION
go

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
go

Con versioni precedenti alla 2019:
DBCC SQLPERF(LOGSPACE);
go

Riferimenti: [https://learn.microsoft.com/it-it/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver17]

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T12:18:19Z

WikiSysop:

==salvataggio==
Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Eventualmente modificarlo:
ALTER DATABASE <database_name> SET RECOVERY FULL
go

Salvataggio
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Per per salvare solo il transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, FORMAT, COMPRESSION
go

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
go

Con versioni precedenti alla 2019:
DBCC SQLPERF(LOGSPACE);
go

[https://learn.microsoft.com/it-it/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver17]

Riferimenti: [https://learn.microsoft.com/it-it/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver17|RESTORE]

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T12:17:42Z

WikiSysop:

==salvataggio==
Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Eventualmente modificarlo:
ALTER DATABASE <database_name> SET RECOVERY FULL
go

Salvataggio
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Per per salvare solo il transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, FORMAT, COMPRESSION
go

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
go

Con versioni precedenti alla 2019:
DBCC SQLPERF(LOGSPACE);
go

[https://learn.microsoft.com/it-it/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver17]

Riferimenti: [RESTORE|https://learn.microsoft.com/it-it/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver17]

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T12:15:11Z

WikiSysop: /* salvataggio */

==salvataggio==
Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Eventualmente modificarlo:
ALTER DATABASE <database_name> SET RECOVERY FULL
go

Salvataggio
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Per per salvare solo il transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, FORMAT, COMPRESSION
go

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
go

Con versioni precedenti alla 2019:
DBCC SQLPERF(LOGSPACE);
go

Riferimenti: [RESTORE|https://learn.microsoft.com/it-it/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver17]

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T12:14:56Z

WikiSysop: /* salvataggio */

==salvataggio==
Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Eventualmente modificarlo:
ALTER DATABASE <database_name> SET RECOVERY FULL
go

Salvataggio
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Per per salvare solo il transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, FORMAT, COMPRESSION
go

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
go

Con versioni precedenti alla 2019:
DBCC SQLPERF(LOGSPACE);
go

Riferimenti: [RESTORE | https://learn.microsoft.com/it-it/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver17]

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:54:00Z

WikiSysop: /* salvataggio */

==salvataggio==
Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Eventualmente modificarlo:
ALTER DATABASE <database_name> SET RECOVERY FULL
go

Salvataggio
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Per per salvare solo il transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, FORMAT, COMPRESSION
go

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
go

Con versioni precedenti alla 2019:
DBCC SQLPERF(LOGSPACE);
go

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:53:19Z

WikiSysop: /* salvataggio */

==salvataggio==
Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Eventualmente modificarlo:
ALTER DATABASE <database_name> SET RECOVERY FULL

Salvataggio
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Per per salvare solo il transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, FORMAT, COMPRESSION
GO

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni precedenti alla 2019:
DBCC SQLPERF(LOGSPACE);
GO

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:50:39Z

WikiSysop: /* salvataggio */

==salvataggio==
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Per backup del solo transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, FORMAT, COMPRESSION
GO

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni per 2019:
DBCC SQLPERF(LOGSPACE);
GO

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:49:51Z

WikiSysop: /* backup */

==salvataggio==
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Per backup del solo transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, COMPRESSION
GO

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni per 2019:
DBCC SQLPERF(LOGSPACE);
GO

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:49:39Z

WikiSysop: /* restore */

==backup==
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Per backup del solo transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, COMPRESSION
GO

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni per 2019:
DBCC SQLPERF(LOGSPACE);
GO

==ripristino==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:49:24Z

WikiSysop: /* ripristino da completo. incrementali e transaction log */

==backup==
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Per backup del solo transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, COMPRESSION
GO

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni per 2019:
DBCC SQLPERF(LOGSPACE);
GO

==restore==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo, incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:49:14Z

WikiSysop: /* ripristino da full incrementali e transaction log */

==backup==
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Per backup del solo transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, COMPRESSION
GO

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni per 2019:
DBCC SQLPERF(LOGSPACE);
GO

==restore==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da completo. incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:48:54Z

WikiSysop: /* backup full incremental e transaction log */

==backup==
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Per backup del solo transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, COMPRESSION
GO

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni per 2019:
DBCC SQLPERF(LOGSPACE);
GO

==restore==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===ripristino da full incrementali e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:46:48Z

WikiSysop: /* con spostamento di percorso */

==backup==
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Per backup del solo transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, COMPRESSION
GO

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni per 2019:
DBCC SQLPERF(LOGSPACE);
GO

==restore==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===backup full incremental e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL backup e restore

2026-06-17T10:45:41Z

WikiSysop: /* caso semplice */

==backup==
backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Verificare il Recovery Model del database:
SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Per backup del solo transaction log:
BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, COMPRESSION
GO

Per verificare svuotamento del transaction log:
SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni per 2019:
DBCC SQLPERF(LOGSPACE);
GO

==restore==
===caso semplice===
Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare
RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

===con spostamento di percorso===
RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE Miodatabase
FROM DISK = '/backup/MioDatabase.bak';
WITH RECOVERY,
MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

===backup full incremental e transaction log===
Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:
RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione <code>recovery</code>:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:
restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

==post restore==

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO
Eventualmente cambiare il compatibility level
select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

Postgres Memoria

2026-06-17T10:38:38Z

WikiSysop: /* memoria condivisa */

=parametri di memoria=

===memoria privata per processo===
Ogni sessione alloca la sua work_mem. Tenere conto del parametro max_connection
alter system set work_mem='32MB';
Memoria allocata solo in caso di operazioni di manutenzione (create index, vacuumdb, etc)
alter system set maintenance_work_mem='1GB';
Per gli oggetti temporanei
alter system set temp_buffers='32MB';

===memoria condivisa===
Allocare circa 1/4 della memoria di sistema.
alter system set shared_buffers='8GB';

Log Buffer
alter system set wal_buffers
alter system set commit_log

===altri parametri===
Impostare 1/2 o 3/4 della ram del server. Non alloca memoria, è un parametro dell'ottimizzatore, che tiene conto di quanta memoria usa il sistema perativo come buffer per i filesystem.
alter system set effective_cache_size='12GB';

===riferimento===
https://severalnines.com/database-blog/architecture-and-tuning-memory-postgresql-databases

[[Categoria:Database]]
[[Categoria:Postgres]]

Postgres Memoria

2026-06-16T20:32:54Z

WikiSysop: /* memoria privata per processo */

=parametri di memoria=

===memoria privata per processo===
Ogni sessione alloca la sua work_mem. Tenere conto del parametro max_connection
alter system set work_mem='32MB';
Memoria allocata solo in caso di operazioni di manutenzione (create index, vacuumdb, etc)
alter system set maintenance_work_mem='1GB';
Per gli oggetti temporanei
alter system set temp_buffers='32MB';

===memoria condivisa===
Allocare circa 1/4 della memoria di sistema.
alter system set shared_buffers='4GB';

Log Buffer
alter system set wal_buffers
alter system set commit_log

===altri parametri===
Impostare 1/2 o 3/4 della ram del server. Non alloca memoria, è un parametro dell'ottimizzatore, che tiene conto di quanta memoria usa il sistema perativo come buffer per i filesystem.
alter system set effective_cache_size='12GB';

===riferimento===
https://severalnines.com/database-blog/architecture-and-tuning-memory-postgresql-databases

[[Categoria:Database]]
[[Categoria:Postgres]]

Postgres Memoria

2026-06-16T20:31:51Z

WikiSysop: /* altri parametri */

=parametri di memoria=

===memoria privata per processo===
Ogni sessione alloca la sua work_mem. Tenere conto del parametro max_connection
alter system set work_mem='32MB';
Memoria allocata solo in caso di operazioni di manutenzione (create index, vacuumdb, etc)
alter system set maintenance_work_mem='1GB';

alter system set temp_buffers='';

===memoria condivisa===
Allocare circa 1/4 della memoria di sistema.
alter system set shared_buffers='4GB';

Log Buffer
alter system set wal_buffers
alter system set commit_log

===altri parametri===
Impostare 1/2 o 3/4 della ram del server. Non alloca memoria, è un parametro dell'ottimizzatore, che tiene conto di quanta memoria usa il sistema perativo come buffer per i filesystem.
alter system set effective_cache_size='12GB';

===riferimento===
https://severalnines.com/database-blog/architecture-and-tuning-memory-postgresql-databases

[[Categoria:Database]]
[[Categoria:Postgres]]

Postgres Memoria

2026-06-16T20:28:01Z

WikiSysop: /* memoria privata per processo */

=parametri di memoria=

===memoria privata per processo===
Ogni sessione alloca la sua work_mem. Tenere conto del parametro max_connection
alter system set work_mem='32MB';
Memoria allocata solo in caso di operazioni di manutenzione (create index, vacuumdb, etc)
alter system set maintenance_work_mem='1GB';

alter system set temp_buffers='';

===memoria condivisa===
Allocare circa 1/4 della memoria di sistema.
alter system set shared_buffers='4GB';

Log Buffer
alter system set wal_buffers
alter system set commit_log

===altri parametri===
Impostare 1/2 o 3/4 della ram del server. Non alloca memoria, è un parametro limite.
alter system set effective_cache_size='12GB';

===riferimento===
https://severalnines.com/database-blog/architecture-and-tuning-memory-postgresql-databases

[[Categoria:Database]]
[[Categoria:Postgres]]

Postgres Memoria

2026-06-16T20:26:55Z

WikiSysop: /* parametri di memoria */

=parametri di memoria=

===memoria privata per processo===
Ogni sessione alloca la sua work:mem
alter system set work_mem='32MB';
Memoria allocata solo in caso di operazioni di manutenzione (create index, vacuumdb, etc)
alter system set maintenance_work_mem='1GB';

alter system set temp_buffers='';

===memoria condivisa===
Allocare circa 1/4 della memoria di sistema.
alter system set shared_buffers='4GB';

Log Buffer
alter system set wal_buffers
alter system set commit_log

===altri parametri===
Impostare 1/2 o 3/4 della ram del server. Non alloca memoria, è un parametro limite.
alter system set effective_cache_size='12GB';

===riferimento===
https://severalnines.com/database-blog/architecture-and-tuning-memory-postgresql-databases

[[Categoria:Database]]
[[Categoria:Postgres]]

Postgres Dump

2026-06-16T15:21:59Z

WikiSysop:

Postgres Utenti

2026-06-16T15:17:41Z

WikiSysop:

=esportare utenti=
pg_dumpall --globals-only -U postgres -f roles.sql

oppure
pg_dumpall --roles-only -U postgres -f roles.sql

==superuser==
create role pgadmin with encrypted password 'XXXYYYYZZZZZ';
alter role pgadmin with login;
grant all privileges on database postgres to pgadmin;
alter role pgadmin with createdb;
alter role pgadmin with createrole;
alter role pgadmin with superuser;

==utenti sola lettura==

create group pasticcio_ro;
grant connect on database pasticci_db to pasticcio_ro;
grant usage on schema pasticci_schema to pasticcio_ro;
grant select on all tables in schema pasticci_schema to pasticcio_ro;
alter default privileges in schema pasticci_schema grant select on tables to group pasticcio_ro;

create role ciccio_pasticcio with login encrypted password 'XXXYYYYZZZZZ';
alter group pasticcio_ro add user ciccio_pasticcio;
alter role ciccio_pasticcio set search_path='pasticci_schema';

==utenti proprietari==
create role pasticcio_app with login encrypted password 'XXXYYYYZZZZZ';
grant all privileges on database pasticci_db to pasticcio_app;
alter user pasticcio_app set search_path='pasticci_schema';

[[Categoria:Database]]
[[Categoria:Postgres]]

Postgres Dump

2026-06-16T10:52:13Z

WikiSysop: Creata pagina con " Categoria:Database Categoria:Postgres"

[[Categoria:Database]]
[[Categoria:Postgres]]

Postgres Spazio Disco

2026-06-16T10:51:17Z

WikiSysop:

SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT datname AS "Database",pg_size_pretty(pg_database_size(datname)) AS "Dimensione" FROM pg_database;

=Tabelle=
SELECT relname AS "Tabella",
pg_size_pretty(pg_total_relation_size(relid)) AS "Dimensione Totale",
pg_size_pretty(pg_relation_size(relid)) AS "Dimensione Dati"
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

[[Categoria:Database]]
[[Categoria:Postgres]]

Postgres Spazio Disco

2026-06-16T10:46:52Z

WikiSysop: Creata pagina con " SELECT pg_size_pretty(pg_database_size(current_database())); SELECT datname AS "Database",pg_size_pretty(pg_database_size(datname)) AS "Dimensione" FROM pg_database; Categoria:Database Categoria:Postgres"

MSSQL varie

2026-06-16T08:56:40Z

WikiSysop: /* metodo online */

MSSQL varie

2026-06-16T08:54:38Z

WikiSysop: /* spostamento datafiles */

==ricalcolo statistiche per database==
use MioDB
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
===metodo detach attach===
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

===metodo rename file===
* elencare files e percorsi:
SELECT name,physical_name,state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MioDB');
* Mettere offline il database:
ALTER DATABASE MioDb SET OFFLINE;
GO
Se serve forzare transazioni in corso:
ALTER DATABASE MioDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
* Spostare o copiare i datafile
mv /vecchio_percorso/miodb01.mdf /nuovo_percorso/
mv /vecchio_percorso/miodb01_log.ldf /nuovo_percorso/
* rinominare i files nel DB
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_DATA, FILENAME = '/nuovo_percorso/miodb01.mdf');
GO
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_LOG, FILENAME = '/nuovo_percorso/miodb01_log.ldf');
GO
* rimettere online il DB
ALTER DATABASE MioDB SET ONLINE;
GO
===metodo online===
* aggiungi un nuovo file
* sposta i dati tutti nel nuovo file
* rimuovi il vecchio file

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-16T08:52:23Z

WikiSysop: /* ricalcolo statistiche per database */

==ricalcolo statistiche per database==
use MioDB
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
===metodo detach attach===
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

===metodo rename file===
* elencare files e percorsi:
SELECT name,physical_name,state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MioDB');
* Mettere offline il database:
ALTER DATABASE MioDb SET OFFLINE;
GO
Se serve forzare transazioni in corso:
ALTER DATABASE MioDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
* Spostare o copiare i datafile
mv /vecchio_percorso/miodb01.mdf /nuovo_percorso/
mv /vecchio_percorso/miodb01_log.ldf /nuovo_percorso/
* rinominare i files nel DB
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_DATA, FILENAME = '/nuovo_percorso/miodb01.mdf');
GO
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_LOG, FILENAME = '/nuovo_percorso/miodb01_log.ldf');
GO
* rimettere online il DB
ALTER DATABASE MioDB SET ONLINE;
GO

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-16T08:50:23Z

WikiSysop: /* metodo rename file */

==ricalcolo statistiche per database==
use miudb
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
===metodo detach attach===
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

===metodo rename file===
* elencare files e percorsi:
SELECT name,physical_name,state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MioDB');
* Mettere offline il database:
ALTER DATABASE MioDb SET OFFLINE;
GO
Se serve forzare transazioni in corso:
ALTER DATABASE MioDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
* Spostare o copiare i datafile
mv /vecchio_percorso/miodb01.mdf /nuovo_percorso/
mv /vecchio_percorso/miodb01_log.ldf /nuovo_percorso/
* rinominare i files nel DB
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_DATA, FILENAME = '/nuovo_percorso/miodb01.mdf');
GO
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_LOG, FILENAME = '/nuovo_percorso/miodb01_log.ldf');
GO
* rimettere online il DB
ALTER DATABASE MioDB SET ONLINE;
GO

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-16T08:50:08Z

WikiSysop: /* metodo rename file */

==ricalcolo statistiche per database==
use miudb
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
===metodo detach attach===
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

===metodo rename file===
* elencare files e percorsi:
SELECT name,physical_name AS path,state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MioDB');
* Mettere offline il database:
ALTER DATABASE MioDb SET OFFLINE;
GO
Se serve forzare transazioni in corso:
ALTER DATABASE MioDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
* Spostare o copiare i datafile
mv /vecchio_percorso/miodb01.mdf /nuovo_percorso/
mv /vecchio_percorso/miodb01_log.ldf /nuovo_percorso/
* rinominare i files nel DB
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_DATA, FILENAME = '/nuovo_percorso/miodb01.mdf');
GO
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_LOG, FILENAME = '/nuovo_percorso/miodb01_log.ldf');
GO
* rimettere online il DB
ALTER DATABASE MioDB SET ONLINE;
GO

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-16T08:48:44Z

WikiSysop: /* metodo rename file */

==ricalcolo statistiche per database==
use miudb
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
===metodo detach attach===
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

===metodo rename file===
* elencare files e percorsi:
SELECT name,physical_name AS CurrentLocation,state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'MioDB');
* Mettere offline il database:
ALTER DATABASE MioDb SET OFFLINE;
GO
Se serve forzare transazioni in corso:
ALTER DATABASE MioDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
* Spostare o copiare i datafile
mv /vecchio_percorso/miodb01.mdf /nuovo_percorso/
mv /vecchio_percorso/miodb01_log.ldf /nuovo_percorso/
* rinominare i files nel DB
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_DATA, FILENAME = '/nuovo_percorso/miodb01.mdf');
GO
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_LOG, FILENAME = '/nuovo_percorso/miodb01_log.ldf');
GO
* rimettere online il DB
ALTER DATABASE MioDB SET ONLINE;
GO

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-16T08:46:44Z

WikiSysop: /* metodo rename file */

==ricalcolo statistiche per database==
use miudb
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
===metodo detach attach===
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

===metodo rename file===
* Mettere offline il database:
ALTER DATABASE MioDb SET OFFLINE;
GO
Se serve forzare transazioni in corso:
ALTER DATABASE MioDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
* Spostare o copiare i datafile
mv /vecchio_percorso/miodb01.mdf /nuovo_percorso/
mv /vecchio_percorso/miodb01_log.ldf /nuovo_percorso/
* rinominare i files nel DB
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_DATA, FILENAME = '/nuovo_percorso/miodb01.mdf');
GO
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_LOG, FILENAME = '/nuovo_percorso/miodb01_log.ldf');
GO
* rimettere online il DB
ALTER DATABASE MioDB SET ONLINE;
GO

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-16T08:45:39Z

WikiSysop: /* metodo rename file */

==ricalcolo statistiche per database==
use miudb
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
===metodo detach attach===
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

===metodo rename file===
* Mettere offline il database:
ALTER DATABASE MioDb SET OFFLINE;
GO
Se serve forzare transazioni in corso:
ALTER DATABASE MioDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
* Spostare o copiare i datafile
* rinominare i files nel DB
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_DATA, FILENAME = '/nuovo_percorso/miodb01.mdf');
GO
ALTER DATABASE MioDB MODIFY FILE (NAME = MioDB_LOG, FILENAME = '/nuovo_percorso/miodb01_log.ldf');
GO
* rimettere online il DB
ALTER DATABASE MioDB SET ONLINE;
GO

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-16T08:27:59Z

WikiSysop: /* spostamento datafiles */

==ricalcolo statistiche per database==
use miudb
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
===metodo detach attach===
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

===metodo rename file===

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-16T07:26:56Z

WikiSysop: /* spostamento datafiles */

==ricalcolo statistiche per database==
use miudb
go
EXEC sp_updatestats;
go

==Elenco files e spazio occupato==

SELECT
d.name AS DatabaseName,
f.name AS LogicalFileName,
f.physical_name AS PhysicalFilePath,
f.type_desc AS FileType,
cast(f.size*8.0/1024.0 as float) mb,
f.max_size,
f.growth
FROM
sys.master_files AS f
INNER JOIN
sys.databases AS d ON f.database_id = d.database_id
order by f.type_desc,f.size;

select type_desc,cast(sum(size)*(8.0/1024) as float) as mb,count(*) as num from sys.master_files group by type_desc

==shrink datafiles e log==

==spostamento datafiles==
Metti offline il database
ALTER DATABASE MioDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'MioDB';
GO

Copia o sposta i file verso il nuovo percorso.

Riattacca il database:
CREATE DATABASE MioDB
ON
(
FILENAME='/nuovo_storage/MioDB.mdf'
),
(
FILENAME='/nuovo_storage/MioDB_log.ldf'
)
FOR ATTACH;
GO

==sessioni bloccate==

SELECT
r.session_id AS [Sessione Bloccata],
r.blocking_session_id AS [ID Bloccante],
t.text AS [Query Bloccante],
w.wait_type AS [Tipo di Attesa],
w.wait_duration_ms AS [Durata Attesa (ms)]
FROM sys.dm_exec_requests r
JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

<pre>
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
r.start_time,
r.total_elapsed_time,
r.wait_type,
r.blocking_session_id,
st.text AS query_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 -- Filtra solo i processi utente (esclude quelli di sistema)
ORDER BY s.session_id;
</pre>

<pre>
SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'suspended'
ORDER BY r.total_elapsed_time DESC;
</pre>

[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-16T07:20:39Z

WikiSysop: /* sessioni bloccate */

MSSQL varie

2026-06-15T12:52:29Z

WikiSysop: /* sessioni bloccate */

MSSQL Esportare Utenze

2026-06-15T12:51:57Z

WikiSysop: /* Script TSQL */

==SSMS==
Export
==Powershell==
Installare dbatools:

Install-Module dbatools -Force

Esegue la copia:

Copy-DbaLogin -Source ServerOrigine -Destination ServerDestinazione

Oppure:

Start-DbaMigration -Source "VecchioServer" -Destination "NuovoServer" -MigrationType Logins

Per migrare anche linked server e job:

Start-DbaInstanceMigration -Source "VecchioServer" -Destination "NuovoServer"

==Script TSQL==

Singolo utente:

<pre>
SELECT 'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
MASTER.sys.fn_varbintohexstr(convert(varbinary(max),LOGINPROPERTY(name, 'PasswordHash'))) + ' HASHED,
DEFAULT_DATABASE = [' + default_database_name + '];'
FROM sys.server_principals
WHERE type = 'S' -- Tipo 'S' per utenti SQL, usa 'U' per utenti Windows
AND name = 'NomeUtente';
</pre>

Tutti gli utenti:

<pre>
SET NOCOUNT ON;

-- 1. Genera la creazione degli Utenti (mappati sui Login)
SELECT 'CREATE USER [' + dp.name + '] FOR LOGIN [' + l.name + ']'
+ CASE WHEN dp.default_schema_name IS NOT NULL THEN ' WITH DEFAULT_SCHEMA=[' + dp.default_schema_name + '];' ELSE ';' END AS [-- T-SQL Script]
FROM sys.database_principals dp
INNER JOIN sys.server_principals l ON dp.sid = l.sid
WHERE dp.type IN ('S', 'U', 'G') AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')

UNION ALL

-- 2. Genera l'associazione ai Ruoli del Database (es. db_owner, db_datareader)
SELECT 'ALTER ROLE [' + role_dp.name + '] ADD MEMBER [' + member_dp.name + '];'
FROM sys.database_role_members drm
INNER JOIN sys.database_principals role_dp ON drm.role_principal_id = role_dp.principal_id
INNER JOIN sys.database_principals member_dp ON drm.member_principal_id = member_dp.principal_id
WHERE member_dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')

UNION ALL

-- 3. Genera i Privilegi espliciti sugli oggetti (GRANT SELECT, EXECUTE, ecc.)
SELECT
dp.state_desc + ' ' + dp.permission_name + ' ON [' +
s.name + '].[' + o.name + '] TO [' + p.name + '];'
FROM sys.database_permissions dp
INNER JOIN sys.objects o ON dp.major_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals p ON dp.grantee_principal_id = p.principal_id
WHERE p.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');
</pre>

===Utenti Orfani===
<pre>
EXEC sp_MSforeachdb '
USE [?];
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
DECLARE @username NVARCHAR(255);
DECLARE user_cursor CURSOR FOR
SELECT dp.name
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN (''S'', ''U'')
AND sp.sid IS NULL
AND dp.name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'');

OPEN user_cursor;
FETCH NEXT FROM user_cursor INTO @username;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Tenta di rimappare l''utente al login omonimo sul nuovo server
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @username)
BEGIN
PRINT ''Riappeso utente orfano: '' + @username + '' nel database [?]'';
EXEC (''ALTER USER ['' + @username + ''] WITH LOGIN = ['' + @username + '']'');
END
FETCH NEXT FROM user_cursor INTO @username;
END

CLOSE user_cursor;
DEALLOCATE user_cursor;
END
';
</pre>
[[Categoria:Database]]
[[Categoria:MS SQL Server]]

MSSQL varie

2026-06-15T12:11:47Z

WikiSysop: /* sessioni bloccate */

MSSQL varie

2026-06-15T11:50:51Z

WikiSysop: /* spostamento datafiles */

MSSQL varie

2026-06-15T09:01:58Z

WikiSysop:

MSSQL varie

2026-06-15T08:57:26Z

WikiSysop: /* Elenco files e spazio occupato */