Backup dei DB in SqlServer

Tramite questo script è possibile eseguire il backup di tutti i databasi di SqlServer, meno quelli di sistema ovviamente.


DECLARE @name VARCHAR(50) -- Nome del Database
DECLARE @path VARCHAR(256) -- Cartella destinazione file di backup
DECLARE @fileName VARCHAR(256) -- Nome file di backup
DECLARE @fileDate VARCHAR(20) -- Data file di backup (accodata a @fileName)
DECLARE @SqlStatment VARCHAR(MAX) -- Statement T-SQL che esegue il backup

SET @path = 'F:\TEST\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
ORDER BY name

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Elaborazione backup ' + @name + ' in corso...' + CHAR(13)

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

SET @SqlStatment = 'BACKUP DATABASE ' + @name + ' TO DISK = ''' + @fileName +
''' WITH NOFORMAT, INIT, NAME = N''' + @name + ' - Completo Database Backup''' + ', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

EXEC (@SqlStatment)
PRINT CHAR(13) + 'Elaborazione backup ' + @name + ' terminata!' + CHAR(13)

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

ricavare tipo dei campi dal database

Per ricavare il tipo dei campi dal database basta eseguire la seguente procedura

public sub RicavaCampi()
Dim dtType As DataTable = getTables()
For Each row As DataRow In dtType.Rows
If Not row(2).Equals("sysdiagrams") Then
Dim res(3) As String
res(2) = row(2) 'nome della tabella
If res(2).Equals("Categories") Then
Dim dtCol As DataTable = _Connection.GetSchema("Columns", res)
If dtCol.Rows.Count > 0 Then
dim colName as string
dim colType as string
For Each col As DataRow In dtCol.Rows
colName = col("COLUMN_NAME")
colType = col("DATA_TYPE")
Next
End If
End If
End If
Next
end sub



Public Function getTables() As DataTable
_Connection.Open()
If Not IsNothing(_Connection) Then
Dim restriction(3) As String
restriction(3) = "BASE TABLE"
Dim dtTables As DataTable = _Connection.GetSchema("Tables", restriction)

Return dtTables
Else
Throw New Exception("Nessuna Connessione attiva...")
End If
_Connection.Close()
End Function