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

DateTime Format in Localized version of MSDE

Hi
How do we determine the date time format in a SQL Server instance.
Specifically I would like to know, if the date time data type in SQL Server
is Language Specific or Language Neutral.
We are facing the following problem. I have a managed app, which is
localized. I need to update some data from the managed app to the
database(we are using MSDE). When I run the managed app in Italian locale,
with Italian build of MSDE, the database update fails.
The problem we figured out was, the date time cast in database fails. This
is because the time separator(for Italian locale) in .NET app is a period,
while in SQL MSDE(Italian build) it is a colon (
The following are my queries.
1. Is Date Time data type in SQL Language specific or Language Neutral? If
it is Language Neutral, I assume it will use the en-US culture, correct me
if I am wrong.
2. If date time is language specific, how is the collation set. Is it set by
default when MSDE is installed? Will the Operating System language version,
impact the collation, while installing MSDE.
3. When I run the query 'Select GetDate()' in Query Analyzer, the time
separator is displayed as a colon. Does the language version of SQL Server
tools(query analyzer/enterprise manager) have an impact on the date time
displayed?
Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
Thanks
RT
In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
<ramjee_t@.infosys.com> writes
>Hi
>How do we determine the date time format in a SQL Server instance.
>Specifically I would like to know, if the date time data type in SQL Server
>is Language Specific or Language Neutral.
>We are facing the following problem. I have a managed app, which is
>localized. I need to update some data from the managed app to the
>database(we are using MSDE). When I run the managed app in Italian locale,
>with Italian build of MSDE, the database update fails.
>The problem we figured out was, the date time cast in database fails. This
>is because the time separator(for Italian locale) in .NET app is a period,
>while in SQL MSDE(Italian build) it is a colon (
>The following are my queries.
>1. Is Date Time data type in SQL Language specific or Language Neutral? If
>it is Language Neutral, I assume it will use the en-US culture, correct me
>if I am wrong.
Not exactly. Physically in the database it is always stored the same way
however, the collation order does determine some of the supported
formats displaying and updating a DateTime field.

>2. If date time is language specific, how is the collation set. Is it set by
>default when MSDE is installed? Will the Operating System language version,
>impact the collation, while installing MSDE.
The default collation order is set when the instance of MSDE is
installed. However under MSDE 2000 / SQL Server 2000 the collation order
of each database can be different. Thats up to you when you CREATE the
DATABASE (ie: you determine the default collation order for each
database). In addtion, you can specify the collation order to use on
each Table and Field if really required. Check BOL for the CREATE
DATABASE and TABLE. You are therefore quite capable of using the same
collation order for every instance of MSDE you install regardless of
country.

>3. When I run the query 'Select GetDate()' in Query Analyzer, the time
>separator is displayed as a colon. Does the language version of SQL Server
>tools(query analyzer/enterprise manager) have an impact on the date time
>displayed?
Its all about handling dates in a consistent manor.
Its generally a good idea to always update a DataTime field using the
universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
confused about which part is the month and day (ie: 2005-01-05 is always
5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this
also solves international differences.
Its also therefore generally a good idea to always retrieve the DateTime
in a known format. Therefore using a command like "SELECT
Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
return the date in a UK format for example. That way your application
does not get confused and the localisation to the client is left to your
application.

>Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
No Problem.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||Hi Andrew,
You are mixing up collation, which is a property of character type columns
and variables in SQL Server, and the language that can be set for a
connection or user. The last one determines how dates as strings are
interpreted.
1) You are right that datetime and smalldatetime in SQL Server are stored in
a binary, language-neutral format. How the datetimes are displayed depends
on the client application however. For example Query Analyzer will by
default display dates in yyyy-mm-dd hh:mm:ss format. Enterprise Manager on
the other hand will use your Windows local settings to decide the display
format. How dates as strings are interpreted when inserting, updating or
deleting depends on the language setting for the connection, which are by
default derived from the language settings for the current user, although
they can be set explicitly with SET LANGUAGE.
2) As I said earlier, collation is irrelevant for datetime. The default
language settings for the user (login) are derived from the language in
which SQL Server is installed, but can be specified explicitly when creating
the login, or changed afterwards.
3) "yyyy-mm-dd hh:nn:ss" is not a safe format for datetime. Try the
following:
SET LANGUAGE us_english
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
GO
SET LANGUAGE british
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
There are 2 safe date formats in SQL Server:
yyyymmdd
and
yyyy-mm-ddThh:mm:ss
It is _not_ a good idea to always retrieve the datetime in a known string
format. Just retrieve the datetime as datetime, and let your application and
your user decide how to display is in a human-readable format. A properly
designed application will just use the Regional Settings from Windows to
decide how to display dates, and if you return datetime in a string format,
you just end up converting datetime values twice.
Jacco Schalkwijk
SQL Server MVP
"Andrew D. Newbould" <newsgroups@.NOzadSPANsoft.com> wrote in message
news:n0wpLfBXbrpCFwsj@.zadsoft.gotadsl.co.uk...
> In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
> <ramjee_t@.infosys.com> writes
> Not exactly. Physically in the database it is always stored the same way
> however, the collation order does determine some of the supported formats
> displaying and updating a DateTime field.
>
> The default collation order is set when the instance of MSDE is installed.
> However under MSDE 2000 / SQL Server 2000 the collation order of each
> database can be different. Thats up to you when you CREATE the DATABASE
> (ie: you determine the default collation order for each database). In
> addtion, you can specify the collation order to use on each Table and
> Field if really required. Check BOL for the CREATE DATABASE and TABLE. You
> are therefore quite capable of using the same collation order for every
> instance of MSDE you install regardless of country.
>
> Its all about handling dates in a consistent manor.
> Its generally a good idea to always update a DataTime field using the
> universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
> confused about which part is the month and day (ie: 2005-01-05 is always
> 5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this also
> solves international differences.
> Its also therefore generally a good idea to always retrieve the DateTime
> in a known format. Therefore using a command like "SELECT
> Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
> return the date in a UK format for example. That way your application does
> not get confused and the localisation to the client is left to your
> application.
>
> No Problem.
> --
> Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
> ZAD Software Systems Web : www.zadsoft.com