By: Angel Gomez | Comments (16) | Related: > PowerShell
Problem
As a DBA, you must control the objects that exist in the databases for your environment. In an ideal environment, no one would have permissions to modify a table, a stored procedure, or a view, but the reality is that's not the case and changes are made without the DBA always knowing. Because of this it would be great to have previous versions of code, so you can always check to see if something has changed. You could restore an old database and compare objects, but having the objects scripted would be easier to access the code.
Solution
Using PowerShell and SQL Server Agent we can create a scheduled job that runs each day and produces scripts for all objects in all databases for an instance of SQL Server and that is what this tip does.
Here is the PowerShell code to generate a script for each object in the database. The below code will script out table definitions, stored procedures, views, user defined functions and triggers. This will generate scripts for every database in the SQL Server instance.
You need to supply the SQL Server name and the path where the objects are to be created.
$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
}
}
}
}
When the PowerShell code is run, it will create the folder and all of the subfolders for the objects like the following. Each folder will contain the objects for that database.
In addition, to help keep the file system clean we can create another step to delete older files and folders.
Here is PowerShell script. You need to specify the limit value of how many days you want to keep and the path which should be the same path as above.
$limit = (Get-Date).AddDays(-7)
$path = "c:\SQL_Server\Backup\Objects"
# Delete files older than the $limit.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Name.EndsWith("sql") } | Remove-Item -Force
# Delete any empty directories left behind after deleting the old files.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { $_.PSIsContainer -and (Get-ChildItem -Path $_.FullName -Recurse -Force | Where-Object { !$_.PSIsContainer }) -eq $null } | Remove-Item -Force -Recurse',
To pull this all together, we can create a SQL Server Agent Job to run each day.
Here is the script to create the SQL Server Agent Job. Remember to update the values to match your environment, like the ServerName, Path, etc. Also, the below script uses a SQL Agent proxy account named "dba". This is used to grant file permissions in the file system to generate the scripts.
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Backup' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Backup'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Maint_BACKUP_DB_OBJECTS',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No Description',
@category_name=N'Backup',
@owner_login_name=N'sa',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [BACKUP_OBJECT] Script Date: 22/11/2016 15:37:07 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BACKUP_OBJECT',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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 haven4t a default 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',
@flags=0,
@proxy_name=N'dba'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [DELETE] Script Date: 22/11/2016 15:37:07 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DELETE',
@step_id=2,
@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'$limit = (Get-Date).AddDays(-7)
$path = "c:\SQL_Server\Backup\Objects"
# Delete files older than the $limit.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Name.EndsWith("sql") } | Remove-Item -Force
# Delete any empty directories left behind after deleting the old files.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { $_.PSIsContainer -and (Get-ChildItem -Path $_.FullName -Recurse -Force | Where-Object { !$_.PSIsContainer }) -eq $null } | Remove-Item -Force -Recurse',
@database_name=N'master',
@flags=0,
@proxy_name=N'dba'
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_jobschedule @job_id=@jobId, @name=N'EveryDay',
@enabled=1,
@freq_type=8,
@freq_interval=62,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20160126,
@active_end_date=99991231,
@active_start_time=210000,
@active_end_time=235959,
@schedule_uid=N'9ce8b816-3403-4111-9d5d-e8af5c553473'
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
Next Steps
- Check out these other PowerShell tips
- Check out these other tips
About the author
Angel Gomez is the head of SQL Server Database Administration and works with more than 1000 databases and 15TB of data.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
