Resetting the count on a Primary Key at a certain record? Impossible?

Hello,
Several consecutive records have been deleted out of an MSDE database
and the program that uses the database doesn't like it. I'm curious
if it's possible to... say... reset the primary key count so that the
records all shift to fill the "dead space" left by the deleted
records.
I have the feeling that this may be humorous to some of you, but any
input (including "you can't do this. that's the point of a primary
key") is appreciated.
Thanks!
Matt
hi Matt,
Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
you can "reset" the identity value using a DBCC CHECKIDENT with the RESEED
option specified..
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
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
|||On Mon, 02 Jul 2007 10:51:23 -0700, Matt Brown - identify wrote:

>Hello,
>Several consecutive records have been deleted out of an MSDE database
>and the program that uses the database doesn't like it. I'm curious
>if it's possible to... say... reset the primary key count so that the
>records all shift to fill the "dead space" left by the deleted
>records.
>I have the feeling that this may be humorous to some of you, but any
>input (including "you can't do this. that's the point of a primary
>key") is appreciated.
Hi Matt,
You can't do this. That's the point of a primary key.
Seriously - a primary key is intended to identify an entity. Think of
what would happen if you would change your name. Or get a new SSN.
Note that I am not contradicting Andrea. DBCC CHECKIDENT WITH RESEED
will affect new IDENTITY values, not existing values. I think that you
were asking about the latter.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
ooopppsss...
Hugo is right, completely right... I did not read with enougth attention and
only saw the "identity" resetting requirement... I'm sorry..
I'm just curious why "..program that uses the database doesn't like it.." :D
Agreeing completely with Hugo about the "keys" immutability, you can perhaps
workaround that..
you could create a "temporary" table where you insert all the current valid
rows of your table, truncate the original table in order to empty it and to
reset the idientity table's value, and finally refill it with the "orphaned"
rows..
so you can write something like
INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
TRUNCATE TABLE dbo.YourTable;
INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
SELECT all_columns_but_the_identity_col
FROM tempTable
WHERE... ORDER BY...;
DROP TABLE tempTable;
but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
or then.. and this "workaround" does not work if you have declarative
referential integrity set on that table, if it's referenced by other rows in
other tables...
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
|||On Jul 3, 6:34 am, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> Matt Brown - identify wrote:
>
>
> ooopppsss...
> Hugo is right, completely right... I did not read with enougth attention and
> only saw the "identity" resetting requirement... I'm sorry..
> I'm just curious why "..program that uses the database doesn't like it.." :D
> Agreeing completely with Hugo about the "keys" immutability, you can perhaps
> workaround that..
> you could create a "temporary" table where you insert all the current valid
> rows of your table, truncate the original table in order to empty it and to
> reset the idientity table's value, and finally refill it with the "orphaned"
> rows..
> so you can write something like
> INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
> TRUNCATE TABLE dbo.YourTable;
> INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
> SELECT all_columns_but_the_identity_col
> FROM tempTable
> WHERE... ORDER BY...;
> DROP TABLE tempTable;
> but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
> or then.. and this "workaround" does not work if you have declarative
> referential integrity set on that table, if it's referenced by other rows in
> other tables...
> --
> 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
I haven't checked this is a while. That's actually perfect. I can
remove some recordsets, then reset the identity key count with this
here DBCC CHECKIDENT WITH RESEED, then everything should progress as
normal.
Righto!
Thanks,
Matt

reset sa password msde (sql server 7)

Hi Guys,
We've lost the password for the sa. No other users belong to the admin
group. I've tried logging in using osql -E (windows authentication)
without success. Is there anyway to reset the sa password? The database
is MSDE and the version seems to be 7 (sql server 7 folder on pc). It
is running on Windows 2000 professional.Any help would be greatly
appreciated.
Regards,
EddieAm 24 Sep 2006 14:10:15 -0700 schrieb eddie:

Quote:

Originally Posted by

Hi Guys,
We've lost the password for the sa. No other users belong to the admin
group. I've tried logging in using osql -E (windows authentication)
without success. Is there anyway to reset the sa password? The database
is MSDE and the version seems to be 7 (sql server 7 folder on pc). It
is running on Windows 2000 professional.Any help would be greatly
appreciated.
Regards,
Eddie

AFAIK since SQL 7 you have authentication and it is not possible to disable
login per windows authentication. So if you are logged in as windows admin
you should always be able to connect to a database like "master" (if the
server is running :-)) ). Then you can use sp_password for setting a new
password for sa.
Or you stop the server, save your database-/logfiles, make a new install of
SQL Server and then attach your database files again.

