Backup error on Microsoft CMS 2002 SP1A Database

I have set up a Database maintenance plan to perform full backup on all
databases on a daily basis. Recently I notice that it is failing to backup
the Microsoft Content Management Server 2002 SP1a Database on SQL Server
2000 with SP3a. The error log for the maintenance job is:
[21] Database Website: Database Backup...
Destination: & #91;d:\MSSQL\BACKUP\Website\Website_db_2
00403091753.BAK]
** Execution Time: 0 hrs, 0 mins, 4 secs **
[22] Database Website: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK'. Device error or
device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
The file d:\MSSQL\Log\ERROR contains the following corresponding entry
2004-03-09 17:53:51.51 backup BACKUP failed to complete the command
BACKUP DATABASE [Website] TO DISK =
N'd:\MSSQL\BACKUP\Website\Website_db_200
403091753.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
2004-03-09 17:53:51.53 spid64 BackupDiskFile::OpenMedia: Backup device
'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK' failed to open.
Operating system error = 32(error not found).
What is going on and how could I rectify this (without stopping and
service)?hi Patrick,
this seems to be a SQL related question.
Please post to an SQL related newsgroup.
Cheers,
Stefan.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:e6kihBgBEHA.688@.tk2msftngp13.phx.gbl...
> I have set up a Database maintenance plan to perform full backup on all
> databases on a daily basis. Recently I notice that it is failing to
backup
> the Microsoft Content Management Server 2002 SP1a Database on SQL Server
> 2000 with SP3a. The error log for the maintenance job is:
> [21] Database Website: Database Backup...
> Destination: & #91;d:\MSSQL\BACKUP\Website\Website_db_2
00403091753.BAK]
> ** Execution Time: 0 hrs, 0 mins, 4 secs **
> [22] Database Website: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Cannot open backup device
> 'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK'. Device error or
> device off-line. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
>
> The file d:\MSSQL\Log\ERROR contains the following corresponding entry
> 2004-03-09 17:53:51.51 backup BACKUP failed to complete the command
> BACKUP DATABASE [Website] TO DISK =
> N'd:\MSSQL\BACKUP\Website\Website_db_200
403091753.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> 2004-03-09 17:53:51.53 spid64 BackupDiskFile::OpenMedia: Backup device
> 'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK' failed to open.
> Operating system error = 32(error not found).
> What is going on and how could I rectify this (without stopping and
> service)?
>|||Patrick,
It sounds like it could be a rights problem, such as described in:
PRB: Unable to Back Up Database to a Network Drive Without Permissions
http://support.microsoft.com/defaul...kb;en-us;207187
Alternatively, the device is offline.
(OR the disk is full and cannot create another file. But I would have
expected a different message.)
Russell Fields
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:e6kihBgBEHA.688@.tk2msftngp13.phx.gbl...
> I have set up a Database maintenance plan to perform full backup on all
> databases on a daily basis. Recently I notice that it is failing to
backup
> the Microsoft Content Management Server 2002 SP1a Database on SQL Server
> 2000 with SP3a. The error log for the maintenance job is:
> [21] Database Website: Database Backup...
> Destination: & #91;d:\MSSQL\BACKUP\Website\Website_db_2
00403091753.BAK]
> ** Execution Time: 0 hrs, 0 mins, 4 secs **
> [22] Database Website: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Cannot open backup device
> 'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK'. Device error or
> device off-line. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
>
> The file d:\MSSQL\Log\ERROR contains the following corresponding entry
> 2004-03-09 17:53:51.51 backup BACKUP failed to complete the command
> BACKUP DATABASE [Website] TO DISK =
> N'd:\MSSQL\BACKUP\Website\Website_db_200
403091753.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> 2004-03-09 17:53:51.53 spid64 BackupDiskFile::OpenMedia: Backup device
> 'd:\MSSQL\BACKUP\Website\Website_db_2004
03091753.BAK' failed to open.
> Operating system error = 32(error not found).
> What is going on and how could I rectify this (without stopping and
> service)?
>|||I don't think the two reasons apply to me, because
1) The backup is done to d:\MSSQL\Backup (a local drive), which the SQL
Server and SQL Agent service account user has permission to right to (the
service account user has local admin priviledges)
2) The database is online (it is functioning otherwise) and so is d:\
The Maintenance plan succeeded in backing up *All* other database on the
same local server running:
1) SQL Server 2000 SP3a
2) Windows 2000 with SP4
The Maintenance plan also succeeded in backing up that database for >1 week
before it become persistently failing!
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OY5grWgBEHA.1380@.TK2MSFTNGP10.phx.gbl...
> Patrick,
> It sounds like it could be a rights problem, such as described in:
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/defaul...kb;en-us;207187
> Alternatively, the device is offline.
> (OR the disk is full and cannot create another file. But I would have
> expected a different message.)
> Russell Fields
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:e6kihBgBEHA.688@.tk2msftngp13.phx.gbl...
> backup
> SQL
device
>|||I just did another search and found
http://groups.google.com/groups?hl=...r />
ie%3DUTF-
8%26oe%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
I am using Symantec Anti Virus Corporate Edition Client (although will move
over to McAfee Virus Scan 7 soon), but confused as to why the DB Maintenance
plan is just failing to back up on this single database. The only thing
special about this database is that there might be people *reading* from it
whilst the backup is being one, but I should have thought that SQL Server
should cope with this OK.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OY5grWgBEHA.1380@.TK2MSFTNGP10.phx.gbl...
> Patrick,
> It sounds like it could be a rights problem, such as described in:
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/defaul...kb;en-us;207187
> Alternatively, the device is offline.
> (OR the disk is full and cannot create another file. But I would have
> expected a different message.)
> Russell Fields
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:e6kihBgBEHA.688@.tk2msftngp13.phx.gbl...
> backup
> SQL
device
>|||Hi Patrick,
Thank your for using the newsgroup. It is my pleasure to help you with your
issue.
From you information, you job to maintenance the database 'Website' ran
fine in the past, however, just recently, it persistently failed.
There must be some change that different with before, so I want to confirm
that when you disable the Anti-Virus Software on your computer, do you
still meet the problem? The following link would take you to the download
of the Utility called ProcessExplorer that you can use to monitor access of
files by different processes.
To find what processes are accessing a particular file, you would need to
Search and enter the File Name for obtaining a list of the processes.
http://www.sysinternals.com/ntw2k/f...e/procexp.shtml
If it is not caused by the Anti-Virus software, you could try the following
steps to narrow down the problem. First, stop the antivirus software and:
1) In the Enterprise Manager, using the Backup Wizard to backup the
database, any problems?
2) Run in you Query Analyzer the following code (Suppose you are using sa
and you login into the Query Analyzer by the account starting the SQL Agent
Service):
Exec xp_cmdshell 'md d:\MSSQL\BACKUP\Website\aaa'
Go
Exec xp_cmdshell 'rd d:\MSSQL\BACKUP\Website\aaa'
go
BACKUP DATABASE [Website] TO DISK
=N'd:\MSSQL\BACKUP\Website\Website_db_20
0403091753.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Is there any Problems and messages?
Looking forward to you reply.
Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||1) I did a serach for handle *.BAK with SysInternal's process explorer and
could not find Symantec Anti Virus trying to access the *.BAK file.
2) I could select the Website database, right click All Task->Backup
Database to do a complete backup successfully
3) For some reason if I log on as a user with DBA role (by Windows admin
group membership) into query analyser, the execution of EXEC xp_cmdshell
failed saying command not found, but more worrying is that when I execute
the backup command, I get the following error
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead
(WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent backed up.
Connection Broken
The SQLServer 2000 with SP3a is installed on a Windows 2000 SP4 running
Microsoft Content Management Server 2002 SP1A, ASP.NET, ASP applications and
it is a domain controller. The SQL Client Network utility is configured as
followed:
- Enabled Shared memory protocol not ticked
- enabled Protocol by order- Named Piped, TCP/IP
SQL Client was set up as above because there were timed-out issues when
using the Microsoft Content Management Server 2002 SP1A Site deployment
import scripts.
What is wrong?
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:jA66KVmBEHA.660@.cpmsftngxa06.phx.gbl...
> Hi Patrick,
> Thank your for using the newsgroup. It is my pleasure to help you with
your
> issue.
> From you information, you job to maintenance the database 'Website' ran
> fine in the past, however, just recently, it persistently failed.
> There must be some change that different with before, so I want to confirm
> that when you disable the Anti-Virus Software on your computer, do you
> still meet the problem? The following link would take you to the download
> of the Utility called ProcessExplorer that you can use to monitor access
of
> files by different processes.

> To find what processes are accessing a particular file, you would need to
> Search and enter the File Name for obtaining a list of the processes.
> http://www.sysinternals.com/ntw2k/f...e/procexp.shtml
> If it is not caused by the Anti-Virus software, you could try the
following
> steps to narrow down the problem. First, stop the antivirus software and:
> 1) In the Enterprise Manager, using the Backup Wizard to backup the
> database, any problems?
> 2) Run in you Query Analyzer the following code (Suppose you are using sa
> and you login into the Query Analyzer by the account starting the SQL
Agent
> Service):
> Exec xp_cmdshell 'md d:\MSSQL\BACKUP\Website\aaa'
> Go
> Exec xp_cmdshell 'rd d:\MSSQL\BACKUP\Website\aaa'
> go
> BACKUP DATABASE [Website] TO DISK
> =N'd:\MSSQL\BACKUP\Website\Website_db_20
0403091753.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> Is there any Problems and messages?
> Looking forward to you reply.
> Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||Strangely, if I change SQL Client Network utility to use TCP/IP before
Named-pipes, then the problem seems to have gone. But why is this? I
thought when SQL Server is installed locally to the server, named-pipes go
via the system kernel which is meant to be super fast?
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:uy6S1VoBEHA.2888@.TK2MSFTNGP09.phx.gbl...
> 1) I did a serach for handle *.BAK with SysInternal's process explorer and
> could not find Symantec Anti Virus trying to access the *.BAK file.
> 2) I could select the Website database, right click All Task->Backup
> Database to do a complete backup successfully
> 3) For some reason if I log on as a user with DBA role (by Windows admin
> group membership) into query analyser, the execution of EXEC xp_cmdshell
> failed saying command not found, but more worrying is that when I execute
> the backup command, I get the following error
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRe
ad
> (WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> 10 percent backed up.
> Connection Broken
> The SQLServer 2000 with SP3a is installed on a Windows 2000 SP4 running
> Microsoft Content Management Server 2002 SP1A, ASP.NET, ASP applications
and
> it is a domain controller. The SQL Client Network utility is configured
as
> followed:
> - Enabled Shared memory protocol not ticked
> - enabled Protocol by order- Named Piped, TCP/IP
> SQL Client was set up as above because there were timed-out issues when
> using the Microsoft Content Management Server 2002 SP1A Site deployment
> import scripts.
> What is wrong?
> "Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
> news:jA66KVmBEHA.660@.cpmsftngxa06.phx.gbl...
> your
confirm
download
> of
>
to
> following
and:
sa
> Agent
rights.
>|||Hi Patrick,
Thanks for your update. You did a lot of work in troubleshooting this
problem and it seems that you have found a workaround for the this problem.
It is much helpful and appreciated.
For the xp_cmdshell, the execute permissions for xp_cmdshell default to
members of the sysadmin fixed server role, but can be granted to other
users. I just want to make sure the account to run the backup job (SQL
Agent Service Account is the account that will finally run the job, if
proxy is not used) has the permission on the specific folder.
For your question of using TCP/IP instead of the Named Pipe, please refer
to the following article:
General Network Error When You Try to Back up or Restore a SQL Server
Database on a Computer That Is Running Windows Server 2003 (also apply to
Windows Server 2000)
http://support.microsoft.com/?id=827452
Then you could use the SQL Agent starting service account to run the backup
statement successfully, you could create the maintance job. If there is
any more problems about it, I will be ready to help.
Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Backup Differential

Hi ,
Can someone provide me futher info about Backup Diffrential ? Is it need
to be perform after the full backup ? How the SQL server detect there is the
changes of the tables ?
1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
all the data available in the database
2) If I backup differential on the Pubs mdb also , do I need to use back
the same backup name ? If it is , the C:\Pubs_Backup will only consist the
latest updated data.
The original full data will be lost . It is true ?
Travis Tan
Travis
Have you read examples and articles in the BOL? It has very good examples.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:8F64EC9D-49AF-4DB7-BDCB-E21F6A5ED700@.microsoft.com...
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it
need
> to be perform after the full backup ? How the SQL server detect there is
the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use
back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan
|||Travis wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
>
look in BooksOnLine (BOL), it's all there
|||Travis,
In order to restore the db, you will need a full backup and the
differential, if there is one, plus all log backups after the diff one, or if
the sequence of log backups have not been broken, you can use the last full
backup and all transactions log backups. But time is precious and
differential backups help you save time.
A diffenrential backup is useful when the full backup of your db take long
time, so if you need to backup your data twice daily and the full backup take
12 hours, you can take one full backup once a week and two diff backups
daily, saving all changes from the last full backup.
As the others members said, BOL has good samples. You can also find some
good articles about this theme in Tibor's web page.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Travis" wrote:

> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan
|||Differential Database Backups
A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft? SQL Server? 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information, see
Using File Backups.
You use differential database backups to restore the database to the point
at which the differential database backup was completed. To recover to the
exact point of failure, you must use transaction log backups. For more
information, see Transaction Log Backups.
Consider using differential database backups when:
Only a relatively small portion of the data in the database has changed
since the last database backup. Differential database backups are
particularly effective if the same data is modified many times.
You are using the Simple Recovery model and want more frequent backups, but
don't want to do frequent full database backups.
You are using the Full or Bulk-Logged Recovery model and want to minimize
the time it takes to roll forward transaction log backups when restoring a
database.
A recommended process for implementing differential database backups is:
Create regular database backups.
Create a differential database backup periodically between database backups,
such as every four hours or more for highly active systems.
If using Full or Bulk-Logged Recovery, create transaction log backups more
frequently than differential database backups, such as every 30 minutes.
The sequence for restoring differential database backups is:
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
For more information on differential database backups go here:
http://msdn.microsoft.com/library/de...kprst_7tv7.asp
"Travis" wrote:

> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan

Backup Differential

Hi ,
Can someone provide me futher info about Backup Diffrential ? Is it need
to be perform after the full backup ? How the SQL server detect there is the
changes of the tables ?
1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
all the data available in the database
2) If I backup differential on the Pubs mdb also , do I need to use back
the same backup name ? If it is , the C:\Pubs_Backup will only consist the
latest updated data.
The original full data will be lost . It is true ?
Travis TanTravis
Have you read examples and articles in the BOL? It has very good examples.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:8F64EC9D-49AF-4DB7-BDCB-E21F6A5ED700@.microsoft.com...
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it
need
> to be perform after the full backup ? How the SQL server detect there is
the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use
back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Travis wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it ne
ed
> to be perform after the full backup ? How the SQL server detect there is t
he
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use bac
k
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
>
look in BooksOnLine (BOL), it's all there|||Travis,
In order to restore the db, you will need a full backup and the
differential, if there is one, plus all log backups after the diff one, or i
f
the sequence of log backups have not been broken, you can use the last full
backup and all transactions log backups. But time is precious and
differential backups help you save time.
A diffenrential backup is useful when the full backup of your db take long
time, so if you need to backup your data twice daily and the full backup tak
e
12 hours, you can take one full backup once a week and two diff backups
daily, saving all changes from the last full backup.
As the others members said, BOL has good samples. You can also find some
good articles about this theme in Tibor's web page.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Travis" wrote:

> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it ne
ed
> to be perform after the full backup ? How the SQL server detect there is t
he
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use bac
k
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Differential Database Backups
A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft? SQL Server? 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information, se
e
Using File Backups.
You use differential database backups to restore the database to the point
at which the differential database backup was completed. To recover to the
exact point of failure, you must use transaction log backups. For more
information, see Transaction Log Backups.
Consider using differential database backups when:
Only a relatively small portion of the data in the database has changed
since the last database backup. Differential database backups are
particularly effective if the same data is modified many times.
You are using the Simple Recovery model and want more frequent backups, but
don't want to do frequent full database backups.
You are using the Full or Bulk-Logged Recovery model and want to minimize
the time it takes to roll forward transaction log backups when restoring a
database.
A recommended process for implementing differential database backups is:
Create regular database backups.
Create a differential database backup periodically between database backups,
such as every four hours or more for highly active systems.
If using Full or Bulk-Logged Recovery, create transaction log backups more
frequently than differential database backups, such as every 30 minutes.
The sequence for restoring differential database backups is:
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
For more information on differential database backups go here:
http://msdn.microsoft.com/library/d... />
t_7tv7.asp
"Travis" wrote:

> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it ne
ed
> to be perform after the full backup ? How the SQL server detect there is t
he
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use bac
k
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan

Backup Differential

Hi ,
Can someone provide me futher info about Backup Diffrential ? Is it need
to be perform after the full backup ? How the SQL server detect there is the
changes of the tables ?
1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
all the data available in the database
2) If I backup differential on the Pubs mdb also , do I need to use back
the same backup name ? If it is , the C:\Pubs_Backup will only consist the
latest updated data.
The original full data will be lost . It is true ?
--
Travis TanTravis
Have you read examples and articles in the BOL? It has very good examples.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:8F64EC9D-49AF-4DB7-BDCB-E21F6A5ED700@.microsoft.com...
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it
need
> to be perform after the full backup ? How the SQL server detect there is
the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use
back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Travis wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
>
look in BooksOnLine (BOL), it's all there|||Travis,
In order to restore the db, you will need a full backup and the
differential, if there is one, plus all log backups after the diff one, or if
the sequence of log backups have not been broken, you can use the last full
backup and all transactions log backups. But time is precious and
differential backups help you save time.
A diffenrential backup is useful when the full backup of your db take long
time, so if you need to backup your data twice daily and the full backup take
12 hours, you can take one full backup once a week and two diff backups
daily, saving all changes from the last full backup.
As the others members said, BOL has good samples. You can also find some
good articles about this theme in Tibor's web page.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Differential Database Backups
A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft® SQL Server� 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information, see
Using File Backups.
You use differential database backups to restore the database to the point
at which the differential database backup was completed. To recover to the
exact point of failure, you must use transaction log backups. For more
information, see Transaction Log Backups.
Consider using differential database backups when:
Only a relatively small portion of the data in the database has changed
since the last database backup. Differential database backups are
particularly effective if the same data is modified many times.
You are using the Simple Recovery model and want more frequent backups, but
don't want to do frequent full database backups.
You are using the Full or Bulk-Logged Recovery model and want to minimize
the time it takes to roll forward transaction log backups when restoring a
database.
A recommended process for implementing differential database backups is:
Create regular database backups.
Create a differential database backup periodically between database backups,
such as every four hours or more for highly active systems.
If using Full or Bulk-Logged Recovery, create transaction log backups more
frequently than differential database backups, such as every 30 minutes.
The sequence for restoring differential database backups is:
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
For more information on differential database backups go here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_7tv7.asp
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan

Backup Databases

I am running SQL2000 SP4 on a Win2000 server and getting following error when
trying to perform a backup of a database within Enterprise Manager.
"Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
references. Reason 127(The specified procedure could not be found.). I have
looked any references to this error message, but yet to find anything on it.
Wondering if someone can assist me.
thanks,
JayHi
If backing up the datrabase from a different PC works, then I would think
re-installing the client tools may help solve this.
John
"Jay" wrote:
> I am running SQL2000 SP4 on a Win2000 server and getting following error when
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I have
> looked any references to this error message, but yet to find anything on it.
> Wondering if someone can assist me.
> thanks,
> Jay
>|||Jay wrote:
> I am running SQL2000 SP4 on a Win2000 server and getting following error when
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I have
> looked any references to this error message, but yet to find anything on it.
> Wondering if someone can assist me.
> thanks,
> Jay
>
Open up Query Analyzer and use the T-SQL BACKUP command - does it work?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup Databases

I am running SQL2000 SP4 on a Win2000 server and getting following error whe
n
trying to perform a backup of a database within Enterprise Manager.
"Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
references. Reason 127(The specified procedure could not be found.). I have
looked any references to this error message, but yet to find anything on it.
Wondering if someone can assist me.
thanks,
JayHi
If backing up the datrabase from a different PC works, then I would think
re-installing the client tools may help solve this.
John
"Jay" wrote:

> I am running SQL2000 SP4 on a Win2000 server and getting following error w
hen
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I ha
ve
> looked any references to this error message, but yet to find anything on i
t.
> Wondering if someone can assist me.
> thanks,
> Jay
>|||Jay wrote:
> I am running SQL2000 SP4 on a Win2000 server and getting following error w
hen
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I ha
ve
> looked any references to this error message, but yet to find anything on i
t.
> Wondering if someone can assist me.
> thanks,
> Jay
>
Open up Query Analyzer and use the T-SQL BACKUP command - does it work?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup and Restore using VBScript (ActiveX)

We are trying to setup a central job server that will perform the backups, restores, and other automated tasks for all of our SQL servers. The goal is that we have one place to check the status of all of our jobs. I have a couple of problems, though.

The first is that, to backup a database on a remote server, we are using VBScript as an ActiveX Script type of step. The script uses SQLDMO. The jobs mostly run fine, but there seems to be a problem with concurrent jobs running against the same server. For instance, I have 4 backup jobs that I kick off at 7:00PM. One of them (not always the same one) will fail with:

Error Code: 0 Error Source= Microsoft SQL-DMO Error Description: [SQL-DMO]The application is busy. Error on Line 11. The step failed.

The databses are all quite small (master, msdb, and a couple of utility dbs we have). Are there threading issues in using SQL-DMO such that I can't run jobs concurrently? The remote server is SQL2000, so I don't believe I can use SMO. My script is at the end of this post.

My second problem is trying to remotely kick off a restore. Currently, when using the local scheduler, we use the RESTORE DATABASE command right after an ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE command. This command is necessary to kill any processes currently using the DB so that a restore will succeed. In DMO I can't find anyway to duplicate this functionality. Any ideas?

Thanks

Steve

-- Backup Script --

Dim sServer, sDatabase, sPath, sDumpFile, oSQLServer, oBackup

sServer = "PRDSQL01"
sDatabase = "master"
sPath = "E:\MSSQL\DUMP\"
sDumpFile = "master.dmp"

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oBackup = CreateObject("SQLDMO.BackUp")

oSQLServer.loginsecure = true
oSQLServer.Connect sServer

oBackup.Database = sDatabase
oBackup.Files = sPath & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer

oSQLServer.Disconnect

Set sServer = nothing
Set sDatabase = nothing
Set sPath = nothing
Set sDumpFile = nothing
Set oSQLServer = nothing
Set oBackup = nothing

Hi Stev

Vimal , here ,

sir its helpful to take backup ,please tell me script for restore the taken dump file.

Vimal

Backup and Restore using VBScript (ActiveX)

We are trying to setup a central job server that will perform the backups, restores, and other automated tasks for all of our SQL servers. The goal is that we have one place to check the status of all of our jobs. I have a couple of problems, though.

The first is that, to backup a database on a remote server, we are using VBScript as an ActiveX Script type of step. The script uses SQLDMO. The jobs mostly run fine, but there seems to be a problem with concurrent jobs running against the same server. For instance, I have 4 backup jobs that I kick off at 7:00PM. One of them (not always the same one) will fail with:

Error Code: 0 Error Source= Microsoft SQL-DMO Error Description: [SQL-DMO]The application is busy. Error on Line 11. The step failed.

The databses are all quite small (master, msdb, and a couple of utility dbs we have). Are there threading issues in using SQL-DMO such that I can't run jobs concurrently? The remote server is SQL2000, so I don't believe I can use SMO. My script is at the end of this post.

My second problem is trying to remotely kick off a restore. Currently, when using the local scheduler, we use the RESTORE DATABASE command right after an ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE command. This command is necessary to kill any processes currently using the DB so that a restore will succeed. In DMO I can't find anyway to duplicate this functionality. Any ideas?

Thanks

Steve

-- Backup Script --

Dim sServer, sDatabase, sPath, sDumpFile, oSQLServer, oBackup

sServer = "PRDSQL01"
sDatabase = "master"
sPath = "E:\MSSQL\DUMP\"
sDumpFile = "master.dmp"

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oBackup = CreateObject("SQLDMO.BackUp")

oSQLServer.loginsecure = true
oSQLServer.Connect sServer

oBackup.Database = sDatabase
oBackup.Files = sPath & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer

oSQLServer.Disconnect

Set sServer = nothing
Set sDatabase = nothing
Set sPath = nothing
Set sDumpFile = nothing
Set oSQLServer = nothing
Set oBackup = nothing

Hi Stev

Vimal , here ,

sir its helpful to take backup ,please tell me script for restore the taken dump file.

Vimal

Backup and restore

Hello to all readers.
I can perform a Backup like this :
Public Sub Backup(ByVal dtCurrentId As DateTime)
Dim sSQLSvrDir As String = GetSqlDataDirectory()
Dim sDataDir As String = sSQLSvrDir & "\Data\"
Dim sServerName As String = Environment.MachineName & "\" & INSTANCE_NAME
Dim sCommand As String
'close the actual connexion
CloseDatabase()
'set the connection string to the the master database
sConnectionString = BuildConnectionString(sServerName, MASTERDB,
String.Empty, SecurityMode.smWindowsIntegrated)
'process the backup
sCommand = "USE master;"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_addumpdevice 'disk', 'PIPTBackup', '" & g_sAppPath &
BACKUPSUBDIR & "\Backup PIP Traceability " & Format(dtCurrentId, "yyyy-MM-dd
HH-mm-ss") & ".dat';"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "BACKUP DATABASE [PIP Traceability] TO PIPTBackup;"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_dropdevice 'PIPTBackup';"
ExecuteSQL(sConnectionString, sCommand)
're open the database connection
Call OpenDatabase()
End Sub
This work fine.
But the restore :
Public Sub Restore(ByVal dtCurrentId As DateTime)
Dim sSQLSvrDir As String = GetSqlDataDirectory()
Dim sDataDir As String = sSQLSvrDir & "\Data\"
Dim sServerName As String = Environment.MachineName & "\" & INSTANCE_NAME
Dim sCommand As String
'close the actual connexion
CloseDatabase()
'set the connection string to the the master database
sConnectionString = BuildConnectionString(sServerName, MASTERDB,
String.Empty, SecurityMode.smWindowsIntegrated)
'process the backup
sCommand = "USE master;"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_addumpdevice 'disk', 'PIPTBackup', '" & g_sAppPath &
BACKUPSUBDIR & "\Backup PIP Traceability " & Format(dtCurrentId, "yyyy-MM-dd
HH-mm-ss") & ".dat';"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "RESTORE DATABASE [PIP Traceability] FROM PIPTBackup; EXEC
sp_dropdevice 'PIPTBackup';"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_dropdevice 'PIPTBackup';"
ExecuteSQL(sConnectionString, sCommand)
're-open the database connection
Call OpenDatabase()
End Sub
does not work. I get an error message telling that the database is used by
an other user !!! (during the Restore SQL)
Any suggestion will be very appreciated.
Thanks
hi Ouaf,
"Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
> does not work. I get an error message telling that the database is used by
> an other user !!! (during the Restore SQL)
> Any suggestion will be very appreciated.
>
it usually depends on Connection Pooling... your connection is closed, but
the connection pooler usualy takes up to 1 minute to completely release
it... so you have to wait a little...
Ado.Net shoul'd expose a "non pooled" property for connections...
but other cons... the connection will neveer be picked up from an existing
pool, so it will take a little more to create it
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea,
do you think that a 'dispose' could help ?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> a crit dans le message de
news:2prk8bFohgqlU1@.uni-berlin.de...[vbcol=seagreen]
> hi Ouaf,
> "Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
> news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
by
> it usually depends on Connection Pooling... your connection is closed,
but
> the connection pooler usualy takes up to 1 minute to completely release
> it... so you have to wait a little...
> Ado.Net shoul'd expose a "non pooled" property for connections...
> but other cons... the connection will neveer be picked up from an existing
> pool, so it will take a little more to create it
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ouaf,
"Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
news:%23ReD5jdkEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Thanks Andrea,
> do you think that a 'dispose' could help ?
really do not know... but I do not think so... :-(
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
OK, that's working. I have to wait for a while for the connection release
from the pool.
(In the dev env, I had also to unsucsribe the database from the server
browser :-) )
Is there a cool way to test that the connection has been released from the
pool ot do I have to catch the error when trying the restore ?
Thanks
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> a crit dans le message de
news:2prk8bFohgqlU1@.uni-berlin.de...[vbcol=seagreen]
> hi Ouaf,
> "Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
> news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
by
> it usually depends on Connection Pooling... your connection is closed,
but
> the connection pooler usualy takes up to 1 minute to completely release
> it... so you have to wait a little...
> Ado.Net shoul'd expose a "non pooled" property for connections...
> but other cons... the connection will neveer be picked up from an existing
> pool, so it will take a little more to create it
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ouaf,
"Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
news:u7d46oykEHA.3520@.tk2msftngp13.phx.gbl...
> Hi Andrea,
> OK, that's working. I have to wait for a while for the connection release
> from the pool.
> (In the dev env, I had also to unsucsribe the database from the server
> browser :-) )
> Is there a cool way to test that the connection has been released from the
> pool ot do I have to catch the error when trying the restore ?
I think you always have to catch the exception(s) =;-DDDD
you can perhaps set the "autoclose" database property to help you this way
(default setting on MSDE platform), but beware that this setting has access
penalties as it always close the database when no active connections are
present, and reopen it as new connections are live..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea & Ouaf,
It does expose such a thing. You can set "Pooling=false" in your connection
string and pooling will be disabled. That's the setting for SqlClient. For
OleDB client, it's "OleDBServices=-4".
I'd suggest avoiding AutoClose. I've seen diabolical problems caused by it,
many more than it was intended to solve, plus it won't really help here.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2prk8bFohgqlU1@.uni-berlin.de...[vbcol=seagreen]
> hi Ouaf,
> "Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
> news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
by
> it usually depends on Connection Pooling... your connection is closed,
but
> the connection pooler usualy takes up to 1 minute to completely release
> it... so you have to wait a little...
> Ado.Net shoul'd expose a "non pooled" property for connections...
> but other cons... the connection will neveer be picked up from an existing
> pool, so it will take a little more to create it
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||"Greg Low [MVP]" <greglow@.lowell.com.au> ha scritto nel messaggio
news:e5ix4$5kEHA.3016@.tk2msftngp13.phx.gbl...
> I'd suggest avoiding AutoClose. I've seen diabolical problems caused by
it,
> many more than it was intended to solve, plus it won't really help here.
>
=;-D
thank's Greg
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backup Agent for SQL Server ?

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Backup Agent for SQL Server ?

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.

> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>

> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Backup Agent for SQL Server

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.

Backup Agent for SQL Server

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.

BACKUP EXPIREDATE & RETAINDAYS

I'm hoping that someone can help me clarify some questions I have
regarding BACKUP and how files are replaced. Ideally I want to perform
backups to a single file, replacing the appropriate backup when it
expires. How can I do this?
Consider the following backup statement:
BACKUP DATABASE
[DB1]
TO DISK = N'C:\back.bak'
WITH
retaindays = 5,
NAME = N'Full Database Backup'
GO
The above statement (exact) is then run 2 days in a row. The file
grows as expected.
Then 5 days pass and the same script is run. Rather than replacing the
first backup within the file, the file itself continues to grow. Is
there a reason for this?
I would like to know how to setup the backup statement so that a
backup is replaced once it expires.This is not what EXPIREDATE or RETAINDAYS do. All the do is not allow you to do INIT before the
date. If you do, you get an error message. You cannot have a backup file and overwrite only a few of
the backups. It is overwrite all (INIT) or nothing (NOINIT, the default).
If you want to keep x number of days worth, work with several backup files. Most name the files
according to the database name and include date and time, and then let the script delete files older
than x days. This is exactly how the maintenance wizard does it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pete" <pcnofelt@.gmail.com> wrote in message
news:1184851878.081775.267520@.x40g2000prg.googlegroups.com...
> I'm hoping that someone can help me clarify some questions I have
> regarding BACKUP and how files are replaced. Ideally I want to perform
> backups to a single file, replacing the appropriate backup when it
> expires. How can I do this?
> Consider the following backup statement:
> BACKUP DATABASE
> [DB1]
> TO DISK = N'C:\back.bak'
> WITH
> retaindays = 5,
> NAME = N'Full Database Backup'
> GO
> The above statement (exact) is then run 2 days in a row. The file
> grows as expected.
> Then 5 days pass and the same script is run. Rather than replacing the
> first backup within the file, the file itself continues to grow. Is
> there a reason for this?
> I would like to know how to setup the backup statement so that a
> backup is replaced once it expires.
>