Post reply

Powershell Restore over another databse

  • July 25, 2017 at 5:05 am

    #402357

    Hi,

    I am trying to script to restore over a DB, however this fails because it tries to over write the original database.

    The script i have is as follows
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”) | Out-Null
    $File =gci C:\sqlbackups\Tuesday -include Coregeo-V2.bak -Recurse
    $CrockfordFile = gci C:\sqlbackups\Tuesday -include Coregeo-V2.bak -Recurse

    copy-Item $file -Destination C:\Webpub\Crockford

    Import-Module SQLPS -DisableNameChecking
    $svr = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "SUPPORTPC14\SQLEXPRESS";
    $svr.KillAllProcesses("Coregeo-v2");

    Restore-SqlDatabase -Serverinstance "SUPPORTPC14\SQLEXPRESS" -database "CSTest" -BackupFile $CrockfordFile -ReplaceDatabase

    The error i get this:
    Restore-SqlDatabase : System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Coregeo-V2.mdf' cannot be overwritten. It is being used
    by database 'Coregeo-V2'.

  • July 25, 2017 at 8:13 am

    #1952566

    Claire1985 - Tuesday, July 25, 2017 5:05 AM

    Hi,

    I am trying to script to restore over a DB, however this fails because it tries to over write the original database.

    The script i have is as follows
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfoâ€) | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnumâ€) | Out-Null
    $File =gci C:\sqlbackups\Tuesday -include Coregeo-V2.bak -Recurse
    $CrockfordFile = gci C:\sqlbackups\Tuesday -include Coregeo-V2.bak -Recurse

    copy-Item $file -Destination C:\Webpub\Crockford

    Import-Module SQLPS -DisableNameChecking
    $svr = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "SUPPORTPC14\SQLEXPRESS";
    $svr.KillAllProcesses("Coregeo-v2");

    Restore-SqlDatabase -Serverinstance "SUPPORTPC14\SQLEXPRESS" -database "CSTest" -BackupFile $CrockfordFile -ReplaceDatabase

    The error i get this:
    Restore-SqlDatabase : System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Coregeo-V2.mdf' cannot be overwritten. It is being used
    by database 'Coregeo-V2'.

    In your restore, you are replacing the database named CSTest but the error indicates there is another database named Coregeo-V2 with the same mdf file and this is not the database you are replacing in your restore.
    If you want to restore the database as CSTest, you need to change the file names or path with RelocateFile so it doesn't conflict with the database files for Coregeo-V2
    If you want to overwrite the Coregeo-V2 database, execute the restore using the database name Coregeo-V2 instead of CSTest.

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

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