bye, Helmut|||Is it possible to upgrade the database without the sa login (and noone
else belonging to admin group)?
Helmut Woess wrote:

Quote:

Originally Posted by

Am 24 Sep 2006 14:10:15 -0700 schrieb eddie:
>

Quote:

Originally Posted by

Hi Guys,
We've lost the password for the sa. No other users belong to the admin
group. I've tried logging in using osql -E (windows authentication)
without success. Is there anyway to reset the sa password? The database
is MSDE and the version seems to be 7 (sql server 7 folder on pc). It
is running on Windows 2000 professional.Any help would be greatly
appreciated.
Regards,
Eddie

>
AFAIK since SQL 7 you have authentication and it is not possible to disable
login per windows authentication. So if you are logged in as windows admin
you should always be able to connect to a database like "master" (if the
server is running :-)) ). Then you can use sp_password for setting a new
password for sa.
Or you stop the server, save your database-/logfiles, make a new install of
SQL Server and then attach your database files again.
>
bye, Helmut

Reset msde sa password

I have installed msde 7.0 on a pc with win98.
Someone has changed sa password
How can I reset sa password?
I'm unable to logon with trusted connection because the pc is not nt.

Thanks"Sam" <z.sam-No-Spam-@.libero.it> wrote in message news:<cguov4$k1b$1@.newsreader.mailgate.org>...
> I have installed msde 7.0 on a pc with win98.
> Someone has changed sa password
> How can I reset sa password?
> I'm unable to logon with trusted connection because the pc is not nt.
> Thanks

Open, EM on the 98 machine. Go to security, login and right click on
it and change the password.

Reset Forgotten MSDE Password

I have forgotten the SA password for my MSDE install. Can some one please
tell me how to reset it? Thanks.
If the account you login with is an administrator on the local machine, you
can use the osql command line tool to execute the sp_password stored
procedure:
osql -E -Q "EXEC sp_password @.new='newpassword', @.loginame = 'sa'"
That's not a spelling mistake in loginame. Apperently long, long ago when
the sp_password procedure was created bytes were really expensive and
someone decided to save some on that extra n. <g>
Jacco Schalkwijk
SQL Server MVP
"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:47F057B4-2D41-450F-BCB0-026ADD4B2123@.microsoft.com...
>I have forgotten the SA password for my MSDE install. Can some one please
> tell me how to reset it? Thanks.
|||When I run that command, I get the following error: "Cannot open user default
database. Login failed." Thanks for the help.
"Jacco Schalkwijk" wrote:

> If the account you login with is an administrator on the local machine, you
> can use the osql command line tool to execute the sp_password stored
> procedure:
> osql -E -Q "EXEC sp_password @.new='newpassword', @.loginame = 'sa'"
> That's not a spelling mistake in loginame. Apperently long, long ago when
> the sp_password procedure was created bytes were really expensive and
> someone decided to save some on that extra n. <g>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Marcus" <Marcus@.discussions.microsoft.com> wrote in message
> news:47F057B4-2D41-450F-BCB0-026ADD4B2123@.microsoft.com...
>
>
|||Hi,
Login into MSDE using trunsted connection and change the default db for the
user
OSQL -E -S Servername (enter)
Now change the default db for the use
sp_defaultdb (see books online for details)
Now change the password for user using SP_password command
Thanks
Hari
SQL Server MVP
"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:FE6B61E1-0C69-4F72-B2CA-2C3DF21DD2D7@.microsoft.com...
> When I run that command, I get the following error: "Cannot open user
default[vbcol=seagreen]
> database. Login failed." Thanks for the help.
> "Jacco Schalkwijk" wrote:
you[vbcol=seagreen]
when[vbcol=seagreen]
please[vbcol=seagreen]
|||I am logged into the server using an administrator account, however, when I
try to run the initial OSQL command you suggest, I get the same: "Cannot open
user default database. Login failed." Thanks for your help.
"Hari Prasad" wrote:

> Hi,
> Login into MSDE using trunsted connection and change the default db for the
> user
> OSQL -E -S Servername (enter)
> Now change the default db for the use
> sp_defaultdb (see books online for details)
> Now change the password for user using SP_password command
> Thanks
> Hari
> SQL Server MVP
> "Marcus" <Marcus@.discussions.microsoft.com> wrote in message
> news:FE6B61E1-0C69-4F72-B2CA-2C3DF21DD2D7@.microsoft.com...
> default
> you
> when
> please
>
>
|||Specify the database explicitly:
osql -E -d master -Q "EXEC sp_password @.new='newpassword', @.loginame = 'sa'"
Jacco Schalkwijk
SQL Server MVP
"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:FE9E1610-F55A-4C60-B4E4-455A562850FF@.microsoft.com...[vbcol=seagreen]
>I am logged into the server using an administrator account, however, when I
> try to run the initial OSQL command you suggest, I get the same: "Cannot
> open
> user default database. Login failed." Thanks for your help.
> "Hari Prasad" wrote:
|||That did the trick! Thanks for everyone's help
"Jacco Schalkwijk" wrote:

> Specify the database explicitly:
> osql -E -d master -Q "EXEC sp_password @.new='newpassword', @.loginame = 'sa'"
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Marcus" <Marcus@.discussions.microsoft.com> wrote in message
> news:FE9E1610-F55A-4C60-B4E4-455A562850FF@.microsoft.com...
>
>

requirements to connect to a named instance of MSDE 2.0

What is the propery syntax to establish a connection with a named instance
of MSDE on a server from a client workstation using the SQL Query analyzer
(or EM for that matter)?
I have tried "myServer\myInstance" to no positive effect.
Could I do this via TCP from a remote PC? What would be the syntax for
that?
Thanks.
Hi
"myServer\myInstance, portnumber"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"astro" <astro@.bcmn.com> wrote in message
news:VorVd.24398$Sq5.7292@.twister.rdc-kc.rr.com...
> What is the propery syntax to establish a connection with a named instance
> of MSDE on a server from a client workstation using the SQL Query analyzer
> (or EM for that matter)?
> I have tried "myServer\myInstance" to no positive effect.
> Could I do this via TCP from a remote PC? What would be the syntax for
> that?
> Thanks.
>
>

requirements to connect to a named instance of MSDE 2.0

What is the propery syntax to establish a connection with a named instance
of MSDE on a server from a client workstation using the SQL Query analyzer
(or EM for that matter)?
I have tried "myServer\myInstance" to no positive effect.
Could I do this via TCP from a remote PC? What would be the syntax for
that?
Thanks.Hi
"myServer\myInstance, portnumber"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"astro" <astro@.bcmn.com> wrote in message
news:VorVd.24398$Sq5.7292@.twister.rdc-kc.rr.com...
> What is the propery syntax to establish a connection with a named instance
> of MSDE on a server from a client workstation using the SQL Query analyzer
> (or EM for that matter)?
> I have tried "myServer\myInstance" to no positive effect.
> Could I do this via TCP from a remote PC? What would be the syntax for
> that?
> Thanks.
>
>

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS
SQL database stored locally
created using MSDE.
TIAStart with this:
http://www.microsoft.com/downloads/...&displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net...
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS SQL database stored locally
created using MSDE.
TIA
Start with this:
http://www.microsoft.com/downloads/d...displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net. ..
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS SQL database stored locally
created using MSDE.
TIAStart with this:
http://www.microsoft.com/downloads/details.aspx?familyid=08e3d5f8-033d-420b-a3b1-3074505c03f3&displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net...
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

