How to Schedule MSDE Backup

Hi guys,
I have an application that runs on MSDE. I need to back up the database on a
dialy basis. The backup file gets written to this folder (C:\backup) and
then I have found a backup software that will automatically write it to a
DVD+RW, overwriting the previous backup file.
To make the schedule easy I will use 31 DVD+RWs, one for each day of the
month. That way I will always have at least a month worth of backups.
I was hoping that somebody could help me with a small SQL script that will
backup my database from Monday thru Saturday, automatically. Also it'd be
great if the backup file would contain a name and the date of that day.
Thanks a lot for your help.
Hi,
MSDE will not come with GUI. So u have to use SQLMAINT.exe. See the below
URL for more info.
http://msdn.microsoft.com/library/de...maint_19ix.asp
Thanks
Hari
SQL Server MVP
"Tom Bombadill" <Genius_poster@.yahoo.com> wrote in message
news:%23D3lQ7zsFHA.2792@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have an application that runs on MSDE. I need to back up the database on
> a dialy basis. The backup file gets written to this folder (C:\backup) and
> then I have found a backup software that will automatically write it to a
> DVD+RW, overwriting the previous backup file.
> To make the schedule easy I will use 31 DVD+RWs, one for each day of the
> month. That way I will always have at least a month worth of backups.
> I was hoping that somebody could help me with a small SQL script that will
> backup my database from Monday thru Saturday, automatically. Also it'd be
> great if the backup file would contain a name and the date of that day.
> Thanks a lot for your help.
>
|||Hi Hari,
Thanks for your attempt to help me. Unfortunately for your suggestion to
work, I have to install SP3a and for some reason I cannot successfully apply
that. I constantly get the "The instance name specified is invalid" error,
even though I did not specify an instance name and there's only one
installaiton of MSDE on my machine.
What do you suggest?
|||hi Tom,
Tom Bombadill wrote:
> Hi Hari,
> Thanks for your attempt to help me. Unfortunately for your suggestion
> to work, I have to install SP3a and for some reason I cannot
> successfully apply that. I constantly get the "The instance name
> specified is invalid" error, even though I did not specify an
> instance name and there's only one installaiton of MSDE on my machine.
> What do you suggest?
you can have a look at a free prj of mine, at the link following my sign,
qhich implements a UI similar to Enterprise Manager where you can define a
SQL Server job to backup your db and of course define your schedules as
desired...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>
> you can have a look at a free prj of mine, at the link following my
> sign, qhich implements a UI similar to Enterprise Manager where you
> can define a SQL Server job to backup your db and of course define
> your schedules as desired...
alternatively, you can prepare a cmd file like
<-->
OSQL -Usa -Pyour_pwd -Q"BACKUP DATABASE pubs TO DISK = 'C:\Pubs.bak' WITH
INIT" >c:\err.txt
<-->
and schedule it via standard OS AT or SCHTASKS, the native Win scheduler...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
You're an angel, thanks a lot!
I downloaded your interface and installed it. I scheduled a backup and will
be testing this thoroughly.
A couple of questions re the interface if you don't mind:
1- I don't want to accumulate backups to the same file, since I will be
taking a fresh full backup on a separate DVD+RW everyday. How do I make sure
the backups do not keep appending to the same file day after day? Do I do
that just by leaving the ADD box unchecked, in the backup properties?
2- How can make it so that the backup file contains the day of the backup?
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Andrea,
> You're an angel, thanks a lot!
you are wellcome.. thank you :D

> I downloaded your interface and installed it. I scheduled a backup
> and will be testing this thoroughly.
> A couple of questions re the interface if you don't mind:
> 1- I don't want to accumulate backups to the same file, since I will
> be taking a fresh full backup on a separate DVD+RW everyday. How do I
> make sure the backups do not keep appending to the same file day
> after day? Do I do that just by leaving the ADD box unchecked, in the
> backup properties?
leaving the "add to media" check box unchecked will add the "WITH INIT"
statement to the full backup statement, so that every backup set will be
initialized..

> 2- How can make it so that the backup file contains the day of the
> backup?
can you please expand on this? I'm sorry but I did not understand your
requirement..
and for DbaMgr2k related questions please feel free to contact me directly,
in order not to be OT on this public microsoft NG...
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
[vbcol=seagreen]
What I mean by that is to have the backup file name include the date the
backup was performed. I'll give you an example:
For today, it would be called "SJDB 09072005.bak"
For tomorrow, "SJDB 09082005.bak"
For the day after tomorrow, "SJDB 09092005.bak"
And so on...
I hope that makes sense!
Andrea, once again thank you for your service to the community and sharing
your hard work with others.
|||Also Andrea,
Do you know how long it would take for a full backup to run for a full size
MSDE database (2048 MB)?
I ask because I wanted to know how much time to allocate before I schedule
the copy of the backup file to DVD.
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Hi Andrea,
>
> What I mean by that is to have the backup file name include the date
> the backup was performed. I'll give you an example:
> For today, it would be called "SJDB 09072005.bak"
> For tomorrow, "SJDB 09082005.bak"
> For the day after tomorrow, "SJDB 09092005.bak"
> And so on...
> I hope that makes sense!

