backup

Good evening!
Rookie question regarding backups. Windows Server 2003 SP2 running SQL 2000
SP4 with several databases.
Currently not using Backup Exec to back up the databases. Using the
Enterprise Manager to backup up each DB individually from within SQL.
However, and here comes the question, how do I schedule these backups from
within SQL?
Now, I see the "schedule" button (Console Root | Microsoft SQL Servers | SQL
Server Group | (local)(Windows NT) | Databases | db....right click the db,
select 'All tasks' and then select 'Backup Databases...". At the bottom of
General Tab I see the"Schedule" check box. However, whatever I set does not
stick! Or, does it and I just do not see it?
Additionally, I am also interested in selecting the "overwrite existing
media" and having it stick! It does not.
Am I doing something incorrectly?
I have set all of the DBs to be backed up to its own folder with the same
'parent' folder (E:\SQLBackups\ and then each DB has its own folder and .bkf
file).
I would like to next us NTBackup to backup the 'parent' folder
(E:\SQLBackups). However, that seems kinda pointless if I can not get some
sort of schedule going for each DB (some twice a week and some every day).
We are entertaining the thought of BackupAssistant but I would like to get
NTBackup going on its own...well, actually it is backup with SQL where I am
having the issue...
Thanks!
CaryHi Cary,
When you check the Schedule box on the Backup window you are creating and
scheduling a SQL Server job to do the backup. You can find these jobs in
Enterprise Manager, Management, SQL Server Agent and Jobs.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Cary Shultz" wrote:
> Good evening!
> Rookie question regarding backups. Windows Server 2003 SP2 running SQL 2000
> SP4 with several databases.
> Currently not using Backup Exec to back up the databases. Using the
> Enterprise Manager to backup up each DB individually from within SQL.
> However, and here comes the question, how do I schedule these backups from
> within SQL?
> Now, I see the "schedule" button (Console Root | Microsoft SQL Servers | SQL
> Server Group | (local)(Windows NT) | Databases | db....right click the db,
> select 'All tasks' and then select 'Backup Databases...". At the bottom of
> General Tab I see the"Schedule" check box. However, whatever I set does not
> stick! Or, does it and I just do not see it?
> Additionally, I am also interested in selecting the "overwrite existing
> media" and having it stick! It does not.
> Am I doing something incorrectly?
> I have set all of the DBs to be backed up to its own folder with the same
> 'parent' folder (E:\SQLBackups\ and then each DB has its own folder and .bkf
> file).
> I would like to next us NTBackup to backup the 'parent' folder
> (E:\SQLBackups). However, that seems kinda pointless if I can not get some
> sort of schedule going for each DB (some twice a week and some every day).
> We are entertaining the thought of BackupAssistant but I would like to get
> NTBackup going on its own...well, actually it is backup with SQL where I am
> having the issue...
> Thanks!
> Cary
>
>|||Brett,
Sounds like you are doing what I am trying to do (only will be using
NTBackup instead of Tivoli). I just did not know about the maintenance
plan. Thanks for the tip!
Cary
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:tbc9m31sdhmd823qon5bo73mm6bd06ur52@.4ax.com...
> Create a maintenance plan and that will let you schedule as well as
> other neat options.
> Most backup programs don't reliably backup mdf/ldf (the db and log
> files) as they are open so SQL Server is used to creat the backup
> files. We put the backup files in a backkup directory with a
> subdirectory for each database and then backup the backup directory
> and it's children (along with other stuff on the server) with Tivoli.
> On Sat, 15 Dec 2007 23:58:06 -0500, "Cary Shultz"
> <cshultz@.nospam.outsourceitcorp.com> wrote:
>>Good evening!
>>Rookie question regarding backups. Windows Server 2003 SP2 running SQL
>>2000
>>SP4 with several databases.
>>Currently not using Backup Exec to back up the databases. Using the
>>Enterprise Manager to backup up each DB individually from within SQL.
>>However, and here comes the question, how do I schedule these backups from
>>within SQL?
>>Now, I see the "schedule" button (Console Root | Microsoft SQL Servers |
>>SQL
>>Server Group | (local)(Windows NT) | Databases | db....right click the
>>db,
>>select 'All tasks' and then select 'Backup Databases...". At the bottom
>>of
>>General Tab I see the"Schedule" check box. However, whatever I set does
>>not
>>stick! Or, does it and I just do not see it?
>>Additionally, I am also interested in selecting the "overwrite existing
>>media" and having it stick! It does not.
>>Am I doing something incorrectly?
>>I have set all of the DBs to be backed up to its own folder with the same
>>'parent' folder (E:\SQLBackups\ and then each DB has its own folder and
>>.bkf
>>file).
>>I would like to next us NTBackup to backup the 'parent' folder
>>(E:\SQLBackups). However, that seems kinda pointless if I can not get
>>some
>>sort of schedule going for each DB (some twice a week and some every day).
>>We are entertaining the thought of BackupAssistant but I would like to get
>>NTBackup going on its own...well, actually it is backup with SQL where I
>>am
>>having the issue...
>>Thanks!
>>Cary|||Ben,
I did not look there...did not know to look there. Thanks for the tip!
Cary
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:C9D9326C-E2E5-4A9A-ACB1-14EB66076610@.microsoft.com...
> Hi Cary,
> When you check the Schedule box on the Backup window you are creating and
> scheduling a SQL Server job to do the backup. You can find these jobs in
> Enterprise Manager, Management, SQL Server Agent and Jobs.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Cary Shultz" wrote:
>> Good evening!
>> Rookie question regarding backups. Windows Server 2003 SP2 running SQL
>> 2000
>> SP4 with several databases.
>> Currently not using Backup Exec to back up the databases. Using the
>> Enterprise Manager to backup up each DB individually from within SQL.
>> However, and here comes the question, how do I schedule these backups
>> from
>> within SQL?
>> Now, I see the "schedule" button (Console Root | Microsoft SQL Servers |
>> SQL
>> Server Group | (local)(Windows NT) | Databases | db....right click the
>> db,
>> select 'All tasks' and then select 'Backup Databases...". At the bottom
>> of
>> General Tab I see the"Schedule" check box. However, whatever I set does
>> not
>> stick! Or, does it and I just do not see it?
>> Additionally, I am also interested in selecting the "overwrite existing
>> media" and having it stick! It does not.
>> Am I doing something incorrectly?
>> I have set all of the DBs to be backed up to its own folder with the same
>> 'parent' folder (E:\SQLBackups\ and then each DB has its own folder and
>> .bkf
>> file).
>> I would like to next us NTBackup to backup the 'parent' folder
>> (E:\SQLBackups). However, that seems kinda pointless if I can not get
>> some
>> sort of schedule going for each DB (some twice a week and some every
>> day).
>> We are entertaining the thought of BackupAssistant but I would like to
>> get
>> NTBackup going on its own...well, actually it is backup with SQL where I
>> am
>> having the issue...
>> Thanks!
>> Cary
>>|||Brett,
You raised some questions that I was going to ask...
I have things set up so that the "data" and the "logs" are backed up after
everyone has gone home (starting at 5:30PM). First the data is backed up to
the "DATA" folder for that specific Database and then, five minutes later,
the logs are backed up to the "LOG" folder for that specific Database. I
have this set up via maintenance plans (thanks for the tip there...I
eventually would have found it I am sure but it is nice to do things once or
twice instead of four or five times!
Now, I was going to ask the question how the restore in SQL works. I am
pretty familiar with how this works in Active Directory (system state) and
Exchange but have no idea in SQL. It *seems* like I would restore the data
as well as the log files (transaction logs)...you mentioned to a specific
point in time in your reply. Not sure if I want/need to play with that at
the moment. I would like to be able to restore it to "yesterday" (read:
yesterdays back up at 5:45PM...yes, they loose all the work from today...)
for the time being, not to "today at 10:45AM"...I have to crawl before I
can walk, right?!
Thanks,
Cary
PS..."MP way"? Might that be "multi-point" or "multiple points", as in
multiple points through out "today"?
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:ovlam3plllc7edmuhtd57d58391dpvrtkh@.4ax.com...
> You're welcome. There are lots of ways to do it but I like the MP way
> because of the flexibility. As someone else pointed out that in SQL
> 2000 these show up under the jobs. In SQL 2005 it's organized a
> little better - in my opinion <G>.
> Remember - the critical part of the backup is the restore plan - does
> it work! You should consider having a system where you can test
> restores.
> And then there is the question - do you want to backup the transaction
> logs so you can get to a point in time? I do on some of our systems -
> I run a full backup early in the morning and then before the users
> start hitting the database I start transaction logging until shortly
> after they go home.
> Also, you need to consider the recovey mode you want to use.
> On Sun, 16 Dec 2007 05:47:32 -0500, "Cary Shultz"
> <cshultz@.nospam.outsourceitcorp.com> wrote:
>>Brett,
>>Sounds like you are doing what I am trying to do (only will be using
>>NTBackup instead of Tivoli). I just did not know about the maintenance
>>plan. Thanks for the tip!
>>Cary
>>
>>"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
>>news:tbc9m31sdhmd823qon5bo73mm6bd06ur52@.4ax.com...
>> Create a maintenance plan and that will let you schedule as well as
>> other neat options.
>> Most backup programs don't reliably backup mdf/ldf (the db and log
>> files) as they are open so SQL Server is used to creat the backup
>> files. We put the backup files in a backkup directory with a
>> subdirectory for each database and then backup the backup directory
>> and it's children (along with other stuff on the server) with Tivoli.
>> On Sat, 15 Dec 2007 23:58:06 -0500, "Cary Shultz"
>> <cshultz@.nospam.outsourceitcorp.com> wrote:
>>Good evening!
>>Rookie question regarding backups. Windows Server 2003 SP2 running SQL
>>2000
>>SP4 with several databases.
>>Currently not using Backup Exec to back up the databases. Using the
>>Enterprise Manager to backup up each DB individually from within SQL.
>>However, and here comes the question, how do I schedule these backups
>>from
>>within SQL?
>>Now, I see the "schedule" button (Console Root | Microsoft SQL Servers |
>>SQL
>>Server Group | (local)(Windows NT) | Databases | db....right click the
>>db,
>>select 'All tasks' and then select 'Backup Databases...". At the bottom
>>of
>>General Tab I see the"Schedule" check box. However, whatever I set does
>>not
>>stick! Or, does it and I just do not see it?
>>Additionally, I am also interested in selecting the "overwrite existing
>>media" and having it stick! It does not.
>>Am I doing something incorrectly?
>>I have set all of the DBs to be backed up to its own folder with the
>>same
>>'parent' folder (E:\SQLBackups\ and then each DB has its own folder and
>>.bkf
>>file).
>>I would like to next us NTBackup to backup the 'parent' folder
>>(E:\SQLBackups). However, that seems kinda pointless if I can not get
>>some
>>sort of schedule going for each DB (some twice a week and some every
>>day).
>>We are entertaining the thought of BackupAssistant but I would like to
>>get
>>NTBackup going on its own...well, actually it is backup with SQL where I
>>am
>>having the issue...
>>Thanks!
>>Cary
>>|||Brett,
MP...so, it is not "Member of Parliment"? Drats!
I lieved in Germany for several years after I graduated from college, so
when I see "MP" I immediately think of Member of Parliment. Looks like I
need to relearn that!
And, thanks for your tips. I learn a lot in these news groups. In fact, I
used to be an MVP for Active Directory a few years back. In my current
position (which will hopefully stay 'current' for a really long time) I do a
lot of the AD stuff. At a few of the clients I am getting more and more
involved with applications that rely on SQL. I do not really speak SQL just
yet...so, I am taking the initiative and starting to learn (just like I did
with AD many moons ago). Backup and restores are hot topics right now so I
am trying to learn quickly.
You will problably be seeing more of me in these two or three news groups.
Hopefully the questions that I ask will not only help me, but all those who
have similar questions. When I first started in AD I looked at the
questions before looking at the replies and tried to figure out what was
going on and then tried to find an answer (without looking at the
reply/replies). That whole "road to discovery" was a fun ride...I hope
that others take it...and enjoy!
Thanks again,
Cary
PS. If you have any AD questions give a shout! Just remove the obvious
part from my e-mail address. I gladly help others when I can (and the
'when' part is the problem!) and will go out of my way to help those who
have helped me.
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:iltam3pjoubfoc8ik1ge1sa3dmlqedlh3f@.4ax.com...
> Sorry - buzzword itis <G>. MP is Maintenance Plan
> I am not an expert by any means on SQL Server. I became a DBA because
> I'm the Windows Server Admin and we needed a DBA (there was none) last
> spring so it seemed like a good fit to management <G>. Actually, I
> love it and have been enjoying it. I'm learning by reading and doing
> what you are doing - asking questions here.
> Restores that I've done I've used the right click in Enterprise
> Manager (on the server) and chosen the restore option. Say you have
> a backup of the database at 0200 - you restore it first. Then you go
> through the transaction logs and restore them. You have to make sure
> that you select the option that leaves the database so it can have the
> next restore done.
> If you're going to be doing this routinely (and it sounds like it <G>)
> get Kalen Daleny's book on SQL 2000 Server. It's big but good reading
> and I used it as a reference a lot. I say used because now I'm moving
> to SQL 2005 and I have her five set book that she coauthored. It's
> worth the money. My Management purchased it for me but I'd buy it for
> myself if I had to.
> Also Books on Line (BOL) is another good reference source.
> Take it one step at a time. I'm still working on the list of "Things
> I want to try and learn" now. Some of it will happen because of
> things that happen to your databases so you'll learn on the job <G>.
> Also - DOCUMENT!! Our group, IT Operations, has a Wiki and I have a
> section for SQL Server where I document our procedures like how to
> create a MP, notes on stuff I thought was good to keep, SQL code,
> everything. Not only will it help if you leave but I find I need to
> refer to it.
>
> On Sun, 16 Dec 2007 13:17:39 -0500, "Cary Shultz"
> <cshultz@.nospam.outsourceitcorp.com> wrote:
>>Brett,
>>You raised some questions that I was going to ask...
>>I have things set up so that the "data" and the "logs" are backed up after
>>everyone has gone home (starting at 5:30PM). First the data is backed up
>>to
>>the "DATA" folder for that specific Database and then, five minutes later,
>>the logs are backed up to the "LOG" folder for that specific Database. I
>>have this set up via maintenance plans (thanks for the tip there...I
>>eventually would have found it I am sure but it is nice to do things once
>>or
>>twice instead of four or five times!
>>Now, I was going to ask the question how the restore in SQL works. I am
>>pretty familiar with how this works in Active Directory (system state) and
>>Exchange but have no idea in SQL. It *seems* like I would restore the
>>data
>>as well as the log files (transaction logs)...you mentioned to a specific
>>point in time in your reply. Not sure if I want/need to play with that at
>>the moment. I would like to be able to restore it to "yesterday" (read:
>>yesterdays back up at 5:45PM...yes, they loose all the work from
>>today...)
>>for the time being, not to "today at 10:45AM"...I have to crawl before I
>>can walk, right?!
>>Thanks,
>>Cary