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 question

We are using SQLServer 2005.
I am looking for a good advice to do our database backup.
For our database, I have created a full backup that is done every day at 12
am. Then daily every 1 hour I backup the Transaction log. Then I have a
backup file cleanup (maintenance cleanup task) to delete files that are
older than 3 days. Is this backup plan good enough ? Shall I also di a
differential backup ?
Thank you.fniles wrote:
> We are using SQLServer 2005.
> I am looking for a good advice to do our database backup.
> For our database, I have created a full backup that is done every day at 12
> am. Then daily every 1 hour I backup the Transaction log. Then I have a
> backup file cleanup (maintenance cleanup task) to delete files that are
> older than 3 days. Is this backup plan good enough ? Shall I also di a
> differential backup ?
> Thank you.
Good enough for what?
You need to answer your own question by determining your Recovery
Point Objective and Recovery Time Objective. That is, the latest point
to which your business must be able to recover its data (RPO) and the
downtime you can afford in which to do it (RTO). These are business
considerations, not technical ones. Start talking to your business
stakeholders.
--
David Portas|||I guess my question is, if I already do Full backup once a day, and
transaction log backup every hour, do I still need to do "Differential"
backup ?
What is the difference between backing up (full backup and transaction log)
and (the differential backup) ?
Say I do a full backup at 12 am and has transaction log every hour after
that.
Say it is 9 am now, and I need to restore my data, do I need to restore the
full backup and restore every transaction log from 12 am until 9 am ?
If I do differential backup every 4 hours, is my differential backup at 8 am
contains only those data from 4 am to 8 am, or does it contain data from 12
am to 8 am ?
Thank you.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at
>> 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas|||Hi fniles
A differential backup contains all the changes since the last full database
backup. So your 8 AM differential would contain all the changes since
midnight.
This can make restore much faster. After restoring the full database, you
then only need to restore the most recent differential and any log backups
from after that differential.
So in your case, assuming you have already made your 9am log backup, you
could either restore 9 log backups, or 1 differential and 1 log backup.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"fniles" <fniles@.pfmail.com> wrote in message
news:uxbAPZ4MIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I guess my question is, if I already do Full backup once a day, and
>transaction log backup every hour, do I still need to do "Differential"
>backup ?
> What is the difference between backing up (full backup and transaction
> log) and (the differential backup) ?
> Say I do a full backup at 12 am and has transaction log every hour after
> that.
> Say it is 9 am now, and I need to restore my data, do I need to restore
> the full backup and restore every transaction log from 12 am until 9 am ?
> If I do differential backup every 4 hours, is my differential backup at 8
> am contains only those data from 4 am to 8 am, or does it contain data
> from 12 am to 8 am ?
> Thank you.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at
>> 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
>> Good enough for what?
>> You need to answer your own question by determining your Recovery
>> Point Objective and Recovery Time Objective. That is, the latest point
>> to which your business must be able to recover its data (RPO) and the
>> downtime you can afford in which to do it (RTO). These are business
>> considerations, not technical ones. Start talking to your business
>> stakeholders.
>> --
>> David Portas
>|||Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uc9teo4MIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi fniles
> A differential backup contains all the changes since the last full
> database backup. So your 8 AM differential would contain all the changes
> since midnight.
> This can make restore much faster. After restoring the full database, you
> then only need to restore the most recent differential and any log backups
> from after that differential.
> So in your case, assuming you have already made your 9am log backup, you
> could either restore 9 log backups, or 1 differential and 1 log backup.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uxbAPZ4MIHA.3400@.TK2MSFTNGP03.phx.gbl...
>>I guess my question is, if I already do Full backup once a day, and
>>transaction log backup every hour, do I still need to do "Differential"
>>backup ?
>> What is the difference between backing up (full backup and transaction
>> log) and (the differential backup) ?
>> Say I do a full backup at 12 am and has transaction log every hour after
>> that.
>> Say it is 9 am now, and I need to restore my data, do I need to restore
>> the full backup and restore every transaction log from 12 am until 9 am ?
>> If I do differential backup every 4 hours, is my differential backup at 8
>> am contains only those data from 4 am to 8 am, or does it contain data
>> from 12 am to 8 am ?
>> Thank you.
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day
>> at 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
>> Good enough for what?
>> You need to answer your own question by determining your Recovery
>> Point Objective and Recovery Time Objective. That is, the latest point
>> to which your business must be able to recover its data (RPO) and the
>> downtime you can afford in which to do it (RTO). These are business
>> considerations, not technical ones. Start talking to your business
>> stakeholders.
>> --
>> David Portas
>>
>|||I like the way David explains the concept of RPO and RTO as this will define
what backup procedures you will need to do. Even if you understand the
concpets behind FULL, DIFFERENTIAL, FILEGROUP or TRANSACTION LOG backups,
your RPO and RTO will determine what you need to use to fulfill your
requirements
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at
>> 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas|||Yes, I backup to a drive.
We do have another backup procedure outside the SQL Server Management Studio
that will backup to a tape.
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:e0a1l3915upjr0kss3hk87273vrh24tm6t@.4ax.com...
> How do you backup the backups? Do you have an Enterprise back system
> like Tivoli that backs up to a drive not on the SQL server or to tape?
> On Sat, 1 Dec 2007 09:57:56 -0600, "fniles" <fniles@.pfmail.com> wrote:
>>We are using SQLServer 2005.
>>I am looking for a good advice to do our database backup.
>>For our database, I have created a full backup that is done every day at
>>12
>>am. Then daily every 1 hour I backup the Transaction log. Then I have a
>>backup file cleanup (maintenance cleanup task) to delete files that are
>>older than 3 days. Is this backup plan good enough ? Shall I also di a
>>differential backup ?
>>Thank you.|||Regarding Recovery Time Objective (RTO), which backup will take the least
time to restore ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at
>> 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas|||It depends. A very simplified example:
You have a db backup and then either a diff or a log backup. You wonder whether a diff backup or a
log backup will take longer time.
Say you've done a *lot* of modifications of the same row (pretty extreme, but just as an example).
If you now restore a log backup, then all those modification will be re-done when you restore. If
you restore a diff backup, then only one extent (8 pages) will be restored.
OTOH, say you've modified a lot of data (say 10,000 modifications), each modification spread over
different extents. Diff restore will restore 10,000 extents. Log restore will re-do those 10,000
modifications.
In general, it isn't one or the other. I tend to have db and log backup as a basic setting. I
complement with diff backup when I realize that restoring all log backups since last db backup takes
too long. So, I restore db backup, last diff backup and all subsequent log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"fniles" <fniles@.pfmail.com> wrote in message news:%230L7QecNIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Regarding Recovery Time Objective (RTO), which backup will take the least time to restore ?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day at 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
>> Good enough for what?
>> You need to answer your own question by determining your Recovery
>> Point Objective and Recovery Time Objective. That is, the latest point
>> to which your business must be able to recover its data (RPO) and the
>> downtime you can afford in which to do it (RTO). These are business
>> considerations, not technical ones. Start talking to your business
>> stakeholders.
>> --
>> David Portas
>|||I had a client hit by this very issue. They had very bad (i.e. bloated,
non-normalized) data structures as well as ugly ADO code, among other
issues. Differential backups were huge compared to transaction log backups
on average.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B927BB46-3329-4E78-A75D-CDE8ED0D8091@.microsoft.com...
> It depends. A very simplified example:
> You have a db backup and then either a diff or a log backup. You wonder
> whether a diff backup or a log backup will take longer time.
> Say you've done a *lot* of modifications of the same row (pretty extreme,
> but just as an example). If you now restore a log backup, then all those
> modification will be re-done when you restore. If you restore a diff
> backup, then only one extent (8 pages) will be restored.
> OTOH, say you've modified a lot of data (say 10,000 modifications), each
> modification spread over different extents. Diff restore will restore
> 10,000 extents. Log restore will re-do those 10,000 modifications.
> In general, it isn't one or the other. I tend to have db and log backup as
> a basic setting. I complement with diff backup when I realize that
> restoring all log backups since last db backup takes too long. So, I
> restore db backup, last diff backup and all subsequent log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230L7QecNIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Regarding Recovery Time Objective (RTO), which backup will take the least
>> time to restore ?
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>> fniles wrote:
>> We are using SQLServer 2005.
>> I am looking for a good advice to do our database backup.
>> For our database, I have created a full backup that is done every day
>> at 12
>> am. Then daily every 1 hour I backup the Transaction log. Then I have a
>> backup file cleanup (maintenance cleanup task) to delete files that are
>> older than 3 days. Is this backup plan good enough ? Shall I also di a
>> differential backup ?
>> Thank you.
>> Good enough for what?
>> You need to answer your own question by determining your Recovery
>> Point Objective and Recovery Time Objective. That is, the latest point
>> to which your business must be able to recover its data (RPO) and the
>> downtime you can afford in which to do it (RTO). These are business
>> considerations, not technical ones. Start talking to your business
>> stakeholders.
>> --
>> David Portas
>>
>

Backup question

We are using SQLServer 2005.
I am looking for a good advice to do our database backup.
For our database, I have created a full backup that is done every day at 12
am. Then daily every 1 hour I backup the Transaction log. Then I have a
backup file cleanup (maintenance cleanup task) to delete files that are
older than 3 days. Is this backup plan good enough ? Shall I also di a
differential backup ?
Thank you.fniles wrote:
> We are using SQLServer 2005.
> I am looking for a good advice to do our database backup.
> For our database, I have created a full backup that is done every day at 1
2
> am. Then daily every 1 hour I backup the Transaction log. Then I have a
> backup file cleanup (maintenance cleanup task) to delete files that are
> older than 3 days. Is this backup plan good enough ? Shall I also di a
> differential backup ?
> Thank you.
Good enough for what?
You need to answer your own question by determining your Recovery
Point Objective and Recovery Time Objective. That is, the latest point
to which your business must be able to recover its data (RPO) and the
downtime you can afford in which to do it (RTO). These are business
considerations, not technical ones. Start talking to your business
stakeholders.
David Portas|||I guess my question is, if I already do Full backup once a day, and
transaction log backup every hour, do I still need to do "Differential"
backup ?
What is the difference between backing up (full backup and transaction log)
and (the differential backup) ?
Say I do a full backup at 12 am and has transaction log every hour after
that.
Say it is 9 am now, and I need to restore my data, do I need to restore the
full backup and restore every transaction log from 12 am until 9 am ?
If I do differential backup every 4 hours, is my differential backup at 8 am
contains only those data from 4 am to 8 am, or does it contain data from 12
am to 8 am ?
Thank you.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas|||Hi fniles
A differential backup contains all the changes since the last full database
backup. So your 8 AM differential would contain all the changes since
midnight.
This can make restore much faster. After restoring the full database, you
then only need to restore the most recent differential and any log backups
from after that differential.
So in your case, assuming you have already made your 9am log backup, you
could either restore 9 log backups, or 1 differential and 1 log backup.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"fniles" <fniles@.pfmail.com> wrote in message
news:uxbAPZ4MIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I guess my question is, if I already do Full backup once a day, and
>transaction log backup every hour, do I still need to do "Differential"
>backup ?
> What is the difference between backing up (full backup and transaction
> log) and (the differential backup) ?
> Say I do a full backup at 12 am and has transaction log every hour after
> that.
> Say it is 9 am now, and I need to restore my data, do I need to restore
> the full backup and restore every transaction log from 12 am until 9 am ?
> If I do differential backup every 4 hours, is my differential backup at 8
> am contains only those data from 4 am to 8 am, or does it contain data
> from 12 am to 8 am ?
> Thank you.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>|||Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uc9teo4MIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi fniles
> A differential backup contains all the changes since the last full
> database backup. So your 8 AM differential would contain all the changes
> since midnight.
> This can make restore much faster. After restoring the full database, you
> then only need to restore the most recent differential and any log backups
> from after that differential.
> So in your case, assuming you have already made your 9am log backup, you
> could either restore 9 log backups, or 1 differential and 1 log backup.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uxbAPZ4MIHA.3400@.TK2MSFTNGP03.phx.gbl...
>|||How do you backup the backups? Do you have an Enterprise back system
like Tivoli that backs up to a drive not on the SQL server or to tape?
On Sat, 1 Dec 2007 09:57:56 -0600, "fniles" <fniles@.pfmail.com> wrote:

>We are using SQLServer 2005.
>I am looking for a good advice to do our database backup.
>For our database, I have created a full backup that is done every day at 12
>am. Then daily every 1 hour I backup the Transaction log. Then I have a
>backup file cleanup (maintenance cleanup task) to delete files that are
>older than 3 days. Is this backup plan good enough ? Shall I also di a
>differential backup ?
>Thank you.
>|||I like the way David explains the concept of RPO and RTO as this will define
what backup procedures you will need to do. Even if you understand the
concpets behind FULL, DIFFERENTIAL, FILEGROUP or TRANSACTION LOG backups,
your RPO and RTO will determine what you need to use to fulfill your
requirements
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas|||Yes, I backup to a drive.
We do have another backup procedure outside the SQL Server Management Studio
that will backup to a tape.
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:e0a1l3915upjr0kss3hk87273vrh24tm6t@.
4ax.com...[vbcol=seagreen]
> How do you backup the backups? Do you have an Enterprise back system
> like Tivoli that backs up to a drive not on the SQL server or to tape?
> On Sat, 1 Dec 2007 09:57:56 -0600, "fniles" <fniles@.pfmail.com> wrote:
>|||Regarding Recovery Time Objective (RTO), which backup will take the least
time to restore ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas|||It depends. A very simplified example:
You have a db backup and then either a diff or a log backup. You wonder whet
her a diff backup or a
log backup will take longer time.
Say you've done a *lot* of modifications of the same row (pretty extreme, bu
t just as an example).
If you now restore a log backup, then all those modification will be re-done
when you restore. If
you restore a diff backup, then only one extent (8 pages) will be restored.
OTOH, say you've modified a lot of data (say 10,000 modifications), each mod
ification spread over
different extents. Diff restore will restore 10,000 extents. Log restore wil
l re-do those 10,000
modifications.
In general, it isn't one or the other. I tend to have db and log backup as a
basic setting. I
complement with diff backup when I realize that restoring all log backups si
nce last db backup takes
too long. So, I restore db backup, last diff backup and all subsequent log b
ackups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"fniles" <fniles@.pfmail.com> wrote in message news:%230L7QecNIHA.4712@.TK2MSFTNGP04.phx.gbl..
.
> Regarding Recovery Time Objective (RTO), which backup will take the least
time to restore ?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>

Backup question

We are using SQLServer 2005.
I am looking for a good advice to do our database backup.
For our database, I have created a full backup that is done every day at 12
am. Then daily every 1 hour I backup the Transaction log. Then I have a
backup file cleanup (maintenance cleanup task) to delete files that are
older than 3 days. Is this backup plan good enough ? Shall I also di a
differential backup ?
Thank you.
fniles wrote:
> We are using SQLServer 2005.
> I am looking for a good advice to do our database backup.
> For our database, I have created a full backup that is done every day at 12
> am. Then daily every 1 hour I backup the Transaction log. Then I have a
> backup file cleanup (maintenance cleanup task) to delete files that are
> older than 3 days. Is this backup plan good enough ? Shall I also di a
> differential backup ?
> Thank you.
Good enough for what?
You need to answer your own question by determining your Recovery
Point Objective and Recovery Time Objective. That is, the latest point
to which your business must be able to recover its data (RPO) and the
downtime you can afford in which to do it (RTO). These are business
considerations, not technical ones. Start talking to your business
stakeholders.
David Portas
|||I guess my question is, if I already do Full backup once a day, and
transaction log backup every hour, do I still need to do "Differential"
backup ?
What is the difference between backing up (full backup and transaction log)
and (the differential backup) ?
Say I do a full backup at 12 am and has transaction log every hour after
that.
Say it is 9 am now, and I need to restore my data, do I need to restore the
full backup and restore every transaction log from 12 am until 9 am ?
If I do differential backup every 4 hours, is my differential backup at 8 am
contains only those data from 4 am to 8 am, or does it contain data from 12
am to 8 am ?
Thank you.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas
|||Hi fniles
A differential backup contains all the changes since the last full database
backup. So your 8 AM differential would contain all the changes since
midnight.
This can make restore much faster. After restoring the full database, you
then only need to restore the most recent differential and any log backups
from after that differential.
So in your case, assuming you have already made your 9am log backup, you
could either restore 9 log backups, or 1 differential and 1 log backup.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"fniles" <fniles@.pfmail.com> wrote in message
news:uxbAPZ4MIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I guess my question is, if I already do Full backup once a day, and
>transaction log backup every hour, do I still need to do "Differential"
>backup ?
> What is the difference between backing up (full backup and transaction
> log) and (the differential backup) ?
> Say I do a full backup at 12 am and has transaction log every hour after
> that.
> Say it is 9 am now, and I need to restore my data, do I need to restore
> the full backup and restore every transaction log from 12 am until 9 am ?
> If I do differential backup every 4 hours, is my differential backup at 8
> am contains only those data from 4 am to 8 am, or does it contain data
> from 12 am to 8 am ?
> Thank you.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
>
|||Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uc9teo4MIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi fniles
> A differential backup contains all the changes since the last full
> database backup. So your 8 AM differential would contain all the changes
> since midnight.
> This can make restore much faster. After restoring the full database, you
> then only need to restore the most recent differential and any log backups
> from after that differential.
> So in your case, assuming you have already made your 9am log backup, you
> could either restore 9 log backups, or 1 differential and 1 log backup.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uxbAPZ4MIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||I like the way David explains the concept of RPO and RTO as this will define
what backup procedures you will need to do. Even if you understand the
concpets behind FULL, DIFFERENTIAL, FILEGROUP or TRANSACTION LOG backups,
your RPO and RTO will determine what you need to use to fulfill your
requirements
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas
|||Yes, I backup to a drive.
We do have another backup procedure outside the SQL Server Management Studio
that will backup to a tape.
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:e0a1l3915upjr0kss3hk87273vrh24tm6t@.4ax.com... [vbcol=seagreen]
> How do you backup the backups? Do you have an Enterprise back system
> like Tivoli that backs up to a drive not on the SQL server or to tape?
> On Sat, 1 Dec 2007 09:57:56 -0600, "fniles" <fniles@.pfmail.com> wrote:
|||Regarding Recovery Time Objective (RTO), which backup will take the least
time to restore ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:c9a7a869-c459-46c3-9ea9-34b77157e471@.o42g2000hsc.googlegroups.com...
> fniles wrote:
> Good enough for what?
> You need to answer your own question by determining your Recovery
> Point Objective and Recovery Time Objective. That is, the latest point
> to which your business must be able to recover its data (RPO) and the
> downtime you can afford in which to do it (RTO). These are business
> considerations, not technical ones. Start talking to your business
> stakeholders.
> --
> David Portas
|||I had a client hit by this very issue. They had very bad (i.e. bloated,
non-normalized) data structures as well as ugly ADO code, among other
issues. Differential backups were huge compared to transaction log backups
on average.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B927BB46-3329-4E78-A75D-CDE8ED0D8091@.microsoft.com...
> It depends. A very simplified example:
> You have a db backup and then either a diff or a log backup. You wonder
> whether a diff backup or a log backup will take longer time.
> Say you've done a *lot* of modifications of the same row (pretty extreme,
> but just as an example). If you now restore a log backup, then all those
> modification will be re-done when you restore. If you restore a diff
> backup, then only one extent (8 pages) will be restored.
> OTOH, say you've modified a lot of data (say 10,000 modifications), each
> modification spread over different extents. Diff restore will restore
> 10,000 extents. Log restore will re-do those 10,000 modifications.
> In general, it isn't one or the other. I tend to have db and log backup as
> a basic setting. I complement with diff backup when I realize that
> restoring all log backups since last db backup takes too long. So, I
> restore db backup, last diff backup and all subsequent log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%230L7QecNIHA.4712@.TK2MSFTNGP04.phx.gbl...
>

Backup procedure

Hello there!
I have SQLServer 2000 running on Windows 2000 advanced server.
I want to schedule daily full backup.
Do I have to stop SQLServer service before it starts backup?
Or can users have full access to the database while it's doing the backup?
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance

Backup procedure

Hello there
I have SQLServer 2000 running on Windows 2000 advanced server
I want to schedule daily full backup
Do I have to stop SQLServer service before it starts backup
Or can users have full access to the database while it's doing the backup
Thanks in advanceRead the chapter on Backup and Restore in BOL (Books On-Line). This will
explain some of the unique considerations and options for backing up a SQL
server installation.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance|||Hi,
It is not at all required to shutdown SQL server while doing a full database
backup. But try to perform the full database backup during the time zone
where user access is less.
Thanks
Hari
MCDBA
"Rexford" <anonymous@.discussions.microsoft.com> wrote in message
news:B5E24755-6290-44CD-9FC2-99309FB5C9D4@.microsoft.com...
> Hello there!
> I have SQLServer 2000 running on Windows 2000 advanced server.
> I want to schedule daily full backup.
> Do I have to stop SQLServer service before it starts backup?
> Or can users have full access to the database while it's doing the backup?
> Thanks in advance

backup problem

when i try to take back from sqlserver 2000 enterprise manager it shows the error. wheni tried with NT itsays tape not found.

