Cannot restore DB using T-SQL script

  • Hello,

    I had a database called Training_01 and did a backup of it. It is now in

    'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\RodierTrainingFood.bak'

    I did a File liste of it and saw that the logical name was

    OS_OS_Training_01 so it is a different name than the name of the original database.

    The goal is to know how to restore the database with a new name. I wrote this code but it is not working :

    Please could you help?

    Thanks

     

    PS:The script should restore the DB to a DB that is not existing yet. So it should create it.

    Database to single user Mode
    ALTER DATABASE OS_Training_99  --Name of the new database
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE

    ----Restore Database
    RESTORE DATABASE [Training_01]
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\RodierTrainingFood.bak'
    WITH MOVE 'OS_OS_Training_01' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OS_Training_99.mdf'
    MOVE 'OS_OS_Training_01' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OS_Training_99.ldf'

    /*If there is no error in statement before database will be in multiuser
    mode.
    If error occurs please execute following command it will convert
    database in multi user.*/
    ALTER DATABASE OS_Training_99 SET MULTI_USER
    GO

     

     

    • This topic was modified 4 months ago by  jbeclapez.
  • I got it working doing something like that.

     

    RESTORE DATABASE OSTraining_98 --This is the destination name of the DB

    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\RodierTrainingFood.bak'

    WITH MOVE 'OS_Trainer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer.mdf',

    MOVE 'OS_Trainer_Default' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_Default.ndf',

    MOVE 'OS_Trainer_Timestamps' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_Timestamps.ndf',

    MOVE 'OS_Trainer_Audit' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_Audit.ndf',

    MOVE 'OS_Trainer_FileSystem' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_FileSystem.ndf',

    MOVE 'OS_Trainer_WorkflowActivity' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_WorkflowActivity.ndf',

    MOVE 'OS_Trainer_WorkflowHistory' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_WorkflowHistory.ndf',

    MOVE 'OS_Trainer_StageHistory' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_StageHistory.ndf',

    MOVE 'OS_Trainer_StageSource' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_StageSource.ndf',

    MOVE 'OS_Trainer_StageTarget' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_StageTarget.ndf',

    MOVE 'OS_Trainer_StageSummary' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_StageSummary.ndf',

    MOVE 'OS_Trainer_StageAttribute' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_StageAttribute.ndf',

    MOVE 'OS_Trainer_DataRecord' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_DataRecord.ndf',

    MOVE 'OS_Trainer_CalcStatus' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_CalcStatus.ndf',

    MOVE 'OS_Trainer_DataEntryAudit' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_DataEntryAudit.ndf',

    MOVE 'OS_Trainer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OSTraining_98_Trainer_log.ldf'

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

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