Backup

Question

Post reply

Backup

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

tt-615680 SSCertifiable Points: 7835 More actions · Answer 1

Ray Mond (8/24/2010)

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.

I have run the backup again just with one of the Databases last night but the same thing happend, where the Backup states that it is still executing but nothing is being backed up.

I did run the following scripts to find out if anything is blocking the job:

sp_who2 and also

SELECT * FROM Master.dbo.sysprocesses WHERE blocked <> 0 and nothing seems to be blocking the job. I have been trying to backup the Databse for sometime now but it doen't seem to work, I've researched for the problem everywhere and I'm not having any luck. Is there anything I'm doing wrong?

Thank you.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

What's the wait type for the process while that backup is running?

Edit: Is that extra quote in your statement a typo?

Should be

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

not

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

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tt-615680 SSCertifiable Points: 7835 More actions · Answer 3

GilaMonster (8/26/2010)

What's the wait type for the process while that backup is running?

Edit: Is that extra quote in your statement a typo?

Should be

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

not

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

I will run the job again tomorrow morning and see what the wait type for the process is and also I will check to see if there are any other processes that is blocking the job.

Thank you.

tt-615680 SSCertifiable Points: 7835 More actions · Answer 4

tt-615680 (8/26/2010)

GilaMonster (8/26/2010)

What's the wait type for the process while that backup is running?

Edit: Is that extra quote in your statement a typo?

Should be

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

not

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

I will run the job again tomorrow morning and see what the wait type for the process is and also I will check to see if there are any other processes that is blocking the job.

Thank you.

I've run the Job again with diffrent times but it still doing the same thing, it's not backing up. I run the script to see the wait type for the process and also if there are any other processses that are blocking the job and here are the results that I got:

kpid block waittype waittime lastwaittype

220 0 0x0000 0 ASYNC_DISKPOOL_LOCK

4808 0 0x0000 0 MISCELLANEOUS

5188 0 0x0000 0 ASYNC_DISKPOOL_LOCK

Thank you.

john.campbell-1020429 Ten Centuries Points: 1194 More actions · Answer 5

Your backup command does not look correct. Try

backup database pubs to disk='e:\pubs.bak'

In your log files job adjust the schedule to stop before your backup begins and start again after the backup finishes.

Work with specific files until you figure out the problem, backup the system databases and get it right there, then do all of the databases.

Run sp_who to see if there are any old backup jobs hanging around causing problems. It there are any, kill them and start fresh.

John.

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 6

AmolNaik

SSCarpal Tunnel

Points: 4767

September 15, 2010 at 4:52 pm

#1221784

run xp_readerrorlog on Query Analyzer and look for any errors, warnings.

Amol Naik

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 7

Jules Bonnot

SSCertifiable

Points: 5488

April 26, 2011 at 3:31 pm

#1316268

using CLR procedure, much more secure,robust free and extensible solution here

http://sql-library.com/?p=186

www.sql-library.com[/url]

subahan.munthamadugu SSChasing Mays Points: 630 More actions · Answer 8

subahan.munthamadugu

SSChasing Mays

Points: 630

May 8, 2013 at 1:46 am

#1613225

Hi,

the databases are in "simple" recovery model then no need to truncate the log file by using some other job . why because in simple recovery model ,once the transaction was completed then automatcally the log file will truncate(automatic checkpoint will work for simple recovery model).

subahan.munthamadugu SSChasing Mays Points: 630 More actions · Answer 9

Hi,

let me know which sql server version it is and up to 2008 version it is not possible to take backup into external Harddisk why because it will treat as removable disk and it is not posible to take backup into the removable disk in 2008 and prious versions.

if want do that just make it as share disk and mention path like \\C:\backup and try and i am not sure it will work just try it

subahan.munthamadugu:-)

deborah.moore33 Newbie Points: 7 More actions · Answer 10

deborah.moore33

Newbie

Points: 7

February 12, 2015 at 11:19 pm

#1777409

hello, Anyone know of a good preferably backup solution that would be good for backup movies?

I've even looked into using CloudBacko (http://free.cloudbacko.com/?r=1d) but I have no experience with that. Just not sure what to go with and wanted to see what you guys would recommend.

Thanks.