Backup Device vs backup command

Hi,

Will backup get completed quickly (the time taken for backing up large databases) on backup device or using backup command? If backup device, why?

You are talking about 2 different things here. You use the backup command to backup the DB and the backup device to store the backed up files.

Unless I dont understand your question.

|||Hi Dinakar,

I need to know which one completes backup faster, backup device or by using backup command. I heard that backup device completes backup quicker than using backup command.

Thanks|||

It's the same procedure, whether you commit it via a GUI (window menu->next->next...) or command prompt (BACKUP command) it makes no difference.

In both cases you use a backup device to backup your database. If you are a learner you'll probably use the user friendly windows menu. If you are advanced you'll probably use the SQL command: BACKUP

|||

Have you checked the same on your environment?

IMHO I don't see much difference in between and it makes a lot of difference betwen normal disks & RAID based ones.

BTW do you have any issues in your environment for such behaviour, if so let us know.

|||Hi Satya,

Im using RAID disks, when backing up 200GB of database, backup device completes faster than backup command. A 30 min difference..

Thanks|||If SQL Server stripes physical devices that have different input/output (I/O) throughputs, SQL Server optimizes for speed. This means that faster devices receive more backup data that is written to disk than the slower device in the same period of time. In SQL Server 2000, regardless of the I/O throughput differences, SQL Server tries to distribute the backup data evenly to the devices. In this case, the slower disk may become a backup bottleneck in terms of performance. If increasing performance is your primary goal, you must avoid using the slow disk in striping and use disks with comparable throughputs instead.

backup causes file size to grow

I backed up my database using the sql command Backup database xxx to disk = 'yyy'
When it completed the file size of my database approximately tripled. Did I
just corrupt some data? The application appears to continue to run ok. I
have experienced performance problems in the past and assumed it was hardware
related. Maybe my database was just sized too small'?Which file tripled? The database is made up of by default one data file and
one log file. Was it the data or log file that grew? If it was the log
file then you need to know that log backups can not finish during a full
backup. So if your log file was small to begin with and someone did a large
(or lots of ) transactions during the full backup the log file may have had
to grow since it could not be truncated during the full backup.
--
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?|||It was the data file (.mdb) that grew. My log file (.ldf) is basically the
same size it was. I went from a 6gb database file to a 20gb database file.
Thanks in advance for any advice.
"Andrew J. Kelly" wrote:
> Which file tripled? The database is made up of by default one data file and
> one log file. Was it the data or log file that grew? If it was the log
> file then you need to know that log backups can not finish during a full
> backup. So if your log file was small to begin with and someone did a large
> (or lots of ) transactions during the full backup the log file may have had
> to grow since it could not be truncated during the full backup.
> --
> Andrew J. Kelly SQL MVP
>
> "doug" <doug@.discussions.microsoft.com> wrote in message
> news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
> >I backed up my database using the sql command Backup database xxx to disk
> >=> > 'yyy'
> >
> > When it completed the file size of my database approximately tripled. Did
> > I
> > just corrupt some data? The application appears to continue to run ok. I
> > have experienced performance problems in the past and assumed it was
> > hardware
> > related. Maybe my database was just sized too small'?
>
>|||Are you positive that no one issued an ALTER DATABASE command that may have
grown the file? I have never heard of a db file growing from a backup
before. If it was due to autogrow there should have been lots of log
entries as well.
--
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:68FFC5B6-9B4B-4194-A360-64FE8B2003AB@.microsoft.com...
> It was the data file (.mdb) that grew. My log file (.ldf) is basically
> the
> same size it was. I went from a 6gb database file to a 20gb database
> file.
> Thanks in advance for any advice.
> "Andrew J. Kelly" wrote:
>> Which file tripled? The database is made up of by default one data file
>> and
>> one log file. Was it the data or log file that grew? If it was the log
>> file then you need to know that log backups can not finish during a full
>> backup. So if your log file was small to begin with and someone did a
>> large
>> (or lots of ) transactions during the full backup the log file may have
>> had
>> to grow since it could not be truncated during the full backup.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "doug" <doug@.discussions.microsoft.com> wrote in message
>> news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>> >I backed up my database using the sql command Backup database xxx to
>> >disk
>> >=>> > 'yyy'
>> >
>> > When it completed the file size of my database approximately tripled.
>> > Did
>> > I
>> > just corrupt some data? The application appears to continue to run ok.
>> > I
>> > have experienced performance problems in the past and assumed it was
>> > hardware
>> > related. Maybe my database was just sized too small'?
>>|||is this sql 2005, and do you have full-text indexing? If so, expect large
backup files.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?

backup causes file size to grow

I backed up my database using the sql command Backup database xxx to disk =
'yyy'
When it completed the file size of my database approximately tripled. Did I
just corrupt some data? The application appears to continue to run ok. I
have experienced performance problems in the past and assumed it was hardwar
e
related. Maybe my database was just sized too small'?Which file tripled? The database is made up of by default one data file and
one log file. Was it the data or log file that grew? If it was the log
file then you need to know that log backups can not finish during a full
backup. So if your log file was small to begin with and someone did a large
(or lots of ) transactions during the full backup the log file may have had
to grow since it could not be truncated during the full backup.
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=
> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?|||It was the data file (.mdb) that grew. My log file (.ldf) is basically the
same size it was. I went from a 6gb database file to a 20gb database file.
Thanks in advance for any advice.
"Andrew J. Kelly" wrote:

> Which file tripled? The database is made up of by default one data file a
nd
> one log file. Was it the data or log file that grew? If it was the log
> file then you need to know that log backups can not finish during a full
> backup. So if your log file was small to begin with and someone did a lar
ge
> (or lots of ) transactions during the full backup the log file may have ha
d
> to grow since it could not be truncated during the full backup.
> --
> Andrew J. Kelly SQL MVP
>
> "doug" <doug@.discussions.microsoft.com> wrote in message
> news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>
>|||Are you positive that no one issued an ALTER DATABASE command that may have
grown the file? I have never heard of a db file growing from a backup
before. If it was due to autogrow there should have been lots of log
entries as well.
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:68FFC5B6-9B4B-4194-A360-64FE8B2003AB@.microsoft.com...[vbcol=seagreen]
> It was the data file (.mdb) that grew. My log file (.ldf) is basically
> the
> same size it was. I went from a 6gb database file to a 20gb database
> file.
> Thanks in advance for any advice.
> "Andrew J. Kelly" wrote:
>|||is this sql 2005, and do you have full-text indexing? If so, expect large
backup files.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=
> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?