Job que gera o script de criação de estrutura de todos os bancos, procedures, functions etc. No servidor
Isso pode ser usado para quando você precisa montar e ter a estrutura de todo o ambiente.
Por padrão ele vai salvar pastas com o nome dos bancos e subpastas como as tabelas, procedures, funções, etc organizando essa estrutura com nomes separados para cada item do banco nessas subpastas.
USE [msdb]
GO
/****** Object: Job [DBA - Gerar Script de Criação de Todos os Bancos e Objetos] Script Date: 28/03/2023 16:33:56 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 28/03/2023 16:33:56 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA - Gerar Script de Criação de Todos os Bancos e Objetos',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Esse job tem como objetivo gerar o script de todos os bancos com todos os objetos envolvidos',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Gerar script de todos os bancos] Script Date: 28/03/2023 16:33:56 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Gerar script de todos os bancos',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'$date_ = (date -f yyyyMMdd)
$ServerName = "." #If you have a named instance, you should put the name.
$path = "c:\SQL_Server\Backup\Objects\"+"$date_"
[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'')
$serverInstance = New-Object (''Microsoft.SqlServer.Management.Smo.Server'') $ServerName
$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup.
$ExcludeSchemas = @("sys","Information_Schema")
$so = new-object (''Microsoft.SqlServer.Management.Smo.ScriptingOptions'')
$dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases.
foreach ($db in $dbs)
{
$dbname = "$db".replace("[","").replace("]","")
$dbpath = "$path"+ "\"+"$dbname" + "\"
if ( !(Test-Path $dbpath))
{$null=new-item -type directory -name "$dbname"-path "$path"}
foreach ($Type in $IncludeTypes)
{
$objpath = "$dbpath" + "$Type" + "\"
if ( !(Test-Path $objpath))
{$null=new-item -type directory -name "$Type"-path "$dbpath"}
foreach ($objs in $db.$Type)
{
If ($ExcludeSchemas -notcontains $objs.Schema )
{
$ObjName = "$objs".replace("[","").replace("]","")
$OutFile = "$objpath" + "$ObjName" + ".sql"
$objs.Script($so)+"GO" | out-File $OutFile
}
}
}
}',
@database_name=N'master',
@output_file_name=N'C:\Temp\Gerar script de todos os bancos.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Comentarios