Backup to Network Drive: failure

Showing posts with label failure. Show all posts

Thursday, March 8, 2012

Backups to network share fail

I am having an odd failure when backing up my SQL 2000 SP3 server to a
network share. I receive the following error:
BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
failed to create. Operating system error = 5(Access is denied.).
However, the following T-SQL command does run and create the appropriate
file in the same network folder:
EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
Both the SQL Server and SQL Server Agent is running under a domain user
account which has the required permissions to this share. Other SQL Servers
(SP3 and SP4) which also run under the same domain user account are backing
up to the same share without any errors.
Any insight into resolving this issue would be appreciated.HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>I am having an odd failure when backing up my SQL 2000 SP3 server to a
> network share. I receive the following error:
> BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> failed to create. Operating system error = 5(Access is denied.).
> However, the following T-SQL command does run and create the appropriate
> file in the same network folder:
> EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> Both the SQL Server and SQL Server Agent is running under a domain user
> account which has the required permissions to this share. Other SQL
> Servers
> (SP3 and SP4) which also run under the same domain user account are
> backing
> up to the same share without any errors.
> Any insight into resolving this issue would be appreciated.
>|||The maintenance plan is properly configured to backup to the UNC name - in
the exact same manner as all the other servers that are working properly.
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > network share. I receive the following error:
> >
> > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > failed to create. Operating system error = 5(Access is denied.).
> >
> > However, the following T-SQL command does run and create the appropriate
> > file in the same network folder:
> >
> > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> >
> > Both the SQL Server and SQL Server Agent is running under a domain user
> > account which has the required permissions to this share. Other SQL
> > Servers
> > (SP3 and SP4) which also run under the same domain user account are
> > backing
> > up to the same share without any errors.
> >
> > Any insight into resolving this issue would be appreciated.
> >
>|||I have found in those cases that it is usually either a name resolution
issue or a permissions issue.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:53C105CD-2656-4A02-A708-4E6C43F4077F@.microsoft.com...
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
> "Geoff N. Hiten" wrote:
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?kbid=555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
>> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>> >I am having an odd failure when backing up my SQL 2000 SP3 server to a
>> > network share. I receive the following error:
>> >
>> > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
>> > failed to create. Operating system error = 5(Access is denied.).
>> >
>> > However, the following T-SQL command does run and create the
>> > appropriate
>> > file in the same network folder:
>> >
>> > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
>> >
>> > Both the SQL Server and SQL Server Agent is running under a domain user
>> > account which has the required permissions to this share. Other SQL
>> > Servers
>> > (SP3 and SP4) which also run under the same domain user account are
>> > backing
>> > up to the same share without any errors.
>> >
>> > Any insight into resolving this issue would be appreciated.
>> >
>>|||Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
\\server\share > \\server\share\test.txt' "
On Apr 10, 4:16 pm, Richard Cote
<RichardC...@.discussions.microsoft.com> wrote:
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
>
> "Geoff N. Hiten" wrote:
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> >http://support.microsoft.com/?kbid=555128
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> > "Richard Cote" <RichardC...@.discussions.microsoft.com> wrote in message
> >news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> > >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > > network share. I receive the following error:
> > > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > > failed to create. Operating system error = 5(Access is denied.).
> > > However, the following T-SQL command does run and create the appropriate
> > > file in the same network folder:
> > > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> > > Both the SQL Server and SQL Server Agent is running under a domain user
> > > account which has the required permissions to this share. Other SQL
> > > Servers
> > > (SP3 and SP4) which also run under the same domain user account are
> > > backing
> > > up to the same share without any errors.
> > > Any insight into resolving this issue would be appreciated.- Hide quoted text -
> - Show quoted text -|||The xp_cmdshell command works for any sysadmin user and the SQL Server and
SQL Server Agent logins. However, backups still fail to the same network
share.
This problem seems to be isolated to this one server, and is very
perplexing. Turning on auditing on the file share does not reveal any audit
failures. Choosing a different share with full file and share permissions
granted to Everyone also results in the same access denied error.
"schal" wrote:
> Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
> \\server\share > \\server\share\test.txt' "
>
> On Apr 10, 4:16 pm, Richard Cote
> <RichardC...@.discussions.microsoft.com> wrote:
> > The maintenance plan is properly configured to backup to the UNC name - in
> > the exact same manner as all the other servers that are working properly.
> >
> >
> >
> > "Geoff N. Hiten" wrote:
> > > HowTo: Backup to UNC name using Database Maintenance Wizard
> > >http://support.microsoft.com/?kbid=555128
> >
> > > --
> > > Geoff N. Hiten
> > > Senior Database Administrator
> > > Microsoft SQL Server MVP
> >
> > > "Richard Cote" <RichardC...@.discussions.microsoft.com> wrote in message
> > >news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> > > >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > > > network share. I receive the following error:
> >
> > > > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > > > failed to create. Operating system error = 5(Access is denied.).
> >
> > > > However, the following T-SQL command does run and create the appropriate
> > > > file in the same network folder:
> >
> > > > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> >
> > > > Both the SQL Server and SQL Server Agent is running under a domain user
> > > > account which has the required permissions to this share. Other SQL
> > > > Servers
> > > > (SP3 and SP4) which also run under the same domain user account are
> > > > backing
> > > > up to the same share without any errors.
> >
> > > > Any insight into resolving this issue would be appreciated.- Hide quoted text -
> >
> > - Show quoted text -
>
>

Backups to network share fail

Saturday, February 25, 2012

Backups

When backing up a database in SQL 2000 I get a failure on
the transaction logs. The database is configured
as 'simple' would this expalin the error? Are the
transaction logs only backed up for 'full' databases.yes full and Bulk.
But can you post error?
Yovan Fernandez
"Sarah Scott" <SarahScott_1@.hotmail.com> wrote in message
news:592101c376dd$858790d0$a401280a@.phx.gbl...
> When backing up a database in SQL 2000 I get a failure on
> the transaction logs. The database is configured
> as 'simple' would this expalin the error? Are the
> transaction logs only backed up for 'full' databases.|||Short answers are: 1. Yes. 2. Question is not meaninful
Please have a look in BOL for the different recovery models. This is an
important decision and you should understand the ramifications of the
various options available to you.
"Sarah Scott" <SarahScott_1@.hotmail.com> wrote in message
news:592101c376dd$858790d0$a401280a@.phx.gbl...
> When backing up a database in SQL 2000 I get a failure on
> the transaction logs. The database is configured
> as 'simple' would this expalin the error? Are the
> transaction logs only backed up for 'full' databases.

Friday, February 24, 2012

BackupIoRequest::WaitForIoCompletion failure when restoring database

I have MS SQL 200 Server Developer Edition Installed from MSDN collection. (New installation with service pack 2). When I try to restore 7GB or bigger database, following error occurs in eventlog:

Source: MSSQLSERVER
Category: (6)
ID: 18210
Date: 11.9.2007
Time: 14:50:09
Description:
BackupIoRequest::WaitForIoCompletion: write failure on backup device ''. Operating system error 1450(here is message in local language like "here is no resources to complete requested service").

But I am not writting to backup device, I am restoring. Thank for any helpful answer.

Not sure about the cause of the error.. Check if its related to this KB : http://support.microsoft.com/default.aspx?scid=kb;en-us;304101

|||

Thank you, dinakar.

KB howto i tried, but with no success. Sad

Sunday, February 12, 2012

Backup transaction logs when the database is damaged.

Hi,
Due to a disk failure, I lost my .mdf file of a database. Of course I have a
backup, of the database and transactionlogs from last night, but I want to
restore untill the time of failure. So I tried to follow the procedure
explained in : how to backup transaction logs when the database is damaged.
But it wont work because I can't bring my database online. The only thing I
can do is restore my backup from last night but all the work that was
performed during the day is lost.
Can anyone help?
Thanks
FelixFirst, you have to be able to bring the SQL server itself online. If you
can't do that, you can't get any further. If the server is online but the
user database is missing the .mdf file, you can use the BACKUP LOG command
with the NO_TRUNCATE option. This will extract the "tail" of the log and
allow you to recover the missing transactions. This command works even with
a suspect or damaged database, provided the log file is undamaged.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> Hi,
> Due to a disk failure, I lost my .mdf file of a database. Of course I have
> a
> backup, of the database and transactionlogs from last night, but I want to
> restore untill the time of failure. So I tried to follow the procedure
> explained in : how to backup transaction logs when the database is
> damaged.
> But it wont work because I can't bring my database online. The only thing
> I
> can do is restore my backup from last night but all the work that was
> performed during the day is lost.
> Can anyone help?
> Thanks
> Felix|||Hi Geoff,
Thanks for the very fast reply. The situation is the following:
My SQL server is online, I only lost the .mdf file of one user database.
The user database is offline (of course .mdf file is lost).
I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE' to
try and save the last portion of the log file but:
Server: MSG 942, Level 14, State3, Line 1
Datebase 'user_database' cannot be opened because it is offline.
How to proceed now?
Thanks in advance
Felix
"Geoff N. Hiten" wrote:
> First, you have to be able to bring the SQL server itself online. If you
> can't do that, you can't get any further. If the server is online but the
> user database is missing the .mdf file, you can use the BACKUP LOG command
> with the NO_TRUNCATE option. This will extract the "tail" of the log and
> allow you to recover the missing transactions. This command works even with
> a suspect or damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> > Hi,
> > Due to a disk failure, I lost my .mdf file of a database. Of course I have
> > a
> > backup, of the database and transactionlogs from last night, but I want to
> > restore untill the time of failure. So I tried to follow the procedure
> > explained in : how to backup transaction logs when the database is
> > damaged.
> > But it wont work because I can't bring my database online. The only thing
> > I
> > can do is restore my backup from last night but all the work that was
> > performed during the day is lost.
> > Can anyone help?
> >
> > Thanks
> >
> > Felix
>
>|||Offline and Suspect are two very different things in SQL Server. Offline is
a SQL controlled database state. Suspect is a reaction to an external
event. Try ALTER DATABASE user_database SET ONLINE and then try the backup
log command.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:93749B04-F531-4C2E-9395-F7F2ED4E2EB4@.microsoft.com...
> Hi Geoff,
> Thanks for the very fast reply. The situation is the following:
> My SQL server is online, I only lost the .mdf file of one user database.
> The user database is offline (of course .mdf file is lost).
> I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE'
> to
> try and save the last portion of the log file but:
> Server: MSG 942, Level 14, State3, Line 1
> Datebase 'user_database' cannot be opened because it is offline.
> How to proceed now?
> Thanks in advance
> Felix
>
> "Geoff N. Hiten" wrote:
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further. If the server is online but
>> the
>> user database is missing the .mdf file, you can use the BACKUP LOG
>> command
>> with the NO_TRUNCATE option. This will extract the "tail" of the log and
>> allow you to recover the missing transactions. This command works even
>> with
>> a suspect or damaged database, provided the log file is undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> > Hi,
>> > Due to a disk failure, I lost my .mdf file of a database. Of course I
>> > have
>> > a
>> > backup, of the database and transactionlogs from last night, but I want
>> > to
>> > restore untill the time of failure. So I tried to follow the procedure
>> > explained in : how to backup transaction logs when the database is
>> > damaged.
>> > But it wont work because I can't bring my database online. The only
>> > thing
>> > I
>> > can do is restore my backup from last night but all the work that was
>> > performed during the day is lost.
>> > Can anyone help?
>> >
>> > Thanks
>> >
>> > Felix
>>|||> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further.
Some nit-picking, if you don't mind ;-)
You can get further. Copy the ldf file to a healthy SQL Server. Create a database with same file
names as the original database. Stop that SQL Server. Delete the newly created database files. Copy
the ldf file from the crashed machine to the place of the newly created database ldf file. Start
that SQL Server. That database is now suspect. Do the backup using NO_TRUNCATE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further. If the server is online but the user database is missing the .mdf file, you can
> use the BACKUP LOG command with the NO_TRUNCATE option. This will extract the "tail" of the log
> and allow you to recover the missing transactions. This command works even with a suspect or
> damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I have a
>> backup, of the database and transactionlogs from last night, but I want to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is damaged.
>> But it wont work because I can't bring my database online. The only thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>|||Good point.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further.
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further. If the server is online but
>> the user database is missing the .mdf file, you can use the BACKUP LOG
>> command with the NO_TRUNCATE option. This will extract the "tail" of the
>> log and allow you to recover the missing transactions. This command
>> works even with a suspect or damaged database, provided the log file is
>> undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I
>> have a
>> backup, of the database and transactionlogs from last night, but I want
>> to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is
>> damaged.
>> But it wont work because I can't bring my database online. The only
>> thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>|||Woooo...Tibor does that work? Kinda cool if it does ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further.
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further. If the server is online but
>> the user database is missing the .mdf file, you can use the BACKUP LOG
>> command with the NO_TRUNCATE option. This will extract the "tail" of the
>> log and allow you to recover the missing transactions. This command
>> works even with a suspect or damaged database, provided the log file is
>> undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I
>> have a
>> backup, of the database and transactionlogs from last night, but I want
>> to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is
>> damaged.
>> But it wont work because I can't bring my database online. The only
>> thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>|||> Woooo...Tibor does that work? Kinda cool if it does ;-)
Yep, sure is. I've done this several times, when customers had suspect databases, stopped SQL
Server, copied the ldf file "for safety", started SQL Server, dropped the database since "it is
suspect anyhow...". See http://support.microsoft.com/default.aspx?scid=kb;en-us;253817. The KB
states rebuilding master on the same machine, but that is no different from doing the same operation
on some other machine. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Woooo...Tibor does that work? Kinda cool if it does ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you can't do that, you
>> can't get any further.
>> Some nit-picking, if you don't mind ;-)
>> You can get further. Copy the ldf file to a healthy SQL Server. Create a database with same file
>> names as the original database. Stop that SQL Server. Delete the newly created database files.
>> Copy the ldf file from the crashed machine to the place of the newly created database ldf file.
>> Start that SQL Server. That database is now suspect. Do the backup using NO_TRUNCATE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you can't do that, you
>> can't get any further. If the server is online but the user database is missing the .mdf file,
>> you can use the BACKUP LOG command with the NO_TRUNCATE option. This will extract the "tail" of
>> the log and allow you to recover the missing transactions. This command works even with a
>> suspect or damaged database, provided the log file is undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I have a
>> backup, of the database and transactionlogs from last night, but I want to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is damaged.
>> But it wont work because I can't bring my database online. The only thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>|||Ok...cudos to you...you da man ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
>> Woooo...Tibor does that work? Kinda cool if it does ;-)
> Yep, sure is. I've done this several times, when customers had suspect
> databases, stopped SQL Server, copied the ldf file "for safety", started
> SQL Server, dropped the database since "it is suspect anyhow...". See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;253817. The KB
> states rebuilding master on the same machine, but that is no different
> from doing the same operation on some other machine. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
>> Woooo...Tibor does that work? Kinda cool if it does ;-)
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If
>> you can't do that, you can't get any further.
>> Some nit-picking, if you don't mind ;-)
>> You can get further. Copy the ldf file to a healthy SQL Server. Create a
>> database with same file names as the original database. Stop that SQL
>> Server. Delete the newly created database files. Copy the ldf file from
>> the crashed machine to the place of the newly created database ldf file.
>> Start that SQL Server. That database is now suspect. Do the backup using
>> NO_TRUNCATE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If
>> you can't do that, you can't get any further. If the server is online
>> but the user database is missing the .mdf file, you can use the BACKUP
>> LOG command with the NO_TRUNCATE option. This will extract the "tail"
>> of the log and allow you to recover the missing transactions. This
>> command works even with a suspect or damaged database, provided the log
>> file is undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I
>> have a
>> backup, of the database and transactionlogs from last night, but I
>> want to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is
>> damaged.
>> But it wont work because I can't bring my database online. The only
>> thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>>
>|||Hi,
I have to thank you all, I succeeded in restoring the userdatabase to the
point of failure. My problem was that I always brought de database offline.
How it worked:
I stopped and started the SQL server, the database was left in status
suspect, not offline.
In this status, I was able to execute : backup log user_db to disk = 'file'
with INIT, NO_TRUNCATE.
Then I restored the backup from last night but with the NORECOVERY clause.
Then I executed restore log user_db from disk = 'file' with RECOVERY
And my database was back online containing everything until the point of
failure.
Thank you very much.
Hope this helps other people too.
"Jerry Spivey" wrote:
> Ok...cudos to you...you da man ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
> >> Woooo...Tibor does that work? Kinda cool if it does ;-)
> >
> > Yep, sure is. I've done this several times, when customers had suspect
> > databases, stopped SQL Server, copied the ldf file "for safety", started
> > SQL Server, dropped the database since "it is suspect anyhow...". See
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;253817. The KB
> > states rebuilding master on the same machine, but that is no different
> > from doing the same operation on some other machine. :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> > news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> >> Woooo...Tibor does that work? Kinda cool if it does ;-)
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> >> in message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> >> First, you have to be able to bring the SQL server itself online. If
> >> you can't do that, you can't get any further.
> >>
> >> Some nit-picking, if you don't mind ;-)
> >>
> >> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> >> database with same file names as the original database. Stop that SQL
> >> Server. Delete the newly created database files. Copy the ldf file from
> >> the crashed machine to the place of the newly created database ldf file.
> >> Start that SQL Server. That database is now suspect. Do the backup using
> >> NO_TRUNCATE.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> >> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> >> First, you have to be able to bring the SQL server itself online. If
> >> you can't do that, you can't get any further. If the server is online
> >> but the user database is missing the .mdf file, you can use the BACKUP
> >> LOG command with the NO_TRUNCATE option. This will extract the "tail"
> >> of the log and allow you to recover the missing transactions. This
> >> command works even with a suspect or damaged database, provided the log
> >> file is undamaged.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >>
> >> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> >> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> >> Hi,
> >> Due to a disk failure, I lost my .mdf file of a database. Of course I
> >> have a
> >> backup, of the database and transactionlogs from last night, but I
> >> want to
> >> restore untill the time of failure. So I tried to follow the procedure
> >> explained in : how to backup transaction logs when the database is
> >> damaged.
> >> But it wont work because I can't bring my database online. The only
> >> thing I
> >> can do is restore my backup from last night but all the work that was
> >> performed during the day is lost.
> >> Can anyone help?
> >>
> >> Thanks
> >>
> >> Felix
> >>
> >>
> >>
> >>
> >>
> >
>
>

Backup transaction logs when the database is damaged.

Friday, February 10, 2012

Backup to Network

We just recently experiences a catastrophic hard drive failure. It is off
right now to the data recovery folks and I hope we get something back. In
the mean time I have been asked to come up with some backup plan. Because
the backups were on the same drive as the database, this is not a good
solution. We are implementing a RAID system, but I have some questions
about the following:
1. How well does log shipping work. The data is not real-time and we could
afford to loose the little data we would produce in the time it would take
to switch to the new server?
2. Can I back up the files to a network. I would like to do a Complete
backup once a sunday, full backups once a sunday, then do incremental
backups every 15-30 minutes. I would like to make the backups to the local
drive and two network locations. How can I back up across the network?
When I set up the backups I only get my local drives (c:,d

.
We are using SQL Server 2000 (will migrate to 2005 in a year) with the
current service pack running on Win 2k3
John
Having your database on the same drive on your backup not only leaves you
vulnerable to the crash you experience but also leads to IO contention
between your backup and your database files.
1) Log shipping works well, but it is not really scalable for very large
databases or large numbers of databases. The main reason people don't use
log shipping is that their exposure to data loss is significantly greater
than with other HA technologies.
2) Yes you can but it is not recommended. You should backup locally and then
copy the backup off the local drive to a network location.
Here is one way you can do backup across the network :
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
> We just recently experiences a catastrophic hard drive failure. It is off
> right now to the data recovery folks and I hope we get something back. In
> the mean time I have been asked to come up with some backup plan. Because
> the backups were on the same drive as the database, this is not a good
> solution. We are implementing a RAID system, but I have some questions
> about the following:
> 1. How well does log shipping work. The data is not real-time and we
> could afford to loose the little data we would produce in the time it
> would take to switch to the new server?
> 2. Can I back up the files to a network. I would like to do a Complete
> backup once a sunday, full backups once a sunday, then do incremental
> backups every 15-30 minutes. I would like to make the backups to the
> local drive and two network locations. How can I back up across the
> network? When I set up the backups I only get my local drives (c:,d

.
> We are using SQL Server 2000 (will migrate to 2005 in a year) with the
> current service pack running on Win 2k3
>
> John
>
|||If having the backup on the same drive as the backup leaves us this
vulnerable, then how we backup to another location. We have a c: and d:
drive (one drive two partitions) and they are the only devices that show.
It seems odd to me that SQL Server would not have the ability to backup
across the network.
When I ran the BACKUP DATABASE command I get the following error:
Operating system error 1385(Logon failure: the user has not been granted the
requested logon type at this computer.).
I am the administrator and have full rights to this location. What user is
this trying to back up to?
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Having your database on the same drive on your backup not only leaves you
> vulnerable to the crash you experience but also leads to IO contention
> between your backup and your database files.
> 1) Log shipping works well, but it is not really scalable for very large
> databases or large numbers of databases. The main reason people don't use
> log shipping is that their exposure to data loss is significantly greater
> than with other HA technologies.
> 2) Yes you can but it is not recommended. You should backup locally and
> then copy the backup off the local drive to a network location.
> Here is one way you can do backup across the network :
> BACKUP DATABASE DatabaseName TO DISK =
> '\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Wright" <riley_wrightx@.hotmail.com> wrote in message
> news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
>
|||If you have your backup on the same drive as your datafile and this drive
fails you have lost your backup and your datafiles.
If you have your backup on a different drive and your backup fails you still
have your data files which is some level of protection.
If you have your backup on a different drive and your database gets corrupt
you still have your backup files which you can restore from.
If you backup to a local drive and immediately copy it off the network your
exposure is only in the time interval it takes to copy the backup off your
local drive.
If you backup to the network it takes longer so your vulnerability is more
than if you were to backup locally and then copy.
to backup to the network drive do this:
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
The error message tells me that the account that you are using to run the
backup does not have rights to write to the network share or the files and
folders beneath it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:%23IWAQ%23seHHA.4872@.TK2MSFTNGP03.phx.gbl...
> If having the backup on the same drive as the backup leaves us this
> vulnerable, then how we backup to another location. We have a c: and d:
> drive (one drive two partitions) and they are the only devices that show.
> It seems odd to me that SQL Server would not have the ability to backup
> across the network.
> When I ran the BACKUP DATABASE command I get the following error:
> Operating system error 1385(Logon failure: the user has not been granted
> the requested logon type at this computer.).
>
> I am the administrator and have full rights to this location. What user
> is this trying to back up to?
> John
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
>

Thursday, March 8, 2012

Backups to network share fail

Backups to network share fail

Backups to network share fail

Saturday, February 25, 2012

Backups

Friday, February 24, 2012

BackupIoRequest::WaitForIoCompletion failure when restoring database

Sunday, February 12, 2012

Backup transaction logs when the database is damaged.

Backup transaction logs when the database is damaged.

Backup transaction logs when the database is damaged.

Friday, February 10, 2012

Backup to Network

Backup to Network Drive

Blog Archive

About Me