SQL 2005 schemas and data backup

  • I currently have serveral servers and more than 20 or so databases that this is becoming a maintence nightmare.I thought we could use schemas to make it a more maintainable situation.For example consolidate all databases into 1 database with 20 different schemas.Now for the problem part.I have no knowledge of how to do what I need done as far as backup and restore.Can the backup be done per schema set(Several tables store procs etc.)?????Right now we can easily backup and restore individuals dbs as needed in the current mess we have.How would I go about this.I need to be able if needed to restore and backup lets say the accounting schema set including data etc.Is this a better solution than what I have?????

  • You cannot backup based on schema. You would need to put the object within each schema on a separate file. Then you could do file backups and restores.

    My advice is to stay with the separate databases if each database supports a specific business process\application. Mainly for the reasons you note. You can backup and restore the specific databases without affecting the other applications, while sticking everything in one database will cause all users to be affected by backups and restores.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply