Unanswered: backups through EM on remote registration
I have installed Sql Server 2000 evaluation copy on Windows XP, which I use to monitor other sql server 2000 installations (on Windows 2000) through Enterprise manager version 8.
What has been noticed is that if you try and set up a backup job through enterprise manager you do not get the option to remove inactive transaction log entries (yes the database's use full recovery models). Also if you select the verify backup option, it is not included within the job.
This is the same if you try the above on the local instance. This is not a problem as you can use the server installations, but what I am wondering is:
1. Has anyone come across this before?
2. Is it sql server 2000 on XP?
3. Is it the evaluation copy that is causing the problem?
4. Is it none of the above but indeed a far more complex reason which will mess with my head?
As always your help / knowledge is very much appreciated.
As to verify, take a look at Database Maintanace Plans - there you can make it a part of your scheduled job. As to Inactive entries, actually it gets into your scheduled job, it is a default option when backing up transaction log - so you cannot see it in script that was generated by EM. Test for yourself - set up job without checking Remove inactive entries option and you can see that NO_TRUNCATE option is included in your command. Hope that it clears matters up a bit. Regards mojza
Thanks for the reply mojza. I have no problems setting them up on the actual server, just on my PC through EM.
The verify problem is still an issue (i.e. You select it on your PC through EM but it is not part of the TSQL in the job).
However, the remove inactive transactions has got slightly stranger. On the server itself through EM, it is an option when creating a job and not touching the backup option (i.e. Database - complete is ticked by default). If you reselect it (click on it again or select another option and go back to complete) the option dissappears (which it should). Odd!!
As I said this is not really an issue but if anyone knows why I would appreciate the knowledge.
I believe that Verify option in EM simply runs RESTORE VERIFYONLY command against backup set just completed so you can include this command plus some logic if you choose backup by T-SQL, otherwise you can set up Backup job in Database Maintanance Plan Wizard (it uses sqlmaint utility to perform its tasks which has -VrfyBackup switch to achieve backup set validation).
If 'remove inactive transactions' option is enabled in other backup type than transaction log, it's strange behavior and I've never seen that or heard about it. Evaluation edition or SQL on XP shouldn't be the cause. mojza
The actual command it uses to berify if created on the server is:
select @i = position from msdb..backupset where database_name='??' and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='??')
RESTORE VERIFYONLY FROM [????] WITH FILE = @i
I would be interested to know if anyone else can reproduce the 'remove inactive transactions' with a complete backup problem. Essentially all I did was right-click on the database, select backup database and under the options tab the option is there.