Detach database, copy to new location, attach database

Question

Post reply

Detach database, copy to new location, attach database

brett-585458

SSC Eights!

Points: 817
More actions
May 10, 2012 at 8:48 am

#73299

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

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

brett-585458 SSC Eights! Points: 817 More actions · Answer 2

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

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

brett-585458 SSC Eights! Points: 817 More actions · Answer 4

Thanks opc.three,

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

Thanks again,

Brett

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

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

brett-585458 SSC Eights! Points: 817 More actions · Answer 6

Well. Now that you mention it. 🙂

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

Brett

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

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