Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself asp.net with non code-generating tools. I am stuck on some basic DB design thoughts and would appreciate some feedback.

Right now I have pages that let me input some of the initial data like Users, Teams and Game Schedules. I need to be able to record whether a user has paid their weekly entrance fee but I don't know which table to store this info even what datatype would be the best solution.

Here is the table design I have so farhttp://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the users tables would be the asp.net 2.0 mambership ones which are too many to list...

Thoughts please. TIA

I would suggest the following as it pertains to the business logic of your task.

Create a table for the "week" with weekId and date (no time reqd). This is a central table that links most of the others together

Create a table formoney input or "Subscription" with a link to your dateId and user

You have a table formoney output - winnings which links to dateId.

You might also need to store more user details - maybe more on address, more phone numbers etc.

|||You are in the right direction about your table design while the previous advice not about table design, you can use Microsoft provided membership for your users and just add DRI(declarative referential integrity) to team, schedules and subscription fee tables, money you need to use Decimal or Numeric because you can set precision and scale. Hope this helps.

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIAWell, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have keys
and relations.
"fwells11@.hotmail.com" wrote:
> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>sql

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIAWell, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have key
s
and relations.
"fwells11@.hotmail.com" wrote:

> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIA
Well, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have keys
and relations.
"fwells11@.hotmail.com" wrote:

> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>

Basic business rules question

How do you represent business rules in SQL Server? I need the database to return an error to a .net app if a business rule fails.

Thank you! :)

I'm not sure if this is what you mean, but you can use constraints (like CHECK CONSTRAINTS and foreign key constraints) and triggers to enforce business rules.|||Thanks. Yes, I mean something that allows me to create an elaborate procedure to verify compliance with a business rule. Are triggers the only way? I've heard negative things about triggers.|||If you can use constraints then you should because they're more optimal in performance (I think) - and you can use user-defined functions in the constraints to have fairly complex logic. But if not (because the rules are elaborate) I don't see what's wrong with triggers - they are a standard way of enforcing business rules. What negative things did you hear?|||Thanks, mostafae. I can't remember specifically what I heard; I just remember hearing that it's best to avoid them for some reason. But I don't see what the problem is, either. Thanks, again.|||

triggers can cause problems and many people (Self included) recommend against them

1. if the trigger contains much logic, it can hurt performance

2. if you're trouble shooting a slow stored procedure, you will NOT see the trigger code in any query plan. in fact, you wont even know the trigger exists unless you explicitly go look for it. This can cause you to spin your wheels trouble shooting the sproc, when the performance issue is in the trigger all along. (Maintenance nightmare)

3. it's preferable to use constraints

4. IF you must use triggers, be sure to kee them SIMPLE and FAST.

Cheers

|||

1. ... on the other hand, much logic might legitimately be needed for a particularly complex business rule. Is there another way to represent such a rule?

2. How disappointing.

3. Can constraints handle complex business rules that require some process to execute?

4. In what situations might you have no choice but to use Triggers?

:)

|||

well.....

therein lies the question and the topic of debate right...?

You may be forced to use triggers IF for some reason users are accessing your DB without going through a Business Tier.

If users can access the system and circumvent the Application, you could be hosed. If you want to be 100% certain that business logic ALWAYS Fires, regardless of how the DB is accessed, then you may be forced to use triggers (you poor soul).

However, if you're in an environment that allows users to access the DB directly, you have more problems than fiddling with triggers anyway.

The performance problem is not really triggers, it's "business logic" on the Database server. it would be the same thing with containing tons of logic in a sproc. You dont want to have a ton of business logic in a sproc (in my opinion) you want your sprocs to be "Primitive" in nature (Simple CRUD ops). The reason for this is not JUST performance, it's also scalability.

if you take complex logic in a sproc and emulate the same progic in compiled code (C# for example), it's going to perform better due to the fact that it's compiled. Furthermore, it's MUCH easier to scale application servers than DB Servers. In a SQL Cluster you can never have more then 3 boxes, so if your SQL Servers are doing a lot of Business processing you "could" hit the wall quite quickly (think LARGE scale applications like on-line banking, etc). IF you move most or all business logic to business tier (Not just logically, but physically to app servers), you have to ability so scale out as much as you need. You can have as many applications servers as you can afford to purchase.

so, Triggers are not necessarily evil, they are just a trade off in architectural decision making....

I hope this makes sense. Constraints are only going to allow you to implement "Simple rule Logic"

Cheers

|||

Thanks, pdxJaxon. That addresses my concerns, very well, actually.

sql

bad query plan when using parameterised queries on partitioned vie

Recently I found rather a nasty consequence by using parameterised queries o
n
a partitioned view.
I have a .net app that sends parameterised queries to SQL Server 2000. SQL
Server obliges by caching the query plan to increase efficiency of subsequen
t
queries.
In my case the query is running against a partitioned view, partitioned by a
date constraint.
A query is run when the cache is cleared and the query optimiser correctly
creates a query plan specifying only the correct partition and ignoring the
other partitions, so far so good…
Subsequent queries run fine and use the cached query plan until we get a
query where the date range selected now relates to a different underlying
partition – blam, full table scan against all partitions! As you might gue
ss
the query execution times blow out by several orders of magnitude! It seems
it tries to use a bad query plan from the cache and when it realises it can
t
get the data it needs it is not smart enough to re-evaluate the query plan
properly.
Currently I am forcing a recompile on these guys go make sure the cached
plan is not used as it obviously cannot be trusted! This workaround does the
trick but I’m wondering if anyone has experienced something similar before
?
Or is there a better way to deal with this? It seems to me to be in fact a
bug as the optimiser should handle this a lot better than it does.
Thoughts anyone?
Cheers,
Chrissounds like parameter sniffing problem. Sure there are ways better than
WITH RECOMPILE. Search for "parameter sniffing" in this group or google.

Bad data

Some knuckleheads have used a cutom indexing app to create several thousand records with this problem: The indexing app permitted field entries longer than the db receiving the index. IE, a 100-char entry when the DB only accepted 50 characters. The app had no safeguard against this error.

Does Oracle or PL/SQL have a function that will allow me to select all records where the content exceeds a certain number of characters in length? Actually in this case it would be better to find strings exactly 50 chars long. The data has been imported to our Oracle db, and I need to find these bad records!Try:

select * from myTable
where length(col_x) = 50;

This will list all rows where col_x is 50 characters long.

:cool:|||That's it! Thanks.

bp

Backward compatibility with AMO clients

We are an ISV whose application uses AMO (and ADOMD.NET) for manipulating SSAS cubes. I tried running our app against SQL Server 2008 (June CTP) and it failed (as I expected) because it is bound to the version of AMO that comes with SQL Server 2005 (9.0.x.x), and the assembly version numbers were bumped to 10.0.0.0 for 2008.

I installed the XMO libraries from the 2005 feature pack and tried those. The assemblies loaded, but when AMO tries to connect it gets the error:

An error occurred creating the OLAP database: Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1019.17'.
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
...

I tried adding binding redirects from the 9.0 version to the 10.0 version of the libraries, and that seemed to work (at least for the minimal testing I've done so far). Of course, I have no idea how compatible the 10.x and 9.x libraries actually are.

I'm curious what Microsoft's general philosophy around compatibility between 2005 clients and 2008 clients is going to be. What will be the officially recommended approach for supporting both platforms (assuming it is supported)?

Hi,

The problem with AMO version 9 being unable to connect to servers 10+ will be fixed in a next cumulative update for SP2.

Meanwhile, the work-around is the one you use: .config files to redirect from 9.0 to 10.0.

AMO version 10.0 is backward compatible, you can use it to connect to AS2005; BI Development Studio version 10.0 also connects to AS2005.

Thank you for posting the feedback,

Adrian Dumitrascu

|||Thanks Adrian.

Will there be a refresh for the Feature Pack components that include that fix? Or can rollups be applied to feature pack installs?

|||

The AS2005 Feature Pack will not be refreshed. The AMO fix will be included in the next Service Pack (if any) and sooner in the next cumulative update for SP2. If you cannot install the full cumulative update, it will work to just install to GAC the AMO (Microsoft.AnalysisServices.dll).

Adrian Dumitrascu

|||

Adrian,

Here's the scenario I'm concerned about. A customer installs our software for use with a server running SSAS 2008. They need the 9.x version of AMO (since that's what we've built against), and since the SQL Server 2008 client tools installs version 10, their only option is to download the Feature Pack components. However, the roll-up won't update components installed through the feature pack install (at least, the latest one doesn't - I tried it). At this point, the customer is hosed - they have no real way to get a version of AMO that works with SSAS2008. The only solutions are to install the binding redirects (ugly) or hack in a patched AMO client assembly (uglier).

Please consider this sort of scenario in your planning. Take pity on us poor ISVs.

|||

Hi,

I forwarded the message to our team, I'll post new info once I have it.

At the moment, none of the SP2 cumulative update builds at http://support.microsoft.com/kb/937137 have the AMO fix (the latest build is 09.00.3182 now). But the next build should have it (you can download it and install from it just the AMO assembly, this will be the work-around until we'll refresh the Feature Packs, if we'll be able to do that).

Adrian

Backward compatibility with AMO clients

We are an ISV whose application uses AMO (and ADOMD.NET) for manipulating SSAS cubes. I tried running our app against SQL Server 2008 (June CTP) and it failed (as I expected) because it is bound to the version of AMO that comes with SQL Server 2005 (9.0.x.x), and the assembly version numbers were bumped to 10.0.0.0 for 2008.

I installed the XMO libraries from the 2005 feature pack and tried those. The assemblies loaded, but when AMO tries to connect it gets the error:

An error occurred creating the OLAP database: Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1019.17'.
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
...

I tried adding binding redirects from the 9.0 version to the 10.0 version of the libraries, and that seemed to work (at least for the minimal testing I've done so far). Of course, I have no idea how compatible the 10.x and 9.x libraries actually are.

I'm curious what Microsoft's general philosophy around compatibility between 2005 clients and 2008 clients is going to be. What will be the officially recommended approach for supporting both platforms (assuming it is supported)?

Hi,

The problem with AMO version 9 being unable to connect to servers 10+ will be fixed in a next cumulative update for SP2.

Meanwhile, the work-around is the one you use: .config files to redirect from 9.0 to 10.0.

AMO version 10.0 is backward compatible, you can use it to connect to AS2005; BI Development Studio version 10.0 also connects to AS2005.

Thank you for posting the feedback,

Adrian Dumitrascu

|||Thanks Adrian.

Will there be a refresh for the Feature Pack components that include that fix? Or can rollups be applied to feature pack installs?

|||

The AS2005 Feature Pack will not be refreshed. The AMO fix will be included in the next Service Pack (if any) and sooner in the next cumulative update for SP2. If you cannot install the full cumulative update, it will work to just install to GAC the AMO (Microsoft.AnalysisServices.dll).

Adrian Dumitrascu

|||

Adrian,

Here's the scenario I'm concerned about. A customer installs our software for use with a server running SSAS 2008. They need the 9.x version of AMO (since that's what we've built against), and since the SQL Server 2008 client tools installs version 10, their only option is to download the Feature Pack components. However, the roll-up won't update components installed through the feature pack install (at least, the latest one doesn't - I tried it). At this point, the customer is hosed - they have no real way to get a version of AMO that works with SSAS2008. The only solutions are to install the binding redirects (ugly) or hack in a patched AMO client assembly (uglier).

Please consider this sort of scenario in your planning. Take pity on us poor ISVs.

|||

Hi,

I forwarded the message to our team, I'll post new info once I have it.

At the moment, none of the SP2 cumulative update builds at http://support.microsoft.com/kb/937137 have the AMO fix (the latest build is 09.00.3182 now). But the next build should have it (you can download it and install from it just the AMO assembly, this will be the work-around until we'll refresh the Feature Packs, if we'll be able to do that).

Adrian

backups OK in MSDE2000 but won't work in SQLexpress

I'm migrating our app from VS2003/MSDE2000 to VS2005/SQLexpress. Generally no
problems, but the backup of database always fails.
The backup command is
"BACKUP DATABASE icespy5 TO DISK='C:\testbackup'"
I get:
"Cannot open backup device 'C:\testbackup'. Operating system error 5(error not
found). BACKUP DATABASE is terminating abnormally."
The same command works fine with the MSDE database. I have tried other
backup destinations but with no success. I have full admin rights; backup
fails in both debug and release environments.
Any ideas what's wrong?
Evidently permissions are not granted! I can back up to the folder where the
..mdf file is stored, so that will be fine, thanks. However it would be useful
to know how to grant SQL permission to an alternative folder.
Thanks for your help.
"Andrea Montanari" wrote:

