Database Backup

Question

Post reply

Database Backup

Deepthy

SSCommitted

Points: 1823
More actions
December 14, 2010 at 3:48 am

#229582

Hi All,

Is there any way to check if a database is backed up or not?

Thanks in advance

Viewing 15 posts - 1 through 14 (of 14 total)

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

jmcmullen SSC Enthusiast Points: 164 More actions · Answer 1

If you want to use t-sql you can query msdb..backupset

To see the backup history stored on one database..

select database_name,[type], backup_finish_date



FROM msdb..backupset b

WHERE database_name = 'myDatabaseName'

ORDER BY b.DATABASE_NAME, b.backup_start_date desc

To see the last full backup on your databases..

select database_name,MAX( backup_finish_date) as [backup date]



FROM msdb..backupset b

WHERE [type] = 'D' --full backups only

GROUP BY database_name

etc..

Deepthy SSCommitted Points: 1823 More actions · Answer 2

Deepthy

SSCommitted

Points: 1823

December 14, 2010 at 5:53 am

#1262139

Thank you. That helps

Deepthy SSCommitted Points: 1823 More actions · Answer 3

Hello All,

I have come accross another problem and no idea how to deal.

The server is upgraded from sql server 2005 to 2008.

From the day the upgradtaion completed,

the backups of databases which used to run on schedule before are not working in 2008.

I checked the compatability levels of databases and set them to 100, but not helping.

Any idea on why the backups not running?

Thanks in advance

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

Is SQL Agent runnning?

Are the jobs failing, or just not running?

Are the jobs enabled?

Are the schedules enabled?

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

jmcmullen SSC Enthusiast Points: 164 More actions · Answer 5

jmcmullen

SSC Enthusiast

Points: 164

December 14, 2010 at 7:04 am

#1262171

Is the SQL Agent service running? If not, start it up and set it to Automatic.

Find the job that backs up the databases and have a look at it's history, there might be an error in there.

Deepthy SSCommitted Points: 1823 More actions · Answer 6

Thanks very much for your for the replies.

The sql server agent is running, the backups are not running on schedule.

There isn't any sql agent job setup in sql server agent for backups.

And I am not sure if the backups are supposed to run using a stored procedure or anything else

because its another developer who set up these backups and he is not currently working with us.

So no idea how to tract the problem.

I am new to sql please bare with me.

Any help is really appreciated

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

Deepthy (12/14/2010)

There isn't any sql agent job setup in sql server agent for backups.

If there's no job, then nothing is going to be running on any schedule. The job is the way SQL agent runs things.

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

Deepthy SSCommitted Points: 1823 More actions · Answer 8

Deepthy

SSCommitted

Points: 1823

December 14, 2010 at 7:38 am

#1262220

Thanks for your reply.

But I wonder how could have the backups run on schedule before upgradation with out a job.

Thanks in advance

jmcmullen SSC Enthusiast Points: 164 More actions · Answer 9

If this was an in-place upgrade you'd expect the jobs and maintenance plans to be preserved, obviously they'd be lost on a clean install.

You might check to see if the backups were previously run through a windows scheduled task rather than a sql job.

Either way you should think about setting up a maintenance plan to backup your dbs. This technet video will show you how:

http://technet.microsoft.com/en-us/sqlserver/Video/dd430301

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

Deepthy (12/14/2010)

Thanks for your reply.

But I wonder how could have the backups run on schedule before upgradation with out a job.

How did you upgrade? Did you keep (upgrade) the old MSDB database? If not, you've lost every job that you had.

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

Deepthy SSCommitted Points: 1823 More actions · Answer 11

Deepthy

SSCommitted

Points: 1823

December 14, 2010 at 8:54 am

#1262293

Thanks JMCMULLEN,

I found out the server was not upgraded but installed with sql server 2008.

Thank you

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

That's why.

You didn't copy the jobs from the old server nor restore MSDB, so you've lost all jobs, backup and other.

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

Deepthy SSCommitted Points: 1823 More actions · Answer 13

Deepthy

SSCommitted

Points: 1823

December 14, 2010 at 1:31 pm

#1262453

thanks Gail.

Thats true

Suresh Babu Palla SSChasing Mays Points: 610 More actions · Answer 14

Suresh Babu Palla

SSChasing Mays

Points: 610

December 16, 2010 at 7:54 am

#1263468

Deepthy,

Verfiy the owner of the job.If the job is running with the credentials of the developer who was workign with you previously if yes change the owner of the job from his credentials to 'SA' account.