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

About: Carol

I've been doing IT for 30 years, and IdM for 15. I live in Australia and build IdM solutions based on Microsoft Identity Manager. I also play the violin, but that doesn't help much with the IdM solutions.

Leave a Reply

Your email address will not be published. Required fields are marked *