Backup to Network Drive: nightly

Showing posts with label nightly. Show all posts

Thursday, March 8, 2012

backupset table info

My employer wants me to extract information for each nightly database backup
s
for each production server.
So I have created a DTS package that extracts this info into excel using the
following script:
use msdb
go
select database_name as dbname,
convert(char(20),backup_start_date,100) as start_time,
convert(char(20),backup_finish_date,100)
as end_time, backup_size from
backupset
where backup_start_date >
(SELECT MAX(backup_start_date) FROM backupset) - 1
order by database_name, backup_start_date
Which gives the following output:
dbname start_time end_time backup_size
db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
He now wants two additional columns in the excel sheet. One with yesterday's
backup_size and 1 w

ago's backup size.
Can anyone tell me how to modify this sql script to be able to retieve this
additional info from the backupset table?
Thanks,
HillaireLook at sysbackuphistory system table.
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> My employer wants me to extract information for each nightly database
backups
> for each production server.
> So I have created a DTS package that extracts this info into excel using
the
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> DB2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with
yesterday's
> backup_size and 1 w

ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve
this
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||Try,
select
a.database_name as dbname,
convert(char(20),a.backup_start_date,100) as start_time,
convert(char(20),a.backup_finish_date,100) as end_time,
a.backup_size,
b.backup_size
from
msdb.dbo.backupset as a
left join
msdb.dbo.backupset as b
on
a.database_name = b.database_name
and a.backup_start_date > (SELECT MAX(backup_start_date) FROM
msdb.dbo.backupset) - 1
and b.backup_start_date = dateadd(w

, -1, (SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset) - 1)
order by
a.database_name,
a.backup_start_date
AMB
"gracie" wrote:

> My employer wants me to extract information for each nightly database back
ups
> for each production server.
> So I have created a DTS package that extracts this info into excel using t
he
> following script:
> use msdb
> go
> select database_name as dbname,
> convert(char(20),backup_start_date,100) as start_time,
> convert(char(20),backup_finish_date,100)
as end_time, backup_size from
> backupset
> where backup_start_date >
> (SELECT MAX(backup_start_date) FROM backupset) - 1
> order by database_name, backup_start_date
> Which gives the following output:
> dbname start_time end_time backup_size
> db1 Mar 14 2005 7:00PM Mar 14 2005 7:00PM 716862464
> db2 Mar 15 2005 8:00AM Mar 15 2005 8:00AM 7368704
> db3 Mar 15 2005 10:00AM Mar 15 2005 10:00AM 1862656
> db4 Mar 15 2005 12:00PM Mar 15 2005 12:00PM 3763200
> He now wants two additional columns in the excel sheet. One with yesterday
's
> backup_size and 1 w

ago's backup size.
> Can anyone tell me how to modify this sql script to be able to retieve thi
s
> additional info from the backupset table?
> Thanks,
> Hillaire
>|||What I actually need to accomplish is a comparison on the same excel sheet
showing yesterday's backup size and 1 w

ago's backup size, side by side s
o
that he can look for any large decrepancies in the sizes.
"Uri Dimant" wrote:

> Look at sysbackuphistory system table.
>
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:C0231A91-23B4-43E6-B6AD-37EDA6EC4D1A@.microsoft.com...
> backups
> the
> yesterday's
> this
>
>

Backups...daily

Hi,
When using the default backup tool and scheduling them nightly that come
with SQL 2000 Standard Edition are we getting good backups on a daily basis?
Meaning usable data for a restore in the event of disaster, I was told by
an associate that we are getting worthless backups without Backupexe.
Also, I know that we have other solutions by backing up the log files
assuming that the database is not set to "Simple". Suggestions?
Thanks!Hi,
SQL Server Standard backup is allways safe. So do not worry. If it is a
production database you could set the recovery model to "FULL"
and do a Transaction log backup every 30 minutes. Every night you could do a
Differential or FULL database backup.
Transaction log backup with FULL recovery model will allow you to recover
the database fully in the eve of a crash as well
as you could do a POINT_IN_TIME recovery.
SEE THE BELOW SITE FOR BACKUP AND RECOVERY STRATEGY:-
http://www1.us.dell.com/content/topics/global.aspx/power/en/ps4q00_martin?c=us&cs=555&l=en&s=biz
Thanks
Hari
SQL Server MVP
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23rrTM$zgFHA.3540@.TK2MSFTNGP12.phx.gbl...
> Hi,
> When using the default backup tool and scheduling them nightly that come
> with SQL 2000 Standard Edition are we getting good backups on a daily
> basis? Meaning usable data for a restore in the event of disaster, I was
> told by an associate that we are getting worthless backups without
> Backupexe.
> Also, I know that we have other solutions by backing up the log files
> assuming that the database is not set to "Simple". Suggestions?
> Thanks!
>|||Thank you!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eEg2wG0gFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi,
> SQL Server Standard backup is allways safe. So do not worry. If it is a
> production database you could set the recovery model to "FULL"
> and do a Transaction log backup every 30 minutes. Every night you could do
> a Differential or FULL database backup.
> Transaction log backup with FULL recovery model will allow you to recover
> the database fully in the eve of a crash as well
> as you could do a POINT_IN_TIME recovery.
> SEE THE BELOW SITE FOR BACKUP AND RECOVERY STRATEGY:-
> http://www1.us.dell.com/content/topics/global.aspx/power/en/ps4q00_martin?c=us&cs=555&l=en&s=biz
>
> Thanks
> Hari
> SQL Server MVP
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23rrTM$zgFHA.3540@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> When using the default backup tool and scheduling them nightly that come
>> with SQL 2000 Standard Edition are we getting good backups on a daily
>> basis? Meaning usable data for a restore in the event of disaster, I was
>> told by an associate that we are getting worthless backups without
>> Backupexe.
>> Also, I know that we have other solutions by backing up the log files
>> assuming that the database is not set to "Simple". Suggestions?
>> Thanks!
>|||Are you backing up to the same drive that youre db is on? If so, yes it is
worthless.
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23rrTM$zgFHA.3540@.TK2MSFTNGP12.phx.gbl...
> Hi,
> When using the default backup tool and scheduling them nightly that come
> with SQL 2000 Standard Edition are we getting good backups on a daily
> basis? Meaning usable data for a restore in the event of disaster, I was
> told by an associate that we are getting worthless backups without
> Backupexe.
> Also, I know that we have other solutions by backing up the log files
> assuming that the database is not set to "Simple". Suggestions?
> Thanks!
>|||You say it's worthless because of the obvious hard drive failures or some
other reason?
"ChrisR" <noemail@.bla.com> wrote in message
news:e9V9AN0gFHA.1468@.TK2MSFTNGP14.phx.gbl...
> Are you backing up to the same drive that youre db is on? If so, yes it is
> worthless.
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23rrTM$zgFHA.3540@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> When using the default backup tool and scheduling them nightly that come
>> with SQL 2000 Standard Edition are we getting good backups on a daily
>> basis? Meaning usable data for a restore in the event of disaster, I was
>> told by an associate that we are getting worthless backups without
>> Backupexe.
>> Also, I know that we have other solutions by backing up the log files
>> assuming that the database is not set to "Simple". Suggestions?
>> Thanks!
>|||The obvious hard drive failures.
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:uBrFAl0gFHA.2904@.tk2msftngp13.phx.gbl...
> You say it's worthless because of the obvious hard drive failures or some
> other reason?
> "ChrisR" <noemail@.bla.com> wrote in message
> news:e9V9AN0gFHA.1468@.TK2MSFTNGP14.phx.gbl...
>> Are you backing up to the same drive that youre db is on? If so, yes it
>> is worthless.
>>
>> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
>> news:%23rrTM$zgFHA.3540@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> When using the default backup tool and scheduling them nightly that come
>> with SQL 2000 Standard Edition are we getting good backups on a daily
>> basis? Meaning usable data for a restore in the event of disaster, I
>> was told by an associate that we are getting worthless backups without
>> Backupexe.
>> Also, I know that we have other solutions by backing up the log files
>> assuming that the database is not set to "Simple". Suggestions?
>> Thanks!
>>
>

Backups...daily

Saturday, February 25, 2012

Backups

I need to restore a backup dbs from one SQL Server to another on a nightly
basis. We do our backups using a Maintenance Plan. The name of the backup
file is different every night as the backups of each db run at different
times. Is there a way to fix the name of the backup files so they don't
have a time element in the name? Is there a way to discover the name that
was used last?
BarrySet up a job to do the backup with T-SQL instead of using the maint plan.
<barryfz@.home.com> wrote in message
news:%23neDZcOnEHA.4004@.TK2MSFTNGP10.phx.gbl...
> I need to restore a backup dbs from one SQL Server to another on a nightly
> basis. We do our backups using a Maintenance Plan. The name of the backup
> file is different every night as the backups of each db run at different
> times. Is there a way to fix the name of the backup files so they don't
> have a time element in the name? Is there a way to discover the name that
> was used last?
> Barry
>|||The following is a TSQL command that will perform a full
database backup to the file specified in the DISK=parameter. You can schedule this backup in SQL Server
Agent.
Backup Database DataBaseName to
DISK='\\Server\ShareName\DataBaseName_Data.BAK' with
RetainDays=1, INIT, Description='Daily Backup Routine'
This is just a direction for you to take your research.
Test this process as well as your restore procedures
prior to putting this into production.
Good luck,
D2
>--Original Message--
>I need to restore a backup dbs from one SQL Server to
another on a nightly
>basis. We do our backups using a Maintenance Plan. The
name of the backup
>file is different every night as the backups of each db
run at different
>times. Is there a way to fix the name of the backup
files so they don't
>have a time element in the name? Is there a way to
discover the name that
>was used last?
>Barry
>
>.
>|||I had to do this for a file that was being sent through a firewall every
night via FTP. So I dynamically created the ftp script every night.
In your situation then in DOS do something like this
Create a batch file
SETLOCAL
SET File_Name=FOR /F "usebackq" %%x IN (`dir *.txt /b /od`) DO SET FILE_NAME=%%x
IF {%File_Name%}={} exit 1
COPY %File_Name% Z:\SomeFolderNamedWhatEveryYouLike
ENDLOCAL
The for loop is the interesting bit, it simply lists every txt file in
date order and assigns the name to a variable File_Name. The only issue
is for every txt file the variable is changed, so if there are 100 files
the loop goes around 100 times before it finds the correct name
Adrian
barryfz@.home.com wrote:
> I need to restore a backup dbs from one SQL Server to another on a nightly
> basis. We do our backups using a Maintenance Plan. The name of the backup
> file is different every night as the backups of each db run at different
> times. Is there a way to fix the name of the backup files so they don't
> have a time element in the name? Is there a way to discover the name that
> was used last?
> Barry
>|||Hi Barry,
As community members had provided sufficient information about the way of
backup, I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Thursday, March 8, 2012

backupset table info

Backups...daily

Backups...daily

Backups...daily

Saturday, February 25, 2012

Backups

Backup to Network Drive

Blog Archive

About Me