Backing up MS SQL?

Hi

I'm hoping that some could point me in the right direction for "best
practice" in:

1) securely backing up a complete sql7 and 2000 server containing many
databases.

2) backing up an individual database

3) Securing the transaction on the hour as a mile stone to return to in the
event of error or system failure.

Thanks

MarkHi

The first place to start is Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_bkprst_9zcj.htm

John

"mark" <kardtech@.on.aibn.com> wrote in message
news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> Hi
> I'm hoping that some could point me in the right direction for "best
> practice" in:
> 1) securely backing up a complete sql7 and 2000 server containing many
> databases.
> 2) backing up an individual database
> 3) Securing the transaction on the hour as a mile stone to return to in
the
> event of error or system failure.
> Thanks
> Mark|||Standard Answer then. Thanks for NO help

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:b8UIb.3645$PB3.32098570@.news-text.cableinet.net...
> Hi
> The first place to start is Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
> insql.chm::/ad_bkprst_9zcj.htm
> John
> "mark" <kardtech@.on.aibn.com> wrote in message
> news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> > Hi
> > I'm hoping that some could point me in the right direction for "best
> > practice" in:
> > 1) securely backing up a complete sql7 and 2000 server containing many
> > databases.
> > 2) backing up an individual database
> > 3) Securing the transaction on the hour as a mile stone to return to in
> the
> > event of error or system failure.
> > Thanks
> > Mark|||"mark" <kardtech@.on.aibn.com> wrote in message
news:iuUIb.85$b06.63@.newsfep3-gui.server.ntli.net...
> Standard Answer then. Thanks for NO help

What about the standard answer don't you like?

With more detail:

1) For backing up the actual installation, I generally find it easier to
reinstall the application if need be.

2) Backing up individual databases, look into creating a maintenance plan.
Or roll your own and use backup database as appropriate.

3) use BACKUP LOG on the required databases.

I'd also recommend getting a book on high availabilibity. The one from MS
press is quite good.

>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:b8UIb.3645$PB3.32098570@.news-text.cableinet.net...
> > Hi
> > The first place to start is Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
> > insql.chm::/ad_bkprst_9zcj.htm
> > John
> > "mark" <kardtech@.on.aibn.com> wrote in message
> > news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> > > Hi
> > > > I'm hoping that some could point me in the right direction for "best
> > > practice" in:
> > > > 1) securely backing up a complete sql7 and 2000 server containing many
> > > databases.
> > > > 2) backing up an individual database
> > > > 3) Securing the transaction on the hour as a mile stone to return to
in
> > the
> > > event of error or system failure.
> > > > Thanks
> > > > Mark
> >|||Thankyou

"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:7oWIb.34408$q55.5924@.twister.nyroc.rr.com...
> "mark" <kardtech@.on.aibn.com> wrote in message
> news:iuUIb.85$b06.63@.newsfep3-gui.server.ntli.net...
> > Standard Answer then. Thanks for NO help
> What about the standard answer don't you like?
> With more detail:
> 1) For backing up the actual installation, I generally find it easier to
> reinstall the application if need be.
> 2) Backing up individual databases, look into creating a maintenance plan.
> Or roll your own and use backup database as appropriate.
> 3) use BACKUP LOG on the required databases.
> I'd also recommend getting a book on high availabilibity. The one from MS
> press is quite good.
>
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:b8UIb.3645$PB3.32098570@.news-text.cableinet.net...
> > > Hi
> > > > The first place to start is Books Online:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
> > > insql.chm::/ad_bkprst_9zcj.htm
> > > > John
> > > > "mark" <kardtech@.on.aibn.com> wrote in message
> > > news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> > > > Hi
> > > > > > I'm hoping that some could point me in the right direction for "best
> > > > practice" in:
> > > > > > 1) securely backing up a complete sql7 and 2000 server containing
many
> > > > databases.
> > > > > > 2) backing up an individual database
> > > > > > 3) Securing the transaction on the hour as a mile stone to return to
> in
> > > the
> > > > event of error or system failure.
> > > > > > Thanks
> > > > > > Mark
> > > > > >|||"mark" <kardtech@.on.aibn.com> wrote in message
news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> Hi
> I'm hoping that some could point me in the right direction for "best
> practice" in:
> 1) securely backing up a complete sql7 and 2000 server containing many
> databases.

1.1) Schedule a complete backup of all production databases and include
the system databases master, msdb, model at some appropriate
timeslot.
To do this for the first time, try using Enterprise Manager, select
the
"schedule" option and then examine the TSQL code in the scheduled
task. Modify the schedule times to suit your operations.

1.2) Take a copy of the backup files offsite.

> 2) backing up an individual database

As above, so here below ... much like the
Hermetic tradition.

> 3) Securing the transaction on the hour as a mile stone to return to in
the
> event of error or system failure.

You will need to examine the differential backup strategies available
and gauge the best approach for this method in relation to your data.

The M$ documentation is not too bad at all if you are prepared to
go and experiment and learn by practice the operations therein
documented, and I'd be surprised if step by step instructions on
all of the above are not clearly established within it.

Pete Brown
Falls Creek
OZ

Backing up 6 SQL servers over a LAN

We are looking at a backup solution for our 6 SQL7 servers. Oh yes - by that I mean we don't have a backup at the moment...lol

Well - thing is we want to use something like a Dell Powervault 122T tape auto-loader. This means backup up our data over the LAN.

The databases range from 130MB to 120GB and I am wondering if a backup over the LAN is a good idea - we have 100MB LAN using cascaded netgear switches - so no real 'backbone' to speak of...

Is it advisable to attach the tape loader to the SQL server with the largest DB and then pull in the rest? Or, should we use separate tape drives for each SQL server?

Yes, it depends on requirements but getting those is like blood from a stone...so I am looking for something that will do the job and offer a little flexabilty...

I am inclined to think that without a good backbone on the network doing a backup this way is going to take forever?

Many thanksIMHO, the real question is how much through put can you get at the time you do your backups, what software you are using (mainly what types of backups does it support) and how many tape drives you will be writing to at once.

One solution woulds be for each sql server to write a backup locally and retain for one or two days. Then your backup software only needs to scrape the file off the server.

Another solution would be to add a 2nd NIC to the SQL and Tape Host servers creating a private network. At least you wouldn't be competing for network resources.

If this tape unit will be backing up other servers, Application , File & Prinet etc. I would NOT hang it off of a SQL server, I feel that a File and Print server or Domain controler would be a better choice as these servers tend to have spare cycles for such things.

One additional thing to consider is RESTORE time. If your main SQL server goes down and a restore from tape is required can you afford the time to wait for the restore?

Any strategy would be better than nothing!|||I like the idea of it having it's own little network...

It is likely to be a single tape autoloader. The network is simply a load of cascaded switches so a server at the top being backed up to a server at the bottom could take ages...

Perhaps dumping to disk and then backing these files up using the dedicated network is the soution until we have a proper network...|||I have seen cascaded hubs, I am assuming you mean hubs not switches, do okay PROVIDED there is little traffic. This may not be your case when you start your backups.

If backing up takes to long or causes to much traffic then this might be an argument to upgrade the network architecture.|||Originally posted by Paul Young
I have seen cascaded hubs, I am assuming you mean hubs not switches, do okay PROVIDED there is little traffic. This may not be your case when you start your backups.

If backing up takes to long or causes to much traffic then this might be an argument to upgrade the network architecture.

No - these are switches - the uplink from one is connected to a port on another...there is a hub in there somehwere but no idea where yet!

AV & contineus stack dumps on SQL7.

Hi,
I've got SQL7-SP3 installation on NT4-SP6. Now, today I
noticed that for number of days it is generating stack
dumps every few minutes. It goes like this:
===================== SqlDumpExceptionHandler: Process 51 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
* BEGIN STACK DUMP:
-
-
-
-
language_exec: Process 51 generated an access violation.
SQL Server is terminating this process.
====================== Input buffer always say:
* select rsc_type, req_mode, req_status from
master..syslockinfo
I tried to search the knowledge base, but it reports tons
of reasons for AV. I have seen AV & stack dumps number of
times in past (on different servers) that goes only for
once, but the one I am looking at currently is doing it
constantly at few minutes interval.
I havn't heard any performance cry yet and don't know how
it is affecting.
Anyone seen this kind of behaviour or have any input on
this are welcome to shed some light. Your input is
appreciated. Thanks.
Adi.We found a monitoring software that was querying
syslockinfo, causing AV and stack dump. Stopping this
monitoring software service stopped stack dumps.
Still I wonder how did it started happening as we have
ditto configuration and setup on number of other servers
where everything seems to be working fine.
Anyways, thanks for your replies guys. Appreciate it.
Adi ..
>--Original Message--
>Hi,
>I've got SQL7-SP3 installation on NT4-SP6. Now, today I
>noticed that for number of days it is generating stack
>dumps every few minutes. It goes like this:
>=====================>SqlDumpExceptionHandler: Process 51 generated fatal
>exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
>is terminating this process.
>* BEGIN STACK DUMP:
>-
>-
>-
>-
>language_exec: Process 51 generated an access violation.
>SQL Server is terminating this process.
>======================>Input buffer always say:
>* select rsc_type, req_mode, req_status from
>master..syslockinfo
>I tried to search the knowledge base, but it reports tons
>of reasons for AV. I have seen AV & stack dumps number of
>times in past (on different servers) that goes only for
>once, but the one I am looking at currently is doing it
>constantly at few minutes interval.
>I havn't heard any performance cry yet and don't know how
>it is affecting.
>Anyone seen this kind of behaviour or have any input on
>this are welcome to shed some light. Your input is
>appreciated. Thanks.
>Adi.
>.
>