Reposting: How to extract (and email) only 1 client's data from th

Hi,
My vb.net app will use msde for database needs. The app is a tool in which the app user (companyA) will do some planning for their clients. So basically, the database will have information about all the clients (Sam, Julia, Peter, Nancy etc) of our cust
omer (companyA). Now suppose there is some error happening in the application for user Nancy, and we want the companyA to send us only Nancy's data so that we can duplicate the error and debug it. How do we do that...
In previous versions of our app where we used the flat files for data storage, we will just tell the companyA to email us Nancy.ourFile so that we can have a look at the data which is causing the problem. How to extract only Nancy's data from the whole d
atabase...
Thanks
dev
hi,
"dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
news:782E4169-30DB-4AEF-B7BC-CC375DABE132@.microsoft.com...
> Hi,
> My vb.net app will use msde for database needs. The app is a tool in
which the app user
>(companyA) will do some planning for their clients. So basically, the
database will have
>information about all the clients (Sam, Julia, Peter, Nancy etc) of our
customer (companyA).
> Now suppose there is some error happening in the application for user
Nancy, and we want
> the companyA to send us only Nancy's data so that we can duplicate the
error and debug it.
> How do we do that...
> In previous versions of our app where we used the flat files for data
storage, we will just tell the
> companyA to email us Nancy.ourFile so that we can have a look at the data
which is causing
>the problem. How to extract only Nancy's data from the whole database...
> Thanks
> dev
>
it depends how you can identify Nancys' data...
if it's a complete database, than detach it, zip it and mail it...
if data is shared with other user's data, than you have to filter on perhaps
owner.tableName.col_User = 'Nancy' and export it to a flat file, probably
using BCP, something like
c:\>BCP "SELECT * FROM [db_name].[owner].[table/view name] WHERE colUser =
'Nancy'" queryout c:\NacyData_objName.txt -c -S(local) -T
make a batch file with all the queryout BCP commands...
or provide a DTS to be executed locally to equally export to file
those file will be then BCP in on testing server for toubleshouting...
good idea? =;-)
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

reposting: bkup and resore strategy. Please reply

Hi,
My vb.net app will use database which will be deployed using MSDE on client
machines. I will provide UI to client in my app to backup and restore the d
atabase. My app will work with one database, for e.g. MyAppDB. My question
is what are the best pract
ices for doing this:
a) Should I choose Full or Simple mode. There is no mission critical data.
But as my clients will be using MSDE, will setting up the recovery mode as
Full cause the database and log size limits to cross the 2GB limit soon. Is
it better if I choose SIMP
LE recovery mode. What do you suggest.
b) Should I back up the system databases also, if yes then what should be th
e order of backing these up (and then in what order should the databases be
restored)
c) Does the MSDE login information also needs to be backed up. At max I wil
l have just one custom login and sa. I am using SQL Authentication.
d) Will I need to close my application and make sure that my apps' MSDE inst
ance is not running before backing up the databases.
d) I want to provide user the option to choose the backup location. How wil
l I figure out if they chose something like tape drive or zip drive. How sh
ould I handle this scenario.
e) Also how can I find out if there is sufficient space on the hard drive to
backup the database. Are there any standard sql functions to check that.
f) Should I do database log file backup too while backing up the database.
sorry for so many questions.
thanks
newbie
Print | Copy URL of this post
Expand All Collapse AllSee answers inline:
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:3DFC5E47-73A3-4CFC-9161-0AF13E789AD5@.microsoft.com...
> Hi,
> My vb.net app will use database which will be deployed using MSDE on
client machines. I will provide UI to client in my app to backup and
restore the database. My app will work with one database, for e.g. MyAppDB.
My question is what are the best practices for doing this:
> a) Should I choose Full or Simple mode. There is no mission critical
data. But as my clients will be using MSDE, will setting up the recovery
mode as Full cause the database and log size limits to cross the 2GB limit
soon. Is it better if I choose SIMPLE recovery mode. What do you suggest.
I almost always use either full or bulk-logged, although there are a few
instances where simple is appropriate. Basically you need to determine in
the event of failure do you want to be able to recover to the point of
failure, or is it good engoug to recover from the last full backup? As for
size limits, you can control the size of the log by backing it up regularly.
Besides, I am pretty sure that the 2Gb size limit is on the data file, not
the log.

> b) Should I back up the system databases also, if yes then what should be
the order of backing these up (and then in what order should the databases
be restored)
Yes, at least master and msdb, but the order of backing them up is not
important. Master would be the first to be restored

> c) Does the MSDE login information also needs to be backed up. At max I
will have just one custom login and sa. I am using SQL Authentication.
The logins are stored in master.

> d) Will I need to close my application and make sure that my apps' MSDE
instance is not running before backing up the databases.
No, backups are an online operation.

> d) I want to provide user the option to choose the backup location. How
will I figure out if they chose something like tape drive or zip drive. How
should I handle this scenario.
That's an application issue, you stated that you were providing them a front
end UI to backup the databases. The destination of the backup files is just
one of the BACKUP command arguments. There are also several arguments that
apply only to tape backup operations that you might want to take into
consideration.

> e) Also how can I find out if there is sufficient space on the hard drive
to backup the database. Are there any standard sql functions to check that.
There isn't a function that will estimate the size of the backup, but you
can read the msdb..backupset table to get the size of the last backup (or an
average of the last several) and estimate the size based on that.

> f) Should I do database log file backup too while backing up the database.
Not at the same time, although it won't hurt anything, there's just no
reason to do them at the same time.

> sorry for so many questions.
> thanks
> newbie
>
> Print | Copy URL of this post
>
> Expand All Collapse All
>|||Thanks don. Here are few more concerns that I have:
1> Is there any way to zip the .bak files into just 1 file ising T-sql or wi
ll I have to resort to 3rd party solutions for compression (as .Net doesn't
have any compression lib)
2> What happens if one user chooses to backup while others are still working
on the same database (imagine a small office setup with few computers and m
y app on all of them and the db on one central machine). Will the backup wor
k fine in this situation or
will the users need to log off.
3> Also we are not aiming at schedule backups option right now. Our clients
will have to go to the UI screen and backup the database themselves when the
y want to. So in this situation should I restore system databases also when
the users choose to restore
my app's db from the backed up location. And do I need to backup the system
databases always when they choose to backup my apps db or just before any ma
jor important changes.
Thanks

reposting: bkup and resore strategy. Please reply

Hi,
My vb.net app will use database which will be deployed using MSDE on client machines. I will provide UI to client in my app to backup and restore the database. My app will work with one database, for e.g. MyAppDB. My question is what are the best pract
ices for doing this:
a) Should I choose Full or Simple mode. There is no mission critical data. But as my clients will be using MSDE, will setting up the recovery mode as Full cause the database and log size limits to cross the 2GB limit soon. Is it better if I choose SIMP
LE recovery mode. What do you suggest.
b) Should I back up the system databases also, if yes then what should be the order of backing these up (and then in what order should the databases be restored)
c) Does the MSDE login information also needs to be backed up. At max I will have just one custom login and sa. I am using SQL Authentication.
d) Will I need to close my application and make sure that my apps' MSDE instance is not running before backing up the databases.
d) I want to provide user the option to choose the backup location. How will I figure out if they chose something like tape drive or zip drive. How should I handle this scenario.
e) Also how can I find out if there is sufficient space on the hard drive to backup the database. Are there any standard sql functions to check that.
f) Should I do database log file backup too while backing up the database.
sorry for so many questions.
thanks
newbie
Print | Copy URL of this post
Expand All Collapse All
See answers inline:
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:3DFC5E47-73A3-4CFC-9161-0AF13E789AD5@.microsoft.com...
> Hi,
> My vb.net app will use database which will be deployed using MSDE on
client machines. I will provide UI to client in my app to backup and
restore the database. My app will work with one database, for e.g. MyAppDB.
My question is what are the best practices for doing this:
> a) Should I choose Full or Simple mode. There is no mission critical
data. But as my clients will be using MSDE, will setting up the recovery
mode as Full cause the database and log size limits to cross the 2GB limit
soon. Is it better if I choose SIMPLE recovery mode. What do you suggest.
I almost always use either full or bulk-logged, although there are a few
instances where simple is appropriate. Basically you need to determine in
the event of failure do you want to be able to recover to the point of
failure, or is it good engoug to recover from the last full backup? As for
size limits, you can control the size of the log by backing it up regularly.
Besides, I am pretty sure that the 2Gb size limit is on the data file, not
the log.

