Backup Sql-server 2005

hi-i want to create automatic Backup scheduler for my sql-server 2005 how can i create sql-server2005 backup scheduler

thanks

http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx

|||thanksthere information about back-up is i have to download a service-pack 1 of sql-server 2005,but when i install in my comuter there is give me error is not apllicable is win32 application,but my os is Xp,i have also instal a sql-server 2005 why not install a sp-1 in my computersql

Backup SQLExpr. 2005

Hi
Is it possible and when yes, then how to create database backup to mapped
drive or CD-R?
Meelis
Hi
No. The problem is the API's that the OS needs to do this.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:O15lYFc5FHA.3688@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is it possible and when yes, then how to create database backup to mapped
> drive or CD-R?
>
> Meelis
>
|||API's?
Then i go back to use sql 2000 again.
Why isnt there Jobs?
Meelis
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OWBE43c5FHA.2816@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi
> No. The problem is the API's that the OS needs to do this.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:O15lYFc5FHA.3688@.TK2MSFTNGP10.phx.gbl...
mapped
>
sql

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...
>

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...
>> 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.
>|||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
>> 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.
>>
>|||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:
>> 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.|||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...
>> 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.
>|||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...
>> 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 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...
>> 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:
>> 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.
>>
>

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 servi
ce
> 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 serv
er
> 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 Agen
t jobs for your clients
(possibly using the Database Maintenance Wizard) which backup the database t
o 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 ba
ckups 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 c
ustomer is small and
> there is no-one onsight with enough knowledge to be able to do anything te
chnical so I am trying
> to find the simplest most straightforward way of providing them with a bac
kup 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...
>

Backup SQL 2005 Plan

Hello everybody,
I'm using SQL 2005 for few weeks, and I'd like to ask you something about
the Backup and Restore Plan. I need to create a complete DR solution for my
client.
So, what I did was: (using Management Studio-Maintenance Plan)
- All the user database are on Full Recovery Mode.
- Run a Full User Database backup every day at 1:00am and save it on day's
folder, it means the backup for Monday will go to Monday's backup folder and
so on.
- At 6:00am (normally the time they start to work), I start to do a Log
Backup every 15 minutes until 11:00pm aprox.
My question is:
- Every time that I ran the log backup, I'm truncating the LOG file? or I
have to run other task to truncate the log?
- Using that Plan, I'll be able to recover the data for the last 15 minutes
in case the database crashed?
- How often I have to shrink the database file and logs?
The Primary data file is : 570Gb
The Secondary data file is : 130Gb
The Log file is : 850Gb
I'm not worry about the space because the server and the backup unit have a
lot of space.
Any ideas or opinions'
Thank you!
JoseHola Jose,
Some comments here:
> The Log file is : 850Gb
Is this a typo? This is too big for a transaction log file. You should
manually shrink it, but probably only once. Do not schedule a job to shrink
it periodically.
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
After the transaction log is shrunk manually doing a backup every 15 minutes
should keep it in a acceptable size.
> - Using that Plan, I'll be able to recover the data for the last 15 minutes
> in case the database crashed?
Yes, if you still have access to the backup folder. Are these folders on the
same computer? Also, if you are lucky and have access to the tail of the log
you can recover your database to the point of failure, losing no data at all.
> - How often I have to shrink the database file and logs?
Do not shrink any file on a job or maintenance plan. Do it only manually
when you really need it.
Hope this helps,
Ben Nevarez
"Jose" wrote:
> Hello everybody,
> I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> the Backup and Restore Plan. I need to create a complete DR solution for my
> client.
> So, what I did was: (using Management Studio-Maintenance Plan)
> - All the user database are on Full Recovery Mode.
> - Run a Full User Database backup every day at 1:00am and save it on day's
> folder, it means the backup for Monday will go to Monday's backup folder and
> so on.
> - At 6:00am (normally the time they start to work), I start to do a Log
> Backup every 15 minutes until 11:00pm aprox.
> My question is:
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
> - Using that Plan, I'll be able to recover the data for the last 15 minutes
> in case the database crashed?
> - How often I have to shrink the database file and logs?
> The Primary data file is : 570Gb
> The Secondary data file is : 130Gb
> The Log file is : 850Gb
> I'm not worry about the space because the server and the backup unit have a
> lot of space.
> Any ideas or opinions'
> Thank you!
> Jose|||Jose
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
You can run BACKUP LOG with or without WITH INIT option (for more details
please see BOL)
If you choose using WITH INIT option that means SQL Server creates one file
per media set and you need to give the file separate name
Like log_20080101_17:00.log
log_20080101_17:30.log
On the other hand if you use WITH NOINIT you can create one file and SQL
Server adds one file to the media set and you will have to refer thopse
file when you restore database
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1,
norecovery --full database
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
> - Using that Plan, I'll be able to recover the data for the last 15
> minutes
> in case the database crashed?
You will be able to restore even at point of time
> - How often I have to shrink the database file and logs?
Do not shrink them at all
"Jose" <Jose@.discussions.microsoft.com> wrote in message
news:A3D3CD10-2D80-422A-AD68-09F9E510CDCB@.microsoft.com...
> Hello everybody,
> I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> the Backup and Restore Plan. I need to create a complete DR solution for
> my
> client.
> So, what I did was: (using Management Studio-Maintenance Plan)
> - All the user database are on Full Recovery Mode.
> - Run a Full User Database backup every day at 1:00am and save it on day's
> folder, it means the backup for Monday will go to Monday's backup folder
> and
> so on.
> - At 6:00am (normally the time they start to work), I start to do a Log
> Backup every 15 minutes until 11:00pm aprox.
> My question is:
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
> - Using that Plan, I'll be able to recover the data for the last 15
> minutes
> in case the database crashed?
> - How often I have to shrink the database file and logs?
> The Primary data file is : 570Gb
> The Secondary data file is : 130Gb
> The Log file is : 850Gb
> I'm not worry about the space because the server and the backup unit have
> a
> lot of space.
> Any ideas or opinions'
> Thank you!
> Jose|||Hi Ben and Uri.
Thanks for your comments!.
And I made a mistake, is not "GB" is "MB"..sorry.
I've asked if every time that I run the backup for the LOG files I'm
truncating the log, because my client is using NAVISION from Microsoft, and
the company who did the installation, gave me some instructions for the
backup, and to be honest I dont know too much about SQL commands, that is way
I did using the Management Studio. And they told me to create :
- 1 full backup every day
- 1 log backup every 15 minutes
- at the end of the day truncate the log to keep the log small...
But if backing up the log and I'm truncating it at the same time, I'll need
to create other job to truncate the log at the end of the day? I guess not.
Right now, when I run the log backup, SQL creates a directory for each
database and it creates every log file for each database every 15 minutes and
the size of the log is 500Kb.
All the files backup are on the server and I have a task who move files and
logs to other (external) unit backup, so, at least I have 2 places to find if
something happens to the datasbase and 1 place if something happens to the
server and to be more protected, those database are been replicated online to
1 external server outside of the company.
When I will really need shrink a database and/or Log File?
Thank you so much for all your comments!
Have a nice day!
Jose
"Ben Nevarez" wrote:
> Hola Jose,
> Some comments here:
> > The Log file is : 850Gb
> Is this a typo? This is too big for a transaction log file. You should
> manually shrink it, but probably only once. Do not schedule a job to shrink
> it periodically.
> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
> > have to run other task to truncate the log?
> After the transaction log is shrunk manually doing a backup every 15 minutes
> should keep it in a acceptable size.
> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
> > in case the database crashed?
> Yes, if you still have access to the backup folder. Are these folders on the
> same computer? Also, if you are lucky and have access to the tail of the log
> you can recover your database to the point of failure, losing no data at all.
> > - How often I have to shrink the database file and logs?
> Do not shrink any file on a job or maintenance plan. Do it only manually
> when you really need it.
> Hope this helps,
> Ben Nevarez
>
>
> "Jose" wrote:
> > Hello everybody,
> > I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> > the Backup and Restore Plan. I need to create a complete DR solution for my
> > client.
> > So, what I did was: (using Management Studio-Maintenance Plan)
> > - All the user database are on Full Recovery Mode.
> > - Run a Full User Database backup every day at 1:00am and save it on day's
> > folder, it means the backup for Monday will go to Monday's backup folder and
> > so on.
> > - At 6:00am (normally the time they start to work), I start to do a Log
> > Backup every 15 minutes until 11:00pm aprox.
> >
> > My question is:
> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
> > have to run other task to truncate the log?
> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
> > in case the database crashed?
> > - How often I have to shrink the database file and logs?
> >
> > The Primary data file is : 570Gb
> > The Secondary data file is : 130Gb
> > The Log file is : 850Gb
> >
> > I'm not worry about the space because the server and the backup unit have a
> > lot of space.
> >
> > Any ideas or opinions'
> > Thank you!
> >
> > Jose|||> When I will really need shrink a database and/or Log File?
If, and only if, it becomes exceptionally large. Larger that it would have to be for your normal
operation. And only if you actually gain something by doing the shrink (i.e., you really need the
disk space). See below:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jose" <Jose@.discussions.microsoft.com> wrote in message
news:7D430938-4FC7-4D15-A4EF-6ED05EF6E386@.microsoft.com...
> Hi Ben and Uri.
> Thanks for your comments!.
> And I made a mistake, is not "GB" is "MB"..sorry.
> I've asked if every time that I run the backup for the LOG files I'm
> truncating the log, because my client is using NAVISION from Microsoft, and
> the company who did the installation, gave me some instructions for the
> backup, and to be honest I dont know too much about SQL commands, that is way
> I did using the Management Studio. And they told me to create :
> - 1 full backup every day
> - 1 log backup every 15 minutes
> - at the end of the day truncate the log to keep the log small...
> But if backing up the log and I'm truncating it at the same time, I'll need
> to create other job to truncate the log at the end of the day? I guess not.
> Right now, when I run the log backup, SQL creates a directory for each
> database and it creates every log file for each database every 15 minutes and
> the size of the log is 500Kb.
> All the files backup are on the server and I have a task who move files and
> logs to other (external) unit backup, so, at least I have 2 places to find if
> something happens to the datasbase and 1 place if something happens to the
> server and to be more protected, those database are been replicated online to
> 1 external server outside of the company.
> When I will really need shrink a database and/or Log File?
> Thank you so much for all your comments!
> Have a nice day!
> Jose
> "Ben Nevarez" wrote:
>> Hola Jose,
>> Some comments here:
>> > The Log file is : 850Gb
>> Is this a typo? This is too big for a transaction log file. You should
>> manually shrink it, but probably only once. Do not schedule a job to shrink
>> it periodically.
>> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
>> > have to run other task to truncate the log?
>> After the transaction log is shrunk manually doing a backup every 15 minutes
>> should keep it in a acceptable size.
>> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
>> > in case the database crashed?
>> Yes, if you still have access to the backup folder. Are these folders on the
>> same computer? Also, if you are lucky and have access to the tail of the log
>> you can recover your database to the point of failure, losing no data at all.
>> > - How often I have to shrink the database file and logs?
>> Do not shrink any file on a job or maintenance plan. Do it only manually
>> when you really need it.
>> Hope this helps,
>> Ben Nevarez
>>
>>
>> "Jose" wrote:
>> > Hello everybody,
>> > I'm using SQL 2005 for few weeks, and I'd like to ask you something about
>> > the Backup and Restore Plan. I need to create a complete DR solution for my
>> > client.
>> > So, what I did was: (using Management Studio-Maintenance Plan)
>> > - All the user database are on Full Recovery Mode.
>> > - Run a Full User Database backup every day at 1:00am and save it on day's
>> > folder, it means the backup for Monday will go to Monday's backup folder and
>> > so on.
>> > - At 6:00am (normally the time they start to work), I start to do a Log
>> > Backup every 15 minutes until 11:00pm aprox.
>> >
>> > My question is:
>> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
>> > have to run other task to truncate the log?
>> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
>> > in case the database crashed?
>> > - How often I have to shrink the database file and logs?
>> >
>> > The Primary data file is : 570Gb
>> > The Secondary data file is : 130Gb
>> > The Log file is : 850Gb
>> >
>> > I'm not worry about the space because the server and the backup unit have a
>> > lot of space.
>> >
>> > Any ideas or opinions'
>> > Thank you!
>> >
>> > Jose

BACKUP SERVICE MASTER KEY TO FILE

Hello,
I would to create a backup file dynamically (the filename will contain
the hostname of the DB).
any idea how can I use a variable for that?
(a variable is easy to use for a regular database backup, but not for
a master key).
I wrote on the comment the error I get.
thanks, Yaniv
use master;
declare @.tmpStr varchar(200)
set @.tmpStr = 'c:\temp\DB_service_2.key'
-- working
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
ENCRYPTION BY
PASSWORD = 't1t1t1t1t1'
-- error: Incorrect syntax near '@.tmpStr'
BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD =
't1t1t1t1t1'
goYou can use dynamic SQL. Built the BACKUP command in a TSQL varchar variable
, then execute it:
EXEC(@.sql)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yaniv.harpaz@.gmail.com> wrote in message
news:1170238336.716966.152710@.p10g2000cwp.googlegroups.com...
> Hello,
> I would to create a backup file dynamically (the filename will contain
> the hostname of the DB).
> any idea how can I use a variable for that?
> (a variable is easy to use for a regular database backup, but not for
> a master key).
> I wrote on the comment the error I get.
> thanks, Yaniv
> use master;
> declare @.tmpStr varchar(200)
> set @.tmpStr = 'c:\temp\DB_service_2.key'
> -- working
> BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
> ENCRYPTION BY
> PASSWORD = 't1t1t1t1t1'
> -- error: Incorrect syntax near '@.tmpStr'
> BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD =
> 't1t1t1t1t1'
> go
>

BACKUP SERVICE MASTER KEY TO FILE

Hello,
I would to create a backup file dynamically (the filename will contain
the hostname of the DB).
any idea how can I use a variable for that?
(a variable is easy to use for a regular database backup, but not for
a master key).
I wrote on the comment the error I get.
thanks, Yaniv
use master;
declare @.tmpStr varchar(200)
set @.tmpStr = 'c:\temp\DB_service_2.key'
-- working
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
ENCRYPTION BY
PASSWORD = 't1t1t1t1t1'
-- error: Incorrect syntax near '@.tmpStr'
BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD = 't1t1t1t1t1'
goYou can use dynamic SQL. Built the BACKUP command in a TSQL varchar variable, then execute it:
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yaniv.harpaz@.gmail.com> wrote in message
news:1170238336.716966.152710@.p10g2000cwp.googlegroups.com...
> Hello,
> I would to create a backup file dynamically (the filename will contain
> the hostname of the DB).
> any idea how can I use a variable for that?
> (a variable is easy to use for a regular database backup, but not for
> a master key).
> I wrote on the comment the error I get.
> thanks, Yaniv
> use master;
> declare @.tmpStr varchar(200)
> set @.tmpStr = 'c:\temp\DB_service_2.key'
> -- working
> BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp\DB_service.key'
> ENCRYPTION BY
> PASSWORD = 't1t1t1t1t1'
> -- error: Incorrect syntax near '@.tmpStr'
> BACKUP SERVICE MASTER KEY TO FILE = @.tmpStr ENCRYPTION BY PASSWORD => 't1t1t1t1t1'
> go
>

