In this article, we will be discussing how to restore the database from the backup file.
If you are restoring the backup file for the first time you can restore using the below command.
Now, Let's see other scenario where the same backup file to be restored into another database ( New database is created in the same location of the previous database)
Let's try whether the above restore command works or not.
Here I am trying to restore the same backup file to another database which is AdventureWorks2012_NEW DB.
Command :
Error:
As the physical files already exist in that location, the backup command is unable to restore it. for this, we have to use the backup command with the move clause.
Picking logical names from backup using the below command.
Restore command :
You can see in the above image. Restore is complete.
Thank you for visiting the blog. hope you got the required information.
If you like this article. Please share your feedback in the comment section.
If you are restoring the backup file for the first time you can restore using the below command.
RESTORE DATABASE AdventureWorks2012 FROM DISK='C:\Ganesh\Backup\AdventureWorks.bak' WITH REPLACE
Now, Let's see other scenario where the same backup file to be restored into another database ( New database is created in the same location of the previous database)
Let's try whether the above restore command works or not.
Here I am trying to restore the same backup file to another database which is AdventureWorks2012_NEW DB.
Command :
RESTORE DATABASE
AdventureWorks2012_NEW FROM DISK='C:\Ganesh\Backup\AdventureWorks.bak' WITH REPLACE
Error:
Msg 1834, Level 16, State 1, Line 8
The file 'C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012.mdf' cannot be
overwritten. It is being used by
database 'AdventureWorks2012'.
Msg 3156, Level 16, State 4, Line 8
File 'AdventureWorks2012' cannot be
restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012.mdf'. Use WITH MOVE
to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 8
The file 'C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_log.ldf' cannot be
overwritten. It is being used by
database 'AdventureWorks2012'.
Msg 3156, Level 16, State 4, Line 8
File 'AdventureWorks2012_log' cannot be
restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_log.ldf'. Use WITH
MOVE to identify a valid location for
the file.
Msg 3119, Level 16, State 1, Line 8
Problems were identified while planning
for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.
As the physical files already exist in that location, the backup command is unable to restore it. for this, we have to use the backup command with the move clause.
Picking logical names from backup using the below command.
RESTORE FILELISTONLY FROM DISK = 'C:\Ganesh\Backup\AdventureWorks.bak'
Restore command :
RESTORE DATABASE [AdventureWorks2012_NEW] FROM DISK = N'C:\Ganesh\Backup\AdventureWorks.bak' WITH
FILE = 1, MOVE N'AdventureWorks2012' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_NEW.mdf',
MOVE N'AdventureWorks2012_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_NEW_log.ldf',
NOUNLOAD, REPLACE, STATS = 5
You can see in the above image. Restore is complete.
Thank you for visiting the blog. hope you got the required information.
If you like this article. Please share your feedback in the comment section.
No comments:
Post a Comment