Post reply

Backup + Restore SQLDatabase via Powershell don't work

  • October 18, 2023 at 7:24 am

    #4310237

    Hi,

    i want to backup one SQLDatabase via Powershell and then i want to restore this backupfile to zwo other SQLDatabase.

    (we need this way because we have to disable something in source database)

    The Backup works fine, then i start the first restore. It works fine. But when the script should restore the second database , it show error, "the file you will restore is not part of this database". It seems, my script has problems with parameters/variables. Does someone who has idea, what to do?

  • October 18, 2023 at 7:25 am

    #4310238

    here the script...

    Attachments:
    You must be logged in to view attached files.

  • October 18, 2023 at 11:41 am

    #4310378

    use "restore filelistonly from disk=youdbackupfile" to check if the file contains backup information for different databases !

    or "restore headeronly from disk=youdbackupfile"

    Are you sure your databases will only have 2 files ? ( check Smo.RelocateFile and provision all logical file names that come from the full backup file )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • October 26, 2023 at 3:22 am

    #4315042

    Did you solve it? You can refer to the following script：

    Back up the SQL database:

    # Connect to a SQL Server Instance

    $serverInstance = "ServerInstanceName"

    $databaseName = "YourDatabaseName"

    $backupPath = "C:\Backup\YourDatabase.bak"

     

    # Creating a SQL Server Connection

    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)

     

    # Select the database to back up

    $database = $server.Databases[$databaseName]

     

    # Creating a Backup Device

    $backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem($backupPath, "File")

     

    # Creating a Backup Job

    $backup = New-Object Microsoft.SqlServer.Management.Smo.Backup

    $backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database

    $backup.Database = $database.Name

    $backup.Devices.Add($backupDevice)

     

    # Perform a backup

    $backup.SqlBackup($server)

     

    Restore the SQL database:

    # Connect to the SQL Server Instance

    $serverInstance = "ServerInstanceName"

    $databaseName = "YourDatabaseName"

    $backupPath = "C:\Backup\YourDatabase.bak"

     

    # Creating the SQL Server Connection

    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)

     

    # Select the database to restore

    $database = $server.Databases[$databaseName]

     

    # Creating a Restore Device

    $restoreDevice = New-Object Microsoft.SqlServer.Management.Smo.RestoreDeviceItem($backupPath, "File")

     

    # Creating a restore job

    $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore

    $restore.Database = $database.Name

    $restore.Devices.Add($restoreDevice)

    $restore.NoRecovery = $false

     

    # Perform a restore

    $restore.SqlRestore($server)

     

    Please customize variables such as server instance name, database name, and file path to suit your specific environment. These PowerShell commands will back up one SQL database and then restore it to another. I hope this will help you!

    • This reply was modified 1 year, 1 month ago by  Luna.

Viewing 4 posts - 1 through 3 (of 3 total)

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