September 22, 2020 at 11:25 am
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
September 22, 2020 at 12:33 pm
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply