Backup Standards and Suggestions

Thanks Ekrem.
In your opinion which one is better, using backup to disk? Or directly to
tape; considering I can get tape drive hooked directly to SQL Server.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
It depends on the scenarios. You can see some scenarios from the following
articles.
Some of them:
http://www.smallbusinesscomputing.com/testdrive/article.php/3528326
http://www.storagesearch.com/engenio-art2.html
Ekrem ?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:79E1C65C-5732-4957-B18B-14B765A9D558@.microsoft.com...
> Thanks Ekrem.
> In your opinion which one is better, using backup to disk? Or directly to
> tape; considering I can get tape drive hooked directly to SQL Server.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
|||Thanks! Those are good links, now I can backup some of what I say to
management ;-). Thanks again.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||I'm glad they helped, good luck! =)
Ekrem ?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:59BFA06B-DCAE-4840-8CBF-2C36A946D056@.microsoft.com...
> Thanks! Those are good links, now I can backup some of what I say to
> management ;-). Thanks again.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
sql

Backup SQL Server DB on Network Drive Big Problem. Please help

Hi All,
I want to using T-SQL execute BACKUP DATABASE command job on the SQL
Server Agent. But, it occured the following error:
My Job Owner is "sa", Category is Database Maintenance
My execute command is ..
BACKUP DATABASE NorthWind TO DISK =
'\\servername\Backup\NorthWind.BAK'
The job failed. The Job was invoked by User Machine\Administrator.
The last step to run was step 1 (DBt Backup).
Please advise.Hi
Make sure that your SQL Server Services are running under a Domain Account
and that the user has correct permissions to the share.
To see the error message, run the T-SQL from Query Analyser.
Regards
Mike
"Kelvin" wrote:

> Hi All,
> I want to using T-SQL execute BACKUP DATABASE command job on the SQL
> Server Agent. But, it occured the following error:
> My Job Owner is "sa", Category is Database Maintenance
> My execute command is ..
> BACKUP DATABASE NorthWind TO DISK =
> '\\servername\Backup\NorthWind.BAK'
> The job failed. The Job was invoked by User Machine\Administrator.
> The last step to run was step 1 (DBt Backup).
> Please advise.
>|||Hi Mike,
How to check SQL Server Services are running under a Domain Account and
the user has correct permissions to the share. ' Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||1. On the SQL Server, go to Control Panel/Administrative Tools/Services
and make sure the MSSQLServer service is running under an account
"Domain\User" instead of Local Service.
2. Go to the share on the network drive and look at Sharing and Security to
make sure that same user has read/write permissions on the share. You can
probably have your network group do this for you.
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:#OWSbgn7EHA.3696@.TK2MSFTNGP10.phx.gbl...
> Hi Mike,
>
> How to check SQL Server Services are running under a Domain Account and
> the user has correct permissions to the share. ' Please advise
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi Mike,
I checked that MSSQLServer service is running under Local Administrator
Account, and I also change the job owner to Local Administrator Account,
but it still occurs following error:
The job failed. The Job was invoked by User LocalMachine\Administrator.
The last step to run was step 1 (MyTesting Backup).
If the Backup Network Drive of PC just only running on Workgroup. How to
using T-SQL Backup Network Drive ?
Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:eJsI9wu7EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi Mike,
> I checked that MSSQLServer service is running under Local Administrator
> Account, and I also change the job owner to Local Administrator Account,
> but it still occurs following error:
> The job failed. The Job was invoked by User LocalMachine\Administrator.
Right. It can NOT run under a Local Administrator account.
It needs to be a domain account.

> The last step to run was step 1 (MyTesting Backup).
> If the Backup Network Drive of PC just only running on Workgroup. How to
> using T-SQL Backup Network Drive ?
You can TRY to give it a username and password that the computer containing
the network drive also has, but I'm not sure it would would work.

> Please advise.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi,
When I change the owner to "Domain Administrator" to execute the T-SQL
to backup database to the network drive.
It display the following error:
The job failed. Unable to determine if the owner (Domain\Administrator)
of job Test Backup has server access (reason: Could not obtain
information about Windows NT group/user 'Domain\Administrator'.
[SQLSTATE 42000] (Error 8198)).
But the Shared Network Drive PC haven't connect Domain, runing on
workgroup only. I can't directly to access that PC. Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Try to change the job owner to sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:ORQOrBy7EHA.3696@.TK2MSFTNGP10.phx.g
bl...
> Hi,
> When I change the owner to "Domain Administrator" to execute the T-SQL
> to backup database to the network drive.
> It display the following error:
> The job failed. Unable to determine if the owner (Domain\Administrator)
> of job Test Backup has server access (reason: Could not obtain
> information about Windows NT group/user 'Domain\Administrator'.
> [SQLSTATE 42000] (Error 8198)).
> But the Shared Network Drive PC haven't connect Domain, runing on
> workgroup only. I can't directly to access that PC. Please advise.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi.. I try to using sa, but it is same result.
Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Did you get the exact same error message after changing job owner to "sa". T
hat would be strange, as
the error messages you posted referred to "Domain\Administrator"...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:uEl5LwC8EHA.3236@.TK2MSFTNGP15.phx.g
bl...
>
> Hi.. I try to using sa, but it is same result.
> Please advise
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Backup SQL Server DB on Network Drive Big Problem. Please help

Hi All,
I want to using T-SQL execute BACKUP DATABASE command job on the SQL
Server Agent. But, it occured the following error:
My Job Owner is "sa", Category is Database Maintenance
My execute command is ..
BACKUP DATABASE NorthWind TO DISK =
'\\servername\Backup\NorthWind.BAK'
The job failed. The Job was invoked by User Machine\Administrator.
The last step to run was step 1 (DBt Backup).
Please advise.
Hi
Make sure that your SQL Server Services are running under a Domain Account
and that the user has correct permissions to the share.
To see the error message, run the T-SQL from Query Analyser.
Regards
Mike
"Kelvin" wrote:

> Hi All,
> I want to using T-SQL execute BACKUP DATABASE command job on the SQL
> Server Agent. But, it occured the following error:
> My Job Owner is "sa", Category is Database Maintenance
> My execute command is ..
> BACKUP DATABASE NorthWind TO DISK =
> '\\servername\Backup\NorthWind.BAK'
> The job failed. The Job was invoked by User Machine\Administrator.
> The last step to run was step 1 (DBt Backup).
> Please advise.
>
|||Hi Mike,
How to check SQL Server Services are running under a Domain Account and
the user has correct permissions to the share. ? Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||1. On the SQL Server, go to Control Panel/Administrative Tools/Services
and make sure the MSSQLServer service is running under an account
"Domain\User" instead of Local Service.
2. Go to the share on the network drive and look at Sharing and Security to
make sure that same user has read/write permissions on the share. You can
probably have your network group do this for you.
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:#OWSbgn7EHA.3696@.TK2MSFTNGP10.phx.gbl...
> Hi Mike,
>
> How to check SQL Server Services are running under a Domain Account and
> the user has correct permissions to the share. ? Please advise
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Mike,
I checked that MSSQLServer service is running under Local Administrator
Account, and I also change the job owner to Local Administrator Account,
but it still occurs following error:
The job failed. The Job was invoked by User LocalMachine\Administrator.
The last step to run was step 1 (MyTesting Backup).
If the Backup Network Drive of PC just only running on Workgroup. How to
using T-SQL Backup Network Drive ?
Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:eJsI9wu7EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi Mike,
> I checked that MSSQLServer service is running under Local Administrator
> Account, and I also change the job owner to Local Administrator Account,
> but it still occurs following error:
> The job failed. The Job was invoked by User LocalMachine\Administrator.
Right. It can NOT run under a Local Administrator account.
It needs to be a domain account.

> The last step to run was step 1 (MyTesting Backup).
> If the Backup Network Drive of PC just only running on Workgroup. How to
> using T-SQL Backup Network Drive ?
You can TRY to give it a username and password that the computer containing
the network drive also has, but I'm not sure it would would work.

> Please advise.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
When I change the owner to "Domain Administrator" to execute the T-SQL
to backup database to the network drive.
It display the following error:
The job failed. Unable to determine if the owner (Domain\Administrator)
of job Test Backup has server access (reason: Could not obtain
information about Windows NT group/user 'Domain\Administrator'.
[SQLSTATE 42000] (Error 8198)).
But the Shared Network Drive PC haven't connect Domain, runing on
workgroup only. I can't directly to access that PC. Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Try to change the job owner to sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:ORQOrBy7EHA.3696@.TK2MSFTNGP10.phx.gbl...
> Hi,
> When I change the owner to "Domain Administrator" to execute the T-SQL
> to backup database to the network drive.
> It display the following error:
> The job failed. Unable to determine if the owner (Domain\Administrator)
> of job Test Backup has server access (reason: Could not obtain
> information about Windows NT group/user 'Domain\Administrator'.
> [SQLSTATE 42000] (Error 8198)).
> But the Shared Network Drive PC haven't connect Domain, runing on
> workgroup only. I can't directly to access that PC. Please advise.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi.. I try to using sa, but it is same result.
Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Did you get the exact same error message after changing job owner to "sa". That would be strange, as
the error messages you posted referred to "Domain\Administrator"...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:uEl5LwC8EHA.3236@.TK2MSFTNGP15.phx.gbl...
>
> Hi.. I try to using sa, but it is same result.
> Please advise
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Backup SQL Server DB on Network Drive Big Problem. Please help

Hi All,
I want to using T-SQL execute BACKUP DATABASE command job on the SQL
Server Agent. But, it occured the following error:
My Job Owner is "sa", Category is Database Maintenance
My execute command is ..
BACKUP DATABASE NorthWind TO DISK = '\\servername\Backup\NorthWind.BAK'
The job failed. The Job was invoked by User Machine\Administrator.
The last step to run was step 1 (DBt Backup).
Please advise.Hi
Make sure that your SQL Server Services are running under a Domain Account
and that the user has correct permissions to the share.
To see the error message, run the T-SQL from Query Analyser.
Regards
Mike
"Kelvin" wrote:
> Hi All,
> I want to using T-SQL execute BACKUP DATABASE command job on the SQL
> Server Agent. But, it occured the following error:
> My Job Owner is "sa", Category is Database Maintenance
> My execute command is ..
> BACKUP DATABASE NorthWind TO DISK => '\\servername\Backup\NorthWind.BAK'
> The job failed. The Job was invoked by User Machine\Administrator.
> The last step to run was step 1 (DBt Backup).
> Please advise.
>sql

Backup Sql database to mapped network drive on another server prob

Until recently (stopped working this week) I have been able to backup my sql
database (on a windows 2000 server) to a mapped network drive(mapped to
another 2000 server). I am a user on both servers (administrator). I can see
the the drive in explorer. I can access the files.
I cannot see the drive in enterprise manager. Can someone help? Thanks.
> I cannot see the drive in enterprise manager. Can someone help? Thanks.
Backup to a network drive must be done with T-SQL Backup command (check it
in Books OnLine). Use UNC names, not mapped drives.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||I understand that's one way to do it and I appreciate your answer. As I said
earlier, I was backing up to a network drive manually and through the
maintenance. I have been doing this for the last 6 months. Something got
change and I don't know where to start. Any suggestions as to what I can take
a look would be appreciated.
"BarbS" wrote:

> Until recently (stopped working this week) I have been able to backup my sql
> database (on a windows 2000 server) to a mapped network drive(mapped to
> another 2000 server). I am a user on both servers (administrator). I can see
> the the drive in explorer. I can access the files.
> I cannot see the drive in enterprise manager. Can someone help? Thanks.
|||It's not just one way to do it , it is the recommended way to do it. What
you see as a user means nothing unless that account is the same one that SQL
Server runs under. It is that account that does the backup regardless of
who initiates it or how they initiate the backup. Mapped drives can easily
get blown away or become unusable when changes are made on either end. So
if you want to continue using a mapped drive (which no one will recommend)
you need to log on to the server (database server) under the account that
sql server is running under and make sure the drive is mapped properly and
it still has the correct permissions to see and write to that share. If not
then the backup can not work properly.
Andrew J. Kelly SQL MVP
"BarbS" <BarbS@.discussions.microsoft.com> wrote in message
news:4FFF5A0E-3592-428D-A732-58378275F3D2@.microsoft.com...[vbcol=seagreen]
>I understand that's one way to do it and I appreciate your answer. As I
>said
> earlier, I was backing up to a network drive manually and through the
> maintenance. I have been doing this for the last 6 months. Something got
> change and I don't know where to start. Any suggestions as to what I can
> take
> a look would be appreciated.
>
> "BarbS" wrote:

Backup Sql database to mapped network drive on another server prob

Until recently (stopped working this week) I have been able to backup my sql
database (on a Windows 2000 server) to a mapped network drive(mapped to
another 2000 server). I am a user on both servers (administrator). I can see
the the drive in explorer. I can access the files.
I cannot see the drive in enterprise manager. Can someone help? Thanks.> I cannot see the drive in enterprise manager. Can someone help? Thanks.
Backup to a network drive must be done with T-SQL Backup command (check it
in Books OnLine). Use UNC names, not mapped drives.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||I understand that's one way to do it and I appreciate your answer. As I said
earlier, I was backing up to a network drive manually and through the
maintenance. I have been doing this for the last 6 months. Something got
change and I don't know where to start. Any suggestions as to what I can tak
e
a look would be appreciated.
"BarbS" wrote:

> Until recently (stopped working this week) I have been able to backup my s
ql
> database (on a Windows 2000 server) to a mapped network drive(mapped to
> another 2000 server). I am a user on both servers (administrator). I can s
ee
> the the drive in explorer. I can access the files.
> I cannot see the drive in enterprise manager. Can someone help? Thanks.|||It's not just one way to do it , it is the recommended way to do it. What
you see as a user means nothing unless that account is the same one that SQL
Server runs under. It is that account that does the backup regardless of
who initiates it or how they initiate the backup. Mapped drives can easily
get blown away or become unusable when changes are made on either end. So
if you want to continue using a mapped drive (which no one will recommend)
you need to log on to the server (database server) under the account that
sql server is running under and make sure the drive is mapped properly and
it still has the correct permissions to see and write to that share. If not
then the backup can not work properly.
Andrew J. Kelly SQL MVP
"BarbS" <BarbS@.discussions.microsoft.com> wrote in message
news:4FFF5A0E-3592-428D-A732-58378275F3D2@.microsoft.com...[vbcol=seagreen]
>I understand that's one way to do it and I appreciate your answer. As I
>said
> earlier, I was backing up to a network drive manually and through the
> maintenance. I have been doing this for the last 6 months. Something got
> change and I don't know where to start. Any suggestions as to what I can
> take
> a look would be appreciated.
>
> "BarbS" wrote:
>

Backup security

Hi,
Our current backup process writes backup to a network drive and that is
transferred to a DVD on a daily basis. How can we prevent people from
installing this backup on the DVD if the DVD is ever stolen? Can the
backups be "password protected" or restricted to prevent un-authorized
restore?
Thanks.From BooksOnLine under BACKUP:
PASSWORD = { password | @.password_variable }
Sets the password for the backup set. PASSWORD is a character string. If a
password is defined for the backup set, the password must be supplied to
perform any restore operation from the backup set.
This may prevent most people from restoring the backup but does nothing to
prevent anyone from reading the file with a simple hex editor. You should
look at one of the 3rd party backup tools that do encryption among other
things.
www.red-gate.com
www.quest.com
Andrew J. Kelly SQL MVP
<RK> wrote in message news:%23Evnr5uIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Our current backup process writes backup to a network drive and that is
> transferred to a DVD on a daily basis. How can we prevent people from
> installing this backup on the DVD if the DVD is ever stolen? Can the
> backups be "password protected" or restricted to prevent un-authorized
> restore?
> Thanks.
>|||Windows has extensive cryptography capabilities that allow creation of
encryption and decryption software relatively easily. If relevant to your
requirements, there are many programs available at little or no cost that
can encrypt and decrypt the backup file(s) separately.
<RK> wrote in message news:%23Evnr5uIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Our current backup process writes backup to a network drive and that is
> transferred to a DVD on a daily basis. How can we prevent people from
> installing this backup on the DVD if the DVD is ever stolen? Can the
> backups be "password protected" or restricted to prevent un-authorized
> restore?
> Thanks.
>

Backup question

