synchronize all databases?
qhartman
Posts: 3
I need to synchronize all of the databases from a production server to a staging/dev server each night. I basically need to do a complete snapshot of the server. However, databases on my source server are constantly being created, so I can't know beforehand what databases exist there.
Is there any sort of built-in functionality in the SQL-toolkit pro (It was called developer edition when my company bought it, I believe it is the same product...) that can achieve this? It seems that this is a use case that SQL toolkit is not particularly well-suited to, but it's the tool I have. Am I missing something?
I've not found anything yet, so I believe that I am going to need to write a program that can retrieve a list of databases from the server and then create and execute SQL compare and data compare commands for each of them based on that list. Is that the case? I'd rather not re-invent the wheel if I can avoid it. I'm most comfortable in programming in Python, so I'm thinking doing this in either "normal" Python and the CLI utility, or possibly IronPython using the .net api that red-gate provides. Any thoughts on the pros/cons of those two approaches. One big con for me is that I have no knowledge of .net right now, so just learning that part of it would likely be a big lift.
Thanks!
Is there any sort of built-in functionality in the SQL-toolkit pro (It was called developer edition when my company bought it, I believe it is the same product...) that can achieve this? It seems that this is a use case that SQL toolkit is not particularly well-suited to, but it's the tool I have. Am I missing something?
I've not found anything yet, so I believe that I am going to need to write a program that can retrieve a list of databases from the server and then create and execute SQL compare and data compare commands for each of them based on that list. Is that the case? I'd rather not re-invent the wheel if I can avoid it. I'm most comfortable in programming in Python, so I'm thinking doing this in either "normal" Python and the CLI utility, or possibly IronPython using the .net api that red-gate provides. Any thoughts on the pros/cons of those two approaches. One big con for me is that I have no knowledge of .net right now, so just learning that part of it would likely be a big lift.
Thanks!
Comments
If I had to do this, I'd simply call the command-line program (sqlcompare.exe) from VBScript. First, you can use the ADODB object to connect to the server's master database to get a list of databases from the server (select [name] from sysdatabases), then use the database name to run SQLCOMPARE against a pre-saved snapshot. You could code additional logic to say that if the snapshot file does not exist, cretae it.
Ok, so I'm making good progress in this effort, except that sqlcompare.exe won't connect to my database servers! I can connect fine using ADO, and I can connect fine using the GUI SQLCompare, but from the command line I get denied. My password has a number of *'s in it, so I thought that character might be causing problems. Everything I've tried to escape or encapsulate the password hadn't gotten me any further, and I can't change the password without causing a whole bunch of other unforeseen consequences in other legacy code we still are using. Does the command line tool not handle *'s correctly, or am I barking up the wrong tree?
EDIT: This also happens when trying to connect to the local DB server, not just remote ones.
EDIT2: Well, don't I feel silly. Turns out there was a type-o in the DB names and that was the root of it. The error mentioning that the login failed apparently confused me. It would be useful if in this case the failure occurred because the requested DB does not exist, not that the connection simply failed. Anyway, back to work!