Backup using Microsoft Access
-
Hi
I need to back up a db using Microsoft Access 2000 to permit the user to
run the backup
Select a named folder
Create the backup file with the date of the backup as part of the file name.
Then permit the user to call such a backup and restore to SQLS erver
It has to be done using Microsoft Access.
Anyone any ideas?
Martin
-
First off they will not be able to backup to a local location it has to be a location on the server or a unc path as long as your server is configured so. The user account will also have to have sufficient rights. Then beyond that you could create an SP that a form can call to run the backup based on their input.
-
Try using SQL-DMO via VBA.
--------------------
Colt 45 - the original point and click interface
-
You might use something like that (part of my logshipping-procedures):
/* ---------------------------------------------------------------------------*/
-- Vollbackup.sql
-- T-SQL-Script zum Erstellen eines Vollbackups aller Datenbanken eines
-- beliebigen Microsoft-SQL-Servers (2000)
-- Erstellt am: 3.11.2003
-- Author:Karl Klingler, karl.klingler@dr-stahl.de
/* ---------------------------------------------------------------------------*/
CREATE PROCEDURE vollbackup
@DestSrv sysname
@DestPath sysname
AS
PRINT '++++++++++ Begin der Vollsicherung vom '+convert(char(22),getdate(),113)+' +++++++++++'
-- Variablen deklarieren
declare @dumpfile varchar(200) -- Pfad der Sicherungsdatei
declare @copycmd varchar(2000) -- Kopierbefehl
declare @name varchar(80)-- Name der Sicherungsdatei
declare @i int-- Laufvariable
declare @Backupdir sysname-- Wo werden die Backupdateien gespeichert
declare @RetainDays int-- Nach wie vielen Tagen sollen die Backupdateien gelöscht werden
declare @BaseBUFileDatalength int-- Wo im Filename fängt das Datum an
declare @BUFile sysname--Backupdatei
declare @rc int--ReturnCode
-- Cursor zum Auslesen der DB-Namen
DECLARE dbnames_cursor CURSOR
FOR
SELECT name
FROM dbo.sysdatabases where name 'tempdb'
OPEN dbnames_cursor
DECLARE @dbname sysname
-- Variablen füllen
select @Backupdir = 't:\backups\'
select @name=@@SERVERNAME+'_Vollsicherung_'+DATENAME(dw,getdate())+'_'+REPLACE(STR(DATEPART(dd,getdate()),2)+'.'+STR(DATEPART(mm,getdate()),2)+'.'+STR(DATEPART(yy,getdate()),4)+'_'+STR(DATEPART(hh,getdate()),2)+'.'+STR(DATEPART(mi,getdate()),2)+'_Uhr',' ','0')+'.bak'
select @dumpfile = @Backupdir +@name
select @i = 0
select @RetainDays = 7
-- Alle DB-Namen auslesen und die Befehle jeweils für jede DB ausführen
FETCH NEXT FROM dbnames_cursor INTO @dbname
WHILE (@@FETCH_STATUS -1)
BEGIN
IF (@@FETCH_STATUS -2)
BEGIN
select @i = @i + 1
SELECT @dbname = RTRIM(@dbname)
-- dbcc checkdb (@dbname) with no_infomsgs, tablock
-- dbcc opentran (@dbname) with tableresults
-- dbcc updateusage (@dbname)
-- execute sp_dboption @dbname
IF ( @i = 1)
BEGIN
-- Bei der ersten DB das File anlegen
EXEC ('BACKUP DATABASE [' + @dbname + '] TO DISK = '''+@dumpfile+''' WITH INIT , NOUNLOAD , NAME = '''
+ @dbname + ''', SKIP , STATS = 100, NOFORMAT ' )
END
ELSE
BEGIN
-- Bei den weiteren DBs nur anhängen
EXEC ('BACKUP DATABASE [' + @dbname + '] TO DISK = '''+@dumpfile+''' WITH NOUNLOAD , NAME = '''
+ @dbname + ''', SKIP , STATS = 100, NOFORMAT ' )
PRINT @dbname
END
END
PRINT convert(char(22),getdate(),113)
FETCH NEXT FROM dbnames_cursor INTO @dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
PRINT '+++++ Datenbank auf Platte gesichert: '+convert(char(22),getdate(),113)+' ++++++++++++'
WAITFOR DELAY '00:00:05'
select @copycmd = 'copy ' + @dumpfile + ' \\'+@DestSrv+'\'+@DestPath
EXEC master..xp_cmdshell @copycmd
PRINT 'Befehl "' + @copycmd + '" ausgeführt: '+convert(char(22),getdate(),113)+' ++++++++++++'
WAITFOR DELAY '00:00:05'
/* --- Logshipping: Löschen der veralteten Backupdateien auf dem Produktivserver */
--Building up Table of Files to Delete
CREATE TABLE #DirOut
(
[Output] varchar(255)
)
SET @copycmd = 'dir "' + @BackupDir + '\' + '*.bak" /B'
print @copycmd
/* --- Logshipping: Ende des Löschen der veralteten Backupdateien auf dem Produktivserver */
INSERT #DirOut EXEC master..xp_cmdshell @copycmd
DECLARE BUFiles CURSOR FOR
SELECT [Output] FROM #DirOut WHERE [Output] IS NOT NULL
FOR READ ONLY
OPEN BUFiles
FETCH NEXT FROM BUFiles INTO @BUFile
WHILE @@FETCH_STATUS = 0
BEGIN
--Reconstruct DateTime From Filename
If PATINDEX ( '%Vollsicherung%' , @BUFile ) >0
SELECT @BaseBUFileDatalength = LEN( @BUFile )-24
else
If PATINDEX ( '%TL-Sicherungen%' , @BUFile ) >0
SELECT @BaseBUFileDatalength = LEN( @BUFile ) - 14
else
break
--Compare Date
--print @BUFile
select SUBSTRING(@BUFile,@BaseBUFileDatalength+1,10)
IF DATEDIFF(d,CONVERT(datetime,SUBSTRING(@BUFile,@BaseBUFileDatalength+1,10)),getdate()) > @RetainDays
BEGIN
SELECT @copycmd = 'del "' + @BackupDir + '\' + @BUFile + '"'
PRINT 'Deleting File : '+ @BUFile
PRINT 'Command to be Executed : ' + @copycmd
EXEC @rc = master..xp_cmdshell @copycmd
END
FETCH NEXT FROM BUFiles INTO @BUFile
END
CLOSE BUFiles
DEALLOCATE BUFiles
DROP TABLE #DirOut
SET NOCOUNT OFF
PRINT '+++++++++++++ Ende der Vollsicherung vom '+convert(char(22),getdate(),113)+' ++++++++++++'
/* ------------------------------------ Ende --------------------------------------*/
GO
Best regards
karl -
yes the best thing is to make a command in your Form and call the above stored procedure (with sending the parameters)..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply