No

This code should help get what you want. It will should the disk\share locations from backup history.

select

a.database_name,

a.Type,

c.physical_device_name

from

msdb.dbo.backupset a

join

msdb.dbo.backupmediafamily as c

on a.media_set_id = c.media_set_id

You are getting the conversion error because DD-MM-YYYY is not the current dateformat on your connection.

Your conversion of the dtTimestamp column to a string and then back to a datetime...

This forum is...

GSquared (10/13/2009)

---

I'd say go ahead and include them. It means a little extra RAM being allocated, but it doesn't hurt anything beyond that.

It allows the dev to build a...

Bradley Jacques (10/13/2009)

---

Hi!

I'd love to help you, can you post the create table statements for the tables you will be using with this query? I've posted what I feel is...

Ask the developer to explain exactly why that isn't a stupid idea.

Taking this a step further, you can see quite a bit of information about the backup it was restored from.

select

a.backup_start_date,

a.backup_finish_date,

Backup_Elapsed_Hours =

convert(numeric(6,2),round(

datediff(ms,0,a.backup_finish_date-a.backup_start_date)

/(3600000.0),2)),

Backup_Size_GB =

convert(numeric(8,2),round(a.backup_size/(1073741824.),2)),

Source_Server = a.server_name,

Source_Database = a.database_name,

Backup_File_Name =c.physical_device_name

from

msdb..backupset a

join

msdb..restorehistory b

on a.backup_set_id...

GETDATE() returns the current local time that the database server is set to.

If you need to deal with data from multiple timezones, you might use GETUTCDATE(). It returns the...

When you restore a backup from another server, the info about the backup you restored from is stored in the same tables where info about local backups goes:

msdb.dbo.backupset

msdb.dbo.backupmediafamily

Garadin (10/12/2009)

---

Michael Valentine Jones (10/12/2009)

---

That is not a good method, since it means that datecol will be cast it an int and back to datetime for every row, and the...

Garadin (10/12/2009)

---

Neither, use this:

SELECT * FROM Table1 WHER CAST(CAST(datecol-.5 as int) as datetime) =

CAST('2009-10-01' as datetime)

The dateadd/datediff method that someone will likely post shortly is almost identical, I just...

Q1 is better, since it actually includes the whole day.

I like it when they mark it as urgent, and then post again an hour later complaining that no one is helping them.

I usually only visit the forums, so I would like it if there were direct URL links for "Posts since my last visit" and "My Posts". I find it...