Taking Backup of database in one server and restoring in other via sql commands

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.

Step 2.1:

----Checking if the drive exist(for ldf n mdf files) in this particular server

EXEC master.dbo.xp_fileexist 'YourLDFLogicalName' 

EXEC master.dbo.xp_fileexist ' YourMDFLogicalName

It will give 3 columns, for file, directory and root directory.

Check if it exist or not for both LDF and MDF files.

Step 2.2

---If not then change the file path using these lines

--For data file

SELECT TOP 1 ms.physical_name FROM master.sys.master_files ms WHERE ms.file_id=2 ORDER BY ms.database_id DESC

--For log file

SELECT TOP 1 ms.physical_name FROM master.sys.master_files ms WHERE ms.file_id=1 ORDER BY ms.database_id DESC

Step 2.3

--Change the file name to the name of the DataNEWYourMDFFile.mdf and DataNEWYourLDFFile.ldf'

Step 2.4

--Checking if the db exist, if yes making it to single user

IF EXISTS (SELECT name FROM master.sys.databases WHERE name = '')

BEGIN

ALTER DATABASE userDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

END

Step 2.5

----Restore Database
RESTORE DATABASE YourDB FROM DISK = '\\ServerIP\\D:\BackUpYourBaackUpFile.bak' 
WITH MOVE 'YourMDFLogicalName' TO 'DataNEWYourMDFFile.mdf', 
MOVE 'YourLDFLogicalName' TO 'DataNEWYourLDFFile.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 YourDB SET MULTI_USER
GO