Detach database, copy to new location, attach database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » SMO/RMO/DMO » Detach database, copy to new location, attach...

Detach database, copy to new location, attach database

Rate Topic

Display Mode

Topic Options

Author

Message

brett-585458

Posted Thursday, May 10, 2012 8:48 AM

Grasshopper

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

opc.three

Posted Thursday, May 10, 2012 12:44 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1298113

brett-585458

Posted Thursday, May 10, 2012 7:34 PM

Grasshopper

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

opc.three

Posted Thursday, May 10, 2012 9:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1298321

brett-585458

Posted Friday, May 11, 2012 8:34 AM

Grasshopper

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

opc.three

Posted Friday, May 11, 2012 8:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

You're welcome Smile

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1298631

brett-585458

Posted Friday, May 11, 2012 8:41 AM

Grasshopper

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

Well. Now that you mention it. Smile

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

Brett

Post #1298633

opc.three

Posted Friday, May 11, 2012 8:52 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1298651

« Prev Topic | Next Topic »

Permissions