February 9, 2015 at 11:16 pm
Hi,
Can you please advise me on possible solutions to automate schema backups in a database.
Currently we have a "Payroll" database, each client on their own schema.
We need to backup each client ( schema ) before they can process the month and roll over to the next month.
I know that in SQL we do not have a option to backup schema's.
Unfortunately I need to find a solution none the less as they deployed this before I could tell them that you can't backup schema's.
Best prise will be if I can somehow create a Proc , then create a Job that Executes the Proc.
Kind Regards,
February 9, 2015 at 11:37 pm
You can use VS database project to backup schema. But I am not aware of how to automate the database project such that database project will get synced with database.
February 9, 2015 at 11:41 pm
Or you can use Red gate schema compare tool. Please refer link below
http://www.red-gate.com/products/sql-development/sql-compare/
Check whether you can execute command line or this tool or VS Database Compare
February 10, 2015 at 4:18 am
You are probably thinking of Oracle exports, which are not available in SQL Server.
The closest thing to an Oracle export is a collection of BCP files. BCP is an utility to import/export data, but it works on a single table at a time.
You could create a script to automate the execution of BCP looping through all the tables in a schema. Powershell could be a good fit for this task.
-- Gianluca Sartori
February 10, 2015 at 4:34 am
In SQL Server, you can't take backups at a schema level. Oracle's schemas are closer to SQL Server databases than they are to SQL schemas. If it's a requirement to be able to restore just one client's data to an earlier point in time, then the client's data should each be in its own database.
You can't restore part of a SQL Server database to an earlier point in time.
About the only way to achieve what you want would be to export (bcp out) the data to flat files and then re-import if needed, however this is not a trivial task as you'd need to take foreign keys into account.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2015 at 5:00 am
Another option is a bacpac file.
Be aware that some limitations apply.
In the export dialog you can select a single schema. The import process is straightforward.
-- Gianluca Sartori
February 10, 2015 at 11:01 am
You might want to consider putting each client schema in a different db, then using synonyms in the main db to point to those objects. It will look to apps, and the client, as if everything is in one db when it really isn't.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply