Restoring a MS SQL database on another server or from another server can be very tricky. You may more than often encounter errors while trying to restore a MS SQL database through the SQL Server Management Studio. Most of the times the errors occur because of any of the following reasons
1. The Logical file names may be different.
2. The physical path may be different.
In such scenarios, we restore databases using SQL query. Following example shows how to restore a database using SQL query.
Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.
Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
GO
Step 2: Use the values in the LogicalName Column in following Step.
RESTORE DATABASE YourDB
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.ldf’,REPLACE
The above query will not only move the logical file name of the database to the physical path of the MDF and LDF files, but also replace them.
If you still face errors while restoring the database using the above method, set the database into single user mode using the following query.
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
Then restore the database using the above restore query. Once the database is restored successfully, convert the database into multiuser mode using the below query.
ALTER DATABASE YourDB SET MULTI_USER
GO
Setting up your first Azure Virtual Machine can be done by following these steps: Create…
Amazon CloudFront is a content delivery network (CDN) that helps you serve static content such…
Step-By-Step Guide To Setting Up An AWS Application Load Balancer Are you looking for a…
MySQL databases often get corrupted due to issues like hardware failure, file system failure etc.…
SQL Server Replication is the process of copying databases from one node to another to…
Here are 101 System Admin tools which make System Admins' life easy.
This website uses cookies.