Backup script

I want to create a simple SQL 2005 database backup script to run a scheduled
times. This was easy with SQL 2000 but I can't find a similar process in
2005. Can anyone tell me where I can find it? Thanks.
David
David,
In SQL Server Management Studio under the node Management / Maintenance
Plans you can create a maintenance plan using the Maintenance Plan Wizard
that backs up the databases. If you back up to disk using the appropriate
BACK UP DATABASE settings you should also be sure to check and fill out the
information for the MAINTENANCE CLEANUP TASK so that old backups get
deleted.
Is that what you are looking for?
RLF
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>
|||Alternatively,
In SQL Server 2005 every nearly window is scriptable. So, go to a database
and right click on it and Tasks\Back up... make your changes from the GUI
and you'll see a button on top of the Back Up Database window which is
labeled "Script". There is an arrow near it, click on it and you'll see your
choices.
You can use these scripts to create Jobs from the SQL Server Agent.
Ekrem nsoy
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>
|||Thank you both. That is what I needed.
David
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:D82FA9CF-70A7-4802-A7C2-8C1BA3B053E8@.microsoft.com...
> Alternatively,
> In SQL Server 2005 every nearly window is scriptable. So, go to a database
> and right click on it and Tasks\Back up... make your changes from the GUI
> and you'll see a button on top of the Back Up Database window which is
> labeled "Script". There is an arrow near it, click on it and you'll see
> your choices.
> You can use these scripts to create Jobs from the SQL Server Agent.
> --
> Ekrem nsoy
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>
sql

Backup script

I want to create a simple SQL 2005 database backup script to run a scheduled
times. This was easy with SQL 2000 but I can't find a similar process in
2005. Can anyone tell me where I can find it? Thanks.
DavidDavid,
In SQL Server Management Studio under the node Management / Maintenance
Plans you can create a maintenance plan using the Maintenance Plan Wizard
that backs up the databases. If you back up to disk using the appropriate
BACK UP DATABASE settings you should also be sure to check and fill out the
information for the MAINTENANCE CLEANUP TASK so that old backups get
deleted.
Is that what you are looking for?
RLF
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>|||Alternatively,
In SQL Server 2005 every nearly window is scriptable. So, go to a database
and right click on it and Tasks\Back up... make your changes from the GUI
and you'll see a button on top of the Back Up Database window which is
labeled "Script". There is an arrow near it, click on it and you'll see your
choices.
You can use these scripts to create Jobs from the SQL Server Agent.
Ekrem nsoy
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>|||Thank you both. That is what I needed.
David
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:D82FA9CF-70A7-4802-A7C2-8C1BA3B053E8@.microsoft.com...
> Alternatively,
> In SQL Server 2005 every nearly window is scriptable. So, go to a database
> and right click on it and Tasks\Back up... make your changes from the GUI
> and you'll see a button on top of the Back Up Database window which is
> labeled "Script". There is an arrow near it, click on it and you'll see
> your choices.
> You can use these scripts to create Jobs from the SQL Server Agent.
> --
> Ekrem nsoy
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>

Backup script

I want to create a simple SQL 2005 database backup script to run a scheduled
times. This was easy with SQL 2000 but I can't find a similar process in
2005. Can anyone tell me where I can find it? Thanks.
DavidDavid,
In SQL Server Management Studio under the node Management / Maintenance
Plans you can create a maintenance plan using the Maintenance Plan Wizard
that backs up the databases. If you back up to disk using the appropriate
BACK UP DATABASE settings you should also be sure to check and fill out the
information for the MAINTENANCE CLEANUP TASK so that old backups get
deleted.
Is that what you are looking for?
RLF
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>|||Alternatively,
In SQL Server 2005 every nearly window is scriptable. So, go to a database
and right click on it and Tasks\Back up... make your changes from the GUI
and you'll see a button on top of the Back Up Database window which is
labeled "Script". There is an arrow near it, click on it and you'll see your
choices.
You can use these scripts to create Jobs from the SQL Server Agent.
--
Ekrem Önsoy
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>|||Thank you both. That is what I needed.
David
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:D82FA9CF-70A7-4802-A7C2-8C1BA3B053E8@.microsoft.com...
> Alternatively,
> In SQL Server 2005 every nearly window is scriptable. So, go to a database
> and right click on it and Tasks\Back up... make your changes from the GUI
> and you'll see a button on top of the Back Up Database window which is
> labeled "Script". There is an arrow near it, click on it and you'll see
> your choices.
> You can use these scripts to create Jobs from the SQL Server Agent.
> --
> Ekrem Önsoy
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>>I want to create a simple SQL 2005 database backup script to run a
>>scheduled times. This was easy with SQL 2000 but I can't find a similar
>>process in 2005. Can anyone tell me where I can find it? Thanks.
>> David
>

Backup Schedule Troubles

Some how, I've managed to create a backup (disk backup) with three different
schedules. At 6pm each day, the database backups three times (three
different names).
What I'd like to have is... each Monday the previous Monday's backup would
be overwritten, each Tuesday the previous Tuesday's backup would be
overwritten, etc...
Now, I can't figure out how to remove the schedules, as it appears that I
only see one schedule and destination when I go in to the backup.
How do I get to a "clean slate" and accomplish my ideal backup scenario.
Thanks.Check under management, SQL Server Agent, Jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"09evaca" <09evaca@.discussions.microsoft.com> wrote in message
news:D8D3EAF7-90A5-495C-B9D5-37E933FDF24A@.microsoft.com...
> Some how, I've managed to create a backup (disk backup) with three different
> schedules. At 6pm each day, the database backups three times (three
> different names).
> What I'd like to have is... each Monday the previous Monday's backup would
> be overwritten, each Tuesday the previous Tuesday's backup would be
> overwritten, etc...
> Now, I can't figure out how to remove the schedules, as it appears that I
> only see one schedule and destination when I go in to the backup.
> How do I get to a "clean slate" and accomplish my ideal backup scenario.
> Thanks.

Backup Schedule Troubles

Some how, I've managed to create a backup (disk backup) with three different
schedules. At 6pm each day, the database backups three times (three
different names).
What I'd like to have is... each Monday the previous Monday's backup would
be overwritten, each Tuesday the previous Tuesday's backup would be
overwritten, etc...
Now, I can't figure out how to remove the schedules, as it appears that I
only see one schedule and destination when I go in to the backup.
How do I get to a "clean slate" and accomplish my ideal backup scenario.
Thanks.
Check under management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"09evaca" <09evaca@.discussions.microsoft.com> wrote in message
news:D8D3EAF7-90A5-495C-B9D5-37E933FDF24A@.microsoft.com...
> Some how, I've managed to create a backup (disk backup) with three different
> schedules. At 6pm each day, the database backups three times (three
> different names).
> What I'd like to have is... each Monday the previous Monday's backup would
> be overwritten, each Tuesday the previous Tuesday's backup would be
> overwritten, etc...
> Now, I can't figure out how to remove the schedules, as it appears that I
> only see one schedule and destination when I go in to the backup.
> How do I get to a "clean slate" and accomplish my ideal backup scenario.
> Thanks.
sql

Backup Schedule Troubles

Some how, I've managed to create a backup (disk backup) with three different
schedules. At 6pm each day, the database backups three times (three
different names).
What I'd like to have is... each Monday the previous Monday's backup would
be overwritten, each Tuesday the previous Tuesday's backup would be
overwritten, etc...
Now, I can't figure out how to remove the schedules, as it appears that I
only see one schedule and destination when I go in to the backup.
How do I get to a "clean slate" and accomplish my ideal backup scenario.
Thanks.Check under management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"09evaca" <09evaca@.discussions.microsoft.com> wrote in message
news:D8D3EAF7-90A5-495C-B9D5-37E933FDF24A@.microsoft.com...
> Some how, I've managed to create a backup (disk backup) with three differe
nt
> schedules. At 6pm each day, the database backups three times (three
> different names).
> What I'd like to have is... each Monday the previous Monday's backup would
> be overwritten, each Tuesday the previous Tuesday's backup would be
> overwritten, etc...
> Now, I can't figure out how to remove the schedules, as it appears that I
> only see one schedule and destination when I go in to the backup.
> How do I get to a "clean slate" and accomplish my ideal backup scenario.
> Thanks.

Backup Schedule

Hi
In MSDE2000 i can create backup from database and schedule it
how can i do this with SQLExpr.2005?
Mex
hi Mex,
Meelis Lilbok wrote:
> Hi
> In MSDE2000 i can create backup from database and schedule it
> how can i do this with SQLExpr.2005?
SQLExpress does not (very unfortunately) provide the SQL Server Agent..
but you can achieve a similar result scheduling your maintenance activities
(say a .sql file) via the AT or SCHTASK operating system scheduler..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backup Question Old Files still attached

I was setting up the backup for a new database last evening and seem
to have created a small problem. Although I was able to create a
backup and restore it, in the process I seem to have left a couple of
other copies of the database files in a state where they cannot be
moved, deleted or copied.
I started by right clicking on the database and selecting Back Up. I
then did a Full Backup of the Database (not the Files and
Filegroups). I then detached the original database (probably a
mistake) and went to the Databases level, right-clicked and selected
Restore Database. I did not find the name of my database in the "To
Database:" list so I typed it in and proceeded to select the From
Database (which did have the name of my database and the correct
backup time) and restored the database.
The database seemed to work fine.
However when I checked the files I found not only my original
DatabaseName_Data.MDF and DatabaseName_Log.LDF files, but two new
ones
DatabaseName.MDF and DatabaseName_1.LDF. These last two files are
the
actual files being used by the restored database.
I would now like to do two things:
1) Move the original DatabaseName_Data.MDF and DatabaseName_Log.LDF
files to a new location, and
2) Rename the files that are actually being used from
DatabaseName.MDF
and DatabaseName_1.LDF to DatabaseName_Data.MDF and
DatabaseName_Log.LDF as they were before.
However I can't seem to do anything with the original files because
they seem to still be attached to the server. How can I SAFELY
accomplish what I want without endangering my good database (with the
wrong file names)?
Thanks.Detach the databases and do whatevery you want. Move them, rename them or
whatever and then reattach whichever one you want to use.
--
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188538011.871157.326450@.q5g2000prf.googlegroups.com...
> I was setting up the backup for a new database last evening and seem
> to have created a small problem. Although I was able to create a
> backup and restore it, in the process I seem to have left a couple of
> other copies of the database files in a state where they cannot be
> moved, deleted or copied.
> I started by right clicking on the database and selecting Back Up. I
> then did a Full Backup of the Database (not the Files and
> Filegroups). I then detached the original database (probably a
> mistake) and went to the Databases level, right-clicked and selected
> Restore Database. I did not find the name of my database in the "To
> Database:" list so I typed it in and proceeded to select the From
> Database (which did have the name of my database and the correct
> backup time) and restored the database.
>
> The database seemed to work fine.
>
> However when I checked the files I found not only my original
> DatabaseName_Data.MDF and DatabaseName_Log.LDF files, but two new
> ones
> DatabaseName.MDF and DatabaseName_1.LDF. These last two files are
> the
> actual files being used by the restored database.
>
> I would now like to do two things:
>
> 1) Move the original DatabaseName_Data.MDF and DatabaseName_Log.LDF
> files to a new location, and
> 2) Rename the files that are actually being used from
> DatabaseName.MDF
> and DatabaseName_1.LDF to DatabaseName_Data.MDF and
> DatabaseName_Log.LDF as they were before.
>
> However I can't seem to do anything with the original files because
> they seem to still be attached to the server. How can I SAFELY
> accomplish what I want without endangering my good database (with the
> wrong file names)?
>
> Thanks.
>

backup question

