Board index » delphi » Backup and Restore MSDE database

Backup and Restore MSDE database


2003-10-19 11:25:00 PM
delphi231
Hi,
I want to backup and restore MSDE tables or database from
my program. I don't know how to program it. Can someone show me
a simple code of example, or is there any sample code on the web
site that i can refer to?
Thanks in advance.
lsg
 
 

Re:Backup and Restore MSDE database

I wrote a pair of routines. One to backup an SQL database; the other to
reload it (on another machine).
Essentially, to backup:
"use" the database to find the name of the files
"use" the master database
Detach the database
Copy the files
Reattach the database and files
To restore:
Copy the files
Attach the database and files
If necessary, create the login to use the database
call sp_Change_Users_Login with "AutoFix" to allow the login to use the
database.
If you'd like a copy of my routines, please send my your e-mail address
to joegriffin<at>cix<dot>co<do>uk.
My routines use a bit of SysTools and Raize components, but you will be
able to see (and work around) those bits.
... Joe
Member of the UK Borland User Group
 

Re:Backup and Restore MSDE database

Quote
Hi,
I want to backup and restore MSDE tables or database from
my program. I don't know how to program it. Can someone show me
a simple code of example, or is there any sample code on the web
site that i can refer to?
Thanks in advance.
Here's one way...
Regards,
--
Jim
--Backup
CREATE PROC sp_dbackup(
@dbname varchar(40))
AS
PRINT 'Backing up '+@dbname
EXEC('BACKUP DATABASE '+@dbname+' TO DISK = N''C:\Program Files\Microsoft
SQL Server\MS SQL\BACKUP\'+@dbname+'.bak'' WITH INIT , NOUNLOAD , NAME =
N'''+@dbname+' backup'', NOSKIP , STATS = 10, NOFORMAT ')
GO
--Restore
CREATE PROC sp_drestore(
@dbname varchar(40))
AS
PRINT 'Restoring '+@dbname
EXEC('RESTORE DATABASE ['+@dbname+'] FROM DISK = N''C:\Program
Files\Microsoft SQL Server\MS SQL\BACKUP\'+@dbname+'.bak''WITH FILE = 1,
NOUNLOAD, RECOVERY, REPLACE');
go