Not sure if this is the correct group......
The tape drive on our SQL Server 2000 box died and I have just had it
replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
problem I am having is that after every single backup job...or every step in
a backup job if it has multiple steps...the tape ejects on its own. I have
made no changes to the backup jobs, just changed the physical tape drive.
Any ideas where to start looking for the cause?
Thanks
Eric StewartCheck out the section in BooksOnLine for Backup and the Options UNLOAD and
NOUNLOAD. I suspect the old tape drive just didn't support this
functionality and you might now need to set NOUNLOAD.
--
Andrew J. Kelly
SQL Server MVP
"Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
> Not sure if this is the correct group......
> The tape drive on our SQL Server 2000 box died and I have just had it
> replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> problem I am having is that after every single backup job...or every step
in
> a backup job if it has multiple steps...the tape ejects on its own. I have
> made no changes to the backup jobs, just changed the physical tape drive.
> Any ideas where to start looking for the cause?
> Thanks
> Eric Stewart
>|||Thanks Andrew. I will check this out first thing in the morning!
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
> > Not sure if this is the correct group......
> >
> > The tape drive on our SQL Server 2000 box died and I have just had it
> > replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> > problem I am having is that after every single backup job...or every
step
> in
> > a backup job if it has multiple steps...the tape ejects on its own. I
have
> > made no changes to the backup jobs, just changed the physical tape
drive.
> > Any ideas where to start looking for the cause?
> >
> > Thanks
> >
> > Eric Stewart
> >
> >
>|||That was it!!!
Thanks again Andrew for the very helpful advice.
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
> > Not sure if this is the correct group......
> >
> > The tape drive on our SQL Server 2000 box died and I have just had it
> > replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> > problem I am having is that after every single backup job...or every
step
> in
> > a backup job if it has multiple steps...the tape ejects on its own. I
have
> > made no changes to the backup jobs, just changed the physical tape
drive.
> > Any ideas where to start looking for the cause?
> >
> > Thanks
> >
> > Eric Stewart
> >
> >
>sql

Backup question

Not sure if this is the correct group......
The tape drive on our SQL Server 2000 box died and I have just had it
replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
problem I am having is that after every single backup job...or every step in
a backup job if it has multiple steps...the tape ejects on its own. I have
made no changes to the backup jobs, just changed the physical tape drive.
Any ideas where to start looking for the cause?
Thanks
Eric StewartCheck out the section in BooksOnLine for Backup and the Options UNLOAD and
NOUNLOAD. I suspect the old tape drive just didn't support this
functionality and you might now need to set NOUNLOAD.
Andrew J. Kelly
SQL Server MVP
"Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...

quote:

---

> Not sure if this is the correct group......
> The tape drive on our SQL Server 2000 box died and I have just had it
> replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> problem I am having is that after every single backup job...or every step

---

in

quote:

---

> a backup job if it has multiple steps...the tape ejects on its own. I have
> made no changes to the backup jobs, just changed the physical tape drive.
> Any ideas where to start looking for the cause?
> Thanks
> Eric Stewart
>

---

|||Thanks Andrew. I will check this out first thing in the morning!
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...

quote:

---

> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
step[QUOTE]
> in
have[QUOTE]
drive.[QUOTE]
>

---

|||That was it!!!
Thanks again Andrew for the very helpful advice.
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...

quote:

---

> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
step[QUOTE]
> in
have[QUOTE]
drive.[QUOTE]
>

---

Backup practices ...

Isn't it better to backup databases and transaction logs to a different
drive than the data files? Both for reliability (if the drive dies you're
hurting since the backup is there as well) and effeciency (less contention
on the data drive). If you're backing up everything to the same drive as the
data, can this cause processor spikes? We do backup to tape as well, but
this is not as fast to recover from when there are a zillion users.
As you probably guessed, some pre-existing maintence plans are set up this
way and I'm thinking I should push to get them changed. But this may require
additional hardware so somebody has to write a check. You know the drill.
Thanks,
Bob Castleman
DBA Poseur"Bob Castleman" <nomail@.here> wrote in message
news:e$madNqaFHA.3684@.TK2MSFTNGP12.phx.gbl...
> Isn't it better to backup databases and transaction logs to a different
> drive than the data files? Both for reliability (if the drive dies you're
> hurting since the backup is there as well) and effeciency (less contention
> on the data drive). If you're backing up everything to the same drive as
the
> data, can this cause processor spikes? We do backup to tape as well, but
> this is not as fast to recover from when there are a zillion users.
> As you probably guessed, some pre-existing maintence plans are set up this
> way and I'm thinking I should push to get them changed. But this may
require
> additional hardware so somebody has to write a check. You know the drill.
> Thanks,
> Bob Castleman
> DBA Poseur
>
Actually Bob, you should always do your backups to the same local drives as
the active databases are on. In fact, I'm not sure why you are even
bothering with doing backups at all... <wink>
You are correct, a good backup (and recovery) strategy should be
implemented. Backing up to the same hard disks is dependent on a lot of
different factors.
1. How much activity is going on during the backup process?
2. How big are the backups that we are talking about?
3. How much network traffic do you have already (if you wanted to push your
backups to a UNC name on a different computer).
That said, having your backups in contention with the active databases on
the same physical drives is almost never a good idea (for the reasons you
listed).
Rick Sawtell|||
> Actually Bob, you should always do your backups to the same local drives
> as
> the active databases are on. In fact, I'm not sure why you are even
> bothering with doing backups at all... <wink>
Facetiously sarcastic. Gotta luv it!

> 1. How much activity is going on during the backup process?
Log backup during the production day every four hours spike the processor
pretty hard. Full backups at night are not an issues, yet.

> 2. How big are the backups that we are talking about?
Several hundred databases. Transaction logs every four hours total about a
gig. Full nightly backups are about 100 gig.

> 3. How much network traffic do you have already (if you wanted to push
> your
> backups to a UNC name on a different computer).
>
None right now. Backups are over a fiber chanel to the RAID and so far
network traffic to the database servers is very low.|||"Bob Castleman" <nomail@.here> wrote in message
news:uKsEHrqaFHA.220@.TK2MSFTNGP10.phx.gbl...
>
> Log backup during the production day every four hours spike the processor
> pretty hard. Full backups at night are not an issues, yet.
>
Well, if you can't get the new hardware, how about doing TLog dumps more
often. You still have the pay the piper for the CPU time, but if they were
every hour instead of every 4 hours, your spikes should be shorter-lived.

> None right now. Backups are over a fiber chanel to the RAID and so far
> network traffic to the database servers is very low.
>
Very nice!!!
Rick Sawtell

backup Plan SQL 2000

Hello all,

I would like to be able to backup our databases to a network share mapped drive. Is this possible? When every I go in to setup a maintenance plan it will only allow me to select local drive as the backup location. If my server crashes its not going to do a whole lot of good if my databases are stored locally (I do also backup up to tape every niight). I backup the databases every hour to local disk and would like to send them to one of my MS Storage Servers on the network.

Any help would be greatly appreciated.

thanks

Jason

YOu could use a network share using the UNC path, but if you want to make sure that the data is transfered correctly, I would stick saving the backup file to a local drive and copying it over afterwards using a restartable copying mechanism like robocopy or xcopy.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||You don't need to do this if the SQL Server is running under a domain account. You simply need to ensure the SQL Server account has rights to the share you want to save your backups to. Then you can specify the network path to the share when creating the backup job/maintenance plan.

backup Plan SQL 2000

Hello all,

I would like to be able to backup our databases to a network share mapped drive. Is this possible? When every I go in to setup a maintenance plan it will only allow me to select local drive as the backup location. If my server crashes its not going to do a whole lot of good if my databases are stored locally (I do also backup up to tape every niight). I backup the databases every hour to local disk and would like to send them to one of my MS Storage Servers on the network.

Any help would be greatly appreciated.

thanks

Jason

YOu could use a network share using the UNC path, but if you want to make sure that the data is transfered correctly, I would stick saving the backup file to a local drive and copying it over afterwards using a restartable copying mechanism like robocopy or xcopy.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||You don't need to do this if the SQL Server is running under a domain account. You simply need to ensure the SQL Server account has rights to the share you want to save your backups to. Then you can specify the network path to the share when creating the backup job/maintenance plan.

Backup or Restore from Enterprise Manager.

We've added a new disk to our server.
When we click through the SQL Server Backup, to select
the "Backup Device Location", the new drive is not
available. The only drive that is available is the one
that existed when SQL was installed.
The same is true for restores.
Any ideas?
TIA,
Sean.
Hi,
Execute the below command from Query Analyzer;
xp_fixeddrives
If you are not able to see the new drive, Check for the existence of that
drive from Windows Explorer, if not contact your system administrator.
Thanks
Hari
MCDBA
"Sean Irving" <sbirving@.hotmail.com> wrote in message
news:178ae01c41dfe$8f99da60$a601280a@.phx.gbl...
> We've added a new disk to our server.
> When we click through the SQL Server Backup, to select
> the "Backup Device Location", the new drive is not
> available. The only drive that is available is the one
> that existed when SQL was installed.
> The same is true for restores.
> Any ideas?
> TIA,
> Sean.
>
|||If SQL Server is clustered, you have to add a dependency (SQL Server has to
depend on the disk). Also make sure you've restarted Enterprise Manager
since the disk was added, it may not have refreshed with the new
information.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

