Backup to Network Drive: transaction

Showing posts with label transaction. Show all posts

Sunday, March 25, 2012

Basic control errors (transaction)

Hi. As I commented before, I'm new with all that and, with your help, I'm
finishing my first SP.
Now I want to wrap it inside a transaction, so if there was any error, make
a rollback. If not, commit the transaction.
How can I do that?
Regards,
Diego F.I tell you what I'm doing:
declare @.errores as bit
begin transaction
set @.errores = 0
...
sql instruction
...
if (@.@.error<>0) set @.errores = 1
...
sql instruction
...
if (@.@.error<>0) set @.errores = 1
if (@.errores=0)
commit transaction
else
rollback transaction
And that's all.
Is that a good way to do it?
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribi en el mensaje
news:etaEJ5RjFHA.3332@.TK2MSFTNGP10.phx.gbl...
> Hi. As I commented before, I'm new with all that and, with your help, I'm
> finishing my first SP.
> Now I want to wrap it inside a transaction, so if there was any error,
> make a rollback. If not, commit the transaction.
> How can I do that?
> --
> Regards,
> Diego F.
>
>|||Hi,
Looks good. see also SAVE TRANSACTION in books online.
Thanks
Hari
SQL Server MVP
"Diego F." <diegofrNO@.terra.es> wrote in message
news:eRdWaDSjFHA.2904@.tk2msftngp13.phx.gbl...
>I tell you what I'm doing:
> declare @.errores as bit
> begin transaction
> set @.errores = 0
> ...
> sql instruction
> ...
> if (@.@.error<>0) set @.errores = 1
> ...
> sql instruction
> ...
> if (@.@.error<>0) set @.errores = 1
> if (@.errores=0)
> commit transaction
> else
> rollback transaction
> And that's all.
> Is that a good way to do it?
> --
> Regards,
> Diego F.
>
>
> "Diego F." <diegofrNO@.terra.es> escribi en el mensaje
> news:etaEJ5RjFHA.3332@.TK2MSFTNGP10.phx.gbl...
>|||Check this out:
http://msdn.microsoft.com/newsgroup...c7ea&sloc=en-us
ML|||Hi
@.@.ERROR contains the error number which you are loosing by doing it your wa
y.
You may want to read the topic "Using @.@.ERROR" in books online. The
following also has examples and are a 'must read'!
http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html
John
"Diego F." wrote:

> I tell you what I'm doing:
> declare @.errores as bit
> begin transaction
> set @.errores = 0
> ...
> sql instruction
> ...
> if (@.@.error<>0) set @.errores = 1
> ...
> sql instruction
> ...
> if (@.@.error<>0) set @.errores = 1
> if (@.errores=0)
> commit transaction
> else
> rollback transaction
> And that's all.
> Is that a good way to do it?
> --
> Regards,
> Diego F.
>
>
> "Diego F." <diegofrNO@.terra.es> escribió en el mensaje
> news:etaEJ5RjFHA.3332@.TK2MSFTNGP10.phx.gbl...
>
>|||OK, but I don't need the error number by now.
Thanks.
Regards,
Diego F.
"John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
news:F2A2A03F-5B9B-40F6-8169-EA1FB3CE9B62@.microsoft.com...
> Hi
> @.@.ERROR contains the error number which you are loosing by doing it your
> way.
> You may want to read the topic "Using @.@.ERROR" in books online. The
> following also has examples and are a 'must read'!
> http://www.sommarskog.se/error-handling-I.html and
> http://www.sommarskog.se/error-handling-II.html
> John
> "Diego F." wrote:
>|||Hi
It is not a good idea to throw away information that may be important when
trying to work out why/what has gone wrong. There error number may
significantly reduce the time it takes to isolate a particular problem and
should be propogated back to whereever the problem is reported.
John
"Diego F." wrote:

> OK, but I don't need the error number by now.
> Thanks.
> --
> Regards,
> Diego F.
>
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:F2A2A03F-5B9B-40F6-8169-EA1FB3CE9B62@.microsoft.com...
>
>

Sunday, March 11, 2012

Bad code clogs replication

I am using Transaction replication on sql2005 SP2.
When deploying a new stored procedure that is supposed to be tested, I
got an error stateing that the number of columns in the select did not
match the number in the insert. I checked the SP and it was fine.
1) Was this really relating to the code, or was there an error in
replication?
2) How do you clear 'bad' transactions from replication?
Thanks,
Peter Cwik
Use the skiperror parameter on the distribution agent to skip these
errors for transactional replication.
Can you provide me with the code for the proc in question. I have not
encountered such an error yet in SQL Server.
I normally deploy changes to procs using sp_addscriptexec.
On Jan 8, 2:15 pm, PeterCwik <pjc...@.gmail.com> wrote:
> I am using Transaction replication on sql2005 SP2.
> When deploying a new stored procedure that is supposed to be tested, I
> got an error stateing that the number of columns in the select did not
> match the number in the insert. I checked the SP and it was fine.
> 1) Was this really relating to the code, or was there an error in
> replication?
> 2) How do you clear 'bad' transactions from replication?
> Thanks,
> Peter Cwik

Wednesday, March 7, 2012

Backups Failing

I set up a full backup maintenance plan for my databases and the database portion of the backup jobs completes successfully, but the transaction log portion successfully backs up master and model, but fails for the other databases with the message:

Backup can not be performed on database 'msdb'. This sub task is ignored.

What's the problem?

Bob

Check the recovery model of your databases. They are probably in SIMPLE mode so transaction logs are not allowed.

HTH!

|||

I thought this was answered, but it's not. I have never had the master db transaction log backed up. I have set it to full backup as I have with the others and they all work, but master still does not.

Bob

|||

You can only perform transaction log backups on databases in the full or bulk-logged recovery modes (check your db options for this). Master and MSDB databases are always set to simple therefore no transaction log backup can occur on these databases. When configuring your maintenance plans these should be omitted.

|||As said above you can perform only Full backups with Master database regardless of the recovery model ! and full,differential and tran log backups with msdb db if the recovery model is full or bulk logged ! for model db if the recovery is full all 3 backups are possible if its simple only tran logs are not possible in model

|||

Thanks all - I see that now.

Saturday, February 25, 2012

backups - complete, differential and transaction log

Hi,
I am new to database administration and I was looking at the backups setup
on a database-
Full backup - every week, overwrite existing media
Differential backup - every night, append to media
Transaction log backup - every hour, append to media
The size of the differential backup file has grown to 40 GB while the actual
database full backup is just 1 GB. This led me to wonder why do we need to
use the setting "Append to media" for the Differential Backup. It should be
"Overwrite existing media" since I am using a disk to write the backups?
Am I correct? What's the correct setting for transaction log backups? Thanks.For DIFFERENTIAL backups: When you have to restore from backups, you will
need to restore the last FULL backup, and then restore the last DIFFERENTIAL
backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
since the last FULL backup. (And then, of course, the remaining TLOG backups
made since the last DIFFERENTIAL.)
Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
during the stressed restore
It is ok to have only the last DIFFERENTIAL.
TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> Hi,
> I am new to database administration and I was looking at the backups setup
> on a database-
> Full backup - every week, overwrite existing media
> Differential backup - every night, append to media
> Transaction log backup - every hour, append to media
> The size of the differential backup file has grown to 40 GB while the
> actual
> database full backup is just 1 GB. This led me to wonder why do we need to
> use the setting "Append to media" for the Differential Backup. It should
> be
> "Overwrite existing media" since I am using a disk to write the backups?
> Am I correct? What's the correct setting for transaction log backups?
> Thanks.|||Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
Am I correct?
"Arnie Rowland" wrote:
> For DIFFERENTIAL backups: When you have to restore from backups, you will
> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
> since the last FULL backup. (And then, of course, the remaining TLOG backups
> made since the last DIFFERENTIAL.)
> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
> during the stressed restore
> It is ok to have only the last DIFFERENTIAL.
> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> > Hi,
> >
> > I am new to database administration and I was looking at the backups setup
> > on a database-
> > Full backup - every week, overwrite existing media
> > Differential backup - every night, append to media
> > Transaction log backup - every hour, append to media
> >
> > The size of the differential backup file has grown to 40 GB while the
> > actual
> > database full backup is just 1 GB. This led me to wonder why do we need to
> > use the setting "Append to media" for the Differential Backup. It should
> > be
> > "Overwrite existing media" since I am using a disk to write the backups?
> >
> > Am I correct? What's the correct setting for transaction log backups?
> > Thanks.
>
>|||What you need to keep only you can decide. When you do a restore, you use:
Most recent db backup
Most recent diff backup
All subsequent log backups after the diff backup.
But:
Say you had a database corruption introduced between the last db backup and the most recent diff
backup. And say that the corruption is not still present if you restore according to above scheme.
You would not want to restore the most recent db backup, and all subsequent log backup (so the
corruption is not likely to be re-introduced).
Or say that you need a state of the database as it were at tuesday 14:34. If you did db backup sun,
diff mon, diff, tue, diff wed, diff thu, you would now want to restore db, diff tue night, and log
backups until that point in time.
So you do need to carefully consider your possible restore scenarios, before you can say what you
need.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:3E7C4E57-2AAB-47C1-A365-99AA537DC2AD@.microsoft.com...
> Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
> 'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
> since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
> Am I correct?
> "Arnie Rowland" wrote:
>> For DIFFERENTIAL backups: When you have to restore from backups, you will
>> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
>> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
>> since the last FULL backup. (And then, of course, the remaining TLOG backups
>> made since the last DIFFERENTIAL.)
>> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
>> during the stressed restore
>> It is ok to have only the last DIFFERENTIAL.
>> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
>> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
>> > Hi,
>> >
>> > I am new to database administration and I was looking at the backups setup
>> > on a database-
>> > Full backup - every week, overwrite existing media
>> > Differential backup - every night, append to media
>> > Transaction log backup - every hour, append to media
>> >
>> > The size of the differential backup file has grown to 40 GB while the
>> > actual
>> > database full backup is just 1 GB. This led me to wonder why do we need to
>> > use the setting "Append to media" for the Differential Backup. It should
>> > be
>> > "Overwrite existing media" since I am using a disk to write the backups?
>> >
>> > Am I correct? What's the correct setting for transaction log backups?
>> > Thanks.
>>

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
..
..
..
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!
I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>
|||One thing you might want to consider is to have separate file for db backup vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then apply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain of log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be stored
> in a seperate file. For example today for our server we would have 26 files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Backups

I have a database maintenance plan to backup the DB and the transaction log.
It is not doing backups any more. I can go and do them manually though. I
looked at the event log and noticed an error that has been send several time
(see below). Is there a relationship. Thanks for your help. BTW this is th
e
MOM server DB!
"The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be abl
e
to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 18278,
Database log truncated: Database: Onepoint."Check the status/history of the job. Look for errors on the SQL Server Error
Log (look at the date/time the job is suppose to run). Also look at the
reports file defined in the maintenance plan.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Niles" wrote:

> I have a database maintenance plan to backup the DB and the transaction lo
g.
> It is not doing backups any more. I can go and do them manually though. I
> looked at the event log and noticed an error that has been send several ti
me
> (see below). Is there a relationship. Thanks for your help. BTW this is
the
> MOM server DB!
> "The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot b
e
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be a
ble
> to use the /AUXSOURCE= flag to retrieve this description; see Help and
> Support for details. The following information is part of the event: 18278
,
> Database log truncated: Database: Onepoint."
>

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
.
.
.
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>|||One thing you might want to consider is to have separate file for db backup
vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then ap
ply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain o
f log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...seagreen">
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a singl
e
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be store
d
> in a seperate file. For example today for our server we would have 26 fil
es
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one fil
e
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Backups

I have a database maintenance plan to backup the DB and the transaction log.
It is not doing backups any more. I can go and do them manually though. I
looked at the event log and noticed an error that has been send several time
(see below). Is there a relationship. Thanks for your help. BTW this is the
MOM server DB!
"The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be able
to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 18278,
Database log truncated: Database: Onepoint."Check the status/history of the job. Look for errors on the SQL Server Error
Log (look at the date/time the job is suppose to run). Also look at the
reports file defined in the maintenance plan.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Niles" wrote:
> I have a database maintenance plan to backup the DB and the transaction log.
> It is not doing backups any more. I can go and do them manually though. I
> looked at the event log and noticed an error that has been send several time
> (see below). Is there a relationship. Thanks for your help. BTW this is the
> MOM server DB!
> "The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information or
> message DLL files to display messages from a remote computer. You may be able
> to use the /AUXSOURCE= flag to retrieve this description; see Help and
> Support for details. The following information is part of the event: 18278,
> Database log truncated: Database: Onepoint."
>

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.

backups

Need to do sql server 2000 database and transaction log
backups. Using the backup wizard. Can back up on the
server machine no problem.
But I need to back up to another M/C which is visibly
connected by the network, and to a CD burner (which is
drive d on the server mc.) I have typed in a variety of
paths for each scenario and nothing is working.
Thank you
AnnAnn,
You need to use the UNC pattern.First create a share in that remote server,
make sure that the account under which SQL Server is running has the
required privileges/permissions on that share.After that, do the backup as:
BACKUP DATABASE <dbname>
TO DISK = '\\destserver\d$\dbbackup.BAK'
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"ann" <akukich@.plaind.com> wrote in message
news:025801c37d32$23687170$a101280a@.phx.gbl...
> Need to do sql server 2000 database and transaction log
> backups. Using the backup wizard. Can back up on the
> server machine no problem.
> But I need to back up to another M/C which is visibly
> connected by the network, and to a CD burner (which is
> drive d on the server mc.) I have typed in a variety of
> paths for each scenario and nothing is working.
> Thank you
> Ann|||Use the UNC paths. Example:
\\MachineName\ShareName
or
\\MachineName\Driver$\Folder1\Folder2
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"ann" <akukich@.plaind.com> wrote in message
news:025801c37d32$23687170$a101280a@.phx.gbl...
Need to do sql server 2000 database and transaction log
backups. Using the backup wizard. Can back up on the
server machine no problem.
But I need to back up to another M/C which is visibly
connected by the network, and to a CD burner (which is
drive d on the server mc.) I have typed in a variety of
paths for each scenario and nothing is working.
Thank you
Ann

Friday, February 24, 2012

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
.
.
.
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>|||One thing you might want to consider is to have separate file for db backup vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then apply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain of log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be stored
> in a seperate file. For example today for our server we would have 26 files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Backup/Restore Tran Log

Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear :)
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
>> Hello Experts,
>> This is my first time that I work with the logic of
>> Transaction Log Backups/Restores seriously.
>> --First I made a full backup, then I backup my log in a
>> file:
>> BACKUP LOG [TEST_3]
>> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
>> --then I wanted to restore this log:
>> declare @.FilePosition as int
>> SELECT @.FilePosition = position
>> FROM msdb..backupset
>> WHERE database_name = 'TEST_3'
>> AND type != 'F'
>> AND backup_set_id = (SELECT MAX(backup_set_id)
>> FROM msdb..backupset
>> WHERE database_name
>> = 'TEST_3')
>> RESTORE LOG TEST_3
>> FROM DISK = N'D:\Microsoft SQL
Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH FILE = @.FilePosition
>> Even though I don't have any other connection in my DB
>> the error message I get is:
>> Server: Msg 3101, Level 16, State 1, Line 11
>> Exclusive access could not be obtained because the
>> database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 11
>> RESTORE LOG is terminating abnormally.
>> Can you please post a transaction backup/restore
example?
>> Thanks in advance!
>
>.
>

Thursday, February 16, 2012

Backup with SQL2000

With SQL 2000 I am aware that a transaction log backup cannot be done while
a
transaction log backup of the same database is already in progress. Instead
of starting a transaction log backup and having it fail, is there some statu
s
to check somewhere (say in the SQL catalog) that indicates that a backup is
in progress?
Thank-you!"Howard" wrote:

> With SQL 2000 I am aware that a transaction log backup cannot be done whil
e a
> transaction log backup of the same database is already in progress. Instea
d
> of starting a transaction log backup and having it fail, is there some sta
tus
> to check somewhere (say in the SQL catalog) that indicates that a backup i
s
> in progress?
> Thank-you!
Oops, I didn't state this quite right. I meant to say that the transaction
log backup would fail if their were a FULL backup in progress. So again, is
there anyway to detect that the full backup is currently in progress. Thanks
again.|||Howard,
I use this script to check if a job is executing, and if it is to stop it.
You could adapt it for your own purposes:
http://www.replicationanswers.com/D...RunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||I already know how to stop a job. What I need to know is whether a backup fo
r
database X is currently in progress. E.g., I know if a restore is in progres
s
because loading state would be set in the 'status' field of sysdatabases. I
s
there any way to know that a backup is in progress? (Again, I want to know
this because I will not start a transaction log backup if there is a full in
progress).
Thanks again.
"Paul Ibison" wrote:

> Howard,
> I use this script to check if a job is executing, and if it is to stop it.
> You could adapt it for your own purposes:
> http://www.replicationanswers.com/D...RunningJobs.txt
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>|||Howard,
this script doesn't just stop a job - the stored procedure call checks to
see if the job is currently executing. As long as your database backup is
done in a job and you know the job name it should be ok.
Paul Ibison|||Paul,
Unfortunately, I wouldn't know the job name of the executing backup or the
jobid. I just need to know that particular database is currently being backe
d
up.
Thanks.
"Paul Ibison" wrote:

> Howard,
> this script doesn't just stop a job - the stored procedure call checks to
> see if the job is currently executing. As long as your database backup is
> done in a job and you know the job name it should be ok.
> Paul Ibison
>
>|||Howard,
please try this:
select cmd, db_name(dbid) as DatabaseName from sysprocesses
where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
Cheers,
Paul Ibison, SQL Server MVP|||Beautiful!
Thank-you, Howard
"Paul Ibison" wrote:

> Howard,
> please try this:
> select cmd, db_name(dbid) as DatabaseName from sysprocesses
> where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
> Cheers,
> Paul Ibison, SQL Server MVP
>
>

Backup with SQL2000

With SQL 2000 I am aware that a transaction log backup cannot be done while a
transaction log backup of the same database is already in progress. Instead
of starting a transaction log backup and having it fail, is there some status
to check somewhere (say in the SQL catalog) that indicates that a backup is
in progress?
Thank-you!"Howard" wrote:
> With SQL 2000 I am aware that a transaction log backup cannot be done while a
> transaction log backup of the same database is already in progress. Instead
> of starting a transaction log backup and having it fail, is there some status
> to check somewhere (say in the SQL catalog) that indicates that a backup is
> in progress?
> Thank-you!
Oops, I didn't state this quite right. I meant to say that the transaction
log backup would fail if their were a FULL backup in progress. So again, is
there anyway to detect that the full backup is currently in progress. Thanks
again.|||Howard,
I use this script to check if a job is executing, and if it is to stop it.
You could adapt it for your own purposes:
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||I already know how to stop a job. What I need to know is whether a backup for
database X is currently in progress. E.g., I know if a restore is in progress
because loading state would be set in the 'status' field of sysdatabases. Is
there any way to know that a backup is in progress? (Again, I want to know
this because I will not start a transaction log backup if there is a full in
progress).
Thanks again.
"Paul Ibison" wrote:
> Howard,
> I use this script to check if a job is executing, and if it is to stop it.
> You could adapt it for your own purposes:
> http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>|||Howard,
this script doesn't just stop a job - the stored procedure call checks to
see if the job is currently executing. As long as your database backup is
done in a job and you know the job name it should be ok.
Paul Ibison|||Paul,
Unfortunately, I wouldn't know the job name of the executing backup or the
jobid. I just need to know that particular database is currently being backed
up.
Thanks.
"Paul Ibison" wrote:
> Howard,
> this script doesn't just stop a job - the stored procedure call checks to
> see if the job is currently executing. As long as your database backup is
> done in a job and you know the job name it should be ok.
> Paul Ibison
>
>|||Howard,
please try this:
select cmd, db_name(dbid) as DatabaseName from sysprocesses
where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
Cheers,
Paul Ibison, SQL Server MVP|||Beautiful!
Thank-you, Howard
"Paul Ibison" wrote:
> Howard,
> please try this:
> select cmd, db_name(dbid) as DatabaseName from sysprocesses
> where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
> Cheers,
> Paul Ibison, SQL Server MVP
>
>

Monday, February 13, 2012

Backup with SQL2000

With SQL 2000 I am aware that a transaction log backup cannot be done while a
transaction log backup of the same database is already in progress. Instead
of starting a transaction log backup and having it fail, is there some status
to check somewhere (say in the SQL catalog) that indicates that a backup is
in progress?
Thank-you!
"Howard" wrote:

> With SQL 2000 I am aware that a transaction log backup cannot be done while a
> transaction log backup of the same database is already in progress. Instead
> of starting a transaction log backup and having it fail, is there some status
> to check somewhere (say in the SQL catalog) that indicates that a backup is
> in progress?
> Thank-you!
Oops, I didn't state this quite right. I meant to say that the transaction
log backup would fail if their were a FULL backup in progress. So again, is
there anyway to detect that the full backup is currently in progress. Thanks
again.
|||Howard,
I use this script to check if a job is executing, and if it is to stop it.
You could adapt it for your own purposes:
http://www.replicationanswers.com/Do...unningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I already know how to stop a job. What I need to know is whether a backup for
database X is currently in progress. E.g., I know if a restore is in progress
because loading state would be set in the 'status' field of sysdatabases. Is
there any way to know that a backup is in progress? (Again, I want to know
this because I will not start a transaction log backup if there is a full in
progress).
Thanks again.
"Paul Ibison" wrote:

> Howard,
> I use this script to check if a job is executing, and if it is to stop it.
> You could adapt it for your own purposes:
> http://www.replicationanswers.com/Do...unningJobs.txt
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Howard,
this script doesn't just stop a job - the stored procedure call checks to
see if the job is currently executing. As long as your database backup is
done in a job and you know the job name it should be ok.
Paul Ibison
|||Paul,
Unfortunately, I wouldn't know the job name of the executing backup or the
jobid. I just need to know that particular database is currently being backed
up.
Thanks.
"Paul Ibison" wrote:

> Howard,
> this script doesn't just stop a job - the stored procedure call checks to
> see if the job is currently executing. As long as your database backup is
> done in a job and you know the job name it should be ok.
> Paul Ibison
>
>
|||Howard,
please try this:
select cmd, db_name(dbid) as DatabaseName from sysprocesses
where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
Cheers,
Paul Ibison, SQL Server MVP
|||Beautiful!
Thank-you, Howard
"Paul Ibison" wrote:

> Howard,
> please try this:
> select cmd, db_name(dbid) as DatabaseName from sysprocesses
> where cmd = 'Backup Database' and db_name(dbid) = 'yourdatabasename'
> Cheers,
> Paul Ibison, SQL Server MVP
>
>

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

Sunday, March 25, 2012

Basic control errors (transaction)

Sunday, March 11, 2012

Bad code clogs replication

Wednesday, March 7, 2012

Backups Failing

Saturday, February 25, 2012

backups - complete, differential and transaction log

backups - complete, differential and transaction log

backups - complete, differential and transaction log

Backups

Backups

Backups

Backups

Backups

backups

Friday, February 24, 2012

Backups

Backup/Restore Tran Log

Thursday, February 16, 2012

Backup with SQL2000

Backup with SQL2000

Monday, February 13, 2012

Backup with SQL2000

Sunday, February 12, 2012

Backup transaction logs when the database is damaged.

Backup transaction logs when the database is damaged.

Backup to Network Drive

Blog Archive

About Me