[dba-SQLServer] dba-SQLServer Digest, Vol 85, Issue 6

Thanks Arthur.

My problem is that I need to be able to copy a database regardless of the contents.  So manually 
scripting each object doesn't really do what I want.

I found the following on the web:

//ServerConnection conn = new ServerConnection("rune\\sql2008");
//Server server = new Server(conn);

Database newdb = new Database(srv, DestDBName);
newdb.Create();

Transfer transfer = new Transfer(srv.Databases[SrcDBName]);
transfer.CopyAllObjects = true;
transfer.CopyAllUsers = true;
transfer.Options.WithDependencies = true;
transfer.DestinationDatabase = newdb.Name;
transfer.DestinationServer = srv.Name;
transfer.DestinationLoginSecure = true;
transfer.CopySchema = true;
transfer.CopyData = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.TransferData();
return true;

The thing is I am not really sure that the copy database wizard does in fact do this kind of thing. 
  From the wizard form it would appear that it detaches the database, copies the physical file, then 
reattaches the original and the copy, then does some renames inside of the new database to give it 
the proper name etc.

Whatever it does, it is pretty fast and I would like to just copy the code it uses if I could get at it.

John W. Colby
www.ColbyConsulting.com


Arthur Fuller wrote:
> Right-click the database name and you should see a "Script Objects" item.
>>From there you can script the whole database including its tables, views,
> sprocs, UDFs -- and even the insert statements to populate the tables. (I
> vaguely recall that that feature is unavailable in some versions, but I
> might be thinking of SQL 2005.
> 
> Incidentally, depending on requirements, I have developed a simple scheme
> based on mdeldb. Because SQL creates a new database based entirely on the
> contents of modeldb, I now have a collection of databases modeldb_xxxx,
> where xxxx denotes the type of model I want. So I do a couple of quick
> renames to select a new model, and then a simple New Database command and
> presto, all nice and pretty.
> 
> I have used this to include a bunch of tables I will almost always want,
> such as Contacts, Cities, Province/States and so on. Another version of
> modeldb is aimed at typical order-entry systems. Another targets
> time-and-billing apps.
> 
> I find that this works quite well.
> 
> hth,
> Arthur
> 
> On Mon, Mar 15, 2010 at 3:54 PM, jwcolby <jwcolby at colbyconsulting.com>wrote:
> 
>> Copy database is not a choice.  Create is but that just creates a database
>> of that name, it does not
>> copy the objects themselves to the new database.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>