sql server 2000 sp3.
I would like to backup a database frequently, maybe every 1/2 hour. I would
like to backup to a file. Can a create a backup device (file) on a network
server so the backup goes directly to another server? Or do I have to backup
to the lcoal hard drive and schedule a script to copy it off the drive?
any info is appreciated. thanks.What you can do is map a network drive on your server, then you can
create a backup device on that mapped drive
--
Eric Li
SQL DBA
MCDBA
djc wrote:
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I would
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to backup
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||If the service account for the SQL Server service has permissions on the drive, then yes, you can use a
network drive. Address it using UNC naming.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I would
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to backup
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||Hi,
Do not perform the FULL database backup every 30 minutes. This will create
issue once your
database grow big. I recommend you to do a transaction log backup every 30
minutes to a remote server and
perform the FULL database backup once every night.
Prerequsites to do a backup remotely
---
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Transaction log backup
--
BACKUP log <dbname> to disk='\\computername\sharename\dbname_tran1.trn'
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
MCDBA
"djc" <noone@.nowhere.com> wrote in message
news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I
would
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to
backup
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||thats what I was thinking. Will I need to log on as the SQLServer Service
account and map the drive? In other words, I was concerned that if I map the
drive under my account it would not exist under the context of the scheduled
task?
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eB2F$XxUEHA.384@.TK2MSFTNGP10.phx.gbl...
> What you can do is map a network drive on your server, then you can
> create a backup device on that mapped drive
> --
> Eric Li
> SQL DBA
> MCDBA
> djc wrote:
> > sql server 2000 sp3.
> >
> > I would like to backup a database frequently, maybe every 1/2 hour. I
would
> > like to backup to a file. Can a create a backup device (file) on a
network
> > server so the backup goes directly to another server? Or do I have to
backup
> > to the lcoal hard drive and schedule a script to copy it off the drive?
> >
> > any info is appreciated. thanks.
> >
> >|||thanks for the reply.
can I do this through GUI Enterprise manager? (under database, management,
backup) I am not very familiar with TSQL at this point.
I think I tried to create a new backup device using a UNC name and it would
not let me? (which leads me to believe what your refering to has to be done
using TSQL)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> If the service account for the SQL Server service has permissions on the
drive, then yes, you can use a
> network drive. Address it using UNC naming.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > sql server 2000 sp3.
> >
> > I would like to backup a database frequently, maybe every 1/2 hour. I
would
> > like to backup to a file. Can a create a backup device (file) on a
network
> > server so the backup goes directly to another server? Or do I have to
backup
> > to the lcoal hard drive and schedule a script to copy it off the drive?
> >
> > any info is appreciated. thanks.
> >
> >
>|||thanks for the reply. Is using TSQL the only way to accomplish this? or
could I use enterprise manager gui. I'm not that familiar with TSQL at this
point.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OfzbFf4UEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Do not perform the FULL database backup every 30 minutes. This will create
> issue once your
> database grow big. I recommend you to do a transaction log backup every 30
> minutes to a remote server and
> perform the FULL database backup once every night.
> Prerequsites to do a backup remotely
> ---
> There are Few Pre requisites to do backup remotely;
> 1. You Should start SQL server using Domain user who got access to remote
> machine Share
> 2. Should have share in the remote machine
> 3. If you need to schedule this as a job then SQL Agent should use the
same
> Domain user in which SQL server was started
> 4. Restart the services
> Now you can execute the Backup script with UNC path
> BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak'
with
> init
> Transaction log backup
> --
> BACKUP log <dbname> to disk='\\computername\sharename\dbname_tran1.trn'
> Note:
> Backup to remote machine will not work if you start SQL server using Local
> system account
> Thanks
> Hari
> MCDBA
>
>
> "djc" <noone@.nowhere.com> wrote in message
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > sql server 2000 sp3.
> >
> > I would like to backup a database frequently, maybe every 1/2 hour. I
> would
> > like to backup to a file. Can a create a backup device (file) on a
network
> > server so the backup goes directly to another server? Or do I have to
> backup
> > to the lcoal hard drive and schedule a script to copy it off the drive?
> >
> > any info is appreciated. thanks.
> >
> >
>|||Yep, you can create the backup device using EM (I just tried it), you just have to type the full UNC name, and
ignore the warning that EM cannot confirm that you have access to the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...
> thanks for the reply.
> can I do this through GUI Enterprise manager? (under database, management,
> backup) I am not very familiar with TSQL at this point.
> I think I tried to create a new backup device using a UNC name and it would
> not let me? (which leads me to believe what your refering to has to be done
> using TSQL)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> > If the service account for the SQL Server service has permissions on the
> drive, then yes, you can use a
> > network drive. Address it using UNC naming.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "djc" <noone@.nowhere.com> wrote in message
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > sql server 2000 sp3.
> > >
> > > I would like to backup a database frequently, maybe every 1/2 hour. I
> would
> > > like to backup to a file. Can a create a backup device (file) on a
> network
> > > server so the backup goes directly to another server? Or do I have to
> backup
> > > to the lcoal hard drive and schedule a script to copy it off the drive?
> > >
> > > any info is appreciated. thanks.
> > >
> > >
> >
> >
>|||I should have added that your problem can very well be permissions problem. Check out the service account for
SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ef5Wi15UEHA.544@.TK2MSFTNGP11.phx.gbl...
> Yep, you can create the backup device using EM (I just tried it), you just have to type the full UNC name,
and
> ignore the warning that EM cannot confirm that you have access to the file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > thanks for the reply.
> >
> > can I do this through GUI Enterprise manager? (under database, management,
> > backup) I am not very familiar with TSQL at this point.
> >
> > I think I tried to create a new backup device using a UNC name and it would
> > not let me? (which leads me to believe what your refering to has to be done
> > using TSQL)
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> > > If the service account for the SQL Server service has permissions on the
> > drive, then yes, you can use a
> > > network drive. Address it using UNC naming.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "djc" <noone@.nowhere.com> wrote in message
> > news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > > sql server 2000 sp3.
> > > >
> > > > I would like to backup a database frequently, maybe every 1/2 hour. I
> > would
> > > > like to backup to a file. Can a create a backup device (file) on a
> > network
> > > > server so the backup goes directly to another server? Or do I have to
> > backup
> > > > to the lcoal hard drive and schedule a script to copy it off the drive?
> > > >
> > > > any info is appreciated. thanks.
> > > >
> > > >
> > >
> > >
> >
> >
>|||ok. Great. I had only very quickly given it one attempt before... and I had
an administrative share in the path (\\server\d$ ...etc...) so maybe that
had something to do with it as well. I will also setup the appropriate
permissions for the sql service account... Thanks for the info!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ef5Wi15UEHA.544@.TK2MSFTNGP11.phx.gbl...
> Yep, you can create the backup device using EM (I just tried it), you just
have to type the full UNC name, and
> ignore the warning that EM cannot confirm that you have access to the
file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > thanks for the reply.
> >
> > can I do this through GUI Enterprise manager? (under database,
management,
> > backup) I am not very familiar with TSQL at this point.
> >
> > I think I tried to create a new backup device using a UNC name and it
would
> > not let me? (which leads me to believe what your refering to has to be
done
> > using TSQL)
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> > > If the service account for the SQL Server service has permissions on
the
> > drive, then yes, you can use a
> > > network drive. Address it using UNC naming.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "djc" <noone@.nowhere.com> wrote in message
> > news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > > > sql server 2000 sp3.
> > > >
> > > > I would like to backup a database frequently, maybe every 1/2 hour.
I
> > would
> > > > like to backup to a file. Can a create a backup device (file) on a
> > network
> > > > server so the backup goes directly to another server? Or do I have
to
> > backup
> > > > to the lcoal hard drive and schedule a script to copy it off the
drive?
> > > >
> > > > any info is appreciated. thanks.
> > > >
> > > >
> > >
> > >
> >
> >
>

