DB Backup Methods

I am trying to better understand the process of backing up a SQL (MSDE)
database. There are several 3rd party tools on the market that include a
backup function, but are dependent on first establishing a connection to the
server. My specific questions are:
1) is it possible to merely copy (using a DOS or windows copy command) the
DB file for backup purposes? If so, is there any trick to restoring the
files?
2) what is the advantage (or requirement) of performing the backup through a
connection to the server? What is the process for creating such a backup --
does it depend on T-SQL statements?
My goal is to create a backup/restore routine within my application so that
the process is very easy for my end-users to manage and also something that
could be set up on an automated schedule.
Appreciate any help! --RS
hi RS,
Rob S wrote:
> I am trying to better understand the process of backing up a SQL
> (MSDE) database. There are several 3rd party tools on the market
> that include a backup function, but are dependent on first
> establishing a connection to the server. My specific questions are:
> 1) is it possible to merely copy (using a DOS or windows copy
> command) the DB file for backup purposes? If so, is there any trick
> to restoring the files?
actually it is... MSDE defaults all it's created databases with the
autoclose option, so that when not in use, they will be freely available for
copy tasks, but you have to consider connection pooling befor proceeding as
that can prevend direct file access... you could then stop the service, copy
the required dbs and restart it, but I do not consider this a best practice
approach.. stopping the service will provide a clean "close" of each db, and
you could even replace the data and log files of a database for a sort of
dirty restore operation, but, again, this is a dirty trick..
BTW... personally I always change that db setting to off, in order to speed
up I/O operation... and this will be a problem in this kind of management...

> 2) what is the advantage (or requirement) of performing the backup
> through a connection to the server? What is the process for creating
> such a backup -- does it depend on T-SQL statements?
yes, it relies on Transact-SQL statements, both for backup and restore
task.. and this is the preferred solution as you do not depend on particular
db settings (autoclose) and a proper backup of the db is taken in "native"
and supported mode... the server can remain up and running for other users
(only during backup, as restore requires esclusive access) supporting "hot"
backups... depending on the recovery model of the dbs, different backup
solutions are available, as full backup and differential, granting high(er)
speed recovery for faults events
for the relative statement syntax and synopsis please have a look at
http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
depending on your needs you can use the SQL Server Agent to schedule backups
in order to plan the correct backup strategy based on your actual need and
critical information stored in the database, as long as depending frequency
of data changes and the like in order to plan for disaster recovery...
http://msdn.microsoft.com/library/de...kprst_63eh.asp
http://msdn.microsoft.com/library/de...kprst_7drn.asp
http://msdn.microsoft.com/library/de...kprst_7kvb.asp

> My goal is to create a backup/restore routine within my application
> so that the process is very easy for my end-users to manage and also
> something that could be set up on an automated schedule.
automated schedule should be better provided via standard SQL Server Agent
managed jobs, where you usually provide a job step of T-SQL subsystem with
the desired backup statement (and this is the way to plan for disaster
recovery strategy as you can not count on your users for manual execution of
"suggested" manual clean up tasks), where monotonic and one shot backup(s)
can be easily directly implemented in your application executing both
dynamic T-SQL statements (say from an ADO/Ado.Net connection) or via
predefined stored procedures provided by your db metaschema...
the same is true for restore operation, tha can be implemented within your
application without problems...
but restore operations are usually not that often perfomed as backup... or I
hope so :D
just a caveat, remember that sysadmin server role membership, or db_owner or
db_backupoperator database role membership is required in order to perform
backup operations, where sysadmin or dbcreator server roles membership is
required for restores, so you have to consider this kind of requirements as
well in your authorization plans...
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
|||Here's a .bat file that I run (part of a LONG run) that backs up all
teh DBs that I have on my local machine (MSDE, so no visual tool to
use)
osql is a command line utility that I think comes with SQL Server, all
installs.
You can check out the parameters of osql.exe -- -E is for logging in
via a trusted connection, I think, -S is for the server name, -Q is the
statement that you want to run.
As for the statement, you can see that it is pretty straightforward --
I haven't really investigated all the options that you can do with
incremental backups or anything, but this should get you started -- to
do this from your progra, I'm assuming VB6, then use the Shell command.
; back it up to a temp file
osql -E -Spbk01imd34\PM -Q"BACKUP DATABASE [EMDB] TO DISK =
'C:\Documents and Settings\Matt\My Documents\DataBackup\EMDB.bak' WITH
INIT "
; copy that temp file to the network that gets backed up.
xcopy "C:\Documents and Settings\Matt\My Documents\DataBackup"
"H:\DataBackup" /S /E /F /H /R /Y
Matt
|||Andrea & Matt,
Great input! I now have some clear direction to begin some testing. My
sincere thanks. --Rob
"Rob S" wrote:

