SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Auto Cold backup sqlserver
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hameedf
Starting Member

Pakistan
27 Posts

Posted - 11/27/2012 :  14:27:10  Show Profile  Reply with Quote
Hello experts !

Is it possible to take offline backup of server 1 and restore and copy of database.bak to server 2 automatically?

Any script ?

Thanks

Hameed

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/27/2012 :  15:16:34  Show Profile  Reply with Quote
Do you mean to Backup mdf and ndf files in Server 1 and then copy them to other Server Where SQL Server is installed with same version and then reattach them? Yes it is possible.
Go to Top of Page

hameedf
Starting Member

Pakistan
27 Posts

Posted - 11/27/2012 :  15:47:56  Show Profile  Reply with Quote
Sodeep,

I have one server 1(production server) sqlserver 2008 R2 and other Server 2(backup or standby server) sqlserver 2008 R2 .

I need to take offline backup of server 1 and restore the backup of server 1 to server 2 and make a copy of database .bak and save on harddisk and then and again online server 1 ?

will we do it automatically any script or some thing automation?

Thanks

Hameed
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1644 Posts

Posted - 11/27/2012 :  17:32:04  Show Profile  Reply with Quote
Have you considered using some of the built in technologies that would accomplish the same end result? Specifically, Database Mirroring seems like a likely solution. In addition to keeping a copy of Server1's database available on Server2, it will automatically cut over to the second server if Server1 becomes unavailable. Depending on your requirements, Replication could also be your solution. Either Snapshot or Transactional Replication would keep a secondary copy of the database available for use if the need arises.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/27/2012 :  20:00:11  Show Profile  Reply with Quote
Depends on whether you need real time or not, you would have to use Database Mirroring for Automatic Failover. If you donot need real-time,Log shipping or Replication would be good choice for you.
Go to Top of Page

hameedf
Starting Member

Pakistan
27 Posts

Posted - 11/28/2012 :  06:44:33  Show Profile  Reply with Quote
Bustaz Kool/sodeep

Thanks you very much !

I go for data mirroing it really suits for my requirement. Please help me in the below cases?

case 1: i need real time data in server 2 when server 1 fails then user will automatically connect to server 2 both servers are on the same location. If Server 1 back to online then is it possible that the server 2 data re mirrored to server 1.

case2: Server 1 at location 1 and server 3 at location 2 same mirroing to server 1 to server 3 as required please.

I really appreciated both of you please help me out ...


Thanks

Hameed

Edited by - hameedf on 11/29/2012 01:34:34
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/29/2012 :  10:43:44  Show Profile  Reply with Quote
quote:
Originally posted by hameedf

Bustaz Kool/sodeep

Thanks you very much !

I go for data mirroing it really suits for my requirement. Please help me in the below cases?

case 1: i need real time data in server 2 when server 1 fails then user will automatically connect to server 2 both servers are on the same location. If Server 1 back to online then is it possible that the server 2 data re mirrored to server 1.

You can achieve this with Database Mirroring .If you have same SID in both server in both servers.

case2: Server 1 at location 1 and server 3 at location 2 same mirroing to server 1 to server 3 as required please.

Yes. but depends on network bandwidth.

I really appreciated both of you please help me out ...


Thanks

Hameed

Go to Top of Page

hameedf
Starting Member

Pakistan
27 Posts

Posted - 11/29/2012 :  13:42:48  Show Profile  Reply with Quote
Sodeep,

Will you share some good link that help me to do data mirroing step by step configuration?


Thanks

Hameed
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/29/2012 :  14:02:21  Show Profile  Reply with Quote
I donot have right now but there should be article with Presentation if you search for it.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1644 Posts

Posted - 11/29/2012 :  14:08:17  Show Profile  Reply with Quote
There is a connection string option (Failover Partner=MyServer2;) that can be added to your connection string that will cause the application to automatically use MyServer2 if it can't make the connection to MyServer1. The mirroring can be configured to automitically failover from the principal server to the mirror server.

=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000