Database Backup to / Restore from Remote Server - SQL Server

PandianS
Posted by in Sql Server category on for Advance level | Points: 150 | Views : 93468 red flag
Rating: 4.75 out of 5  
 4 vote(s)

Backup and Restore is one of the high availability model. In this article, I would like to perform Backup on Remote server and Performing Restore the backup file(.bak) from Remote Server into local SQL Server.

Introduction


Backup and Restore is one of the high availability model. In this article, I would like to perform Backup on Remote server and Performing Restore the backup file(.bak) from Remote Server into local SQL Server.

How to access the Remote Server shared path from local server ?

We can use Map network drive with Remote Server.

How to perform the Map network Drive with Remote Server ?

We have two Servers (ServerA and ServerB),

If we want to perform Database Backup from ServerA and Put the backup file(.bak) to Remote Server(ServerB).

If we want to perform Database Restore on ServerA from ServerB - The Backup file(.bak) will be there in Remote Server(ServerB).


 

Implementing Map Network Drive


Login into the Remote server where you want to perform Backup, Restore and retain / refer the Backup file:



Give the User name and Password to login into the Remote server…

Decide which path you want to share and Map to the Network Map drive…

I would like to create one Folder in D:\ drive named Database Backup and share the folder as given below…







Click the Apply and OK button on Backup Database Properties dialog box.

Login into Local Server/Machine


Login into local Server/Machine where you want to perform Backup and Restore activity…

Right click on My Computer



Click Map Network Drive… as given above



Choose the Drive like Z:\

Click the Browse Button and points the Remote server and Shared Path what you have shared on previous section..
Or
Simply enter the Server IP and Shared Path as given below (\\RemoteServer IP\Database Backup)



Login credential dialog box appeared.

Give the Remote server login credential as given below

User Name : DomainName\UserName
(or)
User Name : UserName

Password   : **********



Click the OK button, Now the system will try to browse the Mapped network drive of the Remote server… as given below



Login into SQL Server…. Try to access the Mapped network drive (Z:) as given below



According to the result given above, The system could not find the drive Z:\, Because the Network Map drive is not identifiable by the SQL Server.

OK. What should be done ?

We have to make the Network Map drive (Z:\) accessible by the SQL Server as given below



Once done the above step, Now The mapped network drive is accessible…. by the SQL Server

Try to access the Network mapped drive from SQL Server as given below…



YES.  Now we can access the Network Mapped drive (Z:)

Backup the Database to Remote Server


Let’s try to Backup a database to Remote server (Z:)

Right Click on Database which you want to backup to Remote server…

Right Click on Database under Object Explore --> Click Tasks --> Click Backup -->






The Database Backup successfully completed and The backup file stored in Remote server(Z:)

Try to make sure by list-out the Backup file from Remote Server…

 

Restore the backup file(.Bak) from Remote Server


Let’s try to Restore the Backup file from Remote Server to Local SQL Server

Right Click on Database which you want to be restored from the Remote server backup backup file(.Bak)…

Right Click on Database under Object Explore --> Click Tasks --> Click Restore --> Click Database…










Now the Backup file from Remote server successfully restored in local SQL Server..

If you want to remove the Mapped network drive… follow the steps as given below



Once ran the script successfully, Follow the steps as given below…





Let’s try to access the Network Map Drive



No, We could not access the Network map drive…

Conclusion


Using Network Map drive, We can perform Database Backup and Restore on Remote Server.  
Page copy protected against web site content infringement by Copyscape

About the Author

PandianS
Full Name: Pandian Sathappan
Member Level: Silver
Member Status: Member,MVP
Member Since: 5/11/2009 2:24:28 AM
Country: India
Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions
http://sqlserverbuddy.blogspot.com/
Microsoft Certification - MCITP: Microsoft Certified IT Professional - SQL Server 2008 Database Administrator - MCTS: Microsoft Certified Technology Specialist - SQL Server 2005 - MCP: Microsoft Certified Professional - SQL Server 2000

Login to vote for this post.

Comments or Responses

Posted by: Webmaster on: 10/27/2010 | Points: 10
Very good article, PandianS.

Keep sharing your knowledge!
Posted by: Ttkprakash on: 10/29/2010 | Points: 10
Pandian,

Good one.

-PRAKASH KANDASMAY
Posted by: Zoe.lin on: 9/22/2011 | Points: 25
Appreciate, it works!!!! I have a question, I need to change my password every month, does it mean I need to do the same steps everytime after I change my password?
Posted by: Praveen.Nakka on: 10/16/2011 | Points: 25
Excellent Job Done Pandian... This has helped me Lot of Restoring DB's of size 1.3 Terra Bytes.

Please keep doing the good works..

Kind Regards,

Praveen Nakka

Login to post response

Comment using Facebook(Author doesn't get notification)