Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Detach database, copy to new location, attach database Expand / Collapse
Author
Message
Posted Thursday, May 10, 2012 8:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 10, 2012 10:42 AM
Points: 13, Visits: 51
Hello,
PowerShell newbie here. I'm trying to write a PS script that will detach a database, copy the data and log files to new location, and attach database using new file location. I'm stuck on getting the current physical file names. Here is the code I have so far:

[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | out-null

$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'BRETTW7'

$database = $server.Databases['Test1'];
$dblogfiles = $database.LogFiles.Item(0).Name;
Write-Host 1: $dblogfiles ;

$fileToRename = $database.FileGroups["PRIMARY"].Files[$dblogfiles];

write-host 2: $database.FileGroups.get_Item(0).Files

Write-Host 3: $fileToRename ;
Write-Host 4: $initialpath;



So -- how do I get the physical file name of the data and log files?

Also, does anyone have any scripts or snippets that would help me?

Thank you in advance.
Brett
Post #1297937
Posted Thursday, May 10, 2012 12:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 7,125, Visits: 12,720
Here is a start for you. Post back if you have more questions:

Add-Type -AssemblyName “Microsoft.SqlServer.Smo”

######################################################################

$instanceName = '.\STD2008R2'
$databaseName = 'test_multi_files'

######################################################################

$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$database = $server.Databases[$databaseName];

Write-Host ("$database has " + $database.LogFiles.Count + " log files.")
foreach($logFile in $database.LogFiles)
{
Write-Host ("`t" + $logFile.Name + " -> " + $logFile.FileName)
}

# $server.DetachDatabase(string databaseName,bool updateStatistics) > http://msdn.microsoft.com/en-us/library/ms210177.aspx



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1298113
Posted Thursday, May 10, 2012 7:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 10, 2012 10:42 AM
Points: 13, Visits: 51
I ran this with my info but am still having a problem. Here is the exact script I'm running (copy of yours with names changed):
Add-Type -AssemblyName “Microsoft.SqlServer.Smo”

######################################################################

$instanceName = 'BRETTW7'
$databaseName = 'test1'

######################################################################

$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$database = $server.Databases[$databaseName]


Write-Host ("$database has " + $database.LogFiles.Count + " log files.")
foreach($logFile in $database.LogFiles)
{
Write-Host ("`t" + $logFile.Name + " -> " + $logFile.FileName)
}

# $server.DetachDatabase(string databaseName,bool updateStatistics) > http://msdn.microsoft.com/en-us/library/ms210177.aspx



And here is the output and error:

[test1] has log files.
The following exception was thrown when trying to enumerate the collection: "Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Cultu
re=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.".
At C:\Users\Brett\AppData\Local\Temp\bdd09e9f-9e90-425a-bfc0-3810a9fb614c.ps1:15 char:8
+ foreach <<<< ($logFile in $database.LogFiles)
+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
+ FullyQualifiedErrorId : ExceptionInGetEnumerator


Thanks for you help,
Brett
Post #1298304
Posted Thursday, May 10, 2012 9:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 7,125, Visits: 12,720
Relevant?

http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/861b57bc-aa21-4b03-bd01-5c164e2bac4e


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1298321
Posted Friday, May 11, 2012 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 10, 2012 10:42 AM
Points: 13, Visits: 51
Thanks opc.three,

That did it! I used your code snippet and installed the package you suggested and did the trick.

Thanks again,
Brett
Post #1298627
Posted Friday, May 11, 2012 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 7,125, Visits: 12,720
You're welcome Post back if you get stuck going beyond what I gave you.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1298631
Posted Friday, May 11, 2012 8:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 10, 2012 10:42 AM
Points: 13, Visits: 51
Well. Now that you mention it.

What is the corresponding object/property for the data file(s)?

Brett
Post #1298633
Posted Friday, May 11, 2012 8:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 7,125, Visits: 12,720
Well, I will say it is not as obvious as Database.LogFiles hanging off the database object straight-away but when you think about it in terms of how SQL Server organizes data files into groups it makes more sense.

I think you'll want code like this:

for each $fileGroup in Database.FileGroups
---- for each $dataFile in $fileGroup.Files
---- ---- do work



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1298651
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse