Benedikt Schackenberg

I found this script to create a database snapshot with multiple filegroups/fils, very usefully!  many thx to Ravikanth

$computerName = "YOURDBINSTANCE"

$dbName = "YOURDB"

$snapshotName = "YOURSNAPSHOTNAME"            

#Load all SQL necesssary assemblies

#this is a common section in all my SQL scripts

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMo") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMoExtended") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMoEnum") | Out-Null

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null            

#Get the server and database objects

$server = New-Object Microsoft.SqlServer.Management.Smo.Server($computerName)

$database = $server.Databases[$DBName]            

#Define a snapshot database and give the same name as the base databse

$snapshot = New-Object ("Microsoft.SqlServer.Management.Smo.Database") ($computerName,$snapshotName)

$snapshot.DatabaseSnapshotBaseName=$database.Name            

#Add all filegroups from the base database to snapshot

foreach ($filegroup in $database.FileGroups) {

$newfg = New-Object ("Microsoft.SqlServer.Management.Smo.FileGroup") ($snapshot,$filegroup.Name)

$snapshot.FileGroups.Add($newfg)

}            

#Add all datafiles from each filegroup of the base database to snapshot with a new datafile name

#in this example, I am using .ss as the datafile extension for the snapshot file

#By default, the snapshot files will reside in the same folder as base DB data files

foreach ($filegroup in $database.FileGroups) {

foreach ($datafile in $filegroup.Files) {

  $newDataFile = New-Object ("Microsoft.SqlServer.Management.Smo.DataFile") ($snapshot.FileGroups[$filegroup.Name],$datafile.Name,"$($database.PrimaryFilePath)\$($datafile.Name).ss")

  $newDataFile.FileName

  $snapshot.FileGroups[$filegroup.Name].Files.Add($newDataFile)

}

}            

#Create the snapshot

#This is it

$snapshot.Create()