Backups from SQLcmd ?

I am working with SQL Express and found myself surprised when I tried to automate a backup... No Agent.

So I am trying to create a simple backup script to run from the command line and probably schedule through the scheduled task manager.

here is my problem. I get the following error :

Msg 2812, Level 16, State 62, Server FIREFLY\SQLEXPRESS, Line 1
Could not find stored procedure 'B'.

Here is the batch file

sqlcmd -i c:\temp\test\DBbackup.sql -o c:\temp\test\output.txt -S FIREFLY\SQLEXPRESS

And here is the sql input file :

BACKUP DATABASE [DNNDEV] TO DISK = 'C:\temp\test\dnndev.bak' WITH NOFORMAT, NOINIT, NAME = 'dnndev-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

If I put a USE statement in front of my backup statement - the error message changes to

Could not find stored procedure 'U'.

So it would appear to list the first character encountered.

As a sanity check I created a similar process which does a select * from a table and I do not get an error saying :Could not find stored procedure 'S'. - instead all is well and I get my output and no error

If I try the backup command in SQLCMD interactively it works.

so what the heck am I missing ?

Thank you for any help...

What editor did you use to save the SQL file? Sounds like you generated a unicode file without BOM (byte order mark) so it is treated as ANSI file. Check the save options of your editor. Notepad for example has a combo box in its save dialog where you should choose ANSI. Unicode will save 2 or more byte per character. For standard letters it is often the ANSI code followed by a 0 byte, which is a terminator for ASCII c strings - so I guess sqlcmd stops after hitting the first 0 byte which follows your first letter.

--
SvenC

|||

Yep - That did it. Somehow my other test using the select statement must have been saved in ANSI - my backup file was saved in unicode

Thanks a million !

Backups Failed

Dear All,
We recently changed our user for the SQL Agent. On Sunday
nights we backup all our databases and perform checks,
this Sunday they did not work. The error is as follows: -
'
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'INVESTMENTS1' as 'NT AUTHORITY\SYSTEM'
(trusted)
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 229:
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'sysdbmaintplans',
database 'msdb', owner 'dbo'.'
The odd thing however is we have other jobs that run on a
day by day basis that still work, can anyone help?
Thanks
PeterHi,
Check whether local administrators group on the machine still a member of
the
sysadmin server role? Else add it and verify the job.
Incase if you still have issues then,
Can you please add that user (User inwhich u start SQL Agent) to
buildin\administrators group and verify the execution.
or else give select permission on 'sysdbmaintplans' table to the user in
which you have started SQL Agent.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a44e01c40a74$e3ee35e0$a601280a@.phx.gbl...
> Dear All,
> We recently changed our user for the SQL Agent. On Sunday
> nights we backup all our databases and perform checks,
> this Sunday they did not work. The error is as follows: -
> '
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'INVESTMENTS1' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 229:
> [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
> permission denied on object 'sysdbmaintplans',
> database 'msdb', owner 'dbo'.'
> The odd thing however is we have other jobs that run on a
> day by day basis that still work, can anyone help?
> Thanks
> Peter|||Thanks Hari,
Someone took out the Sysadmin access to the BUILTIN\Admin
right.
Peter

>--Original Message--
>Hi,
>Check whether local administrators group on the machine
still a member of
>the
>sysadmin server role? Else add it and verify the job.
>Incase if you still have issues then,
>Can you please add that user (User inwhich u start SQL
Agent) to
>buildin\administrators group and verify the execution.
>or else give select permission on 'sysdbmaintplans' table
to the user in
>which you have started SQL Agent.
>
>
>Thanks
>Hari
>MCDBA
>
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a44e01c40a74$e3ee35e0$a601280a@.phx.gbl...
Sunday
follows: -
a
>
>.
>

backups explained

Beginner's query.
please explain the OFA (open file agent) or lock rule when databases are being backed up.
I know files cannot be backed up if open, but what about database tables-not metadata, but data.
and what about the images accessed by a database-the reports or the docuemnts-they are backed up separately?
where can i find some basic rules for DB's...Short of DB's for dummies.Databases can be backed up while on-line.
Don't try to copy the .mdf/.ldf files - they won't be restorable probably - see backup database in bol.|||What concequences (if any) does open file agent or perhaps locks in this case have when backups are in operation and records are being updated?

For example on NT a file will not be backed up if open. I know the mdf and ldf files (or is it trn also) take logs, and snapshots for transactions, so that db's can be restored to a past point in time (rollback?). i know that bak files can be copied and used to create a database (restore maybe), but what about in db's?

Or is it simply that at that moment a backup is being written to file, and if the transaction is not fully committed prior to or at that time, it is not backed up, but will be included in the next back up...|||mdf file is the database file
ldf is the log file

bak is the database backup
trn is the transaction log file backup

It is not advisable to restore the db based on the mdf and ldf files - as they may be open at the time of backup. The only way to be sure you can restore is to use the bak and trn files. These will also take care of database locking and incomplete transactions at the time of the backup.|||Any pages updated while the backup is taking place are marked and written again to the end of the backup file.
Enough of the transaction log is backed up to allow a restore. Uncommitted transactions are rolled back at the restore.

The backup will slow down all processes on the server but will not stop any activity on the database.|||thanks all
appreciate your time :)
have just been on a sql 2000 admin course so it all sounds alot smipler now!
cheers

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

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

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.

Backup Transaction Job with NO truncate

Is there a way to setup a SQL Agent backup job to backup the transaction log and NOT truncate it
I can't seem to find any information on this.How about BACKUP LOG YourDatabase WITH NO_TRUNCATE? Why would you want to
back it up and never truncate it?
--
Andrew J. Kelly
SQL Server MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:C1903053-1B63-433D-A9DD-AACED12D83F2@.microsoft.com...
> Is there a way to setup a SQL Agent backup job to backup the transaction
log and NOT truncate it.
> I can't seem to find any information on this.|||You can use the syntax Backup log dbname to device
with no_truncate.
Regards,
Paul Ibison

Backup Transaction Job with NO truncate

Is there a way to setup a SQL Agent backup job to backup the transaction log
and NOT truncate it.
I can't seem to find any information on this.How about BACKUP LOG YourDatabase WITH NO_TRUNCATE? Why would you want to
back it up and never truncate it?
Andrew J. Kelly
SQL Server MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:C1903053-1B63-433D-A9DD-AACED12D83F2@.microsoft.com...
> Is there a way to setup a SQL Agent backup job to backup the transaction
log and NOT truncate it.
> I can't seem to find any information on this.