Database Cloning

Using SQL Server 2005 Express.

I need to periodically duplicate an entire database and rename it. Most
tables are empty but some are to be prepopulated. I was thinking of having
a "template" database called perhaps "EmptyDatabase", and then copy that
into a freshly created database with a new name.

Has anyone coded anything like this?

Thanks.

GSOn Tue, 21 Nov 2006 10:39:21 -0800, George Shubin wrote:

Quote:

Originally Posted by

>Using SQL Server 2005 Express.
>
>I need to periodically duplicate an entire database and rename it. Most
>tables are empty but some are to be prepopulated. I was thinking of having
>a "template" database called perhaps "EmptyDatabase", and then copy that
>into a freshly created database with a new name.
>
>Has anyone coded anything like this?

Hi George,

One thing you can do is add these standard tables to the "model"
database. Each time you create a new database, it is created as a copy
of the "model" database, so each new database will have those tables.

If you don't want these tables in ALL new databases, then I'd create one
database with the required tables and make a full backup. You can then
create copies of that database by using RESTORE DATABASE with the WITH
MOVE option.

--
Hugo Kornelis, SQL Server MVP|||Thanks, Hugo.

Putting the tables in there just might be the ticket. I didn't know that
was one of the purposes of the Model database.

Thanks for the suggestions.

GS

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALIDwrote in message
news:1eacm2900qqojkaim4npoftlibmasratfm@.4ax.com...

Quote:

Originally Posted by

On Tue, 21 Nov 2006 10:39:21 -0800, George Shubin wrote:
>

Quote:

Originally Posted by

>>Using SQL Server 2005 Express.
>>
>>I need to periodically duplicate an entire database and rename it. Most
>>tables are empty but some are to be prepopulated. I was thinking of
>>having
>>a "template" database called perhaps "EmptyDatabase", and then copy that
>>into a freshly created database with a new name.
>>
>>Has anyone coded anything like this?

>
Hi George,
>
One thing you can do is add these standard tables to the "model"
database. Each time you create a new database, it is created as a copy
of the "model" database, so each new database will have those tables.
>
If you don't want these tables in ALL new databases, then I'd create one
database with the required tables and make a full backup. You can then
create copies of that database by using RESTORE DATABASE with the WITH
MOVE option.
>
--
Hugo Kornelis, SQL Server MVP

|||On Fri, 24 Nov 2006 10:57:21 -0800, George Shubin wrote:

Quote:

Originally Posted by

>Thanks, Hugo.
>
>Putting the tables in there just might be the ticket. I didn't know that
>was one of the purposes of the Model database.
>
>Thanks for the suggestions.

Hi George,

The main purpose of the model DB is to give you an easy way to insure
that all new datbases are created with the same options. But there are
also many DBAs that stick common "utility" tables in there, such as a
numbers table or a calendar table. Your use is less common, but still
correct use of the model DB.

--
Hugo Kornelis, SQL Server MVP

DataAdapter always returns 0 or empty rows

Good day,

I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows.

I thought the connection is not the problem since I got my columns right. Below is my code snippet.

Thanks!

conststring COMMAND_TEXT =@."select distinct somefield as matchcode, count(somefield) "

+

"as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1";SqlDataAdapter adapter =newSqlDataAdapter(COMMAND_TEXT, connection);DataTable dt =newDataTable(sometablename);

adapter.Fill(dt);

Hi,

I might be wrong, but don't you need a 'Return dt;' at the end of all this?

Hope this helps.

Paul

|||

You can not write aggregate function like count in where condition. You need to use subquery.

conststring COMMAND_TEXT =@."select distinct matchcode,recordcount from
(select distinct somefield as matchcode, count(somefield)as recordcount from filteredaccount where StateCode = 0 group by somefield ) As mytbl
whererecordcount > 1

|||Thanks for the replies. I will try your recommended SQL update, my question now is why my SQL is returning rows when run on Query Analyzer but not on the web application?|||

Hi,

The reason is that in your code you are returning nothing! Personally, I didn't find anything wrong with the original query, it worked fine in my tests. I was using an ObjectDataSource calling an object and my function was pretty much identical to yours - except it had a Return statement and mine is in VB rather than C#. However, the code is:

