I have set up an automatic backup daily with my Maxtor external drive. Problem is it keeps showing 0 bytes changed....
Drive works fine, doing a back-up manually with the RMS menu works fine...
My question is can anyone tell me exactly which files are backed-up when you use the back-up database option within the RMS software. With that information I may be able to figure out what the problem is with my daily automatic back-up.
Also, should I be backing up the MySQL database
Any comments, suggestions, or tips on this issue would be greatly appreciated...
Most backup programs without open file option will not backup the database while it's running.
You will need to stop the server with batch file containing: Net Stop Mssqlserver or export the database to a bck file and then capture that with your backup solution. You can do that with Enterprise Manager from the SQL Trial, or use suggestions from the following sql script.
--This Transact-SQL script creates a backup job and calls sp_start_job to run the job.
-- Create job.-- You may specify an e-mail address, commented below, and/or pager, etc.-- For more details about this option or others, see SQL Server Books Online.USE msdbEXEC sp_add_job @job_name = 'myTestBackupJob', @enabled = 1, @description = 'myTestBackupJob', @owner_login_name = 'sa', @notify_level_eventlog = 2, @notify_level_email = 2, @notify_level_netsend =2, @notify_level_page = 2-- @notify_email_operator_name = 'email name'go
-- Add job step (backup data).USE msdbEXEC sp_add_jobstep @job_name = 'myTestBackupJob', @step_name = 'Backup provot Data', @subsystem = 'TSQL', @command = 'BACKUP DATABASE madison TO DISK = ''c:\backup\madison.bck''', @on_success_action = 3, @retry_attempts = 5, @retry_interval = 5go
-- Add job step (backup log).USE msdbEXEC sp_add_jobstep @job_name = 'myTestBackupJob', @step_name = 'Backup provot Log', @subsystem = 'TSQL', @command = 'BACKUP LOG madison TO DISK = ''c:\backup\madison.bak''', @on_success_action = 1, @retry_attempts = 5, @retry_interval = 5go
-- Add the target servers.USE msdbEXEC sp_add_jobserver @job_name = 'myTestBackupJob', @server_name = N'(local)'
-- Run job. Starts the job immediately.USE msdbUSE msdbEXEC sp_add_jobschedule @job_name = 'myTestBackupJob', @name = 'ScheduledBackup_msdb', @freq_type = 4, --daily @freq_interval = 1, --once @active_start_time = '220000' --(3:30 pm) 24hr HHMMSS
Thanks for the help...
Looks pretty straight--forward but my experience level w/MySQL db's is limited...
Where do I find the SQL Trial?
Do I enter above script in the SQL Trial or is it just an example script? (I do realize it would have to be edited w/my data)..
Also, do know of a good tutorial that can be downloaded for the RMS?
Where do I find the SQL Trial? - Google "SQL Trial"
Do I enter above script in the SQL Trial - Use SQL Query Analyzer or HQ/SO Administrator
Also, do know of a good tutorial - Real the manual, help file, install notes, newsgroup, forum, etc.
For real help with install, training, and operations, you need to contract with an MS Partner.
This is what we use and then burn it to a CD/DVD with Nero for Off site storage.
c:cd\cd backupdel *.bckdel *.log
osql -S (local) -E -Q "Backup Database RMSPOSMIS to DISK='C:\BACKUP\RMSPOSMIS.BCK'" -o C:\BACKUP\RMS_BACKUP.log
del *.zipc:\progra~1\winzip\wzzip.exe c:\backup\RMSPOSMIS.ZIP c:\backup\*.bck c:\backup\*.log
This works for Us as a Scheduled task in Windows XP Pro
Thanks for the help ...
In our case we bought an external drive (Bufallo 1 Terabyte) So in times of emergency we could simply plug it out on our server. So we create an automatic backup using SQL Enterprise Manager. Our local backup is working and its destination is on our drive d a partition od our main disk. The problem taht we encounter about the external was, it doesnt backup on the said schedule, even if I created a folder from the external. Another day has passed we checked that the drive is not formatted to FAT32, it was NTFS so mostly backup drive needs only a 20GB or FAT32 format. Then after that we successfully backup now our database. Hope it can help also.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics