MSSQL backup solution

I have a MSSQL database mdf/log that is several tens of gigabytes. I do not want to take the database offline with the MSSQL Management Tool to copy it for scripted backups because of the disruption in service.

I need to back up the thing while it is online and being used. If I use the Windows server 64 bit MSSQL import/export tool it disrupts the online database connections in the backup and it doesn't back up things like dbo stored proceedures and MSSQL functions also stored in the database.

So regardless of cost, I need a solution to back up this mega database on a scheduled process without disrupting connections to it, and without taking it offline.

I would like this to run as a Windows service that can run at a scheduled frequency like a crontab.

I won't bother to mention how easy and free this is on other platforms, because at this point I just want the solution.

How much are you willing to pay me for that?

Just hookup an old dot-matrix printer and write a couple line routine that loops through the relevant sys tables and data tables and prints out the 0s and 1s. Restore is a biatch but it's a non- disruptive and pretty inexpensive.

@PaoloM:

If you want to come over to the office and open up a CSV file and manually copy a few GBs of data using copy paste from rows in the SQL management tool to Excel, I'll give you minimum wage just for kicks. It will give you a nice respit from testing Dungeons and Dragons.

Does anybody know if Acronis MSSQL backup will do it?

Will this one do it? http://www.valesoftware.com/

I find that doing MSSQL backups with the server tools MS provides drops connections to the online MDF, and also the MS SQL backup tools do not back up fn() functions and stored proceedures, nor do they set identities or correctly copy any other meta schema data from MSSQL when you use them.

I won't mention how poor they are considering Microsoft made them for their own database, I am only asking for the best MSSQL scheduled backup which backs up stored proceedures, views, identity and key information for columns and stored fn functions on a schedulable basis.

1. Won't take the .mdf offline

2. needs to clone the DB to another disk or remote without dropping connections or disrupting existing instance

3. needs to run as a service and create scheduled backups

4. needs to clone all views, stored proceedures, stored functions, identities and everything else Microsoft's lousy import/export tools don't for whatever reason.

5. needs to work on 50+ GB database

6. would help if it .tgz compressed the data or bzipped it.

Send some money and i will send you a copy of some "getting started with SQL Server" book ...

@blowdart: With AWS RDB, all you do is click on the "create snapshot" button. But you don't even have to do that because it backs up 8+ days automatically. EC2 also has a snapshot button.

But there is no MSSQL RDB is there?

I do not want a real time replica. I asked for scheduled backup solutions, not read replicas or multi-master.

The MS Windows Server 2008 R2 backup tool did a backup, after I restored it, Identities were missing from columns, functions were gone, and the columns that used functions were broken, ect... It also started dropping live connections to the server while it was backing up, and I had to restart the client after the backup.

Why are you suggesting that after reading what I wrote about it not working correctly?

I need a scheduled backup, not replica, that is not related to the broken import/export tools in 2008 R2. I still need a valid suggestion.

@blowdart:

Also, why aren't .aspx/view exception stack traces logged in the event log? 

.jsp/JSTL stack trace exceptions are in catalina.out.

AFAIK only .dll exceptions are in the event log. That doesn't seem right. So if you have a missing object reference in ViewData['objectReference'] it just dies on the page, and you get no stack trace anywhere? Even PHP provides this. Why Microsoft?

@blowdart: I will try the SQL management tool's maintenance plan next.

What about the lack of .aspx/view exceptions in the event log?

@blowdart: Other people with only HTML skills have to edit the views for various reasons.

I can not pre-compile them. No other language forces you to precompile views to get stack traces from them. 

For instance if a .war is unpacked and deployed to Tomcat I can edit a .jsp and still get output in catalina.out. Same with PHP and other runtimes.

Why would I have to compile .aspx files to get error logging? Doesn't this seem wrong to you?

@blowdart:

I had to enable the SQL Agent or AgentXP which it said was disabled for security reasons to do scheduled backups.

http://technet.microsoft.com/en-us/library/ms178127.aspx

What now? 

What did I just open up that was security restricted?

Also, I am still not getting .aspx view stack traces in the Application event log, only .dll compiled errors, so I dunno what you're running.

1 hour ago, 01001001 wrote

Also, I am still not getting .aspx view stack traces in the Application event log, only .dll compiled errors, so I dunno what you're running.

Although I'm generally a Winforms guy and ASP.NET is a side line, I'll try my best to give an answer (since no one else has so far).

As I understand the process, IIS compiles the code on the fly and executes the assembly within the IIS process. If unhandled exceptions are thrown by the assembly, IIS logs those to the Event Log (because the code is running in the IIS process any unhandled exceptions ultimately come to IIS). If the code handles it's own exceptions and doesn't re-throw them then IIS will never be aware of them and  they won't appear in the Event Log -- that is the fault of the coder, not the fault of IIS.

You're seeing .dll compiled errors -- do you mean errors IIS throws while trying to compile the assembly?  That would mean the assembly never runs, hence no exceptions in the Event Log.

Hopefully if I'm way off the mark someone will correct me.

Herbie

@blowdart:

I find it seriously amusing you did this via SQL commands

I used the wizard. This is your job.