' Initialise the DataTableDim dadAs New SqlDataAdapter("SELECT DISTINCT LastName AS Matchcode, COUNT(Lastname) AS RecordCount " _&"FROM Contacts GROUP BY LastName HAVING COUNT(LastName)>1", _conString)' Create a DataTableDim dtAs New DataTable()' Populate the DataTabledad.Fill(dt)' Return resultsReturn dt

This worked without any issues on my system. By the way, the function returns a DataTable (i.e. Function nameOfFunction() As DataTable).

Hope this helps.

Paul

|||

Hi Paul,

I do return the datatable I just didn't put it there, sorry about that. I put logs after the adapter.fill and checked columns.count and rows.count.

columns were ok - 2 columns but rows.count is always zero.

I'm wondering if anybody has experienced the same, maybe a bug/ setting in SQL server 2005 or ADO.NET that I can check?

Thanks.

|||

Hi,

If I'm understanding correctly, the problem isn't the returning of your data table, but is in giving you a rows.count value? If so, I've just tried it on my code without any ptoblems. The adapted code looks like this:

' Initialise the DataTableDim dadAs New SqlDataAdapter("SELECT DISTINCT LastName AS Matchcode, COUNT(Lastname) AS RecordCount " _ &"FROM Contacts GROUP BY LastName HAVING COUNT(LastName)>1", _conString)' Create a DataTableDim dtAs New DataTable()' Populate the DataTable dad.Fill(dt)' Log result & Return log.Write("Columns & Rows","Test Grid","Columns= " & dt.Columns.Count.ToString &" Rows= " & dt.Rows.Count.ToString, 0,"Test","test")Return dt

If you can't get this to work (I'm sure your logging code won't be the same as mine, but hopefully you get the idea), you could try writing to your log file from the OnDataBound event. Strangely, if you have AutoGenerateColumns set to true, the column count returns zero here, but the row count is OK. This problem is fixed by ensuring you have set out the columns in your GridView.

I hope this (at last!) helps sort your problem. If not, I suggest you post all of the relevant code.

Cheers,

Paul

|||

Thanks Paul. The problem is my datatable when filled always has zero rows. While the SQL query when run on Query Analyzer returns a number of rows.

I'm sure that there's nothing wrong with my logging.

Anyway, I kind of implemented a workaround, my SQL selects from a view.. what I did was to select directly from the table instead. Hence, i believe my SQL view is somewhat corrupt or problematic.

Thanks again for the replies.

What bothers me though is ADO.NET does not fire an exception or anything... it just does not fills the datatable...

|||

Hi,

Do you mean that you can't get the return with a number of rows by selecting from the view in QA ? Try to select from the table directly and fill your datatable with the DataAdapter , then check if you can get a number of rows. If you can, the problem should be caused by the view you use. So what you should do is to rebuild the view and check the selecting in QA first. My personal view is that there should not be anything wrong with your DataAdapter or DataTable. Thanks.

|||

Yes that's what I did I select directly from the table rather than from the view. It is working fine now.

My SQL select from view run on Query Analyzer works fine returns a number of rows

BUT my SQL select from view run on my application always returns 0 rows.

What's strange is that I didn't get any errors when filling up my datatable, rows are just zeroed out.

|||

Hi,

i. You may open your SQL Profiler and start a trace. In the meantime, pls run your application and show us the textdata in the profiler.

ii. Pls show us the whole code and including your view in your database.

Thanks.

|||

Thanks Michael appreciate your suggestions... below is the whole method I'll try the SQL Profiler... what filter should I use to catch the sql statement?

public

DataTable RetrieveAllGroups(int entityTypeCode)

{

const

string COMMAND_TEXT =@."select distinct somefield as matchcode, count(somefield) "

+

"as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1 order by recordcount desc, matchcode asc";

SqlConnection connection = GetDataConnection();

if

(connection !=null)

{

SqlDataAdapter adapter =new SqlDataAdapter(COMMAND_TEXT, connection);

DataTable dt =

new DataTable(entityTableName);

adapter.Fill(dt);

return dt;

}

}