A simple powershell script to copy tables to another database

I’ve finally started using SQL 2008 Reporting Services in earnest to provide access to all that useful data in the FIM Metaverse. Like a good girl I’m replicating the tables to another database, instead of pointing straight at the active FIMSynchronizationService database. Here’s a simple little script that I’ve tacked on to the end of my regular run cycles.

# Copies tables from the FIMSync DB to the FIMReporting DB

$tables = @('mms_metaverse','mms_metaverse_multivalue','mms_mv_link')

if(@(get-pssnapin | where-object {$_.Name -eq “sqlserverprovidersnapin100”} ).count -eq 0) {add-pssnapin sqlserverprovidersnapin100}
if(@(get-pssnapin | where-object {$_.Name -eq “sqlservercmdletsnapin100”} ).count -eq 0) {add-pssnapin sqlservercmdletsnapin100}

set-location SQLSERVER:\SQL\<sqlserver>\<instance>\Databases\FIMReporting

foreach ($table in $tables)
{
  $SqlQuery = "if exists (select * from sys.tables where name = N'$table') drop table $table"
  Invoke-SQLCmd $SqlQuery -SuppressProviderContextWarning

  $SqlQuery = "select * into $table FROM FIMSynchronizationService.dbo.$table"
  Invoke-SQLCmd $SqlQuery -SuppressProviderContextWarning
}