Backups Best Practices

It looks like I have been presented with 2 options for backing up to tape.
1. Use veritas SQL Agent and back up the full every <blank> days and logs ev
ery <blank> days
2. Backup using native SQL agent to disk and using veritas to backup the BAK
files to tape every night.
Anyone have an opinion on which is best and why?I prefer native and than pick up the files to tape. Just be aware that you
have 24 hours potential loss of data of the local backup files are lost
before they are backed up to tape. I generally let the SQL backup copy the
files to another machine directly after the backup is takes, when possible.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
> It looks like I have been presented with 2 options for backing up to tape.
> 1. Use veritas SQL Agent and back up the full every <blank> days and logs
every <blank> days
> 2. Backup using native SQL agent to disk and using veritas to backup the
BAK files to tape every night.
> Anyone have an opinion on which is best and why?|||I agree with Tibor. I prefer to not have to deal with the tape agents if I
can avoid it.
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9hpqvW9DHA.1504@.TK2MSFTNGP12.phx.gbl...
> I prefer native and than pick up the files to tape. Just be aware that you
> have 24 hours potential loss of data of the local backup files are lost
> before they are backed up to tape. I generally let the SQL backup copy the
> files to another machine directly after the backup is takes, when
possible.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "mannie" <anonymous@.discussions.microsoft.com> wrote in message
> news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
tape.
logs
> every <blank> days
the
> BAK files to tape every night.
>|||Thanks for your input..
What is your reason for this preference?
Speed?
You are more comfortable with SQL native agent?
Reliability?
Frequency of backups required?
Are you trying to save I/O over the backup next work?
Any more areas you have to add to this list of things to consider?|||I agree with Tibor, except that I prefer to backup directly to the remote
file system using a unc name, so I don't to coordinate the file
copies...(although the backup itself will run slower and eat network
bandwidth.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
> It looks like I have been presented with 2 options for backing up to tape.
> 1. Use veritas SQL Agent and back up the full every <blank> days and logs
every <blank> days
> 2. Backup using native SQL agent to disk and using veritas to backup the
BAK files to tape every night.
> Anyone have an opinion on which is best and why?|||For me it is simple: I prefer to not have my SQL Server data in the hands on
some 3:rd party vendor. Backup from SQL Server has been around for ages and
we all know that it work and how it work... :-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:E26131EA-B631-4482-A033-B6AD4215D0B9@.microsoft.com...
> Thanks for your input..
> What is your reason for this preference?
> Speed?
> You are more comfortable with SQL native agent?
> Reliability?
> Frequency of backups required?
> Are you trying to save I/O over the backup next work?
> Any more areas you have to add to this list of things to consider?|||I am with Wayne and Tibor on this one. SQL backups (using SQLLiteSpeed for
the really big databases) to a UNC share on another machine. I then have
daily, weekly and monthly rotations to tape with the monthly tapes removed
and archived.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23kaRt6W9DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I agree with Tibor, except that I prefer to backup directly to the remote
> file system using a unc name, so I don't to coordinate the file
> copies...(although the backup itself will run slower and eat network
> bandwidth.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "mannie" <anonymous@.discussions.microsoft.com> wrote in message
> news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
tape.
logs
> every <blank> days
the
> BAK files to tape every night.
>|||If you have a copy of the backup on a local machine (by local meaning
accessible by UNC) you can restore in the quickest possible time where as
with tape it may be a while to get the tape loaded etc.
Andrew J. Kelly
SQL Server MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:E26131EA-B631-4482-A033-B6AD4215D0B9@.microsoft.com...
> Thanks for your input..
> What is your reason for this preference?
> Speed?
> You are more comfortable with SQL native agent?
> Reliability?
> Frequency of backups required?
> Are you trying to save I/O over the backup next work?
> Any more areas you have to add to this list of things to consider?|||Also, some of the tape software components don't support all the backup and
restore options. Especially the WITH MOVE option specifying where each file
gets placed. This is especially important with very large databases where
you will have to spread the data out on multiple devices but you don't want
to overwrite the original database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Rhsx0X9DHA.2696@.TK2MSFTNGP10.phx.gbl...
> If you have a copy of the backup on a local machine (by local meaning
> accessible by UNC) you can restore in the quickest possible time where as
> with tape it may be a while to get the tape loaded etc.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "mannie" <anonymous@.discussions.microsoft.com> wrote in message
> news:E26131EA-B631-4482-A033-B6AD4215D0B9@.microsoft.com...
>|||we are getting ready to buy sql lite speed too
testing the demo for backups is impressive wtih decrease file size and speed
restores seem to take the same amount of time as with native sql versus the
VDI Sqllitespeed
is this what other are seeing
we routinely backup DBs in the 100-250 gig range and restore them on another
server for analytical use
experimenting with ways for smallest over time window for this
things like backup to local SAN -- restore to other server across network
back to UNC path on other server -- restore from local SAN
Trying to copy such large files are a network in windows is too slow -- it
is faster to just backup in SQL and then restore in SQL or move the file
with a tape library
We soon may have a disked based backup system to try DX30 from quantum
comments on getting shortest back and restore windows
example -- a large DB we have create a 170gig backup file the backup restore
rebuild indexes on this puppy takes 15-20 hours -- we do this once a month
as that is the refresh for new data loads -- new data is about 4-6 gig a
month
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23nQaoqX9DHA.2832@.tk2msftngp13.phx.gbl...
> I am with Wayne and Tibor on this one. SQL backups (using SQLLiteSpeed
for
> the really big databases) to a UNC share on another machine. I then have
> daily, weekly and monthly rotations to tape with the monthly tapes removed
> and archived.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:%23kaRt6W9DHA.2308@.TK2MSFTNGP11.phx.gbl...
remote
> tape.
> logs
> the
>

backups and restore

I am backing up SQL databases two ways, once through the Enterprise Manager
and also with Veritas. I need to do a test restore on a database and am
uncertain of the best way to go about it. Is it easier to restore from
Veritas or SQL backups? How do I do it without overwriting the existing
database? Any insights or help would be greatly appreciated.
Here is some info:
Windows 2003 Server
SQL 2000
Veritas 9.1
Thanks"NOBI" schrieb:
> I am backing up SQL databases two ways, once through the Enterprise Manage
r
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks
During the restore process you can define the name of the restored database.
There is no problem with overwriting the existing one (if you just choose a
different name). Don't forget to use the 'move ... to ...' option to give th
e
files a different location from the original!|||Hi Nobi,
You can use the RESTORE DATABASE command from query analyzer to restore the
database with a new name.
STEPS:-
1. Identify the logical name associated with MDf AND LDF using RESTORE
FILELISTONLY (see books online)
2. Use the RESTORE DATABASE command with MOVE options (see books online) to
create and restore the backupfile with new
database name. In the MOVE option for the physical name give a new name.
Thanks
Hari
SQL Server MVP
"NOBI" <NOBI@.discussions.microsoft.com> wrote in message
news:8AAA6DF0-3FCA-4F3B-9BFE-6FEA042E28EB@.microsoft.com...
>I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks|||For full backups you shouldn't have any problems. But be carefull with
Transaction Log backups. If you run TLog backups from both sides (SQL Serve
r
and Veritas) it will be very difficult, almost impossible to restore a DB to
the Point of Failure. TLog backups have a sequence number used to know in
what order they have to be restored after the full backup. Running TLog
backups from both tools will assign a sequence number for every backup
created, then you will have something like this:
FULL BACKUP
SQLTLog(1)
SQLTLog(2)
VERITASTLog(3)
VERITASTLog(4)
SQLTLog(5)
VERITASTLog(6)
Restoring from Full Backup up to the point 6 would require SQL and VERITAS
TLogs. This can be very difficult to achieve depending on the frequency of
TLog backups.
Hope it helps
Lionel Chacon
"NOBI" wrote:

> I am backing up SQL databases two ways, once through the Enterprise Manage
r
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks

backups and restore

I am backing up SQL databases two ways, once through the Enterprise Manager
and also with Veritas. I need to do a test restore on a database and am
uncertain of the best way to go about it. Is it easier to restore from
Veritas or SQL backups? How do I do it without overwriting the existing
database? Any insights or help would be greatly appreciated.
Here is some info:
Windows 2003 Server
SQL 2000
Veritas 9.1
Thanks
"NOBI" schrieb:
> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks
During the restore process you can define the name of the restored database.
There is no problem with overwriting the existing one (if you just choose a
different name). Don't forget to use the 'move ... to ...' option to give the
files a different location from the original!
|||Hi Nobi,
You can use the RESTORE DATABASE command from query analyzer to restore the
database with a new name.
STEPS:-
1. Identify the logical name associated with MDf AND LDF using RESTORE
FILELISTONLY (see books online)
2. Use the RESTORE DATABASE command with MOVE options (see books online) to
create and restore the backupfile with new
database name. In the MOVE option for the physical name give a new name.
Thanks
Hari
SQL Server MVP
"NOBI" <NOBI@.discussions.microsoft.com> wrote in message
news:8AAA6DF0-3FCA-4F3B-9BFE-6FEA042E28EB@.microsoft.com...
>I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks
|||For full backups you shouldn't have any problems. But be carefull with
Transaction Log backups. If you run TLog backups from both sides (SQL Server
and Veritas) it will be very difficult, almost impossible to restore a DB to
the Point of Failure. TLog backups have a sequence number used to know in
what order they have to be restored after the full backup. Running TLog
backups from both tools will assign a sequence number for every backup
created, then you will have something like this:
FULL BACKUP
SQLTLog(1)
SQLTLog(2)
VERITASTLog(3)
VERITASTLog(4)
SQLTLog(5)
VERITASTLog(6)
Restoring from Full Backup up to the point 6 would require SQL and VERITAS
TLogs. This can be very difficult to achieve depending on the frequency of
TLog backups.
Hope it helps
Lionel Chacon
"NOBI" wrote:

> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks

backups and restore

I am backing up SQL databases two ways, once through the Enterprise Manager
and also with Veritas. I need to do a test restore on a database and am
uncertain of the best way to go about it. Is it easier to restore from
Veritas or SQL backups? How do I do it without overwriting the existing
database? Any insights or help would be greatly appreciated.
Here is some info:
Windows 2003 Server
SQL 2000
Veritas 9.1
Thanks"NOBI" schrieb:
> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks
During the restore process you can define the name of the restored database.
There is no problem with overwriting the existing one (if you just choose a
different name). Don't forget to use the 'move ... to ...' option to give the
files a different location from the original!|||Hi Nobi,
You can use the RESTORE DATABASE command from query analyzer to restore the
database with a new name.
STEPS:-
1. Identify the logical name associated with MDf AND LDF using RESTORE
FILELISTONLY (see books online)
2. Use the RESTORE DATABASE command with MOVE options (see books online) to
create and restore the backupfile with new
database name. In the MOVE option for the physical name give a new name.
Thanks
Hari
SQL Server MVP
"NOBI" <NOBI@.discussions.microsoft.com> wrote in message
news:8AAA6DF0-3FCA-4F3B-9BFE-6FEA042E28EB@.microsoft.com...
>I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks|||For full backups you shouldn't have any problems. But be carefull with
Transaction Log backups. If you run TLog backups from both sides (SQL Server
and Veritas) it will be very difficult, almost impossible to restore a DB to
the Point of Failure. TLog backups have a sequence number used to know in
what order they have to be restored after the full backup. Running TLog
backups from both tools will assign a sequence number for every backup
created, then you will have something like this:
FULL BACKUP
SQLTLog(1)
SQLTLog(2)
VERITASTLog(3)
VERITASTLog(4)
SQLTLog(5)
VERITASTLog(6)
Restoring from Full Backup up to the point 6 would require SQL and VERITAS
TLogs. This can be very difficult to achieve depending on the frequency of
TLog backups.
Hope it helps
Lionel Chacon
"NOBI" wrote:
> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks

backups

We use Veritas for backups on SS2000. Right now we do backups using the sql
server agent. Would there be any problem doing just a file backup rather than
whatever the sql server agent does?
If we restored from the file backup, I'm guessing we would restore the file
then do a detach/attach to get the restored file in place. Or would doing
just a file backup miss something?
Thanks,
Dan D.
You can't use file backup on a SQL Server database file. While it is being
written to it is not guaranteed to be in a internally consistent state, and
even Veritas and other file backup software can backup open files, the file
can be corrupted from a SQL Server point of view. When you use SQL Server
backup, SQL Server will make sure that it is in a consistent state before
making the backup.
Jacco Schalkwijk
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||Hi,
If you need to backup the MDF and LDF directly then:-
1. Detach the database <sp_detach_db>
2. Copy the MDF, LDF and NDF for the database to the backup folder
3. Attach back the databasee <SP_attach_db>
While required you could attach the file to a database in any SQL Server
using sp_attach_db command.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||I've personally never cared for the Veritas agent in backing up SQL
databases. There are several good methods available for backing up SQL
data.. However, you cannot simply run a file backup on a SQL server and get
the data backed up. The MDF and LDF files are in use, as someone else posted.
To use the file backup method, you must use a SQL backup to disk, then
backup the backup files. That is, let SQL dump a backup file to DISK, then
use Veritas to backup that file.
I currently manage 40 SQL Servers and use this method. I have had mixed
results with the Veritas agent. There are some other products on the market
to assist with file based backups, such as Red Gate SQL Backup or SQL
Litespeed. They compress the backups to disk.
An easy way to get started with disk based backups is to go through the
database maintenance wizard and have it walk you through configuring and
scheduling a backup. Just remember to make sure your backups are finished on
SQL before Veritas starts it's backup.
Hope this helps.
Bryan
"Dan D." wrote:

> We use Veritas for backups on SS2000. Right now we do backups using the sql
> server agent. Would there be any problem doing just a file backup rather than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||Thanks everyone for your responses. That helps a lot.
Bryan,
I tried LiteSpeed once but it wasn't any faster (which I expected it to be)
and according to our sysadmin it didn't compress the files any smaller than
Veritas.
We've had a lot of problems too with Veritas. I'm not in charge of the
backups so I'm not sure if the problems are Veritas related, hardware related
or something else but it really is a pain. I backup our most critical
databases to another server via Sql Server.
Thanks,
Dan D.
"Bryan Ivie" wrote:
[vbcol=seagreen]
> I've personally never cared for the Veritas agent in backing up SQL
> databases. There are several good methods available for backing up SQL
> data.. However, you cannot simply run a file backup on a SQL server and get
> the data backed up. The MDF and LDF files are in use, as someone else posted.
> To use the file backup method, you must use a SQL backup to disk, then
> backup the backup files. That is, let SQL dump a backup file to DISK, then
> use Veritas to backup that file.
> I currently manage 40 SQL Servers and use this method. I have had mixed
> results with the Veritas agent. There are some other products on the market
> to assist with file based backups, such as Red Gate SQL Backup or SQL
> Litespeed. They compress the backups to disk.
> An easy way to get started with disk based backups is to go through the
> database maintenance wizard and have it walk you through configuring and
> scheduling a backup. Just remember to make sure your backups are finished on
> SQL before Veritas starts it's backup.
> Hope this helps.
> Bryan
>
> "Dan D." wrote:

Backups

How do you backup a SQL database so the logs are commited
to the database file? Will a backup using Veritas Backup
Exec do it for us or do I need to use SQL to backup the
product?
Thanks,
ScottAll inactive (not-currently running) transactions will be included in your
db backup, a db backup does not back up the actual log though. Your log will
still have the same footprint size and used space size.
Ray Higdon MCSE, MCDBA, CCNA
--
"spt" <anonymous@.discussions.microsoft.com> wrote in message
news:bd8b01c40c17$295146b0$a601280a@.phx.gbl...
> How do you backup a SQL database so the logs are commited
> to the database file? Will a backup using Veritas Backup
> Exec do it for us or do I need to use SQL to backup the
> product?
> Thanks,
> Scott|||I would like to get some more information before I can address your
concerns.
Any third party product, will use the SQL APIs for Backup. So the first
thing would be to do a GAP analysis of what SQL Server Backup can provide
and what your specific requirements are. For this, I would recommend that
you go through the BooksOnline Backup section that provides all the
information that you would ever need about SQL Server Backup. I would also
suggest that a Backup Plan is of now use without a Disaster Recovery plan.
For that, plese visit the following links :
http://support.microsoft.com/defaul...kb;en-us;169039
http://support.microsoft.com/defaul...kb;en-us;307775
Once you are done with it, you will get a fair idea of what you can achieve
with SQL Backup and what are the GAP areas for which you might want to use
a third party product.
Please let me know if I can be of any further assistance.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

backups

We use Veritas for backups on SS2000. Right now we do backups using the sql
server agent. Would there be any problem doing just a file backup rather tha
n
whatever the sql server agent does?
If we restored from the file backup, I'm guessing we would restore the file
then do a detach/attach to get the restored file in place. Or would doing
just a file backup miss something?
Thanks,
--
Dan D.You can't use file backup on a SQL Server database file. While it is being
written to it is not guaranteed to be in a internally consistent state, and
even Veritas and other file backup software can backup open files, the file
can be corrupted from a SQL Server point of view. When you use SQL Server
backup, SQL Server will make sure that it is in a consistent state before
making the backup.
Jacco Schalkwijk
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Hi,
If you need to backup the MDF and LDF directly then:-
1. Detach the database <sp_detach_db>
2. Copy the MDF, LDF and NDF for the database to the backup folder
3. Attach back the databasee <SP_attach_db>
While required you could attach the file to a database in any SQL Server
using sp_attach_db command.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||I've personally never cared for the Veritas agent in backing up SQL
databases. There are several good methods available for backing up SQL
data.. However, you cannot simply run a file backup on a SQL server and get
the data backed up. The MDF and LDF files are in use, as someone else poste
d.
To use the file backup method, you must use a SQL backup to disk, then
backup the backup files. That is, let SQL dump a backup file to DISK, then
use Veritas to backup that file.
I currently manage 40 SQL Servers and use this method. I have had mixed
results with the Veritas agent. There are some other products on the market
to assist with file based backups, such as Red Gate SQL Backup or SQL
Litespeed. They compress the backups to disk.
An easy way to get started with disk based backups is to go through the
database maintenance wizard and have it walk you through configuring and
scheduling a backup. Just remember to make sure your backups are finished o
n
SQL before Veritas starts it's backup.
Hope this helps.
Bryan
"Dan D." wrote:

> We use Veritas for backups on SS2000. Right now we do backups using the sq
l
> server agent. Would there be any problem doing just a file backup rather t
han
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the fil
e
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Thanks everyone for your responses. That helps a lot.
Bryan,
I tried LiteSpeed once but it wasn't any faster (which I expected it to be)
and according to our sysadmin it didn't compress the files any smaller than
Veritas.
We've had a lot of problems too with Veritas. I'm not in charge of the
backups so I'm not sure if the problems are Veritas related, hardware relate
d
or something else but it really is a pain. I backup our most critical
databases to another server via Sql Server.
Thanks,
--
Dan D.
"Bryan Ivie" wrote:
[vbcol=seagreen]
> I've personally never cared for the Veritas agent in backing up SQL
> databases. There are several good methods available for backing up SQL
> data.. However, you cannot simply run a file backup on a SQL server and g
et
> the data backed up. The MDF and LDF files are in use, as someone else pos
ted.
> To use the file backup method, you must use a SQL backup to disk, then
> backup the backup files. That is, let SQL dump a backup file to DISK, the
n
> use Veritas to backup that file.
> I currently manage 40 SQL Servers and use this method. I have had mixed
> results with the Veritas agent. There are some other products on the mark
et
> to assist with file based backups, such as Red Gate SQL Backup or SQL
> Litespeed. They compress the backups to disk.
> An easy way to get started with disk based backups is to go through the
> database maintenance wizard and have it walk you through configuring and
> scheduling a backup. Just remember to make sure your backups are finished
on
> SQL before Veritas starts it's backup.
> Hope this helps.
> Bryan
>
> "Dan D." wrote:
>

backups

We use Veritas for backups on SS2000. Right now we do backups using the sql
server agent. Would there be any problem doing just a file backup rather than
whatever the sql server agent does?
If we restored from the file backup, I'm guessing we would restore the file
then do a detach/attach to get the restored file in place. Or would doing
just a file backup miss something?
Thanks,
--
Dan D.You can't use file backup on a SQL Server database file. While it is being
written to it is not guaranteed to be in a internally consistent state, and
even Veritas and other file backup software can backup open files, the file
can be corrupted from a SQL Server point of view. When you use SQL Server
backup, SQL Server will make sure that it is in a consistent state before
making the backup.
--
Jacco Schalkwijk
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Hi,
If you need to backup the MDF and LDF directly then:-
1. Detach the database <sp_detach_db>
2. Copy the MDF, LDF and NDF for the database to the backup folder
3. Attach back the databasee <SP_attach_db>
While required you could attach the file to a database in any SQL Server
using sp_attach_db command.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||I've personally never cared for the Veritas agent in backing up SQL
databases. There are several good methods available for backing up SQL
data.. However, you cannot simply run a file backup on a SQL server and get
the data backed up. The MDF and LDF files are in use, as someone else posted.
To use the file backup method, you must use a SQL backup to disk, then
backup the backup files. That is, let SQL dump a backup file to DISK, then
use Veritas to backup that file.
I currently manage 40 SQL Servers and use this method. I have had mixed
results with the Veritas agent. There are some other products on the market
to assist with file based backups, such as Red Gate SQL Backup or SQL
Litespeed. They compress the backups to disk.
An easy way to get started with disk based backups is to go through the
database maintenance wizard and have it walk you through configuring and
scheduling a backup. Just remember to make sure your backups are finished on
SQL before Veritas starts it's backup.
Hope this helps.
Bryan
"Dan D." wrote:
> We use Veritas for backups on SS2000. Right now we do backups using the sql
> server agent. Would there be any problem doing just a file backup rather than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Thanks everyone for your responses. That helps a lot.
Bryan,
I tried LiteSpeed once but it wasn't any faster (which I expected it to be)
and according to our sysadmin it didn't compress the files any smaller than
Veritas.
We've had a lot of problems too with Veritas. I'm not in charge of the
backups so I'm not sure if the problems are Veritas related, hardware related
or something else but it really is a pain. I backup our most critical
databases to another server via Sql Server.
Thanks,
--
Dan D.
"Bryan Ivie" wrote:
> I've personally never cared for the Veritas agent in backing up SQL
> databases. There are several good methods available for backing up SQL
> data.. However, you cannot simply run a file backup on a SQL server and get
> the data backed up. The MDF and LDF files are in use, as someone else posted.
> To use the file backup method, you must use a SQL backup to disk, then
> backup the backup files. That is, let SQL dump a backup file to DISK, then
> use Veritas to backup that file.
> I currently manage 40 SQL Servers and use this method. I have had mixed
> results with the Veritas agent. There are some other products on the market
> to assist with file based backups, such as Red Gate SQL Backup or SQL
> Litespeed. They compress the backups to disk.
> An easy way to get started with disk based backups is to go through the
> database maintenance wizard and have it walk you through configuring and
> scheduling a backup. Just remember to make sure your backups are finished on
> SQL before Veritas starts it's backup.
> Hope this helps.
> Bryan
>
> "Dan D." wrote:
> > We use Veritas for backups on SS2000. Right now we do backups using the sql
> > server agent. Would there be any problem doing just a file backup rather than
> > whatever the sql server agent does?
> >
> > If we restored from the file backup, I'm guessing we would restore the file
> > then do a detach/attach to get the restored file in place. Or would doing
> > just a file backup miss something?
> >
> > Thanks,
> > --
> > Dan D.