Storm Posted February 21, 2013 Share Posted February 21, 2013 Hi Guys I want some software that can take a sql databse from one machine, and restore it to another, every hour or so, anyone aware of any? Ed Link to comment Share on other sites More sharing options...
Chicane-UK Veteran Posted February 21, 2013 Veteran Share Posted February 21, 2013 Is it a Microsoft SQL Server..? Because if so, you can just use logshipping - it's built in and specifically designed for this purpose :) Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 Yes it is.. any info on that? Link to comment Share on other sites More sharing options...
Chicane-UK Veteran Posted February 21, 2013 Veteran Share Posted February 21, 2013 MSDN / BooksOnline is your friend! :) http://msdn.microsoft.com/en-us/library/ms190640.aspx Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 Thanks! looks like it doesnt support express! :( Link to comment Share on other sites More sharing options...
Chicane-UK Veteran Posted February 21, 2013 Veteran Share Posted February 21, 2013 Ah - I didn't realise you were using SQL Express :( You could still do this with a combination of Windows scheduled tasks and Jobs within SQL Server but it'd be pretty cludgy.. :( What you'd want to do would be doing transaction log backups rather than full database backups which would mean the file you needed to copy over was smaller and would take less time to restore.. that's what (unsurprisingly) log shipping does :) Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 Just putting the full SQl on my test machines now, will give it a go ;) Thanks Link to comment Share on other sites More sharing options...
Chicane-UK Veteran Posted February 21, 2013 Veteran Share Posted February 21, 2013 Sorry - I forgot to say.. In terms of what you use, it depends on how you want to use the 'backup' database - is it to be used as a standby system, or are you just going to be doing reporting off the database or subsets of the data? Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 backup server! Link to comment Share on other sites More sharing options...
Chicane-UK Veteran Posted February 21, 2013 Veteran Share Posted February 21, 2013 OK - logshipping would do exactly what you want. The only thing you need to bear in mind is that, AFAIK, logshipping will only be backing up the contents of the database. Any other things you change (such as adding new users, etc) will not come across in the logshipping - so any such changes that you make to one server, you'll need to do to the other. Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 Brill. Thanks for your help. Just reconfiguring SQL on the 2nd server and i should have it set up then :) Link to comment Share on other sites More sharing options...
Chicane-UK Veteran Posted February 21, 2013 Veteran Share Posted February 21, 2013 Cool - good luck :) Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 My only question.. It asks for a network path to the backup folder on the primary server. These 2 machines are on seperate networks with no VPN etc. is there a way around this>? Link to comment Share on other sites More sharing options...
Chicane-UK Veteran Posted February 21, 2013 Veteran Share Posted February 21, 2013 Ah - that's going to be an issue :| It expects to be able to copy the file over Windows File Sharing :| Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 hmm :/ strange one, when i setup VPn connection it connects but i loose my external connection :( Link to comment Share on other sites More sharing options...
tim_s Posted February 21, 2013 Share Posted February 21, 2013 Do you want? 1: nightly re-build? 2: master to slave setup? (this will keep the backup up-to-date but also requires some configuration with keys to function.) Link to comment Share on other sites More sharing options...
TheDisneyMagic Posted February 21, 2013 Share Posted February 21, 2013 Have you tried on the client PC that is connecting to the VPN... Open Network Connections, Right click the VPN Network Adapter -> Properties -> Networking -> Double click Internet Protocol Version 4 - > Click Advanced -> Untick Use default gateway on remote network. Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 Have you tried on the client PC that is connecting to the VPN... Open Network Connections, Right click the VPN Network Adapter -> Properties -> Networking -> Double click Internet Protocol Version 4 - > Click Advanced -> Untick Use default gateway on remote network. That was it :) found it after a quick help from google lol .Problem im having now is Cannot open backup device '\\server2\backups\test.bak'. Operating system error 5(error not found). RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201) Both backup folders have full write/read access to folders and i can manually copy those folders across. Ed Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 sorted that :D seems to be working now. thanks guys One question.. After the initial config, does it send the full DB every time or does it just send the changes? Ed Link to comment Share on other sites More sharing options...
Chicane-UK Veteran Posted February 21, 2013 Veteran Share Posted February 21, 2013 Changes - it's sending the transaction log files :) Link to comment Share on other sites More sharing options...
Storm Posted February 21, 2013 Author Share Posted February 21, 2013 Cheers Chicane, Final question i hope. Once done, the DB is stuck in the (RESTORING) state... Any ideas why this is? Link to comment Share on other sites More sharing options...
Recommended Posts