> b) Should I back up the system databases also, if yes then what should be
the order of backing these up (and then in what order should the databases
be restored)
Yes, at least master and msdb, but the order of backing them up is not
important. Master would be the first to be restored

> c) Does the MSDE login information also needs to be backed up. At max I
will have just one custom login and sa. I am using SQL Authentication.
The logins are stored in master.

> d) Will I need to close my application and make sure that my apps' MSDE
instance is not running before backing up the databases.
No, backups are an online operation.

> d) I want to provide user the option to choose the backup location. How
will I figure out if they chose something like tape drive or zip drive. How
should I handle this scenario.
That's an application issue, you stated that you were providing them a front
end UI to backup the databases. The destination of the backup files is just
one of the BACKUP command arguments. There are also several arguments that
apply only to tape backup operations that you might want to take into
consideration.

> e) Also how can I find out if there is sufficient space on the hard drive
to backup the database. Are there any standard sql functions to check that.
There isn't a function that will estimate the size of the backup, but you
can read the msdb..backupset table to get the size of the last backup (or an
average of the last several) and estimate the size based on that.

> f) Should I do database log file backup too while backing up the database.
Not at the same time, although it won't hurt anything, there's just no
reason to do them at the same time.

> sorry for so many questions.
> thanks
> newbie
>
> Print | Copy URL of this post
>
> Expand All Collapse All
>
|||Thanks don. Here are few more concerns that I have:
1> Is there any way to zip the .bak files into just 1 file ising T-sql or will I have to resort to 3rd party solutions for compression (as .Net doesn't have any compression lib)
2> What happens if one user chooses to backup while others are still working on the same database (imagine a small office setup with few computers and my app on all of them and the db on one central machine). Will the backup work fine in this situation or
will the users need to log off.
3> Also we are not aiming at schedule backups option right now. Our clients will have to go to the UI screen and backup the database themselves when they want to. So in this situation should I restore system databases also when the users choose to restore
my app's db from the backed up location. And do I need to backup the system databases always when they choose to backup my apps db or just before any major important changes.
Thanks

REPOST: Re: internet connection to msde

You probably need to open up UDP on your firewall also. Here a link with
some information about connecting to SQL Server through a firewall:
http://tinyurl.com/m3g9
Jim
"TJS" <nospam@.here.com> wrote in message
news:113fgtcgdki6f7d@.corp.supernews.com...
> yes, it is going thru the firewall during the test.
> there are no firewall logs.
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%23W5wvWbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
>
Thank you everyone. After installing another instance of MSDE, following
some advise from this forum, I was finallly able to return a dataset from a
webservice I wrote. But I still think it reminds more of gambling than
science;-) Could even display MSDE data in my PocketPC application, connected
via GPRS! Great!
When I tried create a similar webservice on another computer today, I got
stuck in Visual Studio when I single stepped to conn.Open( ) which gave me
the exception message "Failed to login to myMachine\ASPNET".
My question: Why does VS try to login to an account that does not work?
How do I prevent this to happen? Why does it try to login to a non-existing
account?
I don't understand.
"Jim Young" wrote:

> You probably need to open up UDP on your firewall also. Here a link with
> some information about connecting to SQL Server through a firewall:
> http://tinyurl.com/m3g9
> Jim
> "TJS" <nospam@.here.com> wrote in message
> news:113fgtcgdki6f7d@.corp.supernews.com...
>