CODEWITHSUNDEEP
sql-serverdatabase-backupsbackup-sqldatabase
Krishna Chaitanya
Krishna Chaitanya

Reputation: 65

Automate SQL Server database schema only Backups

I need to backup SQL Server schema of all databases on a daily basis. This way I don't have to restore the whole 700GB database for a single table structure.

Any help is appreciated.

Thanks

Upvotes: 0

Views: 324

Answers (1)

Piotr Palka
Piotr Palka

Reputation: 3159

You can extract a .dacpac file and it will contain definition of all object in the database. Then you will use it in Visual Studio schema compare to find changed objects.
Obfuscated ("encrypted") stored procedures will not be included in the extract.
Sample script:

## Set a SMO Server object to the default instance on the local computer.  
CD SQLSERVER:\SQL\localhost\DEFAULT  
$srv = get-item .  

## Specify the database to extract to a DAC.  
$dbname = "MyDB"  

## Specify the DAC metadata.  
$applicationname = "MyApplication"  
$version = "1.0.0.0"  
$description = "This DAC defines the database used by my application."  

## Specify the location and name for the extracted DAC package.  
$dacpacPath = "C:\MyDACs\MyApplication.dacpac"  

## Extract the DAC.  
$extractionunit = New-Object Microsoft.SqlServer.Management.Dac.DacExtractionUnit($srv, $dbname, $applicationname, $version)  
$extractionunit.Description = $description  
$extractionunit.Extract($dacpacPath)

Upvotes: 0

Related Questions