ok, you have to "edit" the backup statement of the generated job's step ...
assuming you are backing up pubs database to C:\ (replace C:\ with the
existing folder you want to backup to), you can provide the file name to
include the current date casting (
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp )
GETDATE() function result as required..
the final statement will look like:
DECLARE @.FileName nvarchar(25)
SET @.FileName = 'C:\' + 'pubs ' + CONVERT(varchar(10) , GETDATE() , 110 ) +
'.bak'
BACKUP DATABASE [pubs] TO DISK = @.FileName WITH INIT ,
NOUNLOAD ,
NAME = N'pubs BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT
as regard your date format, I'd prefer the standard ISO format, thats to say
YYYYMMDD (CONVERT using 112 style), as this data format , 20050908 (for
today) is more readable and sorts better then 09082005..
you can go further, in the "advanced tab", and specify you want to "output"
the result of the execution (like standard messages and/or errors, if any)
to a text file.. if you want to, specify a file name (a text file) in the
"Transact-SQL script command options" ->output file
this will output, in case of success, something like
<-->
Job 'BackUp DB ['pubs'] - #08/09/2005 12.25.12#' : Step 1, 'BackUp DB
['pubs']' : Began Executing 2005-09-08 12:37:16
53 percent backed up. [SQLSTATE 01000]
99 percent backed up. [SQLSTATE 01000]
Processed 224 pages for database 'pubs', file 'pubs' on file 1. [SQLSTATE
01000]
100 percent backed up. [SQLSTATE 01000]
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1. [SQLSTATE
01000]
BACKUP DATABASE successfully processed 225 pages in 0.242 seconds (7.586
MB/sec). [SQLSTATE 01000]
<-->
or the problem found, if any, you can inspect...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

how to save listbox multiple select values

Hi,

I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.

I grab the values for the selection as follow:

foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}

but I'm not sure on what to do next and could use some help.

Thanks
Germanoshouldn't lbAttendees have .SelectedItems?

Next, you should be passing the values to the database through sql or other dataaccess means.|||>>Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there.

I would strongly suggest option 2 (Normalize).

>>I'm not sure how to loop through each value and write it to the database table.

Well you have some options.

i) Loop through your items on the client and perform inserts (one row at a time).

ii) Package up the values as an xml chunk and use openxml to insert (set based method, less chatty)

ii) Package up the values as a delimited string and perform parsing and insert at the server.

And there are others (using OO: collections, persistance frameworks mechanisms, etc..)sql

How to run TSQL from vb net 2003

Hi All
I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
In vb6 I called a function...
retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
which ran the TSQL script against OSQL. The script basically created the
Database in MSDE then imports a heap of tables from Access 2000.
Private Function ExecCmd(cmdline$)
'used for MSDE to launch osql
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret&
' Initialize the STARTUPINFO structure:
start.cb = Len(start)
' Start the shelled application:
ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
'Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
Call GetExitCodeProcess(proc.hProcess, ret&)
Call CloseHandle(proc.hThread)
Call CloseHandle(proc.hProcess)
ExecCmd = ret&
End Function
What is the preferred method of running a TSQL script from VB Net 2003 using
sqlclient class?
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
> In vb6 I called a function...
> retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
> which ran the TSQL script against OSQL. The script basically created
> the Database in MSDE then imports a heap of tables from Access 2000.
> Private Function ExecCmd(cmdline$)
> 'used for MSDE to launch osql
> Dim proc As PROCESS_INFORMATION
> Dim start As STARTUPINFO
> Dim ret&
> ' Initialize the STARTUPINFO structure:
> start.cb = Len(start)
> ' Start the shelled application:
> ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
> NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
> 'Wait for the shelled application to finish:
> ret& = WaitForSingleObject(proc.hProcess, INFINITE)
> Call GetExitCodeProcess(proc.hProcess, ret&)
> Call CloseHandle(proc.hThread)
> Call CloseHandle(proc.hProcess)
> ExecCmd = ret&
> End Function
> What is the preferred method of running a TSQL script from VB Net
> 2003 using sqlclient class?
http://www.absistemi.it/permalink=tn169.ashx
this will use the very same feature with .Net..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

how to run sql scripts from commandline in MSDE

Hi,
I am trying to run sql scripts from Commandline, i am getting the error "the database doesnot exist use sp_helpdb to show available databases".
I am running this sql query to create database table, appreciate your help.
ThanksWhat SQL query are you running and what is the connection string you're using?

