Autonumber field: backed

Showing posts with label backed. Show all posts

Tuesday, March 27, 2012

Backing up SQL databases

I am backing up three databases being used for a
inventory program. I have scheduled these databases to
be backed up at night using enterprise manager. If I
needed to restore any of these databases would I have to
restore all three? I do not back up transaction logs
because they do not need to restore to a specific point
(they could restore back to the previous night when the
databases were being backed up). Also do I have to
backup the other default databases that came with the SQl
2000 server in order to restore the three databases
specific to the inventory program?> If I
> needed to restore any of these databases would I have to
> restore all three?
Technically, each database can be restored independently. However, your
inventory application might need the data in all three databases to be in
sync. It is probably safest to restore all three unless you know enough
about the app to be certain restoring a single database isn't a problem.
> Also do I have to
> backup the other default databases that came with the SQl
> 2000 server in order to restore the three databases
> specific to the inventory program?
You should backup the master and msdb system databases as part of your
backup plan. These system databases only need to be restored if they are
lost or corrupted. You don't need to restore them before your inventory
databases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
> I am backing up three databases being used for a
> inventory program. I have scheduled these databases to
> be backed up at night using enterprise manager. If I
> needed to restore any of these databases would I have to
> restore all three? I do not back up transaction logs
> because they do not need to restore to a specific point
> (they could restore back to the previous night when the
> databases were being backed up). Also do I have to
> backup the other default databases that came with the SQl
> 2000 server in order to restore the three databases
> specific to the inventory program?|||OK thanks. Most of that makes sense to me. However the
last part you said referring to the default
databases, "you don't need to restore them before the
inventory databases." Do you mean if I had to restore
the inventory databases I would not have to restore these
default databases? Also if these default databases
became corrupt like you said would they not be able to be
replaced without having a backup of them. I mean if they
are default databases do they ever change? Are they
(default databases) unique to my particular SQL server
installation and are needed inclusively to run my
inventory app?
Lastly if I backup these three databases at night at
separate times wihtin a couple of hours of each when no
one is accessing the inventory program at all would they
still be in sync if restored all at once. If not could I
schedule all three databases to be backed up at the same
time to the server hard drive(I later back the database
backup files to tape).
>--Original Message--
>> needed to restore any of these databases would I have
to
>> restore all three?
>Technically, each database can be restored
independently. However, your
>inventory application might need the data in all three
databases to be in
>sync. It is probably safest to restore all three unless
you know enough
>about the app to be certain restoring a single database
isn't a problem.
>> Also do I have to
>> backup the other default databases that came with the
SQl
>> 2000 server in order to restore the three databases
>> specific to the inventory program?
>You should backup the master and msdb system databases
as part of your
>backup plan. These system databases only need to be
restored if they are
>lost or corrupted. You don't need to restore them
before your inventory
>databases.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
>> I am backing up three databases being used for a
>> inventory program. I have scheduled these databases to
>> be backed up at night using enterprise manager. If I
>> needed to restore any of these databases would I have
to
>> restore all three? I do not back up transaction logs
>> because they do not need to restore to a specific point
>> (they could restore back to the previous night when the
>> databases were being backed up). Also do I have to
>> backup the other default databases that came with the
SQl
>> 2000 server in order to restore the three databases
>> specific to the inventory program?
>
>.
>|||Hi,
Answers to your queries:-
1. Do you mean if I had to restore the inventory databases I would not
have to restore these
default databases?
No need to restore the default (system) databases while restoring Inventory
databases. System database keeps tract of database, logins, backups , jobs
... information. But you need to backup and keep these datases in a safe
location, which will be helpful during a crash or corruption.
2. I mean if they are default databases do they ever change?
Yes, they will change. Especially MASTER and MSDB database.
Master database will change while you add a new database, add a new login,
change parameters ...
MSDB database will change when you add a new Job (task), operator, backup,
restore ....
SO it is necessory to restore these backup during problem sitation.
3. Are they (default databases) unique to my particular SQL server
installation and are needed inclusively to run my inventory app?
Uniqueness will be there. Because those databases store the Server name,
logins, system informations...
4. If not could I schedule all three databases to be backed up at the same
time to the server hard drive?
You could backup these databases simulateneosly or one by one. One by one
is also fine, Because changes to these database will happen only during the
above mentioned time (answer to my second question)
Thanks
Hari
MCDBA
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
> OK thanks. Most of that makes sense to me. However the
> last part you said referring to the default
> databases, "you don't need to restore them before the
> inventory databases." Do you mean if I had to restore
> the inventory databases I would not have to restore these
> default databases? Also if these default databases
> became corrupt like you said would they not be able to be
> replaced without having a backup of them. I mean if they
> are default databases do they ever change? Are they
> (default databases) unique to my particular SQL server
> installation and are needed inclusively to run my
> inventory app?
> Lastly if I backup these three databases at night at
> separate times wihtin a couple of hours of each when no
> one is accessing the inventory program at all would they
> still be in sync if restored all at once. If not could I
> schedule all three databases to be backed up at the same
> time to the server hard drive(I later back the database
> backup files to tape).
> >--Original Message--
> >> needed to restore any of these databases would I have
> to
> >> restore all three?
> >
> >Technically, each database can be restored
> independently. However, your
> >inventory application might need the data in all three
> databases to be in
> >sync. It is probably safest to restore all three unless
> you know enough
> >about the app to be certain restoring a single database
> isn't a problem.
> >
> >> Also do I have to
> >> backup the other default databases that came with the
> SQl
> >> 2000 server in order to restore the three databases
> >> specific to the inventory program?
> >
> >You should backup the master and msdb system databases
> as part of your
> >backup plan. These system databases only need to be
> restored if they are
> >lost or corrupted. You don't need to restore them
> before your inventory
> >databases.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Andy" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
> >> I am backing up three databases being used for a
> >> inventory program. I have scheduled these databases to
> >> be backed up at night using enterprise manager. If I
> >> needed to restore any of these databases would I have
> to
> >> restore all three? I do not back up transaction logs
> >> because they do not need to restore to a specific point
> >> (they could restore back to the previous night when the
> >> databases were being backed up). Also do I have to
> >> backup the other default databases that came with the
> SQl
> >> 2000 server in order to restore the three databases
> >> specific to the inventory program?
> >
> >
> >.
> >|||>> Lastly if I backup these three databases at night at
>> separate times wihtin a couple of hours of each when no
>> one is accessing the inventory program at all would they
>>still be in sync if restored all at once.
If there is no activity at night, you should not have a consistency problem.
However, if there's activity at night, and if a user adds/modifies data to
database A and B, but not to C, you might hit a consistency issue since the
backups of each database will end at different times. In this case, you
will need to backup the transaction logs and restore to a point-in-time for
all 3 databases to guarantee consistency (or at least close to it, since
there's still a chance a trx is committed on database A, but not yet on B
and C at that time).
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
> OK thanks. Most of that makes sense to me. However the
> last part you said referring to the default
> databases, "you don't need to restore them before the
> inventory databases." Do you mean if I had to restore
> the inventory databases I would not have to restore these
> default databases? Also if these default databases
> became corrupt like you said would they not be able to be
> replaced without having a backup of them. I mean if they
> are default databases do they ever change? Are they
> (default databases) unique to my particular SQL server
> installation and are needed inclusively to run my
> inventory app?
> Lastly if I backup these three databases at night at
> separate times wihtin a couple of hours of each when no
> one is accessing the inventory program at all would they
> still be in sync if restored all at once. If not could I
> schedule all three databases to be backed up at the same
> time to the server hard drive(I later back the database
> backup files to tape).
>|||Can I also schedule backup of nondefault databases(our
inventory program) simultaneously?
>--Original Message--
>Hi,
>Answers to your queries:-
>1. Do you mean if I had to restore the inventory
databases I would not
>have to restore these
>default databases?
>No need to restore the default (system) databases while
restoring Inventory
>databases. System database keeps tract of database,
logins, backups , jobs
>... information. But you need to backup and keep these
datases in a safe
>location, which will be helpful during a crash or
corruption.
>2. I mean if they are default databases do they ever
change?
>Yes, they will change. Especially MASTER and MSDB
database.
>Master database will change while you add a new database,
add a new login,
>change parameters ...
>MSDB database will change when you add a new Job (task),
operator, backup,
>restore ....
>SO it is necessory to restore these backup during problem
sitation.
>3. Are they (default databases) unique to my particular
SQL server
>installation and are needed inclusively to run my
inventory app?
> Uniqueness will be there. Because those databases
store the Server name,
>logins, system informations...
>
>4. If not could I schedule all three databases to be
backed up at the same
>time to the server hard drive?
> You could backup these databases simulateneosly or one
by one. One by one
>is also fine, Because changes to these database will
happen only during the
> above mentioned time (answer to my second question)
>Thanks
>Hari
>MCDBA
>
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
>> OK thanks. Most of that makes sense to me. However the
>> last part you said referring to the default
>> databases, "you don't need to restore them before the
>> inventory databases." Do you mean if I had to restore
>> the inventory databases I would not have to restore
these
>> default databases? Also if these default databases
>> became corrupt like you said would they not be able to
be
>> replaced without having a backup of them. I mean if
they
>> are default databases do they ever change? Are they
>> (default databases) unique to my particular SQL server
>> installation and are needed inclusively to run my
>> inventory app?
>> Lastly if I backup these three databases at night at
>> separate times wihtin a couple of hours of each when no
>> one is accessing the inventory program at all would they
>> still be in sync if restored all at once. If not could
I
>> schedule all three databases to be backed up at the same
>> time to the server hard drive(I later back the database
>> backup files to tape).
>> >--Original Message--
>> >> needed to restore any of these databases would I have
>> to
>> >> restore all three?
>> >
>> >Technically, each database can be restored
>> independently. However, your
>> >inventory application might need the data in all three
>> databases to be in
>> >sync. It is probably safest to restore all three
unless
>> you know enough
>> >about the app to be certain restoring a single database
>> isn't a problem.
>> >
>> >> Also do I have to
>> >> backup the other default databases that came with the
>> SQl
>> >> 2000 server in order to restore the three databases
>> >> specific to the inventory program?
>> >
>> >You should backup the master and msdb system databases
>> as part of your
>> >backup plan. These system databases only need to be
>> restored if they are
>> >lost or corrupted. You don't need to restore them
>> before your inventory
>> >databases.
>> >
>> >--
>> >Hope this helps.
>> >
>> >Dan Guzman
>> >SQL Server MVP
>> >
>> >"Andy" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
>> >> I am backing up three databases being used for a
>> >> inventory program. I have scheduled these databases
to
>> >> be backed up at night using enterprise manager. If I
>> >> needed to restore any of these databases would I have
>> to
>> >> restore all three? I do not back up transaction logs
>> >> because they do not need to restore to a specific
point
>> >> (they could restore back to the previous night when
the
>> >> databases were being backed up). Also do I have to
>> >> backup the other default databases that came with the
>> SQl
>> >> 2000 server in order to restore the three databases
>> >> specific to the inventory program?
>> >
>> >
>> >.
>> >
>
>.
>sql

Backing up SQL databases

backing up reporting services

A few questions regarding Reporting Services

what needs to be backed up for reporting services? We are currently backing up the ReportServer and ReportServerTempDB.

Where would the RDL files be saved on our reportserver?

Finally, is there anything else that should be backed up for disaster recovery purposes?

You don't need to backup ReportServerTempDB for disaster recover. All of the data in it is transient.

You should also backup the key which is used for encrypting data in the ReportServer database.

See this article for a pretty good rundown of all of the pieces that you will want to backup:

http://technet.microsoft.com/en-us/library/ms155814(SQL.90).aspx

|||

>>Where would the RDL files be saved on our reportserver?

I'm pretty sure that the RDL files are stored in binary form (in the Content column of the Catalog table?) in the database, not positive.

>>is there anything else that should be backed up for disaster recovery purposes?

I would also back up the various *.config files from the ReportServer/Manager installation, plus any custom DLLs you may have added to a vanilla installation.

>L<

|||The RDL is stored in the catalog. You can retrieve it by using the GetReportDefinition() SOAP API call.

Monday, March 19, 2012

Back up system DBs

I noticed the transaction logs for master and other system dbs can't be backed up. What are some of the practices of backing up System DBs as far as method, frequency and what type of situation would there be a need to restore from them?
Thanks
You cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks
|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks
|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks

Back up system DBs

I noticed the transaction logs for master and other system dbs can't be back
ed up. What are some of the practices of backing up System DBs as far as me
thod, frequency and what type of situation would there be a need to restore
from them?
ThanksYou cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log
backup (because of
backup history is stored there). As Agent set msdb to simple recovery at sta
rtup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up.
What are some
of the practices of backing up System DBs as far as method, frequency and wh
at type of situation
would there be a need to restore from them?
> Thanks

Back up system DBs

I noticed the transaction logs for master and other system dbs can't be backed up. What are some of the practices of backing up System DBs as far as method, frequency and what type of situation would there be a need to restore from them?
ThanksYou cannot backup logs for the syetm dbs because they are in simple =recovery model.
Daily full backup should easily suffice unless you are in the habit of =creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =be backed up. What are some of the practices of backing up System DBs =as far as method, frequency and what type of situation would there be a =need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks

Back Up question

I currently have my database backed up to the same physical drive where the data resides. In the event of drive failure, I would lose everything. Due to space considerations on a tape, I was wondering if I can copy only the database backup file (.BAK) and not the transaction log backup (.TRN). Can I go ahead with this approach if I do not want point-in-time recovery and I am satisfied with only what resides on .BAK file? Will I able to restore the database from the tape if the tape contains only the (.BAK) file ?

Thanks.Sure...

How often do you dump the transaction log?

But I don't think it's advisable...

How big a database are we talking about...

Ever consider compression?|||I often dump my backups across the network to a share on another server. It won't work with very large databases very well, but I've had pretty good luck so far. I find that writing backups to tape can be extremely slow.

As for not keeping the transaction logs, I have to agree with Brett; I would prefer to keep mine.

Regards,

hmscott|||Do you dump across a network, or locally the copy?

I've always thought a dump over a network could corrupt the dump

Never seen it...just always believed it could happen...|||I had all kinds of problems with it in 6.5 and 7.0. I've never seen any problems with it in 2000 though. We do it all the time. All of our servers are dumped to a central repository. We even do restores from there, including log shipping restores. I've never had any corruption doing it this way. Of course, we do have a dual 16gb core with fiber to all servers. We're upgrading to dual 64gb. A lot has changed over the years I guess.

I would definitely consider compression if I were that strapped on space and bandwidth. Many times SQL Backup files will shrink to 70-80 percent of the original size.

back everything up in a single file?

Greetings,

Our former SQL Server 2000 DBA backed up everything in a single disk
file. By everything I mean, full backup, differential backup and
transaction logs. See below for details of how the backup is currently
set up. When I did 'view contents' of DBBackup, I saw it contained the
mixture of log, full and diff backups of verious dates. She has quit.
Other coworkers said (in a not-sure tone) she was able to restore the
databases from such a single file, although nobody ever saw or knew how
she did recovery. My knowledge about SQL Server, especially regarding
its backup/restore is limited. But I've ever worked with other
databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
backups and differential backups should be completely separated. Also,
in each type of backups, each database should have its own backup file.
Please advise.

=====
Log backup:

CREATE PROCEDURE sp_lbackup AS
Backup log AGEP To DBBackup
Backup log careerfairs To DBBackup
Backup log CoEdocuments To DBBackup
Backup log committee To DBBackup
Backup log conference To DBBackup
GO

Full backup:

CREATE PROCEDURE dbo.sp_fullbackup AS
Backup database AGEP To DBBackup WITH INIT
Backup database CoEdocuments To DBBackup
Backup database careerfairs To DBBackup
Backup database committee To DBBackup
Backup database conference To DBBackup
GO

Diff backup:

CREATE PROCEDURE sp_diffbackup AS
Backup database AGEP To DBBackup with differential
Backup database careerfairs To DBBackup with differential
Backup database CoEdocuments To DBBackup with differential
Backup database committee To DBBackup with differential
Backup database conference To DBBackup with differential
GO
=====

Thanks in advance for any help,

Bing"Bing Du" <bdu@.iastate.edu> wrote in message
news:c1icnk$v0b$1@.news.iastate.edu...
> Greetings,
> Our former SQL Server 2000 DBA backed up everything in a single disk
> file. By everything I mean, full backup, differential backup and
> transaction logs. See below for details of how the backup is currently
> set up. When I did 'view contents' of DBBackup, I saw it contained the
> mixture of log, full and diff backups of verious dates. She has quit.
> Other coworkers said (in a not-sure tone) she was able to restore the
> databases from such a single file, although nobody ever saw or knew how
> she did recovery. My knowledge about SQL Server, especially regarding
> its backup/restore is limited. But I've ever worked with other
> databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
> backups and differential backups should be completely separated. Also,
> in each type of backups, each database should have its own backup file.
> Please advise.
> =====
> Log backup:
> CREATE PROCEDURE sp_lbackup AS
> Backup log AGEP To DBBackup
> Backup log careerfairs To DBBackup
> Backup log CoEdocuments To DBBackup
> Backup log committee To DBBackup
> Backup log conference To DBBackup
> GO
> Full backup:
> CREATE PROCEDURE dbo.sp_fullbackup AS
> Backup database AGEP To DBBackup WITH INIT
> Backup database CoEdocuments To DBBackup
> Backup database careerfairs To DBBackup
> Backup database committee To DBBackup
> Backup database conference To DBBackup
> GO
> Diff backup:
> CREATE PROCEDURE sp_diffbackup AS
> Backup database AGEP To DBBackup with differential
> Backup database careerfairs To DBBackup with differential
> Backup database CoEdocuments To DBBackup with differential
> Backup database committee To DBBackup with differential
> Backup database conference To DBBackup with differential
> GO
> =====
> Thanks in advance for any help,
> Bing

In the code above, DBBackup is not a file, it's a backup device, so in
theory it's possible that your DBA used to point the device at different
physical files to provide some sort of rotation, although from your
description that sounds unlikely. In fact, personally, I can't see how it
would be possible to manage backups effectively using her approach.

In any case, if you need a quick solution, I would suggest creating one or
more database maintenance plans, and back up all your databases to a
convenient disk location. After that, use your standard backup software or
methods to copy the backup files to tape and/or another physical server. The
maintenance plan wizard (Enterprise Manager, Tools menu) should be easy
enough to use that you can set this up fairly quickly. It will back up
directly to files, and can also remove old backups after a period you
specify.

I'm not saying that that is the best or only approach, and some
functionality (eg differential backups) isn't available from the wizard, but
as a way of quickly putting something manageable in place, it should be
fine.

After that, find a new DBA and/or start reading the Books Online information
on "Recovery Models" and "Backup and Restore Operations".

Simon|||Thanks much for your response, Simon. I'll check out the online
information you mentioned. I understand DBBackup is not a file. It's a
logic device that can be changed to point to different physical devices.
But at the same time, each backup device can only point to one physical
device, either disk file or tape, right? Our SQL Server Enterprise
Manager->Management->Backup shows:

==========
Name Physical Location Device Type

DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
===========

So, I don't think this DBBackup.BAK which contains full, differential
and log backups would work in restore. Please correct me if I'm wrong.

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"comp.databases.ms-sqlserver" <anonymous@.devdex.com> wrote in message
news:403cfe5e$0$197$75868355@.news.frii.net...
> Thanks much for your response, Simon. I'll check out the online
> information you mentioned. I understand DBBackup is not a file. It's a
> logic device that can be changed to point to different physical devices.
> But at the same time, each backup device can only point to one physical
> device, either disk file or tape, right? Our SQL Server Enterprise
> Manager->Management->Backup shows:
> ==========
> Name Physical Location Device Type
> DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
> ===========
> So, I don't think this DBBackup.BAK which contains full, differential
> and log backups would work in restore. Please correct me if I'm wrong.
> Bing
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

A single file or device can contain multiple backups, and you can restore
any individual backup from it, as long as it makes sense in the context of
what you're restoring (you can't restore database A logs to database B, for
example, or restore logs out of order). It doesn't matter if you mix backup
types and databases in one file, except from a management perspective.

You can select the individual backup to restore in the Enterprise Manage
restore dialogue using the "First backup to restore" drop down. This may be
a clearer way to demonstrate what you can do (on a test server only, of
course), assuming you have sysadmin permissions:

/* Create a test DB */
create database foo
go

/* Back up the DB twice, and the log once, to the same file,
** with an msdb backup in the middle to show that we can
** mix backups.
*/
backup database foo to disk = 'c:\foo.bak'
go
backup database foo to disk = 'c:\foo.bak'
go
backup database msdb to disk = 'c:\foo.bak'
go
backup log foo to disk = 'c:\foo.bak'
go

/" View the backups available in the file */
restore headeronly from disk = 'c:\foo.bak'
go

/* Restore the 1st backup and make the DB available */
restore database foo from disk = 'c:\foo.bak' with file = 1, recovery
go
/* Restore the 2nd backup, but do not recover (so we can apply the log) */
restore database foo from disk = 'c:\foo.bak' with file = 2, norecovery
go
/* Restore the log and make the DB available */
restore log foo from disk = 'c:\foo.bak' with file = 4, recovery
go

/* Clean up */
drop database foo
go
exec master..xp_cmdshell 'del c:\foo.bak', no_output
go

Simon|||Great. Thanks a lot again for the lucid explanations with examples!
Very helpful. Seems one file is not a big problem. I need to read
more to work out a best solution for our situation.

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I have two MS SQL 2000 servers, s1 and s2. What I want to do is to test
if the backup made on s1 can be restored on s2. Briefly, what hit me
was the backup file made on s1 could not be viewed on s2.

On server s1, back up (both full and differential) all the databases and
logs into a backup device which points to the disk file
e:\tmp\DBBackup.BAK. I have no problem doing 'view contents' of
DBBackup.BAK on s1.

Then I changed e:\tmp on s1 to a shared directory. So, on s2, I can see
DBBackup.BAK as a file in My Documents fine. But in the Enterprise
Manager on s2, when I did 'view contents' of DBBackup.BAK, I got the
error 'This device does not contain any backup sets'.

How so?

Bing

Bing Du wrote:

> Greetings,
> Our former SQL Server 2000 DBA backed up everything in a single disk
> file. By everything I mean, full backup, differential backup and
> transaction logs. See below for details of how the backup is currently
> set up. When I did 'view contents' of DBBackup, I saw it contained the
> mixture of log, full and diff backups of verious dates. She has quit.
> Other coworkers said (in a not-sure tone) she was able to restore the
> databases from such a single file, although nobody ever saw or knew how
> she did recovery. My knowledge about SQL Server, especially regarding
> its backup/restore is limited. But I've ever worked with other
> databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
> backups and differential backups should be completely separated. Also,
> in each type of backups, each database should have its own backup file.
> Please advise.
> =====
> Log backup:
> CREATE PROCEDURE sp_lbackup AS
> Backup log AGEP To DBBackup
> Backup log careerfairs To DBBackup
> Backup log CoEdocuments To DBBackup
> Backup log committee To DBBackup
> Backup log conference To DBBackup
> GO
> Full backup:
> CREATE PROCEDURE dbo.sp_fullbackup AS
> Backup database AGEP To DBBackup WITH INIT
> Backup database CoEdocuments To DBBackup
> Backup database careerfairs To DBBackup
> Backup database committee To DBBackup
> Backup database conference To DBBackup
> GO
> Diff backup:
> CREATE PROCEDURE sp_diffbackup AS
> Backup database AGEP To DBBackup with differential
> Backup database careerfairs To DBBackup with differential
> Backup database CoEdocuments To DBBackup with differential
> Backup database committee To DBBackup with differential
> Backup database conference To DBBackup with differential
> GO
> =====
> Thanks in advance for any help,
> Bing|||Ok, seems like only local disks show up when a new backup device is
defined. After I copied the backup from the network shared directory to
a local directory on s2, restore worked.

Bing

Bing Du wrote:

> I have two MS SQL 2000 servers, s1 and s2. What I want to do is to test
> if the backup made on s1 can be restored on s2. Briefly, what hit me
> was the backup file made on s1 could not be viewed on s2.
> On server s1, back up (both full and differential) all the databases and
> logs into a backup device which points to the disk file
> e:\tmp\DBBackup.BAK. I have no problem doing 'view contents' of
> DBBackup.BAK on s1.
> Then I changed e:\tmp on s1 to a shared directory. So, on s2, I can see
> DBBackup.BAK as a file in My Documents fine. But in the Enterprise
> Manager on s2, when I did 'view contents' of DBBackup.BAK, I got the
> error 'This device does not contain any backup sets'.
> How so?
> Bing
> Bing Du wrote:
>> Greetings,
>>
>> Our former SQL Server 2000 DBA backed up everything in a single disk
>> file. By everything I mean, full backup, differential backup and
>> transaction logs. See below for details of how the backup is
>> currently set up. When I did 'view contents' of DBBackup, I saw it
>> contained the mixture of log, full and diff backups of verious dates.
>> She has quit. Other coworkers said (in a not-sure tone) she was able
>> to restore the databases from such a single file, although nobody ever
>> saw or knew how she did recovery. My knowledge about SQL Server,
>> especially regarding its backup/restore is limited. But I've ever
>> worked with other databases, e.g. Oracle, MySQL and Postgres. I think
>> log backups, full backups and differential backups should be
>> completely separated. Also, in each type of backups, each database
>> should have its own backup file. Please advise.
>>
>> =====
>> Log backup:
>>
>> CREATE PROCEDURE sp_lbackup AS
>> Backup log AGEP To DBBackup
>> Backup log careerfairs To DBBackup
>> Backup log CoEdocuments To DBBackup
>> Backup log committee To DBBackup
>> Backup log conference To DBBackup
>> GO
>>
>> Full backup:
>>
>> CREATE PROCEDURE dbo.sp_fullbackup AS
>> Backup database AGEP To DBBackup WITH INIT
>> Backup database CoEdocuments To DBBackup
>> Backup database careerfairs To DBBackup
>> Backup database committee To DBBackup
>> Backup database conference To DBBackup
>> GO
>>
>> Diff backup:
>>
>> CREATE PROCEDURE sp_diffbackup AS
>> Backup database AGEP To DBBackup with differential
>> Backup database careerfairs To DBBackup with differential
>> Backup database CoEdocuments To DBBackup with differential
>> Backup database committee To DBBackup with differential
>> Backup database conference To DBBackup with differential
>> GO
>> =====
>>
>> Thanks in advance for any help,
>>
>> Bing
>|||"Bing Du" <bdu@.iastate.edu> wrote in message
news:c1l794$aui$1@.news.iastate.edu...
> Ok, seems like only local disks show up when a new backup device is
> defined. After I copied the backup from the network shared directory to
> a local directory on s2, restore worked.
> Bing

<snip
FYI, it is possible to backup and restore from UNC paths, provided that the
account used to run MSSQL has access to the path:

backup database foo to disk = '\\MyServer\MyBackups\foo.bak'

Enteprise Manager only displays local drives, but I believe you can type in
a UNC path in the backup/restore dialogues, although I don't use EM much, so
I'm not 100% sure about that.

Simon|||I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.

Bing

Simon Hayes wrote:

> "Bing Du" <bdu@.iastate.edu> wrote in message
> news:c1l794$aui$1@.news.iastate.edu...
>>Ok, seems like only local disks show up when a new backup device is
>>defined. After I copied the backup from the network shared directory to
>>a local directory on s2, restore worked.
>>
>>Bing
>>
>
> <snip>
> FYI, it is possible to backup and restore from UNC paths, provided that the
> account used to run MSSQL has access to the path:
> backup database foo to disk = '\\MyServer\MyBackups\foo.bak'
> Enteprise Manager only displays local drives, but I believe you can type in
> a UNC path in the backup/restore dialogues, although I don't use EM much, so
> I'm not 100% sure about that.
> Simon|||Bing Du (bdu@.iastate.edu) writes:
> I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.

And what does "did not work" mean?

As Simon says, it depends on the account under which SQL Server is running.
If SQL Server is installed to run as Local Service, then you cannot access
network resources from SQL Server.

A word of caution, though, about backing up or restore from network
devices. I don't think is fully supported. That is, you can do it, but
you will be safe if you back up to local disk, and copy of the network.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949C56828223Yazorman@.127.0.0.1...
> Bing Du (bdu@.iastate.edu) writes:
> > I tried something like '\\pc100\tmp\DBBackup.BAK' in EM, did not work.
> And what does "did not work" mean?
> As Simon says, it depends on the account under which SQL Server is
running.
> If SQL Server is installed to run as Local Service, then you cannot access
> network resources from SQL Server.
> A word of caution, though, about backing up or restore from network
> devices. I don't think is fully supported. That is, you can do it, but
> you will be safe if you back up to local disk, and copy of the network.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

According to Books Online (under BACKUP):

"If using a network server with a Uniform Naming Convention (UNC) name or
using a redirected drive letter, specify a device type of disk."

But anyway, a backup to a UNC path may fail because of network issues, so
it's fair to say that a local backup is a safer option.

Simon

Tuesday, March 27, 2012

Backing up SQL databases

Backing up SQL databases

Backing up SQL databases

backing up reporting services

Monday, March 19, 2012

Back up system DBs

Back up system DBs

Back up system DBs

Back Up question

back everything up in a single file?

Autonumber field

Blog Archive

About Me