I have c# SMO script which copy all database objects to existed empty database or to a new database.
But this approach has two problems
- Existed database is not empty, manual intervention is needed.
- New database will have different options like DB Owner, disk
management etc.
Because I need to Clone existed database from one instance to another and I am able to copy only database objects right now, therefore my question is
- it is possible to create a new database according to settings of
existed one ? - it is possible to drop all objects, regardless on dependencies, from existed database ?
my script look like
Transfer transfer = new Transfer(databaseSource);
transfer.CopyAllObjects = true;
transfer.CopyAllUsers = true;
transfer.Options.WithDependencies = true;
transfer.DestinationServer = serverDestination.Name;
transfer.DestinationDatabase = databaseDestination.Name;
transfer.DestinationLoginSecure = true;
transfer.CopySchema = true;
transfer.CopyData = true;
transfer.Options.Permissions = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.TransferData();
Best Answer
You could use
SELECT * FROM sys.databases d WHERE d.name = '<target_db_name>';
to obtain the existing settings for the database prior to running the transfer. Store the results in aDataSet
so you can potentially use them later.If the database does not exist, you'll get no rows returned. If the database does exist, you could drop the existing database as the first step.
After you run the
TransferData()
method, you could apply the necessary configuration changes based on the items in theDataSet
. For instance therecovery_model_desc
column contents could be used to set the recovery model by running this code:ALTER DATABASE <target_db_name> SET RECOVERY = 'xxx';
wherexxx
is the value contained in the column.