backup question

sql server 2000 sp3.
I would like to backup a database frequently, maybe every 1/2 hour. I would
like to backup to a file. Can a create a backup device (file) on a network
server so the backup goes directly to another server? Or do I have to backup
to the lcoal hard drive and schedule a script to copy it off the drive?
any info is appreciated. thanks.What you can do is map a network drive on your server, then you can
create a backup device on that mapped drive
Eric Li
SQL DBA
MCDBA
djc wrote:

> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I woul
d
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to back
up
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||If the service account for the SQL Server service has permissions on the dri
ve, then yes, you can use a
network drive. Address it using UNC naming.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...[vbc
ol=seagreen]
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I woul
d
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to back
up
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>[/vbcol]|||Hi,
Do not perform the FULL database backup every 30 minutes. This will create
issue once your
database grow big. I recommend you to do a transaction log backup every 30
minutes to a remote server and
perform the FULL database backup once every night.
Prerequsites to do a backup remotely
---
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Transaction log backup
--
BACKUP log <dbname> to disk='\\computername\sharename\dbname_tr
an1.trn'
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
MCDBA
"djc" <noone@.nowhere.com> wrote in message
news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I
would
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to
backup
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||thats what I was thinking. Will I need to log on as the SQLServer Service
account and map the drive? In other words, I was concerned that if I map the
drive under my account it would not exist under the context of the scheduled
task?
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eB2F$XxUEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> What you can do is map a network drive on your server, then you can
> create a backup device on that mapped drive
> --
> Eric Li
> SQL DBA
> MCDBA
> djc wrote:
>
would[vbcol=seagreen]
network[vbcol=seagreen]
backup[vbcol=seagreen]|||thanks for the reply.
can I do this through GUI Enterprise manager? (under database, management,
backup) I am not very familiar with TSQL at this point.
I think I tried to create a new backup device using a UNC name and it would
not let me? (which leads me to believe what your refering to has to be done
using TSQL)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> If the service account for the SQL Server service has permissions on the
drive, then yes, you can use a
> network drive. Address it using UNC naming.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
would[vbcol=seagreen]
network[vbcol=seagreen]
backup[vbcol=seagreen]
>|||thanks for the reply. Is using TSQL the only way to accomplish this? or
could I use enterprise manager gui. I'm not that familiar with TSQL at this
point.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OfzbFf4UEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Do not perform the FULL database backup every 30 minutes. This will create
> issue once your
> database grow big. I recommend you to do a transaction log backup every 30
> minutes to a remote server and
> perform the FULL database backup once every night.
> Prerequsites to do a backup remotely
> ---
> There are Few Pre requisites to do backup remotely;
> 1. You Should start SQL server using Domain user who got access to remote
> machine Share
> 2. Should have share in the remote machine
> 3. If you need to schedule this as a job then SQL Agent should use the
same
> Domain user in which SQL server was started
> 4. Restart the services
> Now you can execute the Backup script with UNC path
> BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak'
with
> init
> Transaction log backup
> --
> BACKUP log <dbname> to disk='\\computername\sharename\dbname_tr
an1.trn'
> Note:
> Backup to remote machine will not work if you start SQL server using Local
> system account
> Thanks
> Hari
> MCDBA
>
>
> "djc" <noone@.nowhere.com> wrote in message
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> would
network[vbcol=seagreen]
> backup
>|||Yep, you can create the backup device using EM (I just tried it), you just h
ave to type the full UNC name, and
ignore the warning that EM cannot confirm that you have access to the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbc
ol=seagreen]
> thanks for the reply.
> can I do this through GUI Enterprise manager? (under database, management,
> backup) I am not very familiar with TSQL at this point.
> I think I tried to create a new backup device using a UNC name and it woul
d
> not let me? (which leads me to believe what your refering to has to be don
e
> using TSQL)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> drive, then yes, you can use a
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> would
> network
> backup
>[/vbcol]|||I should have added that your problem can very well be permissions problem.
Check out the service account for
SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:ef5Wi15UEHA.544@.TK2MSFTNGP11.phx.gbl...
> Yep, you can create the backup device using EM (I just tried it), you just have to
type the full UNC name,
and
> ignore the warning that EM cannot confirm that you have access to the file
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message news:u1np1n5UEHA.1356@.TK2MSFTNG
P09.phx.gbl...
>|||ok. Great. I had only very quickly given it one attempt before... and I had
an administrative share in the path (\\server\d$ ...etc...) so maybe that
had something to do with it as well. I will also setup the appropriate
permissions for the sql service account... Thanks for the info!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ef5Wi15UEHA.544@.TK2MSFTNGP11.phx.gbl...
> Yep, you can create the backup device using EM (I just tried it), you just
have to type the full UNC name, and
> ignore the warning that EM cannot confirm that you have access to the
file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...
management,[vbcol=seagreen]
would[vbcol=seagreen]
done[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
I[vbcol=seagreen]
to[vbcol=seagreen]
drive?[vbcol=seagreen]
>