September 1, 2009 at 5:30 am
I have two SQL Servers both are SQL SERVER 2005.
Lets say ServerA and ServerB.
ServerA has online changes into this database and ServerB is keeping copy of ServerA data on weekly bases.
I have then run some work on ServerB database and make some reports on it.
I want to do backup of ServerA db from ServerB. is it possible that from ServerB I can do backup of a database that is on ServerA? then move that backup file over ServerB through xp_cmdshell and then restore on ServerB and run my SPs on restored data for reporting. If there is any way to run JOB from ServerB througth TSQL to take backup of dbthat is on ServerA, then let me know. How do i do this? Otherwise I have to make two different jobs and don't know when ServerA finishes Back up then i would be able to move backup file to ServerB. so the Job of restore and work for reporting will never know when ServerA backup completed. If someone has worked on this let me know.
Shamshad Ali.
September 1, 2009 at 5:55 am
First to get the servers accessible you can create a link between them in SSMS that will allow an account to have access between the servers. You can 'impersonate' an account across the link, meaning an account on server B runs the job and impersonates an account on server A for permissions. Mind you I don't do this much so there might be a better process, but I would check BOL or Google.
Instead of using the cmd shell can you not just create a share on the network that both servers can access? Or just using the UNC to the admin share of the server (\\serverA\D$\backup.bak).
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 1, 2009 at 11:27 pm
Since you want to take backup from ServerB using script, Linked server must be the solution I suppose, as allready mentioned above.
September 3, 2009 at 12:20 pm
I do something similar-- but I do it daily. I don't try to restore/backup on UNCs since that can be slow and there can be reliability issues.
(I don't think linked servers are the way to go here)
1 Back up serverA DB (mine is done nightly) FROM SERVER A-- nothing special here
2 Copy the bak to server b (my server is locked down but depending on yours you may or may not be able to use a shell command... there are various ways to do this)
3 Restore the bak (which has been copied to serverB) on serverB
4 On the restored DB, you may or may not have a need to drop permissions and/or create permissions.
Assuming you have permission (to copy that bak) you could do steps 2 3 4 from a scheduled SQL JOB.
I had to create an SSIS process to copy bak files... which was a bit of a roundabout way to go.
September 3, 2009 at 12:44 pm
Linked server is a simple way, but it is not the most secure way in serious production environment, because normally every server has own set of the users, admins, etc. with their own scope and data access level, and you don't want to share it.
Mentioned here SSIS has own internal security mechanism and allows you easily initiate any processes on ServerB from ServerA or vice versa.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply