SMO Question...Cloning Databases...
-
I know this isn't DMO, but with SQL 2000 and the SMO objects from 2005, I can use the new transfer object to "clone" a database to a new database with a different name.
So, I'm connecting to a SQL 2000 server, and trying to transfer a database. It works great, except for a minor problem. Not all the permissions are copied. For example, specific revoke commands are not scripted out, even when turning on transfer.options.permissions = true; Even turning on CopyAllLogins and CopyallRoles and CopyAllUsers doesn't do it either. In fact it misses a couple.
Has anyone tried this?
So, someone told me to manually do this...yeah...right...we have 48,000 Production databases. It needs to be a service so we can clone a production DB to the dev environment for testing when there's issues...we also scrub the copy to remove personal information, so a manual clone is not really feasible.
Right now, we backup the database...and then restore, but as you might know...that really hurts performance because a Backup operation puts that database unavailable.
We also have 4 different types of databases...so, I need something that will clone the user permissions for the database roles, but I can't figure out how to do it with the Transfer Object or with the Scripter Object in SMO.
Do I need to manually create dynamic SQL and pass in the Database Role and permissions? How would I select the permissions from the original database to script them out in the new database? That's the other problem. I can't find a script that shows me how to get the permissions so that I can setup something like:
sp_addrolemember 'blah'
GRANT SELECT On 'blah'
Revoke delete on 'blah'
See? Help!
Christopher Ford
-
This was removed by the editor as SPAM
-
I have a VBScript (uses DMO) that will script out a 2000 server and I believe has what you need. In essence, it creates a script that will build an empty clone of the source database. The logins don't have passwords but with 2000 you can bcp out sysxlogins and that will have the binary of the encrypted password.
If you think this will help, PM me and I'll email it to you.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
You may also find this link useful:
---------------------------------------
elsasoft.org -
Oooo...Nice!! That may be exactly what I'm looking for!
I did however figure out how to clone the database, data and schema exactly as it is in one environment into another one using SMO.
It isn't as intuitive as you might think being that some of the Options in the Transfer object actually can get executed twice...such is the case with the users...it copied permissions, then dropped all my users on the server after applying the schema because of another option I had set...then the user I was connected with suddenly has no permissions...then the script bombs because now I have no permissions and the only user account left is SA...
When I figure out which combination of options that is I'll post the completed code here...
I'm also going to check out that database publishing wizard link!
Christopher Ford
-
well, I have found that the publishing wizard pretty much sucks. It doesn't even work against pubs!!
Here's the error I get:
Generating script for database pubs
- Including default object types in script
Error: [dbo].[authors], column 'au_id' is of type [UserDefinedDataType]. This type is currently not supported.
Looks like the programmers didn't bother with the scripting of UDTs. But who cares, nobody uses UDTs right? wrong!
I mean, if it doesn't work against a db as simplistic as pubs, it's pretty much useless in my book. It *is* a CTP on the other hand. Maybe I shouldn't be too critical...
You might find scriptdb.exe useful though. It's an app i wrote that will generate (separate) scripts for each object in any 2000 or 2005 database, using SMO (like the publishing wizard).
Scriptdb.exe doesn't generate one big script like you want though, and it doesn't do permissions/users/logins/etc. On the other hand the source is available so you can modify it how you like. it should be rather simple to add the features you want yourself if you know a little C#.
Here it is: http://www.elsasoft.org/tools.htm
---------------------------------------
elsasoft.org
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply