Backup SQL server using msbackup

Guys, I want to be able to use MSBackup to create a backup of my Windows
2000 server with SQL 2000 server running. I need to be able to include the
SQL databases etc in this backup but obviously the files are open when the
SQL server is running and can not be backed up so I need to stop the service
before the backup and restart it after. Can anyone tell me how best to do
this? I do not want to have to install any other applications on this server
so I am hoping that the various Windows default programs will alow me to
achive this goal.
Thanks for your help with this.
Gerry
For SQL Server you will better of to use BACKUP command of T-SQL
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
news:120bh7gpkhdsbc5@.corp.supernews.com...
> Guys, I want to be able to use MSBackup to create a backup of my Windows
> 2000 server with SQL 2000 server running. I need to be able to include the
> SQL databases etc in this backup but obviously the files are open when the
> SQL server is running and can not be backed up so I need to stop the
> service before the backup and restart it after. Can anyone tell me how
> best to do this? I do not want to have to install any other applications
> on this server so I am hoping that the various Windows default programs
> will alow me to achive this goal.
> Thanks for your help with this.
>
|||Uri, thanks for the response but I do not know much about SQL so this is a
bit over my head, can you explain a bit more what you mean by that? I assume
you are referring to a SQL command and therefore I will have to create a
script of some sort... I want to use MSBackup to create a single source of
recovery for the entire server inclusive of the SQL databases. I was hoping
to create a schedule that would stop the SQL server, start the MSBackup,
then start the SQL server again after the backup is complete. Any
suggetsions on this? Maybe I could use batch files?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O1YrqnUPGHA.812@.TK2MSFTNGP10.phx.gbl...
> Gerry
> For SQL Server you will better of to use BACKUP command of T-SQL
>
> "Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
> news:120bh7gpkhdsbc5@.corp.supernews.com...
>
|||MSBackup cannot back up a working SQL Server by itself. More importantly,
it cannot restore a working SQL Server by itself. It cannot get a
consistent picture of the multiple files that comprise a SQL database with
the system working. SQL includes a utility to create a transactionally
consistant self-describing backup that can be stored on one or more disk
files. Typical best practice is to back up the SQL Server to a disk (local
or network) and then back up those files to some external archive system
such as a tape library.
If you are responsible for restoring the SQL host sytem and database engine
in case of failure or disaster, I strongly suggest you read the section in
BOL (Books-On-Line) on Backuping Up and Restoring Databases (It's under
"Administering SQL Server" in the TOC). SQL backup and recovery is
different from ordinary file-system type restore. Expecting SQL to recover
just like a non-SQL system will lead to failure, disappointment, and
potential unemployment.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
news:120bk4crsfj5s9b@.corp.supernews.com...
> Uri, thanks for the response but I do not know much about SQL so this is a
> bit over my head, can you explain a bit more what you mean by that? I
> assume you are referring to a SQL command and therefore I will have to
> create a script of some sort... I want to use MSBackup to create a single
> source of recovery for the entire server inclusive of the SQL databases. I
> was hoping to create a schedule that would stop the SQL server, start the
> MSBackup, then start the SQL server again after the backup is complete.
> Any suggetsions on this? Maybe I could use batch files?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O1YrqnUPGHA.812@.TK2MSFTNGP10.phx.gbl...
>
|||Gerry Armstrong wrote:
> Guys, I want to be able to use MSBackup to create a backup of my Windows
> 2000 server with SQL 2000 server running. I need to be able to include the
> SQL databases etc in this backup but obviously the files are open when the
> SQL server is running and can not be backed up so I need to stop the service
> before the backup and restart it after. Can anyone tell me how best to do
> this? I do not want to have to install any other applications on this server
> so I am hoping that the various Windows default programs will alow me to
> achive this goal.
> Thanks for your help with this.
>
Hi Gerry
if you really need to backup using msbackup, and assuming that you've
got a default instance, run:
net stop sqlserveragent
net stop mssqlserver
to stop, and net start will get them back up and running again.
|||Ok, judging from these responses I will be suggesting to the customer that
they purchase a backup product that will include the SQL agent. The main
issue here is that the customer is small and there is no-one onsight with
enough knowledge to be able to do anything technical so I am trying to find
the simplest most straightforward way of providing them with a backup that
will also be the easiest means of disaster recovery. I assumed that if the
SQL was not running and the server could access all the files that I would
be able to do a full backup and in the event of failure do a full restore
and the databases will work exactly as they were before the backup was
started but maybe my assumption is wrong like most assumptions.....
"Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
news:du4n3t$1gq$1@.news.freedom2surf.net...
> Gerry Armstrong wrote:
>
> Hi Gerry
> if you really need to backup using msbackup, and assuming that you've got
> a default instance, run:
> net stop sqlserveragent
> net stop mssqlserver
> to stop, and net start will get them back up and running again.
|||In theory that should work. But I'd never trust that. Create SQL Server Agent jobs for your clients
(possibly using the Database Maintenance Wizard) which backup the database to files and then pick up
those files using ntbackup.
You can of course safeguard with *also* stopping SQL Server and grab the raw database file. Restore
from that is *likely* to work, and if it doesn't, you have the SQL server backups as a fall-back.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message news:120bs57po9qave9@.corp.supernews.com...
> Ok, judging from these responses I will be suggesting to the customer that they purchase a backup
> product that will include the SQL agent. The main issue here is that the customer is small and
> there is no-one onsight with enough knowledge to be able to do anything technical so I am trying
> to find the simplest most straightforward way of providing them with a backup that will also be
> the easiest means of disaster recovery. I assumed that if the SQL was not running and the server
> could access all the files that I would be able to do a full backup and in the event of failure do
> a full restore and the databases will work exactly as they were before the backup was started but
> maybe my assumption is wrong like most assumptions.....
> "Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
> news:du4n3t$1gq$1@.news.freedom2surf.net...
>
|||As Tibor said, I wouldn't trust a quiesced system to be restorable. I also
do not recommend purchasing a third-party SQL backup solution with a SQL
agent. I do recommend reading about the system you are giving advice on.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
news:120bs57po9qave9@.corp.supernews.com...
> Ok, judging from these responses I will be suggesting to the customer that
> they purchase a backup product that will include the SQL agent. The main
> issue here is that the customer is small and there is no-one onsight with
> enough knowledge to be able to do anything technical so I am trying to
> find the simplest most straightforward way of providing them with a backup
> that will also be the easiest means of disaster recovery. I assumed that
> if the SQL was not running and the server could access all the files that
> I would be able to do a full backup and in the event of failure do a full
> restore and the databases will work exactly as they were before the backup
> was started but maybe my assumption is wrong like most assumptions.....
> "Tim Purcell" <tim.purcell@.mungos.f2s.com> wrote in message
> news:du4n3t$1gq$1@.news.freedom2surf.net...
>
|||Ok Guys, I take all your suggestions under advisment... guess I got more
reading to do as if I don't have enough going on in my head as yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecr9qSWPGHA.2624@.TK2MSFTNGP12.phx.gbl...
> In theory that should work. But I'd never trust that. Create SQL Server
> Agent jobs for your clients (possibly using the Database Maintenance
> Wizard) which backup the database to files and then pick up those files
> using ntbackup.
> You can of course safeguard with *also* stopping SQL Server and grab the
> raw database file. Restore from that is *likely* to work, and if it
> doesn't, you have the SQL server backups as a fall-back.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Gerry Armstrong" <gerrya@.hotmail.com> wrote in message
> news:120bs57po9qave9@.corp.supernews.com...
>