Search code examples
sql-serverazure-devopsdatabase-backups

Backup SQL DB without data in release pipeline

How to take backup of SQL DB without data in release pipeline (Azure Devops/VSTS). And I have to store it on shared path. The DB is in on-premise server

Update:

After using the below command in "Command Line Scripts" task of release pipeline,

"C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\sqlpackage.exe" /sourceconnectionstring:"Data Source=ServerName;Initial Catalog=DBName;Integrated Security=True" /targetFile:E:\Test.dacpac /action:extract /p:ExtractAllTableData=false /p:IgnoreUserLoginMappings=true /p:IgnorePermissions=true /p:Storage=Memory

I am getting the error as Login failed for user 'XXXXXX'. Below is the detailed log.

enter image description here

Thanks.

Solution

  • Backup SQL DB without data

    If what you want is only back up the definitions of the database objects and their related instance-level elements, which include like tables, stored procedures, views and users. Recommend you generate a DAC package file by calling sqlpackage.exe.

    A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects - like tables, views, and instance objects, including logins - associated with a user's database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.

    To generate a DAC package file, one is using Extract Data-tier Application Wizard but could not achieve in VSTS. Here you can use script in VSTS release pipeline to achieve this generated.

    In release pipeline:

    (1) First steps is apply below script into powershell/command task:

    sqlpackage.exe /a:extract /scs:"server=localhost;database=MerlinTbData;trusted_connection=true" /tf:"C:\SQL Server Management Studio\DAC Packages\AdventureWorks2012.dacpac"

    equal with

    sqlpackage /Action:Extract /SourceServerName:localhost /SourceDatabaseName:"MerlinTbData"  /TargetFile:"C:\SQL Server Management Studio\DAC Packages\MerlinTbData.dacpac"

    In this script, it will save the DAC package file into local file system. But since what you want is let it store at a shared folder. So:

    (2) The next step is using Windows machine file copy task to copy this DAC package file to the shared folder.

    Also, you can continue to use command script to achieve this copy.

    Update:

    Please use this below script which contain your username and password:

    SqlPackage /Action:Extract /SourceServerName:"*********"
 /SourceDatabaseName:"*****" /TargetFile:"*****" /SourceUser:"{SQLuser}" /SourcePassword:"{SQLPassword}"