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 run windows DOS command in SQL?

I want to check to see if any error files from SQL uploads are present in a
folder.
I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
How can I check a windows directory for *.err files in SQL syntax? osql?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1There is an 'undocumented' function that will do that for you
[xp_fileexist]. See this article:
Functions -UnDocumented Check to Verify File Existence
http://www.sqlservercentral.com/columnists/bknight/xpfileexist.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"jsheldon via SQLMonster.com" <u2880@.uwe> wrote in message
news:6909a192e8dbf@.uwe...
>I want to check to see if any error files from SQL uploads are present in a
> folder.
> I want to run IF EXISTS *.err then run EXEC xp_sendmail stored procedure
>
> How can I check a windows directory for *.err files in SQL syntax? osql?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

How to Rollback Transactions

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

You can scope transactions to a container, not just a package. Use Sequence containers to give some separation between the four load processes and their file operations.

You could also use the manual method of managing transactions, (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx), just repeat the pattern four times, once for each load, with four separate connections, one for each load, and the associate SQL tasks used to manage thetransaction.

|||

hi darren,

thanks for your post it was very helpful.

i have used foreach containers for the four load process as i can get multiple files.

now i have one more problem,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

|||

You could make the foreach loop to go through all files and then use expressions in the precedence constraints to decide whether a file needs to be processed or not. You may need aditional variables to get the dates and compare it. A simplier example here:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

How to Rollback Transactions

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

You can scope transactions to a container, not just a package. Use Sequence containers to give some separation between the four load processes and their file operations.

You could also use the manual method of managing transactions, (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx), just repeat the pattern four times, once for each load, with four separate connections, one for each load, and the associate SQL tasks used to manage thetransaction.

|||

hi darren,

thanks for your post it was very helpful.

i have used foreach containers for the four load process as i can get multiple files.

now i have one more problem,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

|||

You could make the foreach loop to go through all files and then use expressions in the precedence constraints to decide whether a file needs to be processed or not. You may need aditional variables to get the dates and compare it. A simplier example here:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

How to retrieve most recent file from local directory?

Hi,

Here's my situation. Every day I will be downloading extracts to a folder. The extracts are named:

20070529.Extract1.csv

20070528.Extract1.csv

20070527.Extract1.csv

20070529.Extract2.csv

20070528.Extract2.csv

20070527.Extract2.csv

So, on any given day, I will want to find the most recent versions of Extract1 and Extract2, for example:

20070529.Extract1.csv & 20070529.Extract2.csv

How would I go about doing this?

Thanks much

How about a ForEach loop on the files in the directory, with a script task inside to compare each filename to a variable. If the filename is "greater", store it in the variable, otherwise go to the next filename. You'd need two variables, one for Extract1 and one for Extract2.

After the ForEach, your variables should hold the correct filenames.

|||

I have an example that you may find helpful:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

|||

Hi Rafael,

I looked at your example. I'm afraid I don't understand how you set the EndDate and StartDate variables. I see that there is hardcoded value in the variables, but what if I don't want to do this?

In my case, on any given day the file will have the previous day's timestamp.

So if today is May 31, the file will have the name:

20070530Extract1.csv

So what I want to do is just get the MOST RECENT file that is less than today's date (since on Monday the time lag will be longer than just one day, due to no files being generated on weekends).

So I don't really need EndDate, just StartDate, and in my case, StartDate must be dynamic, ie today's date.

How do I do this?

Thanks

|||

Actually, I probably do need Start and End Dates. But this becomes complicated due to weekends. That is, on Monday, the file I want is from the previous Friday.

If there's an easy way to solve this problem, I'd love to find out what it is.

Meanwhile, I am wondering if I can leverage SQL Server to help me find what I need. That is, run an EXEC SQL Task, and store the result of my query into a variable. That would solve my problem very nicely. Then it doesn't matter what day of the week I'm running this, nor would I have to concern myself will setting start dates and end dates for comparison.

Here is part of an old backup script I used to use at another job:

CREATE TABLE #FileList (FileName SYSNAME NULL)
/* populate temp table with names of backup files from local backup directory */
INSERT INTO #FileList EXEC master..xp_cmdshell 'dir /B D:\Data\MSSQL\DB_BACKUP\DBServer\*.BAK'

/* select most recent backup file from temp table for recovery */
SET @.NewestFile = (SELECT TOP 1 FileName FROM #FileList WHERE FileName IS NOT NULL AND FileName LIKE 'myFile[_]%' ORDER BY FileName DESC)

So then my question would be, how do I save the "FileName" from the above query into a variable?

Thanks|||

Well, another thought. I don't want to have to use SQL Server to solve this problem.

I just need a script that will find the most recent file from a directory. I'm not even sure I want (or need to) to bother with end dates and start dates at all. There's gotta be a very simple script that can do this?

|||

sadie,

There is more than one way to get the latest file in the directory. Perhaps you can use the first part of my blog post to loop over the files in the folder capture the date part out of the file name. Then follow Jwelch suggestion, use a script task to compare the date of the current iteration with the previous one until you get the latest one.

A simpler approach may be to implement a table where you keep the latest file date you has processed, then have the package to retrieve that value to a variable and with a For Each loop, loop through all files in the folder. Depending in your requirements you write the rest of the logic by comparing the date of the current iteration against the date in the table. At the end of the process the table should be updated with the new latest date.

|||

You may find this thread helpful:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=789950&SiteID=1

|||

Hi,

Well, I can't believe it but I figured it out myself, but thanks to Francesco Balena's "Programming Visual Basic...", I found a code snippet that worked for me.

Here is the solution to my problem. A very simple script, and I just need to output the file name to a variable:

Public Sub Main()

Dim rootDi As New DirectoryInfo("d:\myDir")

Dim newestFile As String

Dim fileDate As Integer

Dim fileDateSaved As Integer = 0 'initialize variable

For Each fi As FileInfo In rootDi.GetFiles("*.csv")

fileDate = CInt(Left(fi.Name, 8)) 'set to current file date in loop

If fileDate > fileDateSaved Then

fileDateSaved = fileDate

newestFile = fi.Name 'set variable to fName loop variable

End If

Next

MsgBox(newestFile)

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Congrats! Best way to learn, as Phil says.

Aren't you missing handling for two different files? Extract1 and Extract2? The code above will only give you one filename.

Simple fix:

Repeat your For Each loop, but use a different filter for each of them:

For Each fi As FileInfo In rootDi.GetFiles("*Extract1.csv")

For Each fi As FileInfo In rootDi.GetFiles("*Extract2.csv")

You'd also need 2 variables, one per filename to store.

|||

I should probably mention, this only works because I created a separate directory for each type of file I'm receiving.

That is, Extract1's goes in their own folder, Extract2's go in another folder.

|||That does make it a bit easier |||I'm trying to use this code but when i go to type dim rootdi as new directoryinfo("c:\mydir")

it does have directoryinfo available as a command i'm using vb6|||sorry it does not have directoryinfo as a command option