1

I create DB snapshot as one of the tasks in SSIS package. The package works with 20 different DBs. Thus, each DB will have a snapshot with hard-coded FILENAME attribute.

CREATE DATABASE Sales_snap ON  
( 
  NAME = SalesData, 
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData_snap.ss'
),  
( 
  NAME = SalesData1, 
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData1_snap.ss'
),  
( 
  NAME = SalesData2, 
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\mssql\data\SalesData2_snap.ss'
) 
AS SNAPSHOT OF Sales

Is there a way to use some default path for FILENAME instead of copy-pasting the same string 20 x 3 times? Alternatively, is there any way to specify base path only once and then reuse it in other SSIS tasks?

3
  • Is your SSIS package just creating db snapshots or doing more business logic ? There are better ways e.g. using dbatools to create snapshots or just use tsql - create dynamic script that will do it for you.
    – Kin Shah
    Feb 2 at 23:13
  • @KinShah DBs are supposed to be available all the time, including when the package is updating them. Snapshots are created to prevent any possible access interruptions to these DBs at the time of SSIS update, so snapshots are used only outside of SSIS package.
    – Anonymous
    Feb 2 at 23:29
  • Also, SSIS package is supposed to run every 15 minutes, so every 15 minutes new set of snapshots should be created. Meaning, it's not possible to use DB tools to create snapshot once and forget about it, it all needs to be done with quite high frequency.
    – Anonymous
    Feb 2 at 23:32
3

Dynamic SQL is probably your best bet.

See 3rd example here: https://www.sqlservercentral.com/scripts/create-database-snapshot-dynamically

0

I will add an answer alternative to dynamic sql -

use dbatools New-DbaDbSnapshot.

The beauty about using dbatools is that its flexible and you can do it for N number of servers or databases.

e.g.

create database snapshots for databases db1 and db2 with name suffix as foo_{0}_snap --> foo_db1_snap, foo_db2_snap etc and you can even use -Path parameter to specify the path that snapshot uses e.g your custom path that you want your snapshots to be created.

New-DbaDbSnapshot -SqlInstance sqlserver2019 -Database db1, db2 --NameSuffix 'foo_{0}_snap' -Path F:\snapshotpath

You can use below commands to better create a streamlined automation :

Get-DbaDbSnapshot
New-DbaDbSnapshot
Remove-DbaDbSnapshot
Restore-DbaDbSnapshot

Run dbatools cmdlets as sqlagent job or using task scheduler and you are done with just few lines of code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.