SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup


Backup

Author
Message
tt-615680
tt-615680
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1609 Visits: 1255
Hi All,


I am trying to back up 6 Databases with the total of 108GB combined, all the databases are in simple recovery. I have tried to backup the databases on an external hard drive of 435GB but for the past 4 nights it hasn't been successful. When I have a look in the Job Activity Monitor the backup keeps saying it is executing but never backs up anything, it is also preventing the Truncate Transaction log backup from running which runs every half hour. I'm using the following script to run the backup:

BACKUP DATABASE 'Databsename'
TO DISK = 'E:\'filename'.BAK'
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32963 Visits: 16637
If your databases are in Simple recovery mode, your transaction log backups will fail. What happens if you run your BACKUP DATABASE statement in a SSMS query window?

John
tt-615680
tt-615680
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1609 Visits: 1255
Thank you for the reply.

When I try to run the Truncate Log job it gives me the following error message:

Msg 3023, Level 16, State 3, Procedure 'stored procedure name', Line 5
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Msg 3013, Level 16, State 1, Procedure 'stored procedure name', Line 5
BACKUP LOG is terminating abnormally.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32963 Visits: 16637
Yes, I would expect that. But you were complaining that your BACKUP DATABASE statement was unsuccessful. What happens when you run that?

John
tt-615680
tt-615680
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1609 Visits: 1255
I set the backup to run at 4am because there are no other backups that occur at that time except for the "Truncate transaction log" job. The job continually runs and when I look for in the Event viewer and Error logs, there are no error or warnings, so I end up having to stop the job.


Thank you.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32963 Visits: 16637
Please copy the command from the job, paste it into a SSMS query window, run it, and tell me what happens.

John
tt-615680
tt-615680
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1609 Visits: 1255
All the Databases are live so I'm concerned backing up any of then during the day. I will set the job to run, just one of the databases (the main database) for tomorrow morning and I will let you know the result.

Thank you very much for your help.
Adiga
Adiga
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5888 Visits: 21012
When you run the job, does the backup file gets created on E drive? Also you may use this query to check the backup status.

SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc



Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
tt-615680
tt-615680
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1609 Visits: 1255
When I check drive E, it has one of the databases backup files but with the file size of 0, which obviously means that nothing has been backed up.
The script that you sent to check the Backup status throws an error; does it work for SQL 2000 Servers?

Thank you for your help.
Ray Mond
Ray Mond
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1421 Visits: 1542
That script won't work on SQL Server 2000. You did post on a SQL Server 2005 forum after all.

Run sp_who2, find if the BACKUP processes are being blocked, if so, by which process.

Do note that given the backup command you are using i.e.

BACKUP DATABASE 'Databasename' TO DISK = 'E:\'filename'.BAK' 



you are appending backup sets to the file, so after 4 days of backing up the 4 databases totaling 108 GB, and assuming most of the database space is used, you will run out of space on that external drive. Also, do check if the external drive has been formatted using FAT32 or NTFS.

Ray Mond
Get a SQL Size 2.0 single-instance license for FREE
Claim one here!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search