So we just need to create ddl trigger for the root event ALTER_SERVER_CONFIGURATION and DDL_SERVER_LEVEL_EVENTS. SELECT, , LEVELįROM EVENTCATALOG ORDER BY LEVEL, TYPE_NAME INNER JOIN EVENTCATALOG EC ON TET.parent_type=EC.TYPE Using system trigger monitor the event.ĭDL trigger can monitor the server level DDL, using the script below can list all the DDL trigger hierarchically I was thinking if there is an appropriate way to backup the master database automatically after any change.The first thing is we need to capture the change event. If the server is corrupted, as there is no differential and log backup for master database, you can not restore the database to point of time, there will be data lost. However, if you maintain the system for customer, you will not be notified when customer change the master database. So except for the regular backup, you need to take a full database backup after you change the data upper. Server objects, for instance the linked server, system trigger. Why? because you can not restore master database with norecovery mode, without norecovery, why we need to keep differential backup and log backup? 🙂Ĥ.
Not only transaction log backup, but also differential backup can not be run on master database. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model. However, BACKUP LOG is not supported for master. In SQL BOL, (v=sql.105).aspx, for master database:įor backwards compatibility with earlier versions of Microsoft SQL Server, the recovery model of master can be set to FULL or BULK_LOGGED. Use BACKUP DATABASE to back up the entire master database.īACKUP DATABASE is terminating abnormally. You can only perform a full backup of the master database. How about run backup command?īACKUP DATABASE TO DISK = N'C:\master.dif' WITH DIFFERENTIAL
Well, I can only select "FULL" backup type.
0 kommentar(er)
