backup and restore sql servers at 2 different locations

Question

i want to take back of SQL database from one location and restore it in SQL database which is at other loaction. Two sql servers are not linked or connected by any means. Totally different places.

How can i do it?

---

h2007

Answer 1

 >>>>connected by any means

We had one client that performed backup a database at night and then come in the guy  and by the car moved the file to another location and the local DBA restores the database over there...

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

Take a full back up from your source database and save it locally on hte machine.

Copy this file to your target location. Then restore from there.

you can do few things you can choose what ever teh name you wanted.

make sure .mdf and .ldf files are have correct paths to fit in your target location.

very simple. if you are transfering across net work know more about network mapping.

thanks

kumar

Answer 3

you can implement snap shot replication on which daily full database is synchronized to the destination server, no need to take backup/copy/restore

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 4

You can check with your OS team if they have proper permission to transfer the backup files to different location then you can transfer..

then at other location you can restore the database from the backup file (you should have proper permission on SQL server ).

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 5

But if different sql server versions, then .mdf and .ldf files path will be different. e.g. sql 2000 and sql 2012 or sql 2005

Still can we do it?

---

h2007

Answer 6

But if different sql server versions, then .mdf and .ldf files path will be different. e.g. sql 2000 and sql 2012 or sql 2005

Still can we do it? i want to take sql 2012 data and put in sql 2000 or sql 2005.

---

h2007

Answer 7

Hi there,

It is not supported to restore the databases from higher version to lower version.

thanks

kumar

Answer 8

It depends. YOu can't go backwards (i.e. 2012 on 2008/R2). You can't restore 2000 on 2012, but you can 2005 or 2008/R2 if the DB is at the right patch level. If going forwards, your DB will be upgraded in the restore process.

---

Allan Hirt Blog: http://www.sqlha.com/blog Coming in 2013: Mission Critical SQL Server 2012 - the followup to Pro SQL Server 2008 Failover Clustering

Answer 9

 

One possible way to do this is to use Windows Azure storage as your go between the two locations - If you have a Windows Azure storage account, you can upload your file from one location and download it to another. Also, if you are using SQL Server 2012 and have SP1 CU2 installed, you can directly create a backup file from SQL Server to Windows Azure Blog storage service. You can then use that to restore to another location.

Here are a couple of articles you can refer to with respect to SQL Server 2012 native backups to Windows Azure storage:

http://msdn.microsoft.com/en-us/library/jj919148.aspx

http://blogs.msdn.com/b/windowsazure/archive/2013/01/24/sql-server-backup-and-restore-to-cloud-simplified.aspx

Thanks,

Karthika Raman [MSFT]

---

Karthika [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.