September 4, 2013 at 4:19 pm
I have full backup jobs that run nightly. I would like to create a maintenance plan to run a 'logical' backup weekly. Can someone point out any scripts for this? I know I can R-Click in Object Explorer and generate scripts for this, but once placed in a stored procedure, it would be a static script. I need a dynamic script to capture new tables.
September 4, 2013 at 4:57 pm
What exactly do you mean by 'logical' backup and why do you want one?
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
September 5, 2013 at 4:16 am
Do you mean an export of the data?
I'd look at Powershell to do this. You can have it walk the user tables of a database and generate export files for each. The beauty of it is, you can load the tables from the database instead of having to list them beforehand. There are multiple examples of this available online.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2013 at 7:58 am
Yes, I guess I do mean to export the data. We have an old VB script that exports all the data and objects once a week. This is useful when you need to restore a single table instead of the entire DB. However, not all the objects are being exported and I am not proficient in VB to fix the script. It seems to easy to R-click and export the data so I figured surely there was maintenance plan option or something prepackaged I could use to perform this task. I didn't want to reinvent the wheel! 😉
September 5, 2013 at 8:04 am
Many of the 3rd party backup tools allow you to restore individual objects from a normal backup. If that's an option, it might be worth looking into.
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
September 5, 2013 at 8:37 am
facemann (9/5/2013)
Yes, I guess I do mean to export the data. We have an old VB script that exports all the data and objects once a week. This is useful when you need to restore a single table instead of the entire DB. However, not all the objects are being exported and I am not proficient in VB to fix the script. It seems to easy to R-click and export the data so I figured surely there was maintenance plan option or something prepackaged I could use to perform this task. I didn't want to reinvent the wheel! 😉
Not really. The main mechanism for this sort of thing is backups. That's what they're for. But, if you need to do exports instead, you will be building a wheel (not so much inventing one).
Like Gail says, some 3rd party tools get the job done. I work for Red Gate Software. Our tools, SQL Compare & SQL Data Compare can be used to compare to backup files directly to pull out structures or data. You can try them out if you like[/url]. There is a fourteen day free trial. You can automate them through the command line or through Powershell, all sorts of stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2013 at 8:49 am
btw (because I'm sure someone would bring it up sooner or later)...
MySQL has this kind of thing 'built in', because that's how MySQL does backups (or did last time I checked), by scripting table definitions and insert statements. It's not how SQL Server does backups.
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
September 5, 2013 at 8:41 pm
GilaMonster (9/5/2013)
that's how MySQL does backups (or did last time I checked)
Still MySQL does the same way scripting objects and its data in the native backup (Mysqldump).
September 6, 2013 at 8:15 am
Thank you all for your replies. We do research and the purpose of the export is to have a text only archive of the data available in addition to the full backup. I think I will use this <script [/url] > to capture the table structure. I still need a method to export the data. bcp is the only method I can think of without going 3rd party. The trick is getting a dynamic list of existing tables to the script. PowerShell is still out of my grasp as this is a SS 2005 instance. We plan to upgrade to SS 2008 but that is probably 6 months out. I am not sure how to proceed unless I refresh my C# and Visual Studio skills. :unsure:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply