Database Backups & Compression

Hi All

I have a database which is 72GB, which is backed up every night as part of the maintenance plan. I have plenty of storage space, and the server that runs the database is fairly powerful (quad-processor 3.2ghz, 64bit, 48GB RAM) and is part of an active-passive cluster. The database backup is also copied to a SAN location.

My issue is with the size of the backup file. As part of the Disaster Recovery plan, I need to copy this database backup file accross the network to a remote site, so that in the event of a disaster at the site, business can continue at the remote site after restoring the database backup file. However, my database backup file is so big that I cannot copy it accross the network in time for the next morning. I have tried using WinRar and have managed to achieve a file about 20% of its original size, but it takes 2 hours to produce this file.

Is there any recommended reeading for this type of issue? Log shipping / mirroring has been investigated and will be part of the DR model but the 'powers that be' insist on having a full copy performed to the remote site.

Any suggestions? Thanks in advance guys n gals :-)Log shipping IS a full copy, in the truest sense of the word. It is not a monolithic file, but that is a feature instead of a problem in my opinion.

-PatP|||okay for a moment let;
machine A = live, machine B = warm standby server on a remote site

I believed that when restoring log shipments to machine B, there are inherent problems, due to the nighly backups that take place on machine A.

When the log shipping from machine A continues after it has performed a nightly backup, the transcation log entries that were processed and removed during the backup will not be included in the next log shipment to machine B, thus losing a porion of transactions during that given period. Therefore, to successfully restore on machine B, the monolithic database backup file from machine A would be required initially to restore the database and then applying any log shipments that have been shipped after that night's backup.

Is that incorrect...anybody?

The problem is getting that monolithic file to copy accross the network in a given timeframe - it's too big to achieve but 'they' are insisting that it is done.|||Is that incorrect...anybody?


Bzzzzzzzz ... thank you for playing ... you will get your consolation prize as you head backstage :)

When databases are in full recovery mode, the backup does not mark the transaction log for re-use. That only occurs after the transaction log backup.

Prove it to yourself and the PTB (powers that be) by setting up log shipping with a copy of Northwind on the target box ... that way you aren't fighting the database size issue. GO thru the full backup, and tran backup cycles, then make database mods and see if any are lost (hint: they won't be).

I have, in the past, restored a backup from three months prior and then brought it current by applying log backups from that point forward, even across weekly full backups (storage team issues ... grrrrrrrrrrr!).

And to make the uneducated happy, you could even slowly copy the full backup across the wire to the failover machine weekly.|||The database backup will not truncate the log, so the subsequent log backups will contain all the log entries.

I have not done this with MS SQL backups yet but
To speedup the transfer of your 72 GB database backup;
Consider using rsync (http://www.google.com/search?num=100&hl=en&q=rsync&meta=)
Also consider using an rsyncable gzip (http://www.google.com/search?num=100&hl=en&q=rsyncable+gzip&meta=) to compress the file
At minimum compression it should reduce it to 14 GB at acceptable speed (assuming you don't have images inside your database)
And will allow rsync to only copy the portion inside the gzip file that changed.
You would probably see that only 1.4 GB of the 14 GB is actually transferred across the line (10 times faster).

PS. I can understand why they want full backups. You only need a problem with one log backup (missing or damaged file) and you won't be able to recover past that point. A full nightly backup and 15 min log backups make sense to me.

Database Backups

I’m trying to backup all my database in SQL server 2005, but I keep getting
the following error message.
Execution failed. See the maintenance plan and SQL Server Agent job history
logs for details.
Job Backup.Subplan_1 was suspended. (SqlManagerUI)
My current operating system structure is this.
OS installed on C: and I have SQL installed on D:
I will appreciate any help on this matter.
Chris Holden
Chris
We need more info about your backup strategy. What did you create?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4A06B2C6-D95C-4892-A070-B326C12623D6@.microsoft.com...
> Im trying to backup all my database in SQL server 2005, but I keep
> getting
> the following error message.
> Execution failed. See the maintenance plan and SQL Server Agent job
> history
> logs for details.
> Job Backup.Subplan_1 was suspended. (SqlManagerUI)
> My current operating system structure is this.
> OS installed on C: and I have SQL installed on D:
> I will appreciate any help on this matter.
> --
> Chris Holden
sql

database backups

I am able to do a backup of a database, but can not do a database maintenance
plan of the same database.
Also on doing a database maintenance plan the status says that it is
"Executing Job Step '1" and just stays there. It never finishes.
You need to refresh EM to see the change.
Andrew J. Kelly SQL MVP
"Carlton" <carlton.edwards@.fmb.com> wrote in message
news:0BFCE421-C5F8-4C37-AB65-00C9EF5C3999@.microsoft.com...
>I am able to do a backup of a database, but can not do a database
>maintenance
> plan of the same database.
> Also on doing a database maintenance plan the status says that it is
> "Executing Job Step '1" and just stays there. It never finishes.

Database Backups

I’m trying to backup all my database in SQL server 2005, but I keep gettin
g
the following error message.
Execution failed. See the maintenance plan and SQL Server Agent job history
logs for details.
Job Backup.Subplan_1 was suspended. (SqlManagerUI)
My current operating system structure is this.
OS installed on C: and I have SQL installed on D:
I will appreciate any help on this matter.
Chris HoldenChris
We need more info about your backup strategy. What did you create?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4A06B2C6-D95C-4892-A070-B326C12623D6@.microsoft.com...
> Im trying to backup all my database in SQL server 2005, but I keep
> getting
> the following error message.
> Execution failed. See the maintenance plan and SQL Server Agent job
> history
> logs for details.
> Job Backup.Subplan_1 was suspended. (SqlManagerUI)
> My current operating system structure is this.
> OS installed on C: and I have SQL installed on D:
> I will appreciate any help on this matter.
> --
> Chris Holden

database backups

I am able to do a backup of a database, but can not do a database maintenanc
e
plan of the same database.
Also on doing a database maintenance plan the status says that it is
"Executing Job Step '1" and just stays there. It never finishes.You need to refresh EM to see the change.
Andrew J. Kelly SQL MVP
"Carlton" <carlton.edwards@.fmb.com> wrote in message
news:0BFCE421-C5F8-4C37-AB65-00C9EF5C3999@.microsoft.com...
>I am able to do a backup of a database, but can not do a database
>maintenance
> plan of the same database.
> Also on doing a database maintenance plan the status says that it is
> "Executing Job Step '1" and just stays there. It never finishes.

database backups

I am able to do a backup of a database, but can not do a database maintenance
plan of the same database.
Also on doing a database maintenance plan the status says that it is
"Executing Job Step '1" and just stays there. It never finishes.You need to refresh EM to see the change.
--
Andrew J. Kelly SQL MVP
"Carlton" <carlton.edwards@.fmb.com> wrote in message
news:0BFCE421-C5F8-4C37-AB65-00C9EF5C3999@.microsoft.com...
>I am able to do a backup of a database, but can not do a database
>maintenance
> plan of the same database.
> Also on doing a database maintenance plan the status says that it is
> "Executing Job Step '1" and just stays there. It never finishes.

database backup job quits for no apparent reason

I created a maintenance plan to back up a database as a file to another server. Full backup was specified. The job has been running for about 3 months, then quit last night (after the server was rebooted). Error is:

Message
Executed as user: xxxxx\Administrator. The command line parameters are invalid. The step failed.

not sure what to do to fix this - -

Do a profiler trace to see if it's a TSQL issue somewhere, maybe the plan was accidentally changed somehow.|||I turned on Profiler to trace database backups - now all the backups are running fine -|||This is typically caused by the account changing the password. If you change the password of the account you used to run the job, you won't see any problems until you reboot your server/restart SQL Server service as this is the only time that the new security credentials will take effect.|||the jobs were created by and are run under the domain master system administrator account|||

Please go the SQL server jobs and change user domain\Administrator to sa (SQL Administrators).

it should be resolve......

Database Backup in a Maintenance Plan

I want to write a SQL query to find if a perticular database is backed up in
any Maintenance plan or not.
Is there any way to write this query.
I can surely open all the Plans and view it, but I need to find it out using
a query.
Please let me know if it is possible.
Thanks in advance, Steven
Which version are you talking about? If it is 2000 then have a look at the
sysdbmaintplan_databases table in msdb.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Steven" <Steven@.nospaml.com> wrote in message
news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>I want to write a SQL query to find if a perticular database is backed up
>in any Maintenance plan or not.
> Is there any way to write this query.
> I can surely open all the Plans and view it, but I need to find it out
> using a query.
> Please let me know if it is possible.
> Thanks in advance, Steven
>
|||Thanks for the suggestion.
I'll take it from her.
Thanks again, Steven
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exZYoUZiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> ... or perhaps the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
>

Database Backup in a Maintenance Plan

I want to write a SQL query to find if a perticular database is backed up in
any Maintenance plan or not.
Is there any way to write this query.
I can surely open all the Plans and view it, but I need to find it out using
a query.
Please let me know if it is possible.
Thanks in advance, StevenWhich version are you talking about? If it is 2000 then have a look at the
sysdbmaintplan_databases table in msdb.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Steven" <Steven@.nospaml.com> wrote in message
news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>I want to write a SQL query to find if a perticular database is backed up
>in any Maintenance plan or not.
> Is there any way to write this query.
> I can surely open all the Plans and view it, but I need to find it out
> using a query.
> Please let me know if it is possible.
> Thanks in advance, Steven
>|||... or perhaps the backup history tables in msdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
> Which version are you talking about? If it is 2000 then have a look at the
> sysdbmaintplan_databases table in msdb.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Steven" <Steven@.nospaml.com> wrote in message news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>>I want to write a SQL query to find if a perticular database is backed up in any Maintenance plan
>>or not.
>> Is there any way to write this query.
>> I can surely open all the Plans and view it, but I need to find it out using a query.
>> Please let me know if it is possible.
>> Thanks in advance, Steven
>>
>|||Thanks for the suggestion.
I'll take it from her.
Thanks again, Steven
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exZYoUZiIHA.5780@.TK2MSFTNGP06.phx.gbl...
> ... or perhaps the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uu1uaHViIHA.4080@.TK2MSFTNGP03.phx.gbl...
>> Which version are you talking about? If it is 2000 then have a look at
>> the sysdbmaintplan_databases table in msdb.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Steven" <Steven@.nospaml.com> wrote in message
>> news:eoeQWEUiIHA.3556@.TK2MSFTNGP04.phx.gbl...
>>I want to write a SQL query to find if a perticular database is backed up
>>in any Maintenance plan or not.
>> Is there any way to write this query.
>> I can surely open all the Plans and view it, but I need to find it out
>> using a query.
>> Please let me know if it is possible.
>> Thanks in advance, Steven
>>
>

Database Backup has stopped working. Cannot tell why

Hi,

I had a Database maintenance plan setup to do complete backup of my SQL Server 2000 database. Same thing was done for the transaction logs as well. And they had resulted in successful backups sometime ago.

But I have noticed that Backups are no longer happening. I cannot find the backup files where they are supposed to land. Some how, I cannot find any error messages relating why the backups are not getting created.

I do not know where I can look up the logs/reports of what possibly is going wrong. I have looked at the usual places and they are not there, for the times I have deliberately tried to submit the jobs.

Any help will be appreciated.

Thanks

Sam

Are the maint. plan jobs erroring? A db maint plan is fired off by a SQL Server Job. You should be able to look at the history of the job for a description of the failure. If there is no failure, check to make sure the job is enabled. If it is enabled, try running it by hand to see if the files are created.
Tim|||Open up your SQL Server error log. Are you actually getting messages in the log that databases are either backing up or are failing?

Database backup fails

Hi,
I have three databases on a SQL Server. I have created three separate
maintenance plans for the three databases. The plans are exactly identical
except for the time of the complete backup. The complete backup of all the
three databases happens every night at different times and the transaction
log backup takes place every 3 hours during 11 AM and 5 PM. However, from the
first day onwards, plan for database one always fails while the plans for the
other two databases execute successfully. I don't know what I am doing
incorrectly. Can someone please let me know how to figure out the reason for
the failure?Sharman,
You can view the error log and the event log for more details. In addition
you can right click the maintenance plan and choose Maintenance Plan
History...
Also make sure you have not selected the 'Attempt to repair any minor
problems' checkbox. Requires the DB to be in single user mode.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:376FA452-F24E-4D3B-8AB2-9AD221645CB4@.microsoft.com...
> Hi,
> I have three databases on a SQL Server. I have created three separate
> maintenance plans for the three databases. The plans are exactly identical
> except for the time of the complete backup. The complete backup of all the
> three databases happens every night at different times and the transaction
> log backup takes place every 3 hours during 11 AM and 5 PM. However, from
> the
> first day onwards, plan for database one always fails while the plans for
> the
> other two databases execute successfully. I don't know what I am doing
> incorrectly. Can someone please let me know how to figure out the reason
> for
> the failure?
>

Database backup directory is invalid

Hi,

I'm using Database Maintenance Plans in Enterprise Manager and I'm trying to backup a database to a different server using a UNC (i.e. \\servername\backupdirectory).
I have done this successfully with two SQL Servers but every time I try to set the third SQL Server to backup to a UNC I get this message:

The complete database backup directory '\\servername\backupdirectory' is invalid.

The only differences that I can think of is that the server is Win2000 Advanced Server and the SQL Server is Enterprise Edn, where as the two that work are Standard Edn and are on Win2000 Servers (not Advanced).

Does anyone know why I am unable to do this on one SQL Server when I have had no trouble on the other two SQL Servers?

Any help would be greatly appreciated

Thanks,

TroyIf I had to make a guess, I'd say that the working box was using a domain login for the SQL Server service, and the non-working box was using localSystem.

-PatP|||Not a bad guess at all :rolleyes:

Thanks for your help.

Regards, Troy.

Database backup and maintenance plan

In EM, what are the diffeneces between these two under the Management folder
?
I can schedule the database backup in Backup and also can schedule the
database schedule in Database Maintenance Plans ?
I set up the full backup and schedule in 'BackUp', it can create the full
backup file in the directory but cannot view contents by right click the
item on the right pane of 'Backup' folder.
Different way of scheduling, quite simply. To understand why you cannot see backups created by maint
plan, read about sp_addumpdevice. Main plan doesn't create backup devices, it addresses files
directly (see the BACKUP command and DISK = 'filename' option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>
|||Another difference between the two is that the backup dialog box simply
allows you do create/schedule a backup..
The database maintenance plan wizard is intended to allow you to set up
several jobs which should cover most of your maintenance needs... not only
backups, but index maintenance, and dbcc checkdb...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management
folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>
|||Thanks.
However, I have setup the backup device for the Northwind database for
example in the Management|Backup subfolder, and schedule the full and
differential backup for that, it can backup according to the schedule,
that's fine, BUT I cannot review the schedule of the backup that I created.
I had schedule the full backup at every Sunday and differential backup every
weekday at 5 pm, how do I review the schedule of these backup ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Another difference between the two is that the backup dialog box simply
> allows you do create/schedule a backup..
> The database maintenance plan wizard is intended to allow you to set up
> several jobs which should cover most of your maintenance needs... not only
> backups, but index maintenance, and dbcc checkdb...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.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
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> folder
full
>
|||Look in the SQL Server agent folder. Each time you press "Schedule" in the backup dialog, a SQL
Server Agent job is created.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> Thanks.
> However, I have setup the backup device for the Northwind database for
> example in the Management|Backup subfolder, and schedule the full and
> differential backup for that, it can backup according to the schedule,
> that's fine, BUT I cannot review the schedule of the backup that I created.
> I had schedule the full backup at every Sunday and differential backup every
> weekday at 5 pm, how do I review the schedule of these backup ?
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> full
>
|||Yes, I got it, thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ueqsT1OWEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Look in the SQL Server agent folder. Each time you press "Schedule" in the
backup dialog, a SQL[vbcol=seagreen]
> Server Agent job is created.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
created.[vbcol=seagreen]
every[vbcol=seagreen]
simply[vbcol=seagreen]
up[vbcol=seagreen]
only[vbcol=seagreen]
Management[vbcol=seagreen]
the[vbcol=seagreen]
the
>

Database backup and maintenance plan

In EM, what are the diffeneces between these two under the Management folder
?
I can schedule the database backup in Backup and also can schedule the
database schedule in Database Maintenance Plans ?
I set up the full backup and schedule in 'BackUp', it can create the full
backup file in the directory but cannot view contents by right click the
item on the right pane of 'Backup' folder.Different way of scheduling, quite simply. To understand why you cannot see
backups created by maint
plan, read about sp_addumpdevice. Main plan doesn't create backup devices, i
t addresses files
directly (see the BACKUP command and DISK = 'filename' option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management fold
er
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Another difference between the two is that the backup dialog box simply
allows you do create/schedule a backup..
The database maintenance plan wizard is intended to allow you to set up
several jobs which should cover most of your maintenance needs... not only
backups, but index maintenance, and dbcc checkdb...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management
folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Thanks.
However, I have setup the backup device for the Northwind database for
example in the Management|Backup subfolder, and schedule the full and
differential backup for that, it can backup according to the schedule,
that's fine, BUT I cannot review the schedule of the backup that I created.
I had schedule the full backup at every Sunday and differential backup every
weekday at 5 pm, how do I review the schedule of these backup ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Another difference between the two is that the backup dialog box simply
> allows you do create/schedule a backup..
> The database maintenance plan wizard is intended to allow you to set up
> several jobs which should cover most of your maintenance needs... not only
> backups, but index maintenance, and dbcc checkdb...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.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
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> folder
full[vbcol=seagreen]
>|||Look in the SQL Server agent folder. Each time you press "Schedule" in the b
ackup dialog, a SQL
Server Agent job is created.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> Thanks.
> However, I have setup the backup device for the Northwind database for
> example in the Management|Backup subfolder, and schedule the full and
> differential backup for that, it can backup according to the schedule,
> that's fine, BUT I cannot review the schedule of the backup that I created
.
> I had schedule the full backup at every Sunday and differential backup eve
ry
> weekday at 5 pm, how do I review the schedule of these backup ?
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> full
>|||Yes, I got it, thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ueqsT1OWEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Look in the SQL Server agent folder. Each time you press "Schedule" in the
backup dialog, a SQL
> Server Agent job is created.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
created.[vbcol=seagreen]
every[vbcol=seagreen]
simply[vbcol=seagreen]
up[vbcol=seagreen]
only[vbcol=seagreen]
Management[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
>

Database backup and maintenance plan

In EM, what are the diffeneces between these two under the Management folder
?
I can schedule the database backup in Backup and also can schedule the
database schedule in Database Maintenance Plans ?
I set up the full backup and schedule in 'BackUp', it can create the full
backup file in the directory but cannot view contents by right click the
item on the right pane of 'Backup' folder.Different way of scheduling, quite simply. To understand why you cannot see backups created by maint
plan, read about sp_addumpdevice. Main plan doesn't create backup devices, it addresses files
directly (see the BACKUP command and DISK = 'filename' option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Another difference between the two is that the backup dialog box simply
allows you do create/schedule a backup..
The database maintenance plan wizard is intended to allow you to set up
several jobs which should cover most of your maintenance needs... not only
backups, but index maintenance, and dbcc checkdb...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> In EM, what are the diffeneces between these two under the Management
folder
> ?
> I can schedule the database backup in Backup and also can schedule the
> database schedule in Database Maintenance Plans ?
> I set up the full backup and schedule in 'BackUp', it can create the full
> backup file in the directory but cannot view contents by right click the
> item on the right pane of 'Backup' folder.
>|||Thanks.
However, I have setup the backup device for the Northwind database for
example in the Management|Backup subfolder, and schedule the full and
differential backup for that, it can backup according to the schedule,
that's fine, BUT I cannot review the schedule of the backup that I created.
I had schedule the full backup at every Sunday and differential backup every
weekday at 5 pm, how do I review the schedule of these backup ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Another difference between the two is that the backup dialog box simply
> allows you do create/schedule a backup..
> The database maintenance plan wizard is intended to allow you to set up
> several jobs which should cover most of your maintenance needs... not only
> backups, but index maintenance, and dbcc checkdb...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.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
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > In EM, what are the diffeneces between these two under the Management
> folder
> > ?
> >
> > I can schedule the database backup in Backup and also can schedule the
> > database schedule in Database Maintenance Plans ?
> >
> > I set up the full backup and schedule in 'BackUp', it can create the
full
> > backup file in the directory but cannot view contents by right click the
> > item on the right pane of 'Backup' folder.
> >
> >
>|||Look in the SQL Server agent folder. Each time you press "Schedule" in the backup dialog, a SQL
Server Agent job is created.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> Thanks.
> However, I have setup the backup device for the Northwind database for
> example in the Management|Backup subfolder, and schedule the full and
> differential backup for that, it can backup according to the schedule,
> that's fine, BUT I cannot review the schedule of the backup that I created.
> I had schedule the full backup at every Sunday and differential backup every
> weekday at 5 pm, how do I review the schedule of these backup ?
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > Another difference between the two is that the backup dialog box simply
> > allows you do create/schedule a backup..
> >
> > The database maintenance plan wizard is intended to allow you to set up
> > several jobs which should cover most of your maintenance needs... not only
> > backups, but index maintenance, and dbcc checkdb...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.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
> >
> > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > > In EM, what are the diffeneces between these two under the Management
> > folder
> > > ?
> > >
> > > I can schedule the database backup in Backup and also can schedule the
> > > database schedule in Database Maintenance Plans ?
> > >
> > > I set up the full backup and schedule in 'BackUp', it can create the
> full
> > > backup file in the directory but cannot view contents by right click the
> > > item on the right pane of 'Backup' folder.
> > >
> > >
> >
> >
>|||Yes, I got it, thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ueqsT1OWEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Look in the SQL Server agent folder. Each time you press "Schedule" in the
backup dialog, a SQL
> Server Agent job is created.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:eaL4RSLWEHA.2928@.tk2msftngp13.phx.gbl...
> > Thanks.
> > However, I have setup the backup device for the Northwind database for
> > example in the Management|Backup subfolder, and schedule the full and
> > differential backup for that, it can backup according to the schedule,
> > that's fine, BUT I cannot review the schedule of the backup that I
created.
> >
> > I had schedule the full backup at every Sunday and differential backup
every
> > weekday at 5 pm, how do I review the schedule of these backup ?
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:uXawQ%23EWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > > Another difference between the two is that the backup dialog box
simply
> > > allows you do create/schedule a backup..
> > >
> > > The database maintenance plan wizard is intended to allow you to set
up
> > > several jobs which should cover most of your maintenance needs... not
only
> > > backups, but index maintenance, and dbcc checkdb...
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.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
> > >
> > > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > > news:%232W0oQCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > > > In EM, what are the diffeneces between these two under the
Management
> > > folder
> > > > ?
> > > >
> > > > I can schedule the database backup in Backup and also can schedule
the
> > > > database schedule in Database Maintenance Plans ?
> > > >
> > > > I set up the full backup and schedule in 'BackUp', it can create the
> > full
> > > > backup file in the directory but cannot view contents by right click
the
> > > > item on the right pane of 'Backup' folder.
> > > >
> > > >
> > >
> > >
> >
> >
>

Database Backup and Backup in Database Maintenance

We are running SQL Enterprise Edition 2000 under Windows 2003 Standard
Edition server. Under Enterprise Manager, is there a difference between
the backup job setup under Databases, All Tasks, Backup Database and
Mangement, Database Management Plans. For some reasons, I am not able to
restore the backup file setup under Management, Data Management Plans.
Thanks.Hi Diane
I assume you mean the database maintenance plan rather than management plan?
If you set up a job using the backup wizard you will create a job that only
backs up the database. If you set up a maintenance plan it will set up one
or more jobs that can backup the database as well as re-index and do other
maintenance tasks on one or more databases.
If you wish to restore a database taken as part of a maintainance job, you
can us the restore wizard or the Restore T-SQL command in Query Analyser.
You may want to read "Designing a Backup and Restore Strategy" in Books
online regarding what you should have in place.
John
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:euTxi%23cYGHA.4760@.TK2MSFTNGP03.phx.gbl...
> We are running SQL Enterprise Edition 2000 under Windows 2003 Standard
> Edition server. Under Enterprise Manager, is there a difference between
> the backup job setup under Databases, All Tasks, Backup Database and
> Mangement, Database Management Plans. For some reasons, I am not able to
> restore the backup file setup under Management, Data Management Plans.
> Thanks.
>

Database Backup and Backup in Database Maintenance

We are running SQL Enterprise Edition 2000 under Windows 2003 Standard
Edition server. Under Enterprise Manager, is there a difference between
the backup job setup under Databases, All Tasks, Backup Database and
Mangement, Database Management Plans. For some reasons, I am not able to
restore the backup file setup under Management, Data Management Plans.
Thanks.Hi Diane
I assume you mean the database maintenance plan rather than management plan?
If you set up a job using the backup wizard you will create a job that only
backs up the database. If you set up a maintenance plan it will set up one
or more jobs that can backup the database as well as re-index and do other
maintenance tasks on one or more databases.
If you wish to restore a database taken as part of a maintainance job, you
can us the restore wizard or the Restore T-SQL command in Query Analyser.
You may want to read "Designing a Backup and Restore Strategy" in Books
online regarding what you should have in place.
John
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:euTxi%23cYGHA.4760@.TK2MSFTNGP03.phx.gbl...
> We are running SQL Enterprise Edition 2000 under Windows 2003 Standard
> Edition server. Under Enterprise Manager, is there a difference between
> the backup job setup under Databases, All Tasks, Backup Database and
> Mangement, Database Management Plans. For some reasons, I am not able to
> restore the backup file setup under Management, Data Management Plans.
> Thanks.
>sql

DataBase Backup

SQL Server 2000
We have a db maintenance job which backup all the db's in a Production
Server to a remote server.
Production Server - Windows 2000 server
Remote Box - Windows 2003 Standard Edition
We recently applied SP1 to Windows 2003 box (Remote Box), after that Db
maintenance job with error message 'Operating system error 64 (The specified
network is no longer available..)
Certain days it will backup all databases but with this error message.
Any thoughts/suggestions/ideas
Thanks
MikeMS User wrote:
> SQL Server 2000
> We have a db maintenance job which backup all the db's in a Production
> Server to a remote server.
> Production Server - Windows 2000 server
> Remote Box - Windows 2003 Standard Edition
> We recently applied SP1 to Windows 2003 box (Remote Box), after that Db
> maintenance job with error message 'Operating system error 64 (The specifi
ed
> network is no longer available..)
> Certain days it will backup all databases but with this error message.
> Any thoughts/suggestions/ideas
> Thanks
> Mike
>
I'm not sure this is a SQL problem, sounds more like a network or OS
problem on the remote side. You're losing the connection to the remote
box during the file copy (the backup) that SQL is doing. Search Google
for "The specified network is no longer available", lots of hits.

Database backup

In EM, I can only configure Database Maintenance Plan by full backup and
transaction log.
How do I set differential/incremental backup ?
Differential database backups are not implemented in Database Maintenance
Plan. You will have to create your own jobs using the BACKUP and RESTORE
commands. More information about these commands and lots of examples are
available in SQL Server Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:umvDl9BWEHA.1380@.TK2MSFTNGP12.phx.gbl...
In EM, I can only configure Database Maintenance Plan by full backup and
transaction log.
How do I set differential/incremental backup ?
|||Hi,
I hope that you can only do FULL and Transaction Log backups by the
maintenance plan.
But you can generate a SQL job to do this. Just right click on the database
in the EM, Select the database, All tasks, backup databse, choose
differential backup, and then choose "Schedule" where you can
make it as a recurring schedule.
Thanks
Hari
MCDBA
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:umvDl9BWEHA.1380@.TK2MSFTNGP12.phx.gbl...
> In EM, I can only configure Database Maintenance Plan by full backup and
> transaction log.
> How do I set differential/incremental backup ?
>

Database backup

In EM, I can only configure Database Maintenance Plan by full backup and
transaction log.
How do I set differential/incremental backup ?Differential database backups are not implemented in Database Maintenance
Plan. You will have to create your own jobs using the BACKUP and RESTORE
commands. More information about these commands and lots of examples are
available in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:umvDl9BWEHA.1380@.TK2MSFTNGP12.phx.gbl...
In EM, I can only configure Database Maintenance Plan by full backup and
transaction log.
How do I set differential/incremental backup ?|||Hi,
I hope that you can only do FULL and Transaction Log backups by the
maintenance plan.
But you can generate a SQL job to do this. Just right click on the database
in the EM, Select the database, All tasks, backup databse, choose
differential backup, and then choose "Schedule" where you can
make it as a recurring schedule.
Thanks
Hari
MCDBA
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:umvDl9BWEHA.1380@.TK2MSFTNGP12.phx.gbl...
> In EM, I can only configure Database Maintenance Plan by full backup and
> transaction log.
> How do I set differential/incremental backup ?
>