Gestione di un database Microsoft SQL Server
In questo articolo vediamo come si fa il backup ed il restore di un database Microsoft SQL Server.
Nel dettaglio io ho fatto le prove con un database Microsoft SQL Server 2008 (chiamato anche ms sql server 2008). L'articolo si concentra sul ripristino del database.
Il backup avrà step molto simili ma più semplici.
Vedremo anche come eseguire script particolarmente pesanti.
Risponderemo anche alla domanda: coma si fa a contare quante tabella ci sono in un db?
Script per la creazione di un DB
Vediamo uno script per poter creare un DB su SQL SERVER (testato sulla versione 2012):
USE [master]
GO
/****** Object: Database [MyDB] Script Date: 18/12/2017 11:45:57 ******/
CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB_DATA', FILENAME = N'C:\MyDB\MyDB_Data.mdf' , SIZE = 2510784KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),
FILEGROUP [MyDB_IDX]
( NAME = N'MyDB_IDX', FILENAME = N'C:\MyDB\MyDB_Idx.mdf' , SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),
FILEGROUP [MyDB_NTXT]
( NAME = N'MyDB_NTXT', FILENAME = N'C:\MyDB\MyDB_Ntxt.mdf' , SIZE = 208896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
LOG ON
( NAME = N'MyDB_LOG', FILENAME = N'C:\MyDB\MyDB_Log.ldf' , SIZE = 1741824KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
GO
ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MyDB].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MyDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MyDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MyDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MyDB] SET ARITHABORT OFF
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MyDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MyDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MyDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MyDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MyDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MyDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MyDB] SET DISABLE_BROKER
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE [MyDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MyDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MyDB] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE [MyDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MyDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [MyDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [MyDB] SET RECOVERY SIMPLE
GO
ALTER DATABASE [MyDB] SET MULTI_USER
GO
ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MyDB] SET DB_CHAINING OFF
GO
ALTER DATABASE [MyDB] SET READ_WRITE
GO
Una volta creato il DB si può anche procedere alsuo restore.
Restore a DB
Il restore di un database è una procedura molto utile sia per rispristinare un database, ad esempio corrotto, ma anche per importare su un altra macchina una copia di un database.
Questo articolo si concentra su quest'ultima procedura.
Per prima cosa occorre avere il database sul server. Anche se vuoto e senza tabella.
Ovviamente, occorre avere il backup del database principale.
A questo punto si deve fare click destro sul nome del database e scegliere la voce Tasks e poi la voce Restore ed infine la voce database.
Si apre a questo punto una finestra. Sulla destra scegliete la voce General e poi sulla sinistra la voce From Device.
Noi vogliamo fare la import del database utilizzando un file presente su file system.
Scegliendo la voce From Device si aprirà una finestra di dialogo che vi permetterà di aggiungere un nuovo file.
Voi dovete aggiungere il file contenente la copia di backup dell'altro database.
Fatto questo cliccate su OK.
Vi comparrirà una lista con l'elenco dei database che è possibile ripristinare in base al file indicato.
Cliccate quindi sul checkbox restore del database che vi interessa.
Sulla sinistra ora scegliete il tab options.
Sulla sinistra selezionate l'opzione Overwrite the existing database.
Sotto troverete una griglia con indicato il file del database che state importando e sovrascrivendo ed il suo file di log. Questi due nomi e questi due path vengono letti dal file che avete indicato. Dovete quindi controllare il match con i vostri da sovrascrivere.
Fatto questo non vi rimane che selezionare OK ed il gioco è fatto.
Le prove da me fatte sono state condotte su database Microsoft SQL Server 2008, ma sul database SQL Server 2005 funzionano lo stesso. Sulle precedenti versioni il meccanismo dovrebbe essere molto simile.
Eseguire script pesanti
Qui il termine pesante sta ad indicare il peso in MB (MegaBytes) dello script. A volte, per effettuare un restore di un database, o per importare su un secondo database l'export di un database fatto altrove, è necessario eseguire script pesanti. L'unico modo per eseguirli è a riga di comando nel seguente modo:
sqlcmd -S <host> -U <user name> -P <password> -i <path to file>\mySqlScript.sql
Modalità Restricted User
Quando si eseguono determinate attività, come quella di restore, è molto conveniente mettere il DB in madalità "Singolo Utente".
In questa maniera siamo sicuri che non ci siano "troppi" utenti collegati che possano creare problemi nella fase di ripristino.
Si può quindi impostare il database così:
ALTER DATABASE database-name SET RESTRICTED_USER;
E alla fine ripristinare il tutto con
ALTER DATABASE database-name SET MULTI_USER;
Una guida più dettagliata la trovate qui.
Assign a login to a user created without login
Gli utenti di un database possono o no avere la possibilità di loggarsi direttamente al loro database.
Ma come si fa a cambiare tale caratterestica? Ovvero, se noi abbiamo un utente "sql user without login" ma vogliamo dargli la possibilità di loggarsi che query dobbiamo usare?
use [MyDB];
alter user myUser with login = myUser;
Queste sono le istruzioni da eseguire!
Contare le tabelle presenti nel DataBase
A volte è un numero che ci può servire e se non vogliamo andare a contare manualmente le tabelle ecco la query che fa al caso nostro:
USE <MyDatabase>
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Query per conoscere le colonne con Always Encrypted sul DB
La seguente query estrae l'elenco delle tabelle e delle colonne che hanno abilitato l'Always Encrypted:
select
t.name as [Table],
c.name as [Column],
c.encryption_type_desc
from
sys.all_columns c inner join
sys.tables t on c.object_id = t.object_id
where
c.encryption_type is not null
order by
t.name,
c.name
Query per trovare le connesioni attive
La query per avere il numero di connessioni totali (1):
Questa query seleziona il numero di connessioni a un database SQL Server, conteggiando le righe della colonna "dbid" nella tabella "sys.sysprocesses" dove "dbid" è maggiore di zero. Il risultato viene etichettato come "TotalConnections".
La query restituisce le connessioni disponibili (2):
Questa query fa le seguenti operazioni in SQL Server:
Dichiarazione di una variabile di tabella temporanea chiamata "@SPWHO1", che contiene tre colonne: "DBName", "NoOfAvailableConnections" e "LoginName".
Inserimento di dati nella tabella temporanea "@SPWHO1" utilizzando una query SELECT-GROUP BY che seleziona il nome del database (da "dbid"), il numero di connessioni disponibili (conteggiando le righe di "dbid") e il nome utente di accesso (da "loginame"). I dati vengono raggruppati per "dbid" e "loginame".
Selezione di tutte le righe nella tabella temporanea "@SPWHO1" dove la colonna "DBName" è uguale al valore passato come parametro @dbName.
In sintesi, questa query seleziona informazioni sulle connessioni a un database SQL Server, filtrandole per nome del database specifico (@dbName). Il risultato viene restituito in una tabella temporanea con informazioni sul nome del database, sul numero di connessioni disponibili e sul nome utente.
La query restituisce le connessioni in running (3):
Questa query fa le seguenti operazioni in SQL Server:
Dichiarazione di una variabile di tabella temporanea chiamata "@SPWHO2", che contiene dodici colonne: "SPID", "Status", "Login", "HostName", "BlkBy", "DBName", "Command", "CPUTime", "DiskIO", "LastBatch", "ProgramName" e "Request".
Inserimento di dati nella tabella temporanea "@SPWHO2" eseguendo la stored procedure di sistema "sp_who2" con l'argomento "Active". Questa stored procedure restituisce informazioni sulle sessioni attive in un istanza di SQL Server.
Selezione di tutte le righe nella tabella temporanea "@SPWHO2" dove la colonna "DBName" è uguale al valore passato come parametro @dbName.
In sintesi, questa query esegue la stored procedure "sp_who2" per selezionare informazioni sulle sessioni attive in un'istanza di SQL Server e filtra i risultati per il nome del database specifico (@dbName). Il risultato viene restituito in una tabella temporanea con informazioni sulle sessioni, tra cui lo stato, il nome utente di accesso, il nome dell'host, il nome del database, il comando in esecuzione, il tempo di CPU, l'I/O su disco e altro.
Declare @dbName varchar(150) set @dbName = 'MyDatabase'
--(1) Total machine connections
SELECT COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0
--(2) Available connections
DECLARE @SPWHO1 TABLE (DBName VARCHAR(1000) NULL, NoOfAvailableConnections VARCHAR(1000) NULL, LoginName VARCHAR(1000) NULL)INSERT INTO @SPWHO1 SELECT db_name(dbid), count(dbid), loginame FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame
SELECT * FROM @SPWHO1 WHERE DBName = @dbName
--(3) Running connections
DECLARE @SPWHO2 TABLE (SPID VARCHAR(1000), [Status] VARCHAR(1000) NULL, [Login] VARCHAR(1000) NULL, HostName VARCHAR(1000) NULL, BlkBy VARCHAR(1000) NULL, DBName VARCHAR(1000) NULL, Command VARCHAR(1000) NULL, CPUTime VARCHAR(1000) NULL, DiskIO VARCHAR(1000) NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 VARCHAR(1000) NULL, Request VARCHAR(1000) NULL) INSERT INTO @SPWHO2 EXEC sp_who2 'Active' SELECT * FROM @SPWHO2 WHERE DBName = @dbName
Cercare nei trigger sql server
SELECT
o.name AS NomeOggetto,
t.name AS NomeTabella,
tr.name AS NomeTrigger,
tr.type_desc AS TipoTrigger
FROM sys.objects o
INNER JOIN sys.triggers tr ON o.object_id = tr.parent_id
INNER JOIN sys.tables t ON tr.parent_id = t.object_id
WHERE t.name = 'SysPortalLanguages'
Cercare nelle stored
SELECT
OBJECT_NAME(object_id) AS NomeStoredProcedure,
* -- Puoi aggiungere altre colonne per avere più informazioni (es. schema_name, create_date, ecc.)
FROM sys.sql_modules
WHERE definition LIKE '%PIPPO%'
Selezionare la dimensione massima di una colonna ed il suo contenuto
SELECT TOP 1 *, LEN(ContentData) AS Lenght FROM MyTable ORDER BY LEN(ContentData) DESC
Buon lavoro!