How to retrieve identity in stored procedure for updatable subscription??

We have transactional replication setup between SQL Server 2000 on Win2003
and MSDE on Win2003 (both SP3a). SQL Server is the publisher and is pushing
an updatable subscription (queued updating) to the MSDE instance.
We are trying to insert master and detail rows into a pair of tables via a
stored proc. After inserting the master row, doing @.@.IDENTITY to retrieve
the ID for the child row's foreign key returns NULL, causing the child row
insert to fail. Looking at the table after running the proc, the parent row
does have a valid identity value.
Any suggestions on how to make this work?
Thanks,
Steve Klein
Steve,
I'd be interested in what DBCC CHECKIDENT (tablename) returns (on the
publisher and the subscriber).
Also, presumably this insert was done on the subscriber?
Do you have just queued or immediate updating subscribers with queued
failover?
Are there any other triggers on the table (apart from the replication ones
if we are talking about the subscriber)?
Regards,
Paul Ibison
|||I have tried to repro this and it does work. Can you post the problem proc
for us?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hbbbf92amjt6a@.corp.supernews.com...
> We have transactional replication setup between SQL Server 2000 on Win2003
> and MSDE on Win2003 (both SP3a). SQL Server is the publisher and is
pushing
> an updatable subscription (queued updating) to the MSDE instance.
> We are trying to insert master and detail rows into a pair of tables via a
> stored proc. After inserting the master row, doing @.@.IDENTITY to retrieve
> the ID for the child row's foreign key returns NULL, causing the child row
> insert to fail. Looking at the table after running the proc, the parent
row
> does have a valid identity value.
> Any suggestions on how to make this work?
> Thanks,
> Steve Klein
>
|||Paul--
My apologies for not sending the following reply to the group. That was my
goal...
Paul--
Thanks for the response. DBCC CHECKIDENT shows no problems (publisher
and subscriber).
We don't have triggers (other than those for replication) on the tables and
there are not any other transactions affecting the transaction in our
distributed database. The test insert was done via a stored procedure on
the subscriber. This
stored procedure does the following:
1) Insert into table A
2) set @.x = @.@.IDENTITY
3) Insert into table B (using @.x as foreign key back to table A)
We have replication configured for queued updating.
Further info -- if we use IDENT_CURRENT('table_name') instead of
@.@.IDENTITY,
we do get an ID back, but IDENT_CURRENT has (claimed) global scope.
--Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uTduWcTfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Steve,
> I'd be interested in what DBCC CHECKIDENT (tablename) returns (on the
> publisher and the subscriber).
> Also, presumably this insert was done on the subscriber?
> Do you have just queued or immediate updating subscribers with queued
> failover?
> Are there any other triggers on the table (apart from the replication ones
> if we are talking about the subscriber)?
> Regards,
> Paul Ibison
>
|||Hilary--
I'll be able to post the procedure later today. Thanks.
--Steve
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I have tried to repro this and it does work. Can you post the problem proc
> for us?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve Klein" <sklein@.singular.com> wrote in message
> news:10hbbbf92amjt6a@.corp.supernews.com...
Win2003[vbcol=seagreen]
> pushing
a[vbcol=seagreen]
retrieve[vbcol=seagreen]
row
> row
>
|||Steve,
there's something strange here, but I'd check to see if Scope_Identity()
also returns NULL.
Finally, I'll keep an eye on your thread with Hilary where you mentioned
posting up the script, because I'd also like to try to reproduce this.
Regards,
Paul Ibison
|||Paul--
Thanks very much for your interest. I (sheepishly) need to report that the
problem was a form of user error. The table causing the problem had a text
column. The way that the error management was implemented was evidently
causing the @.@.IDENTITY to return NULL.
Why Ident_Current() worked is odd, but since removing the text column caused
the @.@.IDENTITY to work, we are not too inclined to look much further
By the way, we have changed to Scope_Identity() rather than @.@.IDENTITY.
Thanks again.
--Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uGr2g3qfEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Steve,
> there's something strange here, but I'd check to see if Scope_Identity()
> also returns NULL.
> Finally, I'll keep an eye on your thread with Hilary where you mentioned
> posting up the script, because I'd also like to try to reproduce this.
> Regards,
> Paul Ibison
>
|||Hilary--
Thanks for your help. As I indicated in the response to Paul's last post,
this was a stored procedure error, which I would label "user error"... Check
there for more details.
--Steve
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hg0lla3jcmu8f@.corp.supernews.com...[vbcol=seagreen]
> Hilary--
> I'll be able to post the procedure later today. Thanks.
> --Steve
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...
proc[vbcol=seagreen]
> Win2003
via[vbcol=seagreen]
> a
> retrieve
> row
parent
>
|||Steve,
many thanks for the update.
Cheers,
Paul