> hi,
> quilkin wrote:
> please verify the Windows account the SQLExpress instance is running on has
> been granted adeguate NTFS permissions on the destination path..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||quilkin,
Try changing SQL service to log in as a Local System instead of a Local or Network Service
Jeff
Check Point Software
================================================== ===
You must be using Outlook Express/Windows Mail or some other type of newsgroup reader to
see and download the file attachment(s). If you are not using a reader, follow
the link below to setup Outlook Express. Click on "Open with newsreader"
under the MS Retail Management System on the right.
http://tinyurl.com/75bgz
================================================== ===
"quilkin" <quilkin@.discussions.microsoft.com> wrote in message news:D58DA277-121E-49AE-96B1-7F91C674DD8D@.microsoft.com...
Evidently permissions are not granted! I can back up to the folder where the
.mdf file is stored, so that will be fine, thanks. However it would be useful
to know how to grant SQL permission to an alternative folder.
Thanks for your help.
"Andrea Montanari" wrote:

> hi,
> quilkin wrote:
> please verify the Windows account the SQLExpress instance is running on has
> been granted adeguate NTFS permissions on the destination path..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>

backupRestore progress bar

Hi,
My app uses MSDE and I have created a UI for user which has buttons to backup and restore the database. Everything works fine but I want to give a visual display to user about the progress of the operation. You know like a progress bar indicating how mu
ch work is done.. so the question is:
Is there a way to find out how much time backup/restore will take.. does sql server provide any such event to us telling this information. I just want to trap this event and give the progress indication to my loyal users.
Thanks all.
dev
Thanks Andrea for the reply. I am using T-SQL commands right now to backup and restore database. If I use sql-dmo to trap progress event then would that mean that I will have to change the backup/restore sourcecode also to use sql-dmo. Please confirm.
Thanks
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:8CD41CBC-6EF7-46F8-BC3C-3415F14A6BD5@.microsoft.com...
> Thanks Andrea for the reply. I am using T-SQL commands right now to
backup
>and restore database. If I use sql-dmo to trap progress event then would
that
>mean that I will have to change the backup/restore sourcecode also to use
>sql-dmo. Please confirm.
yep.. you have to use the SQL-DMO backup object to trap the raised events...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||oh no.. so what is this sql-dmo. What is the purpose of it's existance. Why do we need it (besides for the progress bar). How is it different from programming with T-SQL. How can we choose which way to go, T-SQL or SQL-DMO. What are it's advantages an
d disadvantages.
Thanks
|||Also was it unwise of me to opt for T-SQL for doing everything instead of going for DMO. And is it ok to use DMO for backup/restore and use T-SQL for everything else. What are your recommendations.
We are as before an ISV, app will be deployed using MSDE, will have max a couple of databases. Will provide an interface to the user to backup and restore database. Will creare database from app and do version checks for database.
What should we do... or should we wait for SMO..
Thanks for your valuable time.
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:07DA4684-529D-48D5-9D6A-56DDFDB077A7@.microsoft.com...
> oh no.. so what is this sql-dmo. What is the purpose of it's existance.
Why do we need it
>(besides for the progress bar). How is it different from programming with
T-SQL. How can
> we choose which way to go, T-SQL or SQL-DMO. What are it's advantages and
disadvantages.
> Thanks
SQL-DMO is an acronym for SQL Distributed Management Object and is a full
object model to manage and administer SQL Server..
it exposes a nice object model you can navigate to perform quiet all
management tasks on SQL Server..
it's not designed for data manipulation even if it provides some features
to.
it comes with MSDE and/or can be installed from the Client Tools
installation package of SQL Server.
it is not provided as a separate download and/or package, so you have to
depoly it yourself in MSDE scenarios (you are legitimate to) ... this can be
count as a disadvantage too :-)
it's a little buggy =;-) and eats a lot of memory, but is very handy for
some admin scenario...
Transact-SQL, on the contrary, is the language SQL Server better understand,
and can perform both data manipulation and administration tasks... it's a
separate language where DMO is just a COM object model you can use in any
COM compliant client, so they can not be compared... you shoul'd stick with
Transact-SQL, and use DMO where and when appropriated...
by the way... there's only 1 book worth reading about SQL-DMO, by SQL Server
MVPs Allan Mitchell and Mark Allison,
http://www.compman.co.uk/cgi-win/browse.exe?ref=552118 , which I personally
recommend reading...
FWIW, you can have a look at a free prj of mine at the link following my
sign to have an idea aboout what you can do with SQL-DMO, a prj that
provides a user interface similar to Enteprise Manager written in VB6
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:AF907AC5-9610-41F9-95DD-FC68828844F6@.microsoft.com...
> Also was it unwise of me to opt for T-SQL for doing everything instead of
going for DMO.
>And is it ok to use DMO for backup/restore and use T-SQL for everything
else. What are your
>recommendations.
> We are as before an ISV, app will be deployed using MSDE, will have max a
couple of
>databases. Will provide an interface to the user to backup and restore
database.
>Will creare database from app and do version checks for database.
> What should we do... or should we wait for SMO..
if you only need DMO for presenting a progress bar indicating backup
progress, let it be...
you have to talk to SQL Server via Transact-SQL...
perform backup via ADO.Net commands...
SMO will be only available wit SQL Server 2005, and anyway I don't think it
shoul'd be used for traditional programming, but for the same things now
covered by DMO...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea for the insight. I already use your tool since quiet some time and appreciate it. So do you recommend I use T-SQL for everything as now, you know db creation, data manipulation and use dmo for backup/restore which I can later port to use s
mo when yukon arrives..
dev
|||I am sorry Andrea but I didn't understand what you said here:
"if you only need DMO for presenting a progress bar indicating backup
progress, let it be...
you have to talk to SQL Server via Transact-SQL...
perform backup via ADO.Net commands..."
Do you mean that I use T-SQL for everything else and just use DMO for the backup/restore.
Sorry to bother you like this but Thanks
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:778938C6-1142-4954-B633-BBF0669342D3@.microsoft.com...
> I am sorry Andrea but I didn't understand what you said here:
> "if you only need DMO for presenting a progress bar indicating backup
> progress, let it be...
> you have to talk to SQL Server via Transact-SQL...
> perform backup via ADO.Net commands..."
> Do you mean that I use T-SQL for everything else and just use DMO for the
backup/restore.
> Sorry to bother you like this but Thanks
please excuse my poor english...
what I mean is go with Transact-SQL for all you stuffs, including
Backup/Restore, if you only need SQL-DMO for providing a progress
indicator...
if you need SQL-DMO for other things, then you can consider adding it to
your project references [and setup package :-( ]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backup.ExpirationDate in Sql Server SMO

I'm writing a console app in Visual Studio 2005 to backup databases using SMO and I have run into a minor snag.

It appears that when I have the Initialize property set to false to allow backups to append onto existing backups, the backups that are past their expiration dates do not get overwritten. I know the RetainDays property requires the Intialize property to be set to true but I haven't seen anything that suggests that the ExpirationDate property does as well.

Does anyone have any suggestions?

I don't think you have run into a minor snag, you have run into desired and expected behavior because Backup is the best thing you have to recreate a SQL Server database including the an actual copy of the database. The reason is when backup is copied to tape, then the .bak file can be deleted manually. That said I think you can find some backup code in the BOL (books online) but try the link below to get started. Hope this helps.

http://www.sqldbatips.com/showarticle.asp?ID=37

|||

Thanks for the response Caddre.

I have the backup code written and the application works fine. I'm just afraid that if the backups go unmonitored (and in our case that is a possibility) the backup file will get extremely large.

Every time I run the console app it appends to the end of an existing backup .bak file, thus doubling it in size. I thought if you established an expiration date, "expired" backups would be overwriiten within the .bak file. That way the .bak file would never be more than twice its size; however, my testing shows that it just keeps getting bigger and bigger.

Thanks again

|||That is what I am trying to explain to you Backup files cannot be allowed to be overwritten because someone could make the mistake of overwriting needed .bak files. You could write code to delete expired .bak files or you could do it manually weekly.

Backup without tabledata

Hello.

I am using SQL express and we have developed our app. til now.

Now we have to move the database to the another server and go live. It is fine just to backup the database but how to do it without tabledata? We have many test posts there and when going live we would like to start from beginning.

Is there any way to do it or do we have to delete all data before backing up?

Hi

This is possible. Under MSSQL 2005 Management Studio select the Database > All Tasks > Generate Script and Walk thru the all options.

Doing this will generate the script which you can use on empty database to get the tables etc.

Thanks

Rubal Jain

|||Hi!

Isn't there any Export function in SQL Express or MSSQL 2005 Management Studio?