Sql Backing up Tables: msde

Showing posts with label msde. Show all posts

Wednesday, March 7, 2012

backup and restore MSDE database

Hello,
We are currently migrating from NT to 2K servers and I
have been assigned to port over an MSDE database from the
NT environment to Windows 2000 Server.
Can someone please point me to a tutorial on how to backup
and restore an MSDE database..
Thanks,
niv
Hi ,
There is no syntax difference in backup and restore commands for a regular
sql server installation and msde.
You can use the following sample commands to perform backup and restore
Examples
A. Back up the entire MyNwind database
Note The MyNwind database is shown for illustration only.
This example creates a logical backup device in which a full backup of the
MyNwind database is placed.
-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
B. Back up the database and log
This example creates both a full database and log backup. The database is
backed up to a logical backup device called MyNwind_2, and then the log is
backed up to a logical backup device called MyNwindLog1.
Note Creating a logical backup device needs to be done only once.
-- Create the backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_2',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_2.dat'
--Create the log backup device.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwindLog1',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwindLog1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_2
-- Update activity has occurred since the full database backup.
-- Back up the log of the MyNwind database.
BACKUP LOG MyNwind
TO MyNwindLog1
http://msdn.microsoft.com/library/de...us/adminsql/ad
_bkprst_8v3n.asp - Detailed informatin about backup and restore operations
along with examples.

Saturday, February 25, 2012

Backup and restore databases In my MSDE

Hi all! How can i backup databases which are running in my MSDE 2000 & then how can i restore them with all the data, Yes i know that their is no visual tool for doing this in MSDE but can we do it with scripts, if Yes then howThe SQL to backup a database in MSDE is:


BACKUP DATABASE { database_name | @.database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
 [ BLOCKSIZE = { blocksize | @.blocksize_variable } ]
 [ [ , ] DESCRIPTION = { 'text' | @.text_variable } ]
 [ [ , ] DIFFERENTIAL ]
 [ [ , ] EXPIREDATE = { date | @.date_var }
 | RETAINDAYS = { days | @.days_var } ]
 [ [ , ] PASSWORD = { password | @.password_variable } ]
 [ [ , ] FORMAT | NOFORMAT ]
 [ [ , ] { INIT | NOINIT } ]
 [ [ , ] MEDIADESCRIPTION = { 'text' | @.text_variable } ]
 [ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
 [ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
 [ [ , ] NAME = { backup_set_name | @.backup_set_name_var } ]
 [ [ , ] { NOSKIP | SKIP } ]
 [ [ , ] { NOREWIND | REWIND } ]
 [ [ , ] { NOUNLOAD | UNLOAD } ]
 [ [ , ] RESTART ]
 [ [ , ] STATS [ = percentage ] ]
]

and to restore:

RESTORE DATABASE { database_name | @.database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
 [ RESTRICTED_USER ]
 [ [ , ] FILE = { file_number | @.file_number } ]
 [ [ , ] PASSWORD = { password | @.password_variable } ]
 [ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
 [ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
 [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
 [ ,...n ]
 [ [ , ] KEEP_REPLICATION ]
 [ [ , ] { NORECOVERY | RECOVERY | STANDBY = {undo_file_name|@.undo_file_name_var} } ]
 [ [ , ] { NOREWIND | REWIND } ]
 [ [ , ] { NOUNLOAD | UNLOAD } ]
 [ [ , ] REPLACE ]
 [ [ , ] RESTART ]
 [ [ , ] STATS [ = percentage ] ]
]

Things in square brackets are not strictly speaking required.

Backup and recovery problem

i've got a project that using MSDE and JAVA
but how can i backup and recovery the database by using Java code??
or any alternative method?Check this :http://www.devx.com/vb2themax/Tip/18621

Sunday, February 19, 2012

Backup /Restore options

I'm trying to decide what path to take in implementing Backup & Restore
processes in my VB6/MSDE application.
Either (a) create and execute Maintenance Plans or (b) use the BACKUP /
RESTORE / DBSHRINK commands in the program.
Can anybody explain to me the pros/cons of using one of the other?
TIA
Jim K
Hi Jim,
This depends upon the type of client in my opinion. A scheduled backup to a
file (for example) is great for small sites (followed by an operating system
backup of that file) but ONLY if they're going to check that the file
date/time keeps changing. If the SQL Agent stops (for example), they'll
blissfully keep backing up the same file and not actually have a backup
(apart from an old one). Maintenance plans have the same issues.
If that's a problem, I'd rather have an option in the program because you
are more likely to have those sorts of places carry out a "procedure" than
to look for something more subtle.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Jim K" <krusej@.megsinet.net> wrote in message
news:OcvVvds5EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I'm trying to decide what path to take in implementing Backup & Restore
> processes in my VB6/MSDE application.
> Either (a) create and execute Maintenance Plans or (b) use the BACKUP /
> RESTORE / DBSHRINK commands in the program.
> Can anybody explain to me the pros/cons of using one of the other?
> TIA
> Jim K
>

Backup / Restore Question - MSDE

MSDE2000

I have an application in which I am running a TSQL command of BACKUP DATABASE and RESTORE DATABASE for the backup and restore commands for my application. For testing purposes, i did the following:

1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
2) Deleted the database completely.
3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the database)

Now I have the database back with all my data. What are the gotchas when doing a backup and restore using this method? I am not relying on transaction logs to restore to a certain point, the user can only restore back to their last backup (may be daily, weekly or monthly)

TIA

--
Tim MorrisonThis method will work find for the SIMPLE recovery model. It doesn't matter
whether or not the target database exists since it will be recreated during
the restore if needed.

Be sure to backup WITH INIT or the backup will append to the existing backup
file and the file will grow indefinitely. Also, consider copying the backup
file elsewhere for disaster recovery.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:YE3Db.545312$Tr4.1480932@.attbi_s03...
MSDE2000

I have an application in which I am running a TSQL command of BACKUP
DATABASE and RESTORE DATABASE for the backup and restore commands for my
application. For testing purposes, i did the following:

1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
2) Deleted the database completely.
3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the
database)

Now I have the database back with all my data. What are the gotchas when
doing a backup and restore using this method? I am not relying on
transaction logs to restore to a certain point, the user can only restore
back to their last backup (may be daily, weekly or monthly)

TIA

--
Tim Morrison|||Yes, I have the database set to simple recovery in my initial SQL script the
user uses to create the database.

I also have WITH INIT in my backup command.

I also discovered that the restore database name does not have to be the
same as the initial database. This is both a benefit and a risk.

I even 100% uninstalled MSDE, and reinstalled (Including SP3a), then
performed my restore command, and everything seems to work perfectly.

Im guessing that if I wanted to send a sample database with my application
that includes sample data, it would be very easy to do using this method.

Im learning more and more every day. I have a SAMS SQL Server 2000 book
which is always helpfull

Tim Morrison

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:7O5Db.1465$Bg5.648@.newsread2.news.atl.earthli nk.net...
> This method will work find for the SIMPLE recovery model. It doesn't
matter
> whether or not the target database exists since it will be recreated
during
> the restore if needed.
> Be sure to backup WITH INIT or the backup will append to the existing
backup
> file and the file will grow indefinitely. Also, consider copying the
backup
> file elsewhere for disaster recovery.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Tim Morrison" <sales@.kjmsoftware.com> wrote in message
> news:YE3Db.545312$Tr4.1480932@.attbi_s03...
> MSDE2000
> I have an application in which I am running a TSQL command of BACKUP
> DATABASE and RESTORE DATABASE for the backup and restore commands for my
> application. For testing purposes, i did the following:
> 1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
> 2) Deleted the database completely.
> 3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the
> database)
> Now I have the database back with all my data. What are the gotchas when
> doing a backup and restore using this method? I am not relying on
> transaction logs to restore to a certain point, the user can only restore
> back to their last backup (may be daily, weekly or monthly)
> TIA
>
> --
> Tim Morrison|||Yes, I have the database set to simple recovery in my initial SQL script the
user uses to create the database.

I also have WITH INIT in my backup command.

I also discovered that the restore database name does not have to be the
same as the initial database. This is both a benefit and a risk.

I even 100% uninstalled MSDE, and reinstalled (Including SP3a), then
performed my restore command, and everything seems to work perfectly.

Im guessing that if I wanted to send a sample database with my application
that includes sample data, it would be very easy to do using this method.

Im learning more and more every day. I have a SAMS SQL Server 2000 book
which is always helpfull

Tim Morrison

Thursday, February 16, 2012

Backup & Restore in MSDE, How

Hi all;

I am looking to add "Make Bakup" and "Make Restore" functionalty in my applicayion menu which uses MSDE for database.

My question is How to add these two functionalty (What is the source code)??

Thanks in advanced!You'll need to use SQLDMO to accomplish this.view post 373287 andview post 389472 should help you get started.

Terri

Tuesday, February 14, 2012

Backup

I have a 2003 server with MSDE on it. When I attempted to backup this server
from another server using Arc Serve, the backup was incomplete because it
could not backup the MSDE database files because they were in use.
Does anyone know of any commands that I may be able to run in a .BAT file to
backup an open database? Or if I have to shut the database down before a
backup, what are the commands to do that? I have searched the Internet and
can not find too much information. Any help is greatly appreciated.
Thank you.
hi,
dlmcguire77 wrote:
> I have a 2003 server with MSDE on it. When I attempted to backup this
> server from another server using Arc Serve, the backup was incomplete
> because it could not backup the MSDE database files because they were
> in use.
> Does anyone know of any commands that I may be able to run in a .BAT
> file to backup an open database? Or if I have to shut the database
> down before a backup, what are the commands to do that? I have
> searched the Internet and can not find too much information. Any help
> is greatly appreciated.
MSDE, as long as SQL Server, supports "hot" backup... backup must be
intended as the native SQL Server backup and not the OS backup... for the
least, you have to stop the server (locally via NET STOP service_name or
remotely via SQL-DMO or whatever)...
if you nead an OS backup I'd suggest to "native" backup MSDE database(s) you
are interested with via the standard
BACKUP DATABASE ...
(http://msdn.microsoft.com/library/de...ba-bz_35ww.asp)
to a specific location and then back it up via OS facilities that file...
this will help not to have down times for your server...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||.... or.. have a look at
http://www3.ca.com/Solutions/ProductOption.asp?ID=2748
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

backup

I'm using ms access 2000 .adp file, and tables on sql server / msde. On the
menu there's a backup option. It creates a file with a .Dat extension. How
do I take the Dat file and restore data? This news group has been real
helpful-- thanks."JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message news:<UXgNc.139258$OB3.8907@.bgtnsc05-news.ops.worldnet.att.net>...
> I'm using ms access 2000 .adp file, and tables on sql server / msde. On the
> menu there's a backup option. It creates a file with a .Dat extension. How
> do I take the Dat file and restore data? This news group has been real
> helpful-- thanks.

It's not clear which menu in which application you're talking about -
is this Enterprise Manager, or some third-party tool? You can test if
it's a valid MSSQL backup like this (from Query Analyzer or osql.exe):

restore headeronly from disk = 'c:\temp\myfile.dat'

This should give you the details of the backup - if you get an error
instead, it's probably not an MSSQL backup.

In general, to backup and restore MSSQL databases, you use the BACKUP
and RESTORE commands, or you can create maintenance plans from
Enterprise Manager which will do this for you. If you're new to this,
I would suggest you spend some time reviewing the "Backing Up and
Restoring Databases" section in Books Online.

Simon|||I'm in ms access 2000, and backup is on a dropdown menu.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0407262346.7e3552b2@.posting.google.c om...
> "JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:<UXgNc.139258$OB3.8907@.bgtnsc05-news.ops.worldnet.att.net>...
> > I'm using ms access 2000 .adp file, and tables on sql server / msde. On
the
> > menu there's a backup option. It creates a file with a .Dat extension.
How
> > do I take the Dat file and restore data? This news group has been real
> > helpful-- thanks.
> It's not clear which menu in which application you're talking about -
> is this Enterprise Manager, or some third-party tool? You can test if
> it's a valid MSSQL backup like this (from Query Analyzer or osql.exe):
> restore headeronly from disk = 'c:\temp\myfile.dat'
> This should give you the details of the backup - if you get an error
> instead, it's probably not an MSSQL backup.
> In general, to backup and restore MSSQL databases, you use the BACKUP
> and RESTORE commands, or you can create maintenance plans from
> Enterprise Manager which will do this for you. If you're new to this,
> I would suggest you spend some time reviewing the "Backing Up and
> Restoring Databases" section in Books Online.
> Simon|||"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message news:<hlXNc.145886$OB3.71566@.bgtnsc05-news.ops.worldnet.att.net>...
> I'm in ms access 2000, and backup is on a dropdown menu.

<snip
Well, I know nothing about Access, so I don't know if it's a generic
backup function, or if it's something specific in your Access
application. If it's a standard Access function, you might get more
help in an Access newsgroup.

Simon

Friday, February 10, 2012

backing up tables

Can anyone tell me how I can backup / restore individual tables?
At the moment I have been backing up the msde database on the development
machine and restoring it onto the web server in order to transfer data.
But I have now reached the point where the data on the web server no longer
matches the data on the development machine. The data on the web server is
good but I have some structural changes to some tables on the development
machine.
Is there a method by which I can update only some of the tables on my web
server?
To date I have been using DBAMGR2K for all data manipulation.
Suggestions would be most appreciated.
Neme
If you want to make schema changes to the tables, you can create a script to
alter the table and add columns etc. You can either do this manually (see
ALTER TABLE in Books Online) or use a tool to generate the scripts for you,
for example SQLCompare from www.red-gate.com.
Jacco Schalkwijk
SQL Server MVP
"June Macleod" <junework@.hotmail.com> wrote in message
news:e7UZhiXzEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Can anyone tell me how I can backup / restore individual tables?
> At the moment I have been backing up the msde database on the development
> machine and restoring it onto the web server in order to transfer data.
> But I have now reached the point where the data on the web server no
> longer
> matches the data on the development machine. The data on the web server
> is
> good but I have some structural changes to some tables on the development
> machine.
> Is there a method by which I can update only some of the tables on my web
> server?
> To date I have been using DBAMGR2K for all data manipulation.
> Suggestions would be most appreciated.
> Neme
>