backup medium ::reportloerror:write failure on backup device\\.\tape0.operating system error 23(data error(cyclic reduncancy check));

pls help out in this issueeDo you have a tape drive?

You'd be better off dumping to disk and then copying to tape (faster)

Have you tried to set up a maint plan with the wizard (easiest)

Backup of mssql server in LAN

hi everyone,
I am trying to backup and restore mssql server using VDI Interface.
Where i can list the no. of sqlserver's in LAN.
To backup and restore, i have to separately run my application in
individual machines where
mssql server was installed. I would like to configure backup and
restore mssql server available in connected LAN, and to control in one
machine using only one application in only one machine.
I am getting GetConfiguration method of VDI API time out while trying
to connect mssql server another machine.
vds->GetConfiguration(INFINITE, &config)
never times out but for more than half an hour couldn't get connected
with mssql server
running in another machine.
I would like to get a solution to connect all mssql server for backup
and restore from a single
machine in a LAN using VDI API without time out in GetConfiguration
method.
Any Suggestions on this will be greatly appreciated.
Thanx in advance
regards,
deenaHi
As no-one else has replied I thought I would put in my 2c!!
Most backup solutions seem to have an agent component, which would indicate
that it may not be possible to do this. You may have to log a call for this
as it is not a feature that many people would use.
John
"tsgd84@.gmail.com" wrote:

> hi everyone,
> I am trying to backup and restore mssql server using VDI Interface.
> Where i can list the no. of sqlserver's in LAN.
> To backup and restore, i have to separately run my application in
> individual machines where
> mssql server was installed. I would like to configure backup and
> restore mssql server available in connected LAN, and to control in one
> machine using only one application in only one machine.
> I am getting GetConfiguration method of VDI API time out while trying
> to connect mssql server another machine.
> vds->GetConfiguration(INFINITE, &config)
> never times out but for more than half an hour couldn't get connected
> with mssql server
> running in another machine.
> I would like to get a solution to connect all mssql server for backup
> and restore from a single
> machine in a LAN using VDI API without time out in GetConfiguration
> method.
> Any Suggestions on this will be greatly appreciated.
> Thanx in advance
> regards,
> deena
>

Backup of mssql server in LAN

hi everyone,
I am trying to backup and restore mssql server using VDI Interface.
Where i can list the no. of sqlserver's in LAN.
To backup and restore, i have to separately run my application in
individual machines where
mssql server was installed. I would like to configure backup and
restore mssql server available in connected LAN, and to control in one
machine using only one application in only one machine.
I am getting GetConfiguration method of VDI API time out while trying
to connect mssql server another machine.
vds->GetConfiguration(INFINITE, &config)
never times out but for more than half an hour couldn't get connected
with mssql server
running in another machine.
I would like to get a solution to connect all mssql server for backup
and restore from a single
machine in a LAN using VDI API without time out in GetConfiguration
method.
Any Suggestions on this will be greatly appreciated.
Thanx in advance
regards,
deenaHi
As no-one else has replied I thought I would put in my 2c!!
Most backup solutions seem to have an agent component, which would indicate
that it may not be possible to do this. You may have to log a call for this
as it is not a feature that many people would use.
John
"tsgd84@.gmail.com" wrote:
> hi everyone,
> I am trying to backup and restore mssql server using VDI Interface.
> Where i can list the no. of sqlserver's in LAN.
> To backup and restore, i have to separately run my application in
> individual machines where
> mssql server was installed. I would like to configure backup and
> restore mssql server available in connected LAN, and to control in one
> machine using only one application in only one machine.
> I am getting GetConfiguration method of VDI API time out while trying
> to connect mssql server another machine.
> vds->GetConfiguration(INFINITE, &config)
> never times out but for more than half an hour couldn't get connected
> with mssql server
> running in another machine.
> I would like to get a solution to connect all mssql server for backup
> and restore from a single
> machine in a LAN using VDI API without time out in GetConfiguration
> method.
> Any Suggestions on this will be greatly appreciated.
> Thanx in advance
> regards,
> deena
>

backup of database

Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
Daily backups should suffice.
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh
|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas

"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh

backup of database

Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
MangeshDaily backups should suffice.
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh

backup of database

Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
MangeshDaily backups should suffice.
--
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh

BackUp my DataBase on MSSQLServer Express

Hello my friends!

I have designed a database on a SQLServer Express. When I am going to backup it, I provide a filename like as "c:/test.bak" but when executing I recieve a message that SQLServer cannot open the backup device with the name above.

hi,

please verify (via SQL Server Configuration Manager->SQL Server 2005 services->YourSQLInstance->Properties) that the account running the service has enought permissions on the target path...

by default SQLExpress installs setting that account as Network Service and this account does not have that permissions (read-write) outside the instance path.. you can modify the NTFS permissions of the path as well allowing the running account as required..

regards

backup log in sqlserver 2000

Hi
I want to activate my standby database.
My primary database is fine and I want to continue to use it for some other
purpose.
My question :
Do I need to use no_truncate option during the remaining transactions backup
?
If I use NO_TRUNCATE option can I continue to use my primary database for
other work.
What is the difference between the regular T-LOG bkup and backup with
NO_TRUNCATE option.
Tks
MangeshNo, you shouldn't use NO_TRUNCATE. This is only there to support backing up
log of a corrupted
database. See http://www.karaszi.com/SQLServer/in...no_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in me
ssage
news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some othe
r
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions back
up?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh|||Hi,
Backup Log command is used to backup the transaction log when database is
accessible. NO_TRUNCATE option is used when a database is inaccessi le and
you need to take a log backup. See the detailed explanation by tibor:-
http://www.karaszi.com/SQLServer/in...no_truncate.asp
Thanks
Hari
SQL server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some
> other
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions
> backup?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh

backup log in sqlserver 2000

Hi
I want to activate my standby database.
My primary database is fine and I want to continue to use it for some other
purpose.
My question :
Do I need to use no_truncate option during the remaining transactions backup?
If I use NO_TRUNCATE option can I continue to use my primary database for
other work.
What is the difference between the regular T-LOG bkup and backup with
NO_TRUNCATE option.
Tks
Mangesh
No, you shouldn't use NO_TRUNCATE. This is only there to support backing up log of a corrupted
database. See http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some other
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions backup?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh
|||Hi,
Backup Log command is used to backup the transaction log when database is
accessible. NO_TRUNCATE option is used when a database is inaccessi le and
you need to take a log backup. See the detailed explanation by tibor:-
http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Thanks
Hari
SQL server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some
> other
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions
> backup?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh

backup log in sqlserver 2000

Hi
I want to activate my standby database.
My primary database is fine and I want to continue to use it for some other
purpose.
My question :
Do I need to use no_truncate option during the remaining transactions backup?
If I use NO_TRUNCATE option can I continue to use my primary database for
other work.
What is the difference between the regular T-LOG bkup and backup with
NO_TRUNCATE option.
Tks
MangeshNo, you shouldn't use NO_TRUNCATE. This is only there to support backing up log of a corrupted
database. See http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some other
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions backup?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh|||Hi,
Backup Log command is used to backup the transaction log when database is
accessible. NO_TRUNCATE option is used when a database is inaccessi le and
you need to take a log backup. See the detailed explanation by tibor:-
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
Thanks
Hari
SQL server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:49D98498-C81E-4B7C-B117-F8E5AB7B7E3B@.microsoft.com...
> Hi
> I want to activate my standby database.
> My primary database is fine and I want to continue to use it for some
> other
> purpose.
> My question :
> Do I need to use no_truncate option during the remaining transactions
> backup?
> If I use NO_TRUNCATE option can I continue to use my primary database for
> other work.
> What is the difference between the regular T-LOG bkup and backup with
> NO_TRUNCATE option.
> Tks
> Mangesh

Backup Job Sometimes Stops at msdb

Hi,

We have been using the same stored procedures for backing up our

databases for years with no issues. We now have a puzzling issue on SQL

Server 2005 (sp1) and msdb. The stored procedure uses a cursor to get a

list of databases in the group (alphabetically). The cursor always gets

all of the databases but sometimes it doesn't back all of them up (like

it thinks that it's done). It always seems to stop at msdb. It's not

because msdb is the last in the list, I created a database called mz

and it still only backed up master and model (the only other two in

this group). I used @.@.cursor_rows so I know that all for database names

were in the cursor. This doesn't ALWAYS happen - sometimes it backs up

all of the databases just fine. This seems to ONLY happen on the 64-bit

machines. We're using litespeed and I'll try to reproduce the issue

using native commands.

DECLARE @.db VARCHAR(100) -- database name

DECLARE @.NumBakFiles INT -- number of backup files to be created

DECLARE @.DestServer VARCHAR(50) -- backup destination server

DECLARE @.DestShare VARCHAR(50) -- backup destination file share

DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
DECLARE cDB CURSOR FOR

SELECT [name]

FROM master.sys.databases

--WHERE [name] in ('msdb')

WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFILE')

-- AND UPPER([name]) < 'H' -- AthruG

AND [name] > 'h%' -- HthruM

AND [name] < 'n%' -- HthruM

-- AND [name] >= 'n%' -- NthruS

-- AND [name] <= 't%' -- NthruS

-- AND [name] <= 't%' -- TthruZ

ORDER BY

[name],

create_date

OPEN cDB

print @.@.cursor_rows

FETCH NEXT

FROM cDB

INTO @.db

WHILE (@.@.FETCH_STATUS=0)

BEGIN

PRINT @.db

SET @.NumBakFiles = 1

SET @.DestServer = '\\SQLDUMP\'

--SELECT @.DestServer = 'D:\SQLDATADUMP\'

SET @.DestShare = 'SQLDUMPSHARE\'

SET @.dbDestDisk = @.DestServer + @.DestShare +

REPLACE(@.@.SERVERNAME, '\', '_') + '\SQLDATADUMP\' + @.db + '_FullDB_' +

REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','')

+ '.bak'

--SELECT @.dbDestDisk = @.DestServer + '\' + @.db +

'_FullDB_' + REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':',

''), ' ','') + '.bak'

-- Backup Database File

EXEC master.dbo.xp_backup_database @.database = @.db, @.filename = @.dbDestDisk, @.init=1

FETCH NEXT

FROM cDB

INTO @.db

END --WHILE

CLOSE cDB

DEALLOCATE cDB

GOI was able to reproduce the issue using native sql server commands:

4

master

Processed 360 pages for database 'master', file 'master' on file 10.

Processed 2 pages for database 'master', file 'mastlog' on file 10.

BACKUP DATABASE successfully processed 362 pages in 0.186 seconds (15.921 MB/sec).

model

Processed 160 pages for database 'model', file 'modeldev' on file 10.

Processed 2 pages for database 'model', file 'modellog' on file 10.

BACKUP DATABASE successfully processed 162 pages in 0.081 seconds (16.333 MB/sec).|||I changed the cursor to a static cursor and have not had any issues

since then. It was intermittent before but it happened at least once or

twice per week.

Backup Files

I am going to move to a different host. Can I get back up files of my SQL
Server database (from the host) and use those to set up on the new host? And
if so, is that what I should be asking for - backup files? Someone told me
there should be 2 files.

Thanksask them to do sql backup to *.bak file. and if u could, zip it.
just 1 file

On Nov 20, 6:41 am, "JA" <jarmour at kc.rr.comwrote:

Quote:

Originally Posted by

I am going to move to a different host. Can I get back up files of my SQL
Server database (from the host) and use those to set up on the new host? And
if so, is that what I should be asking for - backup files? Someone told me
there should be 2 files.
>
Thanks