CODEWITHSUNDEEP
sql-serverpowershell
sidhartha R
sidhartha R

Reputation: 1

Create a copy of an existing database and rename it with a Powershell script

I am trying to automate a task in my project as described below using powershell script which i found on stack overflow.

  1. Login SQL Server from integrated environment.
  2. Create a copy of existing database in the same SQL Server and rename it.
Import-Module SQLPS -DisableNameChecking

$Server = "Servername"

$SQLInstanceName = "SQLInsta\Test01"

$SourceDBName   = "mrl_mpr_2_4_db"

Param(
     [Parameter(Mandatory=$true)] [string]$CopyDBName
     )

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

$SourceDB = $Server.Databases[$SourceDBName]


**$CopyDB = New-Object -TypeName 'Microsoft.SqlServer.Management.SMO.Database' -  ArgumentList $Server, $CopyDBName**

below Error While executing the above highlighted line

PS SQLSERVER:> $CopyDB = New-Object -TypeName 'Microsoft.SqlServer.Management.SMO.Database' -ArgumentList $Server, $CopyDBName

New-Object : Exception calling ".ctor" with "2" argument(s): "SetParent failed for Database 'Test_DB_1_0_DB'. "

At line:1 char:11

  • $CopyDB = New-Object -TypeName 'Microsoft.SqlServer.Management.SMO.Database' -Ar ...

  • 
+ CategoryInfo          : InvalidOperation: (:) [New-Object], MethodInvocationException
+ FullyQualifiedErrorId :  ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

Upvotes: 0

Views: 1182

Answers (2)

Mike Fleming
Mike Fleming

Reputation: 139

Try this script. It will handle all aspects of copying a database including, checking space on the target drive, checking if the required certificates are present on the target machine, checking the compatibility of the SQL versions. It will run in 1 line, and will apply all required configurations, such as setting the compatibility level to match the new version, setting the recovery level, optionally copying users and logins, or restoring the original permissions of the target database, etc.

See the Wiki

https://github.com/MikeyMing/powershell-sql/wiki/BackupAndRestore

Please feel free to contribute.

Thanks

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

Try to set domain of your server 

 $Server = "Domain\Servername"

Edit:

Firstly, change

$CopyDB = New-Object -TypeName 'Microsoft.SqlServer.Management.SMO.Database' -  ArgumentList $Server, $CopyDBName

as 

$CopyDB = New-Object -TypeName 'Microsoft.SqlServer.Management.SMO.Database' -ArgumentList $Server, $CopyDBName

then follow these steps to debug the issue

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

Instance Name                   Version    ProductLevel UpdateLevel  HostPlatform HostDistribution
-------------                   -------    ------------ -----------  ------------ ----------------
your-server-name                13.0.5101  SP2          n/a          Windows      n/a


$SourceDB = $Server.Databases[$SourceDBName]
$SourceDB


  Name                 Status           Size Space Avai Recovery Compat. Owner                     Collation
                                                lable  Model     Level
----                 ------           ---- ---------- -------- ------- -----                     ---------
xxxxxxxxxxx          Normal          xx GB     xx  GB Full         100 xxxx\xxxxxx         Turkish_CI_AS


$CopyDB = New-Object -TypeName 'Microsoft.SqlServer.Management.SMO.Database' -ArgumentList $Server, "test"
$CopyDB

Name Status Size Space Avai Recovery Compat. Owner Collation lable Model Level ---- ------ ---- ---------- -------- ------- ----- --------- test xx MB xx KB 0

Upvotes: 0

Related Questions