> I am trying to better understand the process of backing up a SQL (MSDE)
> database. There are several 3rd party tools on the market that include a
> backup function, but are dependent on first establishing a connection to the
> server. My specific questions are:
> 1) is it possible to merely copy (using a DOS or windows copy command) the
> DB file for backup purposes? If so, is there any trick to restoring the
> files?
> 2) what is the advantage (or requirement) of performing the backup through a
> connection to the server? What is the process for creating such a backup --
> does it depend on T-SQL statements?
> My goal is to create a backup/restore routine within my application so that
> the process is very easy for my end-users to manage and also something that
> could be set up on an automated schedule.
> Appreciate any help! --RS

Day of the month

I am trying to code a proceedure that will run every weekend. This process will run for a number of hours beginning at 0900 Saturday and ending at 2100 Sunday. However, on the 3rd weekend of the month, I need it run for a shorter time and to also skip some tables. What I have now to find the day of the month I need is

Begin
SET @.3rdSaturday = @.1stDayMonth
/*Loops until the date of the first Saturday(DayofWeek #7)
of the current month is found */
WHILE DATEPART(dw,@.3rdSaturday) <> 7

/*Adds 1 day to the first day of the month until it
reaches the date of the first Friday of the month */
SET @.3rdSaturday = DATEADD(d,1,@.3rdSaturday)
/*Adds 14 days to the first Friday of the month.
The end result is the 3rd Fridays date for the current month*/
SET @.3rdSaturday = DATEADD(d,14,@.3rdSaturday)
End

I am trying to find a better way to find the 3rd Saturday (or similar) of the month. I have also investigated DATENAME function but ran into the same problem. Also if I were using SQLDMOFreq_Monthly I could do it but I am trying to write all of this in T-SQL.

Any and all help is appreciated.

Akinja

Akinja-Earl:

Take a look at this article about establishing a calendar table:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Also, are you running on SQL Server 2005 or SQL Server 2000?

Dave

|||Thanks, I will.|||

If you prefer not to use a calendar table you can try something like:

set nocount on

declare @.dateOfMonth datetime
set @.dateOfMonth = '10/13/6'

declare @.monthString varchar (2)
set @.monthString = convert (varchar (2), month (@.dateOfMonth))
declare @.yearString varchar (4)
set @.yearString = convert (varchar (4), year (@.dateOfMonth))

/*
select sampleDate,
datepart (dw, sampleDate) as dayOfWeek,
datename (dw, sampleDate) as nameOfDay
from ( select @.monthString + '/' + convert (char(2), 14 + iter) + '/' + @.yearString as sampleDate
from small_iterator (nolock)
where iter <= 7
) a
where datepart (dw, sampleDate) = 7
*/

-- --
-- create an iterator if you don't already have one.
-- --
declare @.iterator table (iter integer not null)
insert into @.iterator values (1)
insert into @.iterator values (2)
insert into @.iterator values (3)
insert into @.iterator values (4)
insert into @.iterator values (5)
insert into @.iterator values (6)
insert into @.iterator values (7)

select sampleDate,
datepart (dw, sampleDate) as dayOfWeek,
datename (dw, sampleDate) as nameOfDay
from ( select @.monthString + '/' + convert (char(2), 14 + iter) + '/' + @.yearString as sampleDate
from @.iterator
) a
where datepart (dw, sampleDate) = 7

-- -
-- S A M P L E O U T P U T :
-- -

-- sampleDate dayOfWeek nameOfDay
-- - --
-- 10/21/2006 7 Saturday

|||

Thanks you for both suggestions. I like the calendar creation method since I can reuse it for other purposes. The iteration method will work with what I am working now since I am trying to get it done quickly.

Akinja

Datepart??

OK, I need to process info from my database in quarterly increments...
I think there is a datepart function to do this but I am not sure..
I need to determine what quarter it is and assign it to a declared variable
named @.QuarterInfo
Any suggestions?datepart("q",yourdatetime)

Dateadd - Time accumulation

Hi.
I'm in the process of converting seconds into an HH:mm:ss format using the
following statement...
=DateAdd("s", Sum(Fields!TimeInSeconds.Value), #01/01/0001#)
This gives me an absolute value of the beginning of time, which is great.
The problem arises when I clock over the 24h scenario, and my output with
the format of HH:mm:ss just displays as 01:00:00 (if 25 hours have
accumulated).
The base value of my field would now show as #01/02/001 01:00:00#.
I would like to see this as 25:00:00
Any ideas.
Thanks
GaryYou may want to look at the DateDiff VB.NET function.
E.g. =Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
Details on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctdatediff.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:2E179571-45B0-4C7B-A316-FBA29E8F53D7@.microsoft.com...
> Hi.
> I'm in the process of converting seconds into an HH:mm:ss format using the
> following statement...
> =DateAdd("s", Sum(Fields!TimeInSeconds.Value), #01/01/0001#)
> This gives me an absolute value of the beginning of time, which is great.
> The problem arises when I clock over the 24h scenario, and my output with
> the format of HH:mm:ss just displays as 01:00:00 (if 25 hours have
> accumulated).
> The base value of my field would now show as #01/02/001 01:00:00#.
> I would like to see this as 25:00:00
> Any ideas.
> Thanks
> Gary