April 18, 2010 at 7:18 am
I have set up a backup plan to include Check Integrity, Rebuild Index, Backup Database. The Database is in SIMPLE Recovery Mode. I have tried the Wizard as well as the "Designer" but both are giving me errors as below. I have setup similar Maint Plans for other sites and they are all working fine. In this case only the backup is failing.
Similar setup for System Databases is working fine on the same SQL Server.
Here is the Error:
Error No. 1073548784
Executing the query "BACKUP DATABASE [Production] TO DISK = N'E:\\SQL Backup\\Production\\Production_backup_201004181602.bak' WITH RETAINDAYS = 14, NOFORMAT, NOINIT, NAME = N'Production_backup_20100418160258', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thanks.
April 18, 2010 at 7:28 am
Further to my above post, I find that SQL Integration Services is not appearing/running under the Services. Is this required for backup? As mentioned, System Databases are being backed up correctly. Thanks
April 18, 2010 at 10:35 am
SSIS is not required. (you have at applied at least SP1 , yes?)
The double '\\' does not look right in your backup path. amend that to single '\'.
N'E:\\SQL Backup\\Production\\Production_backup_201004181602.bak'
check the SQL errorlog as well, I expect you will see an OS error 3 with this backup
---------------------------------------------------------------------
April 18, 2010 at 10:45 am
Yes, as mentioned in the subject, I have installed SP3.
I have to check where the \\ is coming from as I remember that the path in the Plan is only E:
But the same path is used for System Databases which are backing up.
Thanks
April 18, 2010 at 11:23 am
the SQL errorlog should provide the answer.
---------------------------------------------------------------------
April 19, 2010 at 6:32 am
The SQL logs show Stack Dump, etc. Probably a bad area in the disk?
Anyway what I did was to copy the Database from Mgmt Studio on the same server with a new DB name (oldname_new)and took a back of the new copied database. The copy went thru properly, both mdf and ldf size of the copy is same as original.
I was able to backup the new copied database (not backing up Log) but the back up size is only around 900 KB whereas the mdf is around 3 GB. Does that mean that this Database is having some corrupt areas or blanks? Thanks
April 19, 2010 at 7:50 am
file size is not necesarily representative of space used in database
run sp_spaceused to see how much actual data in database
then run dbcc checkdb to confirm database ok
---------------------------------------------------------------------
April 19, 2010 at 8:07 am
Thanks. Will try that
April 19, 2010 at 8:13 am
Prithvi(4/18/2010)
what I did was to copy the Database from Mgmt Studio on the same server with a new DB name (oldname_new)and took a back of the new copied database.
Did you use "\\" in your destination path (where you are backin up databse) ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 19, 2010 at 10:50 am
In the Maint Plan Designer, the path for the backup was E:\Backups. No \\ etc just plain drive letter path.
April 19, 2010 at 2:45 pm
Bhuvnesh (4/19/2010)
Prithvi(4/18/2010)
what I did was to copy the Database from Mgmt Studio on the same server with a new DB name (oldname_new)and took a back of the new copied database.Did you use "\\" in your destination path (where you are backin up databse) ?
The Backup maintenance task adds the extra slashes into the generated code to escape the actual slash that is needed in the path. It is normal.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 19, 2010 at 10:01 pm
Thanks for the update. Elsewhere in this forum, it was mentioned that \\ should be made \ etc but no mention how this can be done! Anyway, the copy to a new DB and backup is working but cant figure out why the back up size is only around 900 KB whereas the mdf is over 3 GB!
April 19, 2010 at 10:22 pm
prithvi-1147092 (4/19/2010)
Thanks for the update. Elsewhere in this forum, it was mentioned that \\ should be made \ etc but no mention how this can be done! Anyway, the copy to a new DB and backup is working but cant figure out why the back up size is only around 900 KB whereas the mdf is over 3 GB!
The backup will only be as large as the data in the database - not the size of the mdf file. So, your data file (mdf) is 3GB - and you are only using 900KB of that.
Don't worry - it's a database, you will eventually use all of that space and then some as you add data. :hehe:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 19, 2010 at 10:51 pm
Yes, but it has grown suddenly. It was around 700 KB six months back and now 3+ GB. Shrink database hasnt made any difference. Thanks.
April 20, 2010 at 7:07 am
Well, you shouldn't be shrinking the database - it is just a waste of time since it will grow again.
What are the sizes of the mdf/ldf file?
What is the recovery model for the database?
If recovery model is full or bulk-logged, are you backing up the transaction log?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply