backup strategy question (sql 2005)

Hi,
im thinking about the backup strategy for our new sql 2005 database. I would
like to know how you would do it. The scenario is the following:
- the db consists of 2 file groups (primary + another one)
- the second file group is pretty large (250 GB) and contains a lot of blob
data that changes rarely
- the primary group is rather small (3 GB), but changes frequently
Since the first file group is small, I plan to backup it every day (full
backup). The secound group should be fully backuped every 2 weeks. In the
meantime I would backup the daily changes of the second group using
differncial backups. Any better ideas?
What I dont understand is how the transaction log behaves in this case. The
log contains the changes for all file groups. So what happens if I backup
only one file group? Are the changes of that group removed from the log and
the changes of the other group stay logged? I wonder how this works.
thanks in advance,
BenjaminThe only backup operation that removes log records is BACKUP LOG. The other types of backup (db,
diff, file, filegroup, filegrup with diff etc) does not empty the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
> Hi,
> im thinking about the backup strategy for our new sql 2005 database. I would
> like to know how you would do it. The scenario is the following:
> - the db consists of 2 file groups (primary + another one)
> - the second file group is pretty large (250 GB) and contains a lot of blob
> data that changes rarely
> - the primary group is rather small (3 GB), but changes frequently
> Since the first file group is small, I plan to backup it every day (full
> backup). The secound group should be fully backuped every 2 weeks. In the
> meantime I would backup the daily changes of the second group using
> differncial backups. Any better ideas?
> What I dont understand is how the transaction log behaves in this case. The
> log contains the changes for all file groups. So what happens if I backup
> only one file group? Are the changes of that group removed from the log and
> the changes of the other group stay logged? I wonder how this works.
> thanks in advance,
> Benjamin|||Hi,
ok, interesting. But if this is the case, why should I create full database
backups at all? I mean, I dont want to store the logs forever. If I backup
the entire database or a part of it I don't want to keep the old log files.
Can you tell me how to achieve this?
"Tibor Karaszi" wrote:
> The only backup operation that removes log records is BACKUP LOG. The other types of backup (db,
> diff, file, filegroup, filegrup with diff etc) does not empty the log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
> news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
> > Hi,
> >
> > im thinking about the backup strategy for our new sql 2005 database. I would
> > like to know how you would do it. The scenario is the following:
> >
> > - the db consists of 2 file groups (primary + another one)
> > - the second file group is pretty large (250 GB) and contains a lot of blob
> > data that changes rarely
> > - the primary group is rather small (3 GB), but changes frequently
> >
> > Since the first file group is small, I plan to backup it every day (full
> > backup). The secound group should be fully backuped every 2 weeks. In the
> > meantime I would backup the daily changes of the second group using
> > differncial backups. Any better ideas?
> >
> > What I dont understand is how the transaction log behaves in this case. The
> > log contains the changes for all file groups. So what happens if I backup
> > only one file group? Are the changes of that group removed from the log and
> > the changes of the other group stay logged? I wonder how this works.
> >
> > thanks in advance,
> > Benjamin
>|||Hmm, I'm afraid that I don't get the question...
Are you saying that you don't want to perform transaction log backups? Find, just set the recovery
model for the database to simple.
Or are you saying something else?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
news:EF2882DD-C711-4F3B-A092-BD34C4DDD723@.microsoft.com...
> Hi,
> ok, interesting. But if this is the case, why should I create full database
> backups at all? I mean, I dont want to store the logs forever. If I backup
> the entire database or a part of it I don't want to keep the old log files.
> Can you tell me how to achieve this?
>
> "Tibor Karaszi" wrote:
>> The only backup operation that removes log records is BACKUP LOG. The other types of backup (db,
>> diff, file, filegroup, filegrup with diff etc) does not empty the log.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
>> news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
>> > Hi,
>> >
>> > im thinking about the backup strategy for our new sql 2005 database. I would
>> > like to know how you would do it. The scenario is the following:
>> >
>> > - the db consists of 2 file groups (primary + another one)
>> > - the second file group is pretty large (250 GB) and contains a lot of blob
>> > data that changes rarely
>> > - the primary group is rather small (3 GB), but changes frequently
>> >
>> > Since the first file group is small, I plan to backup it every day (full
>> > backup). The secound group should be fully backuped every 2 weeks. In the
>> > meantime I would backup the daily changes of the second group using
>> > differncial backups. Any better ideas?
>> >
>> > What I dont understand is how the transaction log behaves in this case. The
>> > log contains the changes for all file groups. So what happens if I backup
>> > only one file group? Are the changes of that group removed from the log and
>> > the changes of the other group stay logged? I wonder how this works.
>> >
>> > thanks in advance,
>> > Benjamin
>>|||I have no idea if this would work or not. What are your business
requirements for availability? How much data loss is acceptable? How long
can the system be down for a recovery operation? What type of hardware are
you using?
Sure, you can simply backup the databases using virtually any method that
you choose. But, that doesn't mean the backups are going to accomplish
something. If your business rules state that you can only be offline for 5
minutes and you setup backups that are going to take 1 hour to restore, then
your backups are essentially worthless to the business, because they do not
meet business needs.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
> Hi,
> im thinking about the backup strategy for our new sql 2005 database. I
> would
> like to know how you would do it. The scenario is the following:
> - the db consists of 2 file groups (primary + another one)
> - the second file group is pretty large (250 GB) and contains a lot of
> blob
> data that changes rarely
> - the primary group is rather small (3 GB), but changes frequently
> Since the first file group is small, I plan to backup it every day (full
> backup). The secound group should be fully backuped every 2 weeks. In the
> meantime I would backup the daily changes of the second group using
> differncial backups. Any better ideas?
> What I dont understand is how the transaction log behaves in this case.
> The
> log contains the changes for all file groups. So what happens if I backup
> only one file group? Are the changes of that group removed from the log
> and
> the changes of the other group stay logged? I wonder how this works.
> thanks in advance,
> Benjamin

Backup Strategy for MSSQL

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!We back our stuff up nightly, however you have to decide how much info you are willing to lose should your system crash. If weekly backups have been working and you feel confident in your hardware then stick to that schedule.

HTH,
Aric|||

Quote:

Originally Posted by DbAFtW

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!

Hi there,

Backup / recovery plan should be designed based on the importance of your data. As a consultant i am managing 17 database servers, some servers are configured to run backup routine every hour, whereas, some servers are configured to run backup routine every day. Good luck & Take care.|||Thanks folks for the replies.
Well, the vendors that put the web server and the MSSQL DB in place were using a Simple Recovery Model and I felt the nature of the data dictated transaction log backups. The system accepts college applications and I felt any data loss was something I wanted to avoid due to the importance of that data! The intention is that we can experience minimal loss utilizing the strategy to restore from the weekly full, nightly differential and every half hour transaction log backups.|||</bumpitybump>sql

Backup SQL Server Failures

I get the following errors when I use a maintenance plan to backup all of my databases on one of our servers. Note: When I run them manually they work as long as I put them in a different place on the same drive. The Everone group has full access on the NTFS drive that this is writing to so it can't be a rights issue. Also some of my databases are backed up while others fail. The account that I'm using to back these up is an administrator of the machine, I'm using the same account when I automate them through a maintenance plan that I do for doing them manually. The E:\ drive (where these are trying to be backed up) is Raid 5 with 30 gb free and 30gb used. These backups would only take up about 5gb of space (if they would work).

Please help!!!

BACKUP failed to complete the command BACKUP LOG [WebLogs] TO DISK = N'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731. TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Internal I/O request 0x2E0CB728: Op: Write, pBuffer: 0x17D20000, Size: 983040, Position: 328286720, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x13914200, OffsetHigh: 0x0, m_buf: 0x17D20000, m_len: 983040, m_actualBytes: 0, m_errcode: 2, BackupFile: E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.TR N

BackupMedium::ReportIoError: write failure on backup device 'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.T RN'. Operating system error 2(The system cannot find the file specified.).Try to redefine the Maint.plan and also try using BACKUP LOG statement individually for this database.|||Thanks for your reply.

I've redefined my Maintenance Plan several times, as you could imagine, but to no avail.

Also, I've ran the BACKUP LOG statement on all of my databases in the place of the maintenance plan and received the same error.|||What is the SP[service pack] level on the SQL Server & OS?|||Windows 2000 Server SP3

SQL Server 2000 SP3 (Standard Edition not Enterprise)|||Then how about RECOVERY MODEL on these DBs.|||All of the user databases on this server have the Recovery Model option set to FULL.|||Looks like issues with NTFS permissions, check whether the SQL Services have necessary privileges and also under REGISTRY keys for these accounts.|||Since this is an internal machine and not our Internet SQL Server, the SQL Server Services (Main, Agent, etc.) all run under an account that is an administrator of the machine.

Security rights for both logical drives on the machine have the Everyone group having all rights locally. (The network shares only allow administrators in)|||As you'd mentioned the disk is 60GB, whereas 30Gb is free and another 30 is used.

Could you point out exact size of databases and free space available on the disk. It looks like when the Maint.plan is running typically the error refers to Free space on the server.

For a test try to redesing maint.plan for each database and see the results.|||The drive has 21.0 GB used. It has 38.7 GB free.

There are 16 databases to be backed up. The error happens randomly upon the 3 largest databases.

Email database = 263.88 mb (data and log)
ESP database = 316.25 mb (data and log)
WebLogs database = 5,069.76 gb (data and log)

(note 5 datafiles in the WebLogs and one log file... low amount of transactions and the database and the log is shrunk periodically throughout the day. We backup and truncate the log after a main import process is executed each day.)

I've ran the backup by hand using BACKUP DATABASE and it seldom works on these databases. Sometimes it will actually complete, others it doesn't. Also, the WebLogs database never completes and it always seems to fail at 24 percent (stats = 1 on the backup database command).

The backup log doesn't work at all on these databases whether I run it by hand or through the maintenance plan.|||Sorry for wasting your time.

I finally became fed up and went throught the security check points and actually went to the physical machine and looked in the event viewer of windows.

There I found that one of the Raid disks was going bad. That explains a lot.

Thanks for all of your help.

You had a lot of great ideas. If I had the ability to "dial in" to the machine, I would have spotted the problem much earlier.

I'm betting you agree that a bad disk could cause all of the quirky backup errors?|||Dont' be sorry.... Glad to hear your resolution and efforts, keep it up.
No second thought for these failures, but anytime if you've them again then follow the listings above.

Good luck.

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 Server 2005

Hi,

I created a job to backup my database. Sometimes I get the following error:
The job failed. Unable to determine if the owner (DOMAIN\Administrator) of job Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\Administrator'<c/> error code 0x54b. [SQLSTATE 42000] (Error 15404))

I already deleted the job and made it again. Same problem. I don't get that error every day. For example: I got it 4 times last month.

Does anybody know what I can do to resolve the error?This appears to be a problem with AD. I would guess the job is not able to authenticate itself, or query information from AD. That said, it REALLY a bad idea to run jobs as Domain Admin. You should use service accounts.

backup speed SQL 2000

I'm running a manually written nightly backup script on all dbs on a
server. it's a dev server FYI.
the script does the following for all databases:
dbcc checkdb
dbcc dbreindex (on all relevant tables) -- this is done once a week
only
backup transaction with no_log
backup database
now... the duration for this is around 2 hours, given the number &
size of the databases. however, on the night I do the dbcc dbreindex,
the duration drops to 5 minutes. i've added the dbcc dbreindex to run
on other nights, and yes it drops down to 5 minutes each time. next
time it runs without, back up to 2 hours.....
why?
if any other info is required, i will supply.
Specify an output file and check for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1179959856.718711.314480@.x35g2000prf.googlegr oups.com...
> I'm running a manually written nightly backup script on all dbs on a
> server. it's a dev server FYI.
> the script does the following for all databases:
> dbcc checkdb
> dbcc dbreindex (on all relevant tables) -- this is done once a week
> only
> backup transaction with no_log
> backup database
> now... the duration for this is around 2 hours, given the number &
> size of the databases. however, on the night I do the dbcc dbreindex,
> the duration drops to 5 minutes. i've added the dbcc dbreindex to run
> on other nights, and yes it drops down to 5 minutes each time. next
> time it runs without, back up to 2 hours.....
> why?
> if any other info is required, i will supply.
>

backup speed SQL 2000

I'm running a manually written nightly backup script on all dbs on a
server. it's a dev server FYI.
the script does the following for all databases:
dbcc checkdb
dbcc dbreindex (on all relevant tables) -- this is done once a week
only
backup transaction with no_log
backup database
now... the duration for this is around 2 hours, given the number &
size of the databases. however, on the night I do the dbcc dbreindex,
the duration drops to 5 minutes. i've added the dbcc dbreindex to run
on other nights, and yes it drops down to 5 minutes each time. next
time it runs without, back up to 2 hours.....
why?
if any other info is required, i will supply.Specify an output file and check for error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1179959856.718711.314480@.x35g2000prf.googlegroups.com...
> I'm running a manually written nightly backup script on all dbs on a
> server. it's a dev server FYI.
> the script does the following for all databases:
> dbcc checkdb
> dbcc dbreindex (on all relevant tables) -- this is done once a week
> only
> backup transaction with no_log
> backup database
> now... the duration for this is around 2 hours, given the number &
> size of the databases. however, on the night I do the dbcc dbreindex,
> the duration drops to 5 minutes. i've added the dbcc dbreindex to run
> on other nights, and yes it drops down to 5 minutes each time. next
> time it runs without, back up to 2 hours.....
> why?
> if any other info is required, i will supply.
>sql

backup speed SQL 2000

I'm running a manually written nightly backup script on all dbs on a
server. it's a dev server FYI.
the script does the following for all databases:
dbcc checkdb
dbcc dbreindex (on all relevant tables) -- this is done once a week
only
backup transaction with no_log
backup database
now... the duration for this is around 2 hours, given the number &
size of the databases. however, on the night I do the dbcc dbreindex,
the duration drops to 5 minutes. i've added the dbcc dbreindex to run
on other nights, and yes it drops down to 5 minutes each time. next
time it runs without, back up to 2 hours.....
why?
if any other info is required, i will supply.Specify an output file and check for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<bmwbase-newsgroup@.yahoo.com> wrote in message
news:1179959856.718711.314480@.x35g2000prf.googlegroups.com...
> I'm running a manually written nightly backup script on all dbs on a
> server. it's a dev server FYI.
> the script does the following for all databases:
> dbcc checkdb
> dbcc dbreindex (on all relevant tables) -- this is done once a week
> only
> backup transaction with no_log
> backup database
> now... the duration for this is around 2 hours, given the number &
> size of the databases. however, on the night I do the dbcc dbreindex,
> the duration drops to 5 minutes. i've added the dbcc dbreindex to run
> on other nights, and yes it drops down to 5 minutes each time. next
> time it runs without, back up to 2 hours.....
> why?
> if any other info is required, i will supply.
>

Backup Setup

I am trying to setup the following and was hoping if someone could tell me a more efficient way to set this up. I am not using the maintenance plan or wizard cause Ive had nothing but issues with them:

1. Create a backup device for each day of the week (Sunday - Saturday)

2. Create a job using T-SQL to run maintenance (Rebuild Indexes) and then at the conclusion of the maintenance run a full backup every night at midnight. I would need once job for each day of the week and store them on the proper backup device.

BACKUP DATABASE [DBS1] TO [Sunday Backup] WITH NOFORMAT, NOINIT, NAME = N'DBS1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

GO

declare @.backupSetId as int

select @.backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )

if @.backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND

GO

3. Create a job using T-SQL for transaction log backups to run every hour and store them in the proper backup device. I would need one job for each day of the week.

BACKUP LOG [DBS1 [Sunday Backup] WITH NOFORMAT, NOINIT,

NAME = N'DBS1-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

GO

declare @.backupSetId as int

select @.backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )

if @.backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1"' not found.', 16, 1) end

RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND

GO

This method would give me 7 backups devices and 14 jobs but at least things would be organized nice and neat. Im sure there is a better way to do this so I am looking for some help. Any help would be much appreciated.

Thanks

One simplification would be to have the script build the name of the backup device based on the current day of the week. This way you have one backup job and one log backup job that each run every day.

The following would be an example of building the backup device name:

DECLARE @.BackupDev AS NVARCHAR(30)

select @.BackupDev = DATENAME(dw,GETDATE()) + N'_BACKUP_DEV'

Backup schedule (Differential backups)

Hello all, hope someone will be able to help.
I have a number of medium-sized systems that have the following backup regime:
1. Daily full backups
2. Hourly transaction log backups
3. During the window 9p.m â' 6.a.m the system drive is backed up by TSM.
The plan is created and maintained by the SQL Maintenance plan (sqlmaint).
The old backup files deleted after 48hours as part of the plan to free up
disk space.
A couple of the database grew just over 12GB and I would like implement the
following back schedule for them:
Mon - Differential database backup
Tue - Differential database backup
Wed - Differential database backup
Thu - Differential database backup
Fri - Differential database backup
Sat - Differential database backup
Sun Full database backup
All days Transaction log backups as above
I have scheduled two jobs, one for weekly full backups and the other for
daily differential backups. My question is â'How do I rollover to the next
week?â' I need somehow override the backup device used for differential
backups at the end of the week. I would like to keep all differential
backups for the week and use append to media option. I could not figure out
how to create a separate file as sqlmaint does for complete backups.
Regards,
ruskiIt's pretty easy to create a new file name for each backup.
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
SET @.DBName = 'YourDBName'
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Differential Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
But if you want to use the same filename and overwrite it once a week you
can do something like this:
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
SET @.DBName = 'YourDBName'
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff.BAK'
SET @.Name = @.DBName + N' Differential Backup'
IF DATEPART(dw,GETDATE()) = 1
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
ELSE
BACKUP DATABASE @.DBName TO DISK = @.Device WITH NOINIT , DIFFERENTIAL,
NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
Andrew J. Kelly SQL MVP
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:420D0400-5FD9-461C-B164-2CE18E17444B@.microsoft.com...
> Hello all, hope someone will be able to help.
> I have a number of medium-sized systems that have the following backup
> regime:
> 1. Daily full backups
> 2. Hourly transaction log backups
> 3. During the window 9p.m - 6.a.m the system drive is backed up by TSM.
> The plan is created and maintained by the SQL Maintenance plan (sqlmaint).
> The old backup files deleted after 48hours as part of the plan to free up
> disk space.
> A couple of the database grew just over 12GB and I would like implement
> the
> following back schedule for them:
> Mon - Differential database backup
> Tue - Differential database backup
> Wed - Differential database backup
> Thu - Differential database backup
> Fri - Differential database backup
> Sat - Differential database backup
> Sun Full database backup
> All days Transaction log backups as above
> I have scheduled two jobs, one for weekly full backups and the other for
> daily differential backups. My question is "How do I rollover to the next
> week?" I need somehow override the backup device used for differential
> backups at the end of the week. I would like to keep all differential
> backups for the week and use append to media option. I could not figure
> out
> how to create a separate file as sqlmaint does for complete backups.
> Regards,
> ruski
>|||Thanks, Andrew. That's exactly what I was hopping to achieve. And if I go
with the first solution, would you use xp_cmdshell for deletion of the all
old backup files at the end of the week?
Regards,
ruski
"Andrew J. Kelly" wrote:
> It's pretty easy to create a new file name for each backup.
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> SET @.DBName = 'YourDBName'
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> SET @.Name = @.DBName + N' Differential Backup'
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
> NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>
>
> But if you want to use the same filename and overwrite it once a week you
> can do something like this:
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> SET @.DBName = 'YourDBName'
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff.BAK'
> SET @.Name = @.DBName + N' Differential Backup'
> IF DATEPART(dw,GETDATE()) = 1
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
> NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>
> ELSE
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH NOINIT , DIFFERENTIAL,
> NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>
> --
> Andrew J. Kelly SQL MVP
>
> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
> news:420D0400-5FD9-461C-B164-2CE18E17444B@.microsoft.com...
> > Hello all, hope someone will be able to help.
> >
> > I have a number of medium-sized systems that have the following backup
> > regime:
> > 1. Daily full backups
> > 2. Hourly transaction log backups
> > 3. During the window 9p.m - 6.a.m the system drive is backed up by TSM.
> > The plan is created and maintained by the SQL Maintenance plan (sqlmaint).
> > The old backup files deleted after 48hours as part of the plan to free up
> > disk space.
> >
> > A couple of the database grew just over 12GB and I would like implement
> > the
> > following back schedule for them:
> >
> > Mon - Differential database backup
> > Tue - Differential database backup
> > Wed - Differential database backup
> > Thu - Differential database backup
> > Fri - Differential database backup
> > Sat - Differential database backup
> > Sun Full database backup
> > All days Transaction log backups as above
> >
> > I have scheduled two jobs, one for weekly full backups and the other for
> > daily differential backups. My question is "How do I rollover to the next
> > week?" I need somehow override the backup device used for differential
> > backups at the end of the week. I would like to keep all differential
> > backups for the week and use append to media option. I could not figure
> > out
> > how to create a separate file as sqlmaint does for complete backups.
> >
> > Regards,
> > ruski
> >
>
>|||That is one way to do it. See the attached example. But you can also use
other methods such as the File Scripting Object (FSO) in an Active-X job as
well.
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
--
Andrew J. Kelly SQL MVP
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:E480C761-8130-4A7B-B7EB-B032B0440C1E@.microsoft.com...
> Thanks, Andrew. That's exactly what I was hopping to achieve. And if I go
> with the first solution, would you use xp_cmdshell for deletion of the all
> old backup files at the end of the week?
> Regards,
> ruski
> "Andrew J. Kelly" wrote:
>> It's pretty easy to create a new file name for each backup.
>>
>> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>> SET @.DBName = 'YourDBName'
>> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff_' +
>> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
>> SET @.Name = @.DBName + N' Differential Backup'
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
>> NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>>
>>
>> But if you want to use the same filename and overwrite it once a week you
>> can do something like this:
>> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>> SET @.DBName = 'YourDBName'
>> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff.BAK'
>> SET @.Name = @.DBName + N' Differential Backup'
>> IF DATEPART(dw,GETDATE()) = 1
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
>> NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>>
>> ELSE
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH NOINIT ,
>> DIFFERENTIAL,
>> NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
>> news:420D0400-5FD9-461C-B164-2CE18E17444B@.microsoft.com...
>> > Hello all, hope someone will be able to help.
>> >
>> > I have a number of medium-sized systems that have the following backup
>> > regime:
>> > 1. Daily full backups
>> > 2. Hourly transaction log backups
>> > 3. During the window 9p.m - 6.a.m the system drive is backed up by TSM.
>> > The plan is created and maintained by the SQL Maintenance plan
>> > (sqlmaint).
>> > The old backup files deleted after 48hours as part of the plan to free
>> > up
>> > disk space.
>> >
>> > A couple of the database grew just over 12GB and I would like implement
>> > the
>> > following back schedule for them:
>> >
>> > Mon - Differential database backup
>> > Tue - Differential database backup
>> > Wed - Differential database backup
>> > Thu - Differential database backup
>> > Fri - Differential database backup
>> > Sat - Differential database backup
>> > Sun Full database backup
>> > All days Transaction log backups as above
>> >
>> > I have scheduled two jobs, one for weekly full backups and the other
>> > for
>> > daily differential backups. My question is "How do I rollover to the
>> > next
>> > week?" I need somehow override the backup device used for differential
>> > backups at the end of the week. I would like to keep all differential
>> > backups for the week and use append to media option. I could not
>> > figure
>> > out
>> > how to create a separate file as sqlmaint does for complete backups.
>> >
>> > Regards,
>> > ruski
>> >
>>

backup restore question

Hi,
I wanted to clarify something about the backup restore process. If have the
following backups -
Day1 - 3 AM - Full backup
Day1 - 9 AM - Log backup
Day1 - 3 PM - Log backup
Day1 - 9 PM - Log backup
Day2 - 3 AM - Full backup
Day2 - 9 AM - Log backup
Day2 - 3 PM - Log backup
Day2 - 9 PM - Log backup
I want to restore the database to a point in time of 9 PM on Day2. The Full
backup of Day2 is corrupt. Can I restore the database by applying the
following backups in sequence -
Day1 - 3 AM - Full backup - not recovered
Day1 - 9 AM - Log backup - not recovered
Day1 - 3 PM - Log backup - not recovered
Day1 - 9 PM - Log backup - not recovered
Day2 - 9 AM - Log backup - not recovered
Day2 - 3 PM - Log backup - not recovered
Day2 - 9 PM - Log backup - recovered
Thanks in advance.sharman,
Yes, if none of your log files are corrupt, you can indeed apply them over a
missing full backup, just as you describe. (Thus explaining, for anyone who
wondered, why the full backup does not free up the transaction log.)
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDA7766C-91AB-49E5-85F2-696B1807D59B@.microsoft.com...
> Hi,
> I wanted to clarify something about the backup restore process. If have
> the
> following backups -
> Day1 - 3 AM - Full backup
> Day1 - 9 AM - Log backup
> Day1 - 3 PM - Log backup
> Day1 - 9 PM - Log backup
> Day2 - 3 AM - Full backup
> Day2 - 9 AM - Log backup
> Day2 - 3 PM - Log backup
> Day2 - 9 PM - Log backup
> I want to restore the database to a point in time of 9 PM on Day2. The
> Full
> backup of Day2 is corrupt. Can I restore the database by applying the
> following backups in sequence -
> Day1 - 3 AM - Full backup - not recovered
> Day1 - 9 AM - Log backup - not recovered
> Day1 - 3 PM - Log backup - not recovered
> Day1 - 9 PM - Log backup - not recovered
> Day2 - 9 AM - Log backup - not recovered
> Day2 - 3 PM - Log backup - not recovered
> Day2 - 9 PM - Log backup - recovered
> Thanks in advance.
>
>

Backup Restore Fails

Hi,

I ran a backup procedure and BAK file created.
I moved the file to another server, tried to restore and failed with following error:
"Cannot open backup device 'C:\*****.bak'. Operating system error 5(Access is denied.)"

I checked the file on the original server and the file is accessible: I can see its content with RESTORE FILELISTONLY command.

Any clues?

Thanks,
Mark

Hello Mark

Are you using the Restore dialog to do this operation or are you using T-SQL?

Either case, ensure that the account under which SQL server service is running has access to the 'C' drive.

[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Backup restore fail

Hi,
I am trying to restore database (25 GB) and getting
following error
The backup data in 'F:\BACKUP\ABC.BAK' is incorrectly
formatted. Backups cannot be appended, but existing backup
sets may still be usable.
Has any one solution for this ?
Thanks,
ManojHi Manoj,
This error normally occurs when the filemark in the backup device could not
be read.
The various reasons for this error can be,
1. A media failure on the device where the backup is located.
2. A write failure during the creation of the backup. Eg: Network failre
during a network backup
Solution:
This error normally comes if any one of the file in backup set is
problematic. It might be possible to retrieve other backup sets
from the device by specifying the file number
You can use the RESTORE HEADERONLY command to idenfify the backup file list
and use RESTORE DATABASE to restore the specific file.
Sample code
RESTORE HEADERONLY FROM DISK='c:\backup\dbname.bak'
RESTORE DATABASE mydatabase FROM DISK='C:\backup\dbname.bak' WITH FILE =FileNumber
Have a look into this article,
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q290787
Thanks
Hari
MCDBA
"Manoj" <manoj_raheja@.hotmail.com> wrote in message
news:4d8e01c3ff6e$f2f3df60$a101280a@.phx.gbl...
> Hi,
> I am trying to restore database (25 GB) and getting
> following error
> The backup data in 'F:\BACKUP\ABC.BAK' is incorrectly
> formatted. Backups cannot be appended, but existing backup
> sets may still be usable.
> Has any one solution for this ?
> Thanks,
> Manoj

Backup question

Using sql server 2000...
We are having issues with growing transaction log files...
Is the following a safe practice ?
Assume you create a "full" backup job using the Database Maintenance plan
wizard... This will create Step 1 in an SQL Job.
Would it be safe to add the following code as a step that was executed "on
success" of the first...
Use DatabaseName
BACKUP LOG DatabaseNameWITH NO_LOG
DBCC SHRINKFILE(DatabaseName_Log,10)
GORob
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rob" <robc1@.yahoo.com> wrote in message
news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.co
mcast.com...
> Using sql server 2000...
> We are having issues with growing transaction log files...
> Is the following a safe practice ?
> Assume you create a "full" backup job using the Database Maintenance plan
> wizard... This will create Step 1 in an SQL Job.
> Would it be safe to add the following code as a step that was executed
> "on success" of the first...
> Use DatabaseName
> BACKUP LOG DatabaseNameWITH NO_LOG
> DBCC SHRINKFILE(DatabaseName_Log,10)
> GO
>|||Thanks Uri,
But I still need an interpreter...
Left unchecked, the Log file appears to grow and fill up space on the
server. My assumption, is that IF a good full backup has been successful,
then it should be OK to use SHRINKFILE to lose the transaction file. At
that point, there would be no need to restore a transaction log file...
BTW - I never use shrinkdatabase
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Rob
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.co
mcast.com...
>|||Rob
If you have database is set up withj FULL RECOVERY mode then in order to
control a physical size of the LOG file , you need to BACKUP LOG ...
command, otherwise set up the database with SIMPLE recovery mode and the SQL
Server will take care for it.
If you implement BACKUP LOG file you probably won't lose the data if the
database get corrupted , please make sure what is your/or your boss
requirements.
"Rob" <robc1@.yahoo.com> wrote in message
news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.co
mcast.com...
> Thanks Uri,
> But I still need an interpreter...
> Left unchecked, the Log file appears to grow and fill up space on the
> server. My assumption, is that IF a good full backup has been successful,
> then it should be OK to use SHRINKFILE to lose the transaction file. At
> that point, there would be no need to restore a transaction log file...
> BTW - I never use shrinkdatabase
> Rob
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>|||Hello,
To add on to Uri, perform the transaction log backup in regular intervals
(say 15 minutes), this will make sure that your
log (LDF) file will not grow. Shrinking the LDF life after the log backup is
not a good approch, this will shrino the
LDF file and after each DML operation file will autogrow and this will take
consifderable amount of I/O resources.
One more thing is you can archive all your transaction log backup files
which was taken before the
last full database backup.
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMZ9F$5bHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Rob
> If you have database is set up withj FULL RECOVERY mode then in order to
> control a physical size of the LOG file , you need to BACKUP LOG ...
> command, otherwise set up the database with SIMPLE recovery mode and the
> SQL Server will take care for it.
>
> If you implement BACKUP LOG file you probably won't lose the data if the
> database get corrupted , please make sure what is your/or your boss
> requirements.
>
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.co
mcast.com...
>

backup question

Dear all,
i want to verify whether the following backup planning can restore to the
original environment or not
1.) backup master, msdb, model with full backup to bak
2.) backup all user database with full backup and tran log backup to bak
question
how about the DTS package, user login, replication, job schelding, is it can
restore when i backup to the master and model?Users, packages and jobs are all in the databases you are
backup up (DTS Packages - as long as they are saved to SQL
Server as the location) so backing up all of your databases
and restoring all databases, including system databases will
get things back.
Replication restore strategies depend on how you have
implemented replication. You'd want to backup the publisher,
distributor, subscriber. There are different strategies for
merge, transactional, snapshot replication. You can find
them in books online. Look up the index topic:
replication, backup and restore operations
-Sue
On Mon, 3 Oct 2005 04:17:13 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:

>Dear all,
>i want to verify whether the following backup planning can restore to the
>original environment or not
>1.) backup master, msdb, model with full backup to bak
>2.) backup all user database with full backup and tran log backup to bak
>question
>how about the DTS package, user login, replication, job schelding, is it ca
n
>restore when i backup to the master and model?

Backup question

Using sql server 2000...
We are having issues with growing transaction log files...
Is the following a safe practice ?
Assume you create a "full" backup job using the Database Maintenance plan
wizard... This will create Step 1 in an SQL Job.
Would it be safe to add the following code as a step that was executed "on
success" of the first...
Use DatabaseName
BACKUP LOG DatabaseNameWITH NO_LOG
DBCC SHRINKFILE(DatabaseName_Log,10)
GO
Rob
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rob" <robc1@.yahoo.com> wrote in message
news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com. ..
> Using sql server 2000...
> We are having issues with growing transaction log files...
> Is the following a safe practice ?
> Assume you create a "full" backup job using the Database Maintenance plan
> wizard... This will create Step 1 in an SQL Job.
> Would it be safe to add the following code as a step that was executed
> "on success" of the first...
> Use DatabaseName
> BACKUP LOG DatabaseNameWITH NO_LOG
> DBCC SHRINKFILE(DatabaseName_Log,10)
> GO
>
|||Thanks Uri,
But I still need an interpreter...
Left unchecked, the Log file appears to grow and fill up space on the
server. My assumption, is that IF a good full backup has been successful,
then it should be OK to use SHRINKFILE to lose the transaction file. At
that point, there would be no need to restore a transaction log file...
BTW - I never use shrinkdatabase
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Rob
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com. ..
>
|||Rob
If you have database is set up withj FULL RECOVERY mode then in order to
control a physical size of the LOG file , you need to BACKUP LOG ...
command, otherwise set up the database with SIMPLE recovery mode and the SQL
Server will take care for it.
If you implement BACKUP LOG file you probably won't lose the data if the
database get corrupted , please make sure what is your/or your boss
requirements.
"Rob" <robc1@.yahoo.com> wrote in message
news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com. ..
> Thanks Uri,
> But I still need an interpreter...
> Left unchecked, the Log file appears to grow and fill up space on the
> server. My assumption, is that IF a good full backup has been successful,
> then it should be OK to use SHRINKFILE to lose the transaction file. At
> that point, there would be no need to restore a transaction log file...
> BTW - I never use shrinkdatabase
> Rob
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>
|||Hello,
To add on to Uri, perform the transaction log backup in regular intervals
(say 15 minutes), this will make sure that your
log (LDF) file will not grow. Shrinking the LDF life after the log backup is
not a good approch, this will shrino the
LDF file and after each DML operation file will autogrow and this will take
consifderable amount of I/O resources.
One more thing is you can archive all your transaction log backup files
which was taken before the
last full database backup.
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMZ9F$5bHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Rob
> If you have database is set up withj FULL RECOVERY mode then in order to
> control a physical size of the LOG file , you need to BACKUP LOG ...
> command, otherwise set up the database with SIMPLE recovery mode and the
> SQL Server will take care for it.
>
> If you implement BACKUP LOG file you probably won't lose the data if the
> database get corrupted , please make sure what is your/or your boss
> requirements.
>
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com. ..
>
|||i need to creat a log.ldf file . i am only having the Data.Mdf file with me. I does'nt want to attach the database but i want to only create the log.ldf file how can i creat it.
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Backup question

Using sql server 2000...
We are having issues with growing transaction log files...
Is the following a safe practice ?
Assume you create a "full" backup job using the Database Maintenance plan
wizard... This will create Step 1 in an SQL Job.
Would it be safe to add the following code as a step that was executed "on
success" of the first...
Use DatabaseName
BACKUP LOG DatabaseNameWITH NO_LOG
DBCC SHRINKFILE(DatabaseName_Log,10)
GORob
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rob" <robc1@.yahoo.com> wrote in message
news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
> Using sql server 2000...
> We are having issues with growing transaction log files...
> Is the following a safe practice ?
> Assume you create a "full" backup job using the Database Maintenance plan
> wizard... This will create Step 1 in an SQL Job.
> Would it be safe to add the following code as a step that was executed
> "on success" of the first...
> Use DatabaseName
> BACKUP LOG DatabaseNameWITH NO_LOG
> DBCC SHRINKFILE(DatabaseName_Log,10)
> GO
>|||Thanks Uri,
But I still need an interpreter...
Left unchecked, the Log file appears to grow and fill up space on the
server. My assumption, is that IF a good full backup has been successful,
then it should be OK to use SHRINKFILE to lose the transaction file. At
that point, there would be no need to restore a transaction log file...
BTW - I never use shrinkdatabase
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Rob
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
>> Using sql server 2000...
>> We are having issues with growing transaction log files...
>> Is the following a safe practice ?
>> Assume you create a "full" backup job using the Database Maintenance plan
>> wizard... This will create Step 1 in an SQL Job.
>> Would it be safe to add the following code as a step that was executed
>> "on success" of the first...
>> Use DatabaseName
>> BACKUP LOG DatabaseNameWITH NO_LOG
>> DBCC SHRINKFILE(DatabaseName_Log,10)
>> GO
>|||Rob
If you have database is set up withj FULL RECOVERY mode then in order to
control a physical size of the LOG file , you need to BACKUP LOG ...
command, otherwise set up the database with SIMPLE recovery mode and the SQL
Server will take care for it.
If you implement BACKUP LOG file you probably won't lose the data if the
database get corrupted , please make sure what is your/or your boss
requirements.
"Rob" <robc1@.yahoo.com> wrote in message
news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com...
> Thanks Uri,
> But I still need an interpreter...
> Left unchecked, the Log file appears to grow and fill up space on the
> server. My assumption, is that IF a good full backup has been successful,
> then it should be OK to use SHRINKFILE to lose the transaction file. At
> that point, there would be no need to restore a transaction log file...
> BTW - I never use shrinkdatabase
> Rob
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>> Rob
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
>> Using sql server 2000...
>> We are having issues with growing transaction log files...
>> Is the following a safe practice ?
>> Assume you create a "full" backup job using the Database Maintenance
>> plan wizard... This will create Step 1 in an SQL Job.
>> Would it be safe to add the following code as a step that was executed
>> "on success" of the first...
>> Use DatabaseName
>> BACKUP LOG DatabaseNameWITH NO_LOG
>> DBCC SHRINKFILE(DatabaseName_Log,10)
>> GO
>>
>|||Hello,
To add on to Uri, perform the transaction log backup in regular intervals
(say 15 minutes), this will make sure that your
log (LDF) file will not grow. Shrinking the LDF life after the log backup is
not a good approch, this will shrino the
LDF file and after each DML operation file will autogrow and this will take
consifderable amount of I/O resources.
One more thing is you can archive all your transaction log backup files
which was taken before the
last full database backup.
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMZ9F$5bHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Rob
> If you have database is set up withj FULL RECOVERY mode then in order to
> control a physical size of the LOG file , you need to BACKUP LOG ...
> command, otherwise set up the database with SIMPLE recovery mode and the
> SQL Server will take care for it.
>
> If you implement BACKUP LOG file you probably won't lose the data if the
> database get corrupted , please make sure what is your/or your boss
> requirements.
>
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com...
>> Thanks Uri,
>> But I still need an interpreter...
>> Left unchecked, the Log file appears to grow and fill up space on the
>> server. My assumption, is that IF a good full backup has been
>> successful, then it should be OK to use SHRINKFILE to lose the
>> transaction file. At that point, there would be no need to restore a
>> transaction log file...
>> BTW - I never use shrinkdatabase
>> Rob
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>> Rob
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
>> Using sql server 2000...
>> We are having issues with growing transaction log files...
>> Is the following a safe practice ?
>> Assume you create a "full" backup job using the Database Maintenance
>> plan wizard... This will create Step 1 in an SQL Job.
>> Would it be safe to add the following code as a step that was executed
>> "on success" of the first...
>> Use DatabaseName
>> BACKUP LOG DatabaseNameWITH NO_LOG
>> DBCC SHRINKFILE(DatabaseName_Log,10)
>> GO
>>
>>
>|||i need to creat a log.ldf file . i am only having the Data.Mdf file with me. I does'nt want to attach the database but i want to only create the log.ldf file how can i creat it.
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Backup Problem - multiple family members

I have a backup that has been working successfully for the past two months, but this weekend the following error message appeared.

"The media set for database 'MyDB' has 2 family members, but only 1 are provided. All members must be provided."

I checked the contents from the previous backups, and they all have 1 family member, and 1 media set?

Any ideas on what has changed, and what I need to do to fix this?i have encountered this. just delete your backup device from the harddisk totally then make a new backup device.

hope it should work for you.|||Thanks, that worked. Though I still wonder what caused the problem.

Originally posted by kenz
i have encountered this. just delete your backup device from the harddisk totally then make a new backup device.

hope it should work for you.