backup on the network

Hi,
I can create a DataBase Maintenance Plan and save my
backups in a Map Network drive (f.e. h?
How I can?
Thank to all
Pippo
Instead of mapped drives, create network shares, and access them using UNC
paths. More info at: http://vyaskn.tripod.com/administration_faq.htm#q17
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Pippo" <anonymous@.discussions.microsoft.com> wrote in message
news:2d9a01c51e55$a12ad740$a601280a@.phx.gbl...
Hi,
I can create a DataBase Maintenance Plan and save my
backups in a Map Network drive (f.e. h?
How I can?
Thank to all
Pippo

backup on the network

Hi,
I can create a DataBase Maintenance Plan and save my
backups in a Map Network drive (f.e. h?
How I can?
Thank to all
PippoInstead of mapped drives, create network shares, and access them using UNC
paths. More info at: http://vyaskn.tripod.com/administration_faq.htm#q17
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Pippo" <anonymous@.discussions.microsoft.com> wrote in message
news:2d9a01c51e55$a12ad740$a601280a@.phx.gbl...
Hi,
I can create a DataBase Maintenance Plan and save my
backups in a Map Network drive (f.e. h?
How I can?
Thank to all
Pippo

backup on the network

Hi,
I can create a DataBase Maintenance Plan and save my
backups in a Map Network drive (f.e. h:)?
How I can?
Thank to all
PippoInstead of mapped drives, create network shares, and access them using UNC
paths. More info at: http://vyaskn.tripod.com/administration_faq.htm#q17
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Pippo" <anonymous@.discussions.microsoft.com> wrote in message
news:2d9a01c51e55$a12ad740$a601280a@.phx.gbl...
Hi,
I can create a DataBase Maintenance Plan and save my
backups in a Map Network drive (f.e. h:)?
How I can?
Thank to all
Pippo

Backup on Network Drive

This is a multi-part message in MIME format.
--=_NextPart_000_004E_01C3ECC2.CF1CAF40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
We want to take back up of databases on network drives. How should we do = this ?
- Ketan

--=_NextPart_000_004E_01C3ECC2.CF1CAF40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
We want to take back up = of databases on network drives. How should we do this ?
- Ketan


--=_NextPart_000_004E_01C3ECC2.CF1CAF40--It is not generally recommended to backup directly to network drives. You
might be better off backup up to a local array, & then copying the file
across the network. There is a discussion on this here:
http://www.sql-server-performance.com/backup_restore_tuning.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Use an UNC path in the destination file name. Make sure that the SQL Server service account has
appropriate permissions on that share.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ketan Patel" <ketanforever@.indiatimes.com> wrote in message
news:ua9$kQJ7DHA.2392@.TK2MSFTNGP11.phx.gbl...
Hi,
We want to take back up of databases on network drives. How should we do this ?
- Ketan|||Thanks for your reply.
I have verified the service account. It was System account. I have changed
this to other having appropriate access to network drives and this works.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OqqmdWJ7DHA.3360@.tk2msftngp13.phx.gbl...
> Use an UNC path in the destination file name. Make sure that the SQL
Server service account has
> appropriate permissions on that share.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Ketan Patel" <ketanforever@.indiatimes.com> wrote in message
> news:ua9$kQJ7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We want to take back up of databases on network drives. How should we do
this ?
> - Ketan
>
>
>
>|||I will vouch for that from experience.
:-)
Greg Jackson
PDX, Oregon

Backup on Network Drive

Hi,
We want to take back up of databases on network drives. How should we do thi
s ?
- KetanIt is not generally recommended to backup directly to network drives. You
might be better off backup up to a local array, & then copying the file
across the network. There is a discussion on this here:
http://www.sql-server-performance.c...tore_tuning.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Use an UNC path in the destination file name. Make sure that the SQL Server
service account has
appropriate permissions on that share.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Ketan Patel" <ketanforever@.indiatimes.com> wrote in message
news:ua9$kQJ7DHA.2392@.TK2MSFTNGP11.phx.gbl...
Hi,
We want to take back up of databases on network drives. How should we do thi
s ?
- Ketan|||Thanks for your reply.
I have verified the service account. It was System account. I have changed
this to other having appropriate access to network drives and this works.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OqqmdWJ7DHA.3360@.tk2msftngp13.phx.gbl...
> Use an UNC path in the destination file name. Make sure that the SQL
Server service account has
> appropriate permissions on that share.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Ketan Patel" <ketanforever@.indiatimes.com> wrote in message
> news:ua9$kQJ7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We want to take back up of databases on network drives. How should we do
this ?
> - Ketan
>
>
>
>|||I will vouch for that from experience.
:-)
Greg Jackson
PDX, Oregon

Backup on Local/Network drive using Maintenance Plan

Hi,

I have created an sql server 2005 maintenance plan for a daily backup. The plan has two 'Back up database task' i.e. one backup on the local drive while the second on a network drive. When the plan is executed, a backup is created on the local drive but not on the network drive. If i check the log, it says "Access Denied" whereas i have full access to the network drive with complete permissions to read, write and delete.

Can anyone help me understand how to take a backup on both a local and network drive at the same time using a maintenance plan ? I shall be obliged...

Regards...

you have to make sure that the user used to run the maintanence task has sufficient rights on the network share.

Alternatively, you could copy the backup files with a scheduled copy command in windows.. that way, you'll only have to do the backup once in SQL (during backups, your server may suffer a performance hit)

|||

Thanks Erik for the reply, i found one another similar reply. It seems to be a privilige problem...

Regards...

Backup locations

I need to set up SQL (preferably in a DB Maint Plan) to do scheduled backups, but place them on a mapped drive. (Running out of room on my server!) Whenever I try to set anything up, I can only "see" the local drives. I've tried it mapped and UNC. I've also made sure that both the server's logins appear on both machines and have permissions on the shared folder. What am I missing' Any help would be greatly appreciated
Thanks
TinaMethod 1: Create your maintenance plan and pick an
arbitrary backup directory. When you have finished
creating the plan, go to the Jobs under SQL Server Agent
and modify the DB backup Job for the maintenance plan. The
T-SQL in the job step should be something like:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 1115857B-656B-
4064-9F7A-5BD2C6C1A2E6 -BkUpMedia DISK -
BkUpDB "e:\MSSQLBackup" -BkExt "BAK"'
Change the value following -BkUpDB to a UNC of your choice.
Method 2: Forget about the maintenance plan and create a
SQL Server Agent backup job yourself with the BACKUP
statement. You can then specify whatever UNC you fancy,
assuming there is no permission issue.
Linchi
>--Original Message--
>I need to set up SQL (preferably in a DB Maint Plan) to
do scheduled backups, but place them on a mapped drive.
(Running out of room on my server!) Whenever I try to set
anything up, I can only "see" the local drives. I've tried
it mapped and UNC. I've also made sure that both the
server's logins appear on both machines and have
permissions on the shared folder. What am I missing' Any
help would be greatly appreciated.
>Thanks!
>Tina
>.
>|||Enterprise Mangler will only show local drives. SQL cannot use mapped
drives consistantly because the SQL Service may not see the same mappings as
the console login. The solution is to type the UNC name of the folder in
the target location field.
You also have to make sure that SQL Server can write files to that location.
The easiest way is for you to run SQL under a domain-level account and give
that account permissions on the target folder. Use the following SQL Script
to test access.
xp_cmdshell 'dir \\MyUNCRemote\ShareName'
I suggest giving FULL CONTROL on the target folder to the SQL Service
account. Make sure you have both share permission and underlying NTFS
permissions set correctly.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Tina Tysinger" <ttysinger@.co.hernando.fl.us> wrote in message
news:2D848E29-A745-4682-B70A-D549A2760558@.microsoft.com...
> I need to set up SQL (preferably in a DB Maint Plan) to do scheduled
backups, but place them on a mapped drive. (Running out of room on my
server!) Whenever I try to set anything up, I can only "see" the local
drives. I've tried it mapped and UNC. I've also made sure that both the
server's logins appear on both machines and have permissions on the shared
folder. What am I missing' Any help would be greatly appreciated.
> Thanks!
> Tina