Can't backup a database - also -Full vs Incremental

[dmvdmv]

Happy Holidays!
I am trying to backup 2 SQL databases and even though my history says it is successful, nothing backs up. If I do a test run it looks good and says it will backup the 13gb but the actual backup give me 500kb with nothing on it. I am checking off both the mdf and ndf extensions in my selection.

My reason for doing this is that we do a Full backup on Friday and Incrementals on Mon - Thurs. The problem is if someone loads a database on Monday, I get errors on the Monday night backup saying there was no Full Backup of the database so it can't perform the Incremental. If anyone has any workarounds for this issue, that would be great.

In the meantime, I'm trying to do Fulls and it just won't work. What am I doing wrong? I can backup other things....just not any databases.

 

[justin2000]

you need the SQL option to be able to backup the open SQL databases.

or you can use sql to dump the databases to a file, and then use be to backup those files.

 

[dmvdmv]

I backup SQL databases all the time on my Friday Full backups. I am just trying to select these 2 on a separate backup for right now.

 

[justin2000]

yes but what do you use to backup the sql databases all the time on my Friday Full backups? Do you stop the SQL service?

By you saying this - "am checking off both the mdf and ndf extensions in my selection."
That means to me you are selecting to backup the actual physical files themselves - not using the sql agent.

Now unless the mdf and ndf are closed you will not get a backup of them unless you are using the SQL agent to backup the databases themselves.
BE 9.x also has active exclusions in place for SQL and exchange databases, so it won't even try to back up those files, even if you have selected them.

 

[SQLBill]

You cannot - I repeat CANNOT - backup the .mdf, .ndf, or .ldf files while they are in use. That will not work. Okay, it will work, you might get a backup but it won't be one that SQL Server will be able to use. Just try restoring them and using them. They will be marked corrupt. UNLESS your SQL Server is offline or the databases detached when you do that backup.

-SQLBill

 

[dmvdmv]

Well, as you can see, I am not the SQL expert here. Thank you both for the info. At least I know what the problem is.

Have a Happy Holiday!

 

[SQLBill]

If there is a period of time the SQL Server can be offline, backup the files then. To restore, all you need to do is copy the files from tape to disk and attach the databases to SQL Server (there is an attach command that is used).

If SQL Server can not be taken offline you have two options.

1. Use SQL Server backup commands to create a backup on disk. Then use BE to copy the backup files to tape. Restoring would require copying the files from tape to disk and then using the SQL Server RESTORE command. Con: This process can take time as you are really doing two backups (one to disk and then one to tape). Pro: This is the recognized way of doing the backups. Less chance of the files being corrupted and unusable by SQL Server. Less expensive (maybe - if you don't have the disk space, it could be more expensive to get more disks).

2. Buy the Veritas Agent for SQL Server. Use that to backup the SQL Server databases. Pro: this can be quicker since it's only one backup. Cons: Can be expensive - do you have a license already for this agent? SQL Server doesn't guarantee that the Veritas backups will actually work with SQL Server (I had problems in the past where I wasn't notified of a patch and all my backups were useless. I don't use the Agent anymore, I use option 1).

-SQLBill