backup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

backup

Author

Topic

raul11
Starting Member

48 Posts

Posted - 2010-03-25 : 13:46:29

query to find out databases backed up more than once in a day

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 13:54:18

You can adapt this to your needs:
select
database_name,
backup_start_date,
Backuptype,
physical_device_name
from
(
select
database_name,
backup_start_date,
case type
when 'L' then 'Log'
when 'D' then 'Data'
else '??? '+type
end as Backuptype,
physical_device_name
from msdb.dbo.backupset a
join msdb..backupmediaset b on a.media_set_id = b.media_set_id
join msdb.dbo.backupmediafamily c on a.media_set_id = c.media_set_id
where backup_start_date > dateadd(hour,-24,getdate()) -- backup der letzten 24 Stunden
)dt
order by backup_start_date,database_name asc, Backuptype

No, you're never too old to Yak'n'Roll if you're too young to die.

raul11
Starting Member

48 Posts

Posted - 2010-03-25 : 14:10:03

thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-26 : 04:29:27

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

raul11
Starting Member

48 Posts

Posted - 2010-04-12 : 10:09:13

wat is the query to find date and time in the following format
Mon, Apr 12, 2010, 19:00PM

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-12 : 12:09:30

To find or to display the date in that format?

No, you're never too old to Yak'n'Roll if you're too young to die.

raul11
Starting Member

48 Posts

Posted - 2010-04-12 : 12:10:07

display in the above format

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-12 : 13:10:05

like this?

select
database_name,
--backup_start_date,
datename(dw,backup_start_date)+', '+convert(varchar(30),backup_start_date,100) as backup_start_date,
Backuptype,
physical_device_name
from
(
select 
database_name,
backup_start_date,
case type 
when 'L' then 'Log' 
when 'D' then 'Data'
else '??? '+type 
end as Backuptype,
physical_device_name
from msdb.dbo.backupset a 
join msdb..backupmediaset b on a.media_set_id = b.media_set_id 
join msdb.dbo.backupmediafamily c on a.media_set_id = c.media_set_id 
where backup_start_date > dateadd(hour,-24,getdate()) -- backup der letzten 24 Stunden
)dt
order by backup_start_date,database_name asc, Backuptype

No, you're never too old to Yak'n'Roll if you're too young to die.

raul11
Starting Member

48 Posts

Posted - 2010-04-12 : 13:40:10

i need only the below output

Mon, Apr 12, 2010, 19:00PM

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-13 : 01:49:06

Have a look here
http://msdn.microsoft.com/de-de/library/ms187928.aspx
and see the possibilities.

No, you're never too old to Yak'n'Roll if you're too young to die.

raul11
Starting Member

48 Posts

Posted - 2010-04-14 : 11:55:21

Can someone help me to complete this query
select left( datename(dw,getdate()),3)+' , '+ cast(getdate() as varchar(6))+ ','

to get the below output

Mon, Apr 12, 2010, 19:00PM

raul11
Starting Member

48 Posts

Posted - 2010-06-30 : 00:26:46

create table test1 (firstname varchar(20),age int,lastname varchar(10),
dateofbirth datetime,phone int);
for the above query, if the date of birth is 1980-03-05. how do i insert this value in the datetime column. it should be in yyyy-mm-dd format

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 03:12:39

Your column is of type datetime.
While INSERTING data there is no need to consider a later wanted format because sql server stores the date in a internal format.
You can insert a hard coded date as '19800305' (YYYYMMDD).
Your wanted format for displaying is only to consider in a select statement or better, in front end.

No, you're never too old to Yak'n'Roll if you're too young to die.

raul11
Starting Member

48 Posts

Posted - 2011-04-11 : 02:03:41

why does a database go into suspect mode? can it be recoverd & how

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-11 : 05:07:46

Please post new and unrelated questions in a new thread.

--
Gail Shaw
SQL Server MVP

Haz.vali
Starting Member

3 Posts

Posted - 2011-04-26 : 00:27:12

select DATENAME(M,GETDATE())+', '+ SUBSTRING(CONVERT(varchar,getdate(),100),1,6)
+', '+SUBSTRING(CONVERT(varchar,getdate(),100),8,4)
+', '+RIGHT(CONVERT(varchar,getdate(),100),6)

Haz.vali
Starting Member

3 Posts

Posted - 2011-04-26 : 00:28:53

select DATENAME(M,GETDATE())+', '+ SUBSTRING(CONVERT(varchar,getdate(),100),1,6)
+', '+SUBSTRING(CONVERT(varchar,getdate(),100),8,4)
+', '+RIGHT(CONVERT(varchar,getdate(),100),6)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-26 : 03:26:27

quote:
Originally posted by Haz.vali

select DATENAME(M,GETDATE())+', '+ SUBSTRING(CONVERT(varchar,getdate(),100),1,6)
+', '+SUBSTRING(CONVERT(varchar,getdate(),100),8,4)
+', '+RIGHT(CONVERT(varchar,getdate(),100),6)

Is that an answer to a question?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources