Restore?

I am trying to restore a .bak to a test server in order to test the integrity
of my production DB backups but the test server rejects the .bak file because
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.
What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>
|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
.....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>
|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||It is a totally different server. Maybe I am doing something wrong. How would
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:

> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>
|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:

> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
>
>
|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the test
> server and execute the backup statement. By log.bak did you mean .trn files?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Restore?

I am trying to restore a .bak to a test server in order to test the integrit
y
of my production DB backups but the test server rejects the .bak file becaus
e
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It is a totally different server. Maybe I am doing something wrong. How woul
d
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:

> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
>|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:

> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
>
>|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
>|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the tes
t
> server and execute the backup statement. By log.bak did you mean .trn file
s?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

restore: EXCEPTION_ACCESS_VIOLATION c0000005 (17052)

Because of a corrupt mainboard we tried to restore our SQL Server 7
databases to a new computer but get the same error for all backups we
try to restore:
---
ODBC: Nachr.-Nr. 0, Schweregrad 19, Status 1
SqlDumpExceptionHandler: Prozess 21 erzeugte eine schwere
Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION.Sql Server
beendet diesen Prozess.
---
The Windows event log shows an event ID 17052. What can be wrong?I check out www.eventid.net for this issue and found something about it and
wanted to share with you. Maybe you can find something useful.
From www.EventId.net
Adrian Grigorof (Last update 1/4/2005):
This appears to be a generic event that can be recorded by the MS SQL Server
service for a wide range of problems. See the information for various errors
(ordered by the error number).
Error: 0, Description: SqlDumpExceptionHandler: Process 10 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating
this process. - This type of error indicates a bug in one of the MS SQL
Server binaries. Installation of the latest service pack recommended. If
that does not fix it then call Microsoft Product support. Some newsgroup
postings suggest that this may occur due to lack of space on drive
containing the SQL logs
Event ID: 17052
Source MSSQLSERVER
Type Error
Description You do not have sufficient operating system permission to open
the SQLServerAgent status event.
Comments Adrian Grigorof (Last update 7/28/2004):
From a newsgroup post: "Installing SP3 or SP3a on MS SQL Server 2000
Enterprise Edition caused this problem in my case (MSSQLServer service is
running under different local user account than the SqlServerAgent service
and MSSQLServer is not given Administrator privileges). I see this buggy
behavior on various installations: W2K Advanced Server SP4 or W2K3. SP3 or
SP3a. Default instance or named instance. New freshly installed server or
old one. The only condition is that the MSSQLServer and SQLServerAgent
services run with different user accounts and the MSSQLServer
one does not have admin rights. Before SP3 installation everything runs OK.
What helps: Add MSSQLServer service account to Administrators group. OR Run
MSSQLServer and SQLServerAgent with the same account.
What doesn't help: Changing the services' user accounts using Enterprise
Manager. Add services' accounts to Power Users."
An answer from a MS engineer in a newsgroup post: "I suspect that the issue
is addressed in the cross-database ownership chain which uses in Microsoft
SQL Server Service Pack 3 (SP3). Based on my research, SP3 provides a new
security enhancement related option for configuring cross-database ownership
chaining; enable cross-database ownership chaining for all databases during
setup. With this new option, you can control whether or not you permit
cross-database ownership chaining. By default, this option is disabled.
Microsoft recommends that you use the default option, because it makes your
database server more secure.
However, database objects may have different owners. When an object such as
a view, a stored procedure, or a user-defined function references another
object, an ownership chain can be established. When the ownership chain is
unbroken, SQL Server checks permissions on the source object but not on the
target objects.
Therefore, it is recommended that you change the configuration of the
cross-database ownership chaining. You can reference the following
step-by-step article to perform such a change: M810474 (Cross-Database
Ownership Chaining Behavior Changes in SQL Server).
Additionally, I also suggest you running the MSSQLServer service with a
local account and
SQLServerAgent with Admin privilege to see if the jobs work fine. Otherwise,
it seems that you
should run these both services with the FULL privileges."
From a newsgroup post: "This is most likely a permissions issue. Make sure
the account SQLAgent starting under has permissions to:
- Replace A process level token
- Increase Quotas
- Logon as a service.
Books online states that the account needs to be a member of the local
admins group as well."
Anonymous (Last update 7/28/2004):
I got this error after I have created different accounts for sqlserver and
sqlagent service.
Ekrem Önsoy
"dentaku" <dentaku@.web.de> wrote in message
news:1189672135.231927.120740@.d55g2000hsg.googlegroups.com...
> Because of a corrupt mainboard we tried to restore our SQL Server 7
> databases to a new computer but get the same error for all backups we
> try to restore:
> ---
> ODBC: Nachr.-Nr. 0, Schweregrad 19, Status 1
> SqlDumpExceptionHandler: Prozess 21 erzeugte eine schwere
> Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION.Sql Server
> beendet diesen Prozess.
> ---
> The Windows event log shows an event ID 17052. What can be wrong?
>|||We have plenty of free disk and memory space (multiple GBs). We also
have the latest microsoft service packs and updates. And our DB user
is an admin (DBO rights didn't help either).
All restore verifyonly/filelistonly/headeronly requests returned no
error. But every time I try to restore the database (file) I get this
error and the sql error log files says
Bypassing recovery for database 'mine' because it is marked IN LOAD.
I can delete this database, and restore again, but with always the
same error.

RESTORE WITH VERIFYONLY Problem

Hi there,
I am having a problem with Microsoft SQL Server 2000, a copy of one of
my backups was corrupt, which I only discovered on the server I wanted
to res. Running the RESTORE WITH VERIFYONLY gave me the standard error
about there being an issue with the media set/family, which I could
accept.
However when I then opened query analyser against the original server
and ran the verifyonly restore command (and I have done it twice with
different results).
Myself and a colleague ran this against the original server and
received very odd messages. They received a weird error like this:
Command Issued:
RESTORE WITH ONLY Disk='C:\abc.BAK'
And received a error back saying that it could not find the file 'X:
\db_files\C:\abc.BAK' (where X:\db_files is the actual path where the
db files are kept!!!)
Later on, I ran the same command however, this time I received an
error telling me that the device was an invalid tape object! I was
running the same command, with the DISK='<drive>:\XYZ.BAK'
Has anyone ever seen something like this?
Obviously the error numbers would be useful here, but I don't have
these on me at the moment.
Any help appreciated.On Jan 29, 9:32 pm, Marc <mvandenhee...@.gmail.com> wrote:
> Hi there,
> I am having a problem with Microsoft SQL Server 2000, a copy of one of
> my backups was corrupt, which I only discovered on the server I wanted
> to res. Running the RESTORE WITH VERIFYONLY gave me the standard error
> about there being an issue with the media set/family, which I could
> accept.
> However when I then opened query analyser against the original server
> and ran the verifyonly restore command (and I have done it twice with
> different results).
> Myself and a colleague ran this against the original server and
> received very odd messages. They received a weird error like this:
> Command Issued:
> RESTORE WITH ONLY Disk='C:\abc.BAK'
> And received a error back saying that it could not find the file 'X:
> \db_files\C:\abc.BAK' (where X:\db_files is the actual path where the
> db files are kept!!!)
> Later on, I ran the same command however, this time I received an
> error telling me that the device was an invalid tape object! I was
> running the same command, with the DISK='<drive>:\XYZ.BAK'
> Has anyone ever seen something like this?
> Obviously the error numbers would be useful here, but I don't have
> these on me at the moment.
> Any help appreciated.
Also I should of added that SP 4 has been applied on this server...|||Mark
What is it ? T_SQL?
RESTORE WITH ONLY Disk='C:\abc.BAK'
Perhaps you meant
RESTORE VERIFYONLY FROM DISK = 'C:\abc.BAK'
"Marc" <mvandenheever@.gmail.com> wrote in message
news:12a10458-6e70-4817-b7a4-edf360167106@.d4g2000prg.googlegroups.com...
> Hi there,
> I am having a problem with Microsoft SQL Server 2000, a copy of one of
> my backups was corrupt, which I only discovered on the server I wanted
> to res. Running the RESTORE WITH VERIFYONLY gave me the standard error
> about there being an issue with the media set/family, which I could
> accept.
> However when I then opened query analyser against the original server
> and ran the verifyonly restore command (and I have done it twice with
> different results).
> Myself and a colleague ran this against the original server and
> received very odd messages. They received a weird error like this:
> Command Issued:
>
> RESTORE WITH ONLY Disk='C:\abc.BAK'
> And received a error back saying that it could not find the file 'X:
> \db_files\C:\abc.BAK' (where X:\db_files is the actual path where the
> db files are kept!!!)
> Later on, I ran the same command however, this time I received an
> error telling me that the device was an invalid tape object! I was
> running the same command, with the DISK='<drive>:\XYZ.BAK'
> Has anyone ever seen something like this?
> Obviously the error numbers would be useful here, but I don't have
> these on me at the moment.
> Any help appreciated.
>
>|||On Jan 29, 9:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Mark
> What is it ? T_SQL?RESTOREWITH ONLY Disk='C:\abc.BAK'
> Perhaps you meantRESTOREVERIFYONLYFROM DISK = 'C:\abc.BAK'
> "Marc" <mvandenhee...@.gmail.com> wrote in message
> news:12a10458-6e70-4817-b7a4-edf360167106@.d4g2000prg.googlegroups.com...
> > Hi there,
> > I am having a problem with Microsoft SQL Server 2000, a copy of one of
> > my backups was corrupt, which I only discovered on the server I wanted
> > to res. Running theRESTOREWITHVERIFYONLYgave me the standard error
> > about there being an issue with the media set/family, which I could
> > accept.
> > However when I then opened query analyser against the original server
> > and ran theverifyonlyrestorecommand (and I have done it twice with
> > different results).
> > Myself and a colleague ran this against the original server and
> > received very odd messages. They received a weird error like this:
> > Command Issued:
> >RESTOREWITH ONLY Disk='C:\abc.BAK'
> > And received a error back saying that it could not find the file 'X:
> > \db_files\C:\abc.BAK' (where X:\db_files is the actual path where the
> > db files are kept!!!)
> > Later on, I ran the same command however, this time I received an
> > error telling me that the device was an invalid tape object! I was
> > running the same command, with the DISK='<drive>:\XYZ.BAK'
> > Has anyone ever seen something like this?
> > Obviously the error numbers would be useful here, but I don't have
> > these on me at the moment.
> > Any help appreciated.
Yes sorry that is simply a typo...

Restore Transaction Log Backups

Hy Guys,

I have a problem with restore Log.

We make a FULL backup everyday at 22:00.
Transaction LOGs Backups are made at every 30 minutes (all the day) (WITH NO INIT) - One Log Backup per Day

I restored a a FULL Backup database from 10/17/2002 with this code:

RESTORE DATABASE dbTest
from disk = 'd:\MSSQL7\BACKUP\DB_20021017_dbsolomonprodapp.bak '
WITH norecovery
GO

Now I need to restore Transactions until 10/18/2002 16:30.
So, I have to restore
Lg_20021017_dbSolomonProdApp.bak
AND
Lg_20021018_dbSolomonProdApp.bak (Until 16:30)

My problem is.. How to make this Log Restore?Q1 My problem is.. How to make this Log Restore?
A1 Sequentially (in your case, by position similar to when restoring from tape, using time constraints as necessary). I suggest the following:
i I find it best to Dump to individual (dump) files. (multiple reasons)
ii Doing so also makes it clearer to see what you are doing, for example:

Restore Database
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_db_200210250 830.Bak'
With Stats = 1, Replace, NoRecovery,
Move 'dbSolomonProdApp_Data' To 'c:\Test_dbSolomonProdApp_Data.mdf',
Move 'dbSolomonProdApp_Log' To 'c:\Test_dbSolomonProdApp_Log.ldf'

--> Sequential dTL Restore Statements:

--> dTL file: 1
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50843.Trn'
With Stats = 1, NoRecovery

--> dTL file: 2
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50913.Trn'
With Stats = 1, NoRecovery

--> dTL file: 3
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50943.Trn'
With Stats = 1, NoRecovery

--> dTL file: 4
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51013.Trn'
With Stats = 1, NoRecovery

--> dTL file: 5
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51043.Trn'
With Stats = 1, NoRecovery

--> dTL file: 6
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51113.Trn'
With Stats = 1, NoRecovery

--> dTL file: 7
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51143.Trn'
With Stats = 1, NoRecovery

--> dTL file: 8
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51213.Trn'
With Stats = 1, NoRecovery

--> dTL file: 9
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51243.Trn'
With Stats = 1, NoRecovery

--> dTL file: 10
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51313.Trn'
With Stats = 1, NoRecovery

--> dTL file: 11
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51343.Trn'
With Stats = 1, NoRecovery

--> dTL file: 12
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51413.Trn'
With Stats = 1, NoRecovery

--> dTL file: 13
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51443.Trn'
With Stats = 1, NoRecovery

--> dTL file: 14
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51513.Trn'
With Stats = 1, NoRecovery

--> dTL file: 15
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51543.Trn'
With Stats = 1, NoRecovery

--> dTL file: 16
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51613.Trn'
With Stats = 1, NoRecovery

--> dTL file: 17
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51643.Trn'
With Stats = 1, NoRecovery

--> dTL file: 18
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51716.Trn'
With Stats = 1, NoRecovery

--> dTL file: 19
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51743.Trn'
With Stats = 1, NoRecovery

--> dTL file: 20
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51813.Trn'
With Stats = 1, NoRecovery

Restore DataBase
[Test_SolomonProdApp]
With Recovery|||Hy DBA,
Your answer works perfect. But i was with my transaction logs in one file. Then i made like this:

-- This command returns many important information about this backup.
-- Like the date of the backup sets and their numbers.
RESTORE HEADERONLY FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak' WITH NOUNLOAD
GO

--Restoring the last Full Backup
RESTORE DATABASE dbSolomonTeste
from disk = 'd:\20021016_dbsolomonprodapp.bak'
WITH norecovery
GO

--Now I recovery all backup sets in the Log backup
RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 2,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 3,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO
.
.
.

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 48, --In my Case, the last backup set i needed!
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

Thank you for helping me!!|||RE: Thank you for helping me!!

You are welcome.

As I alluded to, I generally avoid monolithic TL dump files.
Some pain I've experienced with them includes:
i) You want to move it elsewhere to use it, but cannot because it has become huge (either connectivity loss, corruption, etc., occurs during attempted file transfer processes, or the target only has sufficient free disk space on multiple smaller volumes).
ii) RESTORE HEADERONLY results take FOREVER, and fails before the complete header result set is returned (fortunatly, the needed TL dumps on it were usable).
iii) It (a single monolithic TL dump file) gets corrupted, and the whole thing is useless! (That can be extremely painful for an organization, and is the main reason I implement and / or encourage the use of individual dump files.) While I have seen this happen on a Windows OS fileserver, it seems to happen most frequently on third party NAS boxes, (especially older SNAP storage servers), with large monolithic TL dump files. I don't recall ever seeing it happen on a local data storage volume, however; so, if that is your situuation, you may have relativly little (corruption) risk in using monolithic TL dump files.

Restore Timing Transaction Logs versus Differentials

Currently we backup out SQL Server 2005 databases with a combination of full, differential and transaction log backups and plan on continuing this practice. Currently all backups are written to physical servers other than the servers where the DBs are running and all backups are also written to tape. Our tape backups are costly and I am considering no longer writing the differential backups to tape to reduce costs. Before making this decision I would like to understand how much longer it would take to recover several days of transaction log backups, versus a single differential and only a few hours of transation log backups. Does anyone have any any information about the time difference? Any ratios or rules of thumb?

Thanks,
Julia

Stopping the tape copies of differentials seems like a reasonable plan IF you kept two or three of the DIFFERENTIAL BACKUP copies on disk. (I prefer to keep all differentials since the last FULL BACKUP, tossing them only after the next FULL BACKUP is verified.

Time to recover: Seems like the major time issue is the manual process of handling each individual restore. The fewer files to restore, the less 'manual' time. The greated the number of files involved, the greater the risk of a corrupt or damaged file.

|||
Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?
|||

There are so many variables on that, I wouldn't hazard a guess.

You could run a comparision test on another server, comparing the timings.

|||Has anyone run these types of timed tests comparing the restore times from 2 types of backups?|||

That is purely depends upon the number of transactions on your database to restore, see this blog http://sqlserver-qa.net/blogs/perftune/archive/2007/06/12/get-backup-and-restore-performance-stats-with-a-dmv.aspx on the performance stats for backup & restore tasks.

EgleK wrote:

Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?

Restore sql server database

I did one complete backup, and two transaction log backups.
The first of log backups is corrupt.
So I have to make a restore with the complete backup and only the
second log backup.
Is it possible?
Thanks.Hi,
if any of u r log backup is corrupt u cant restore subsequential log back
files.
Amol Lembhe
"giminera@.libero.it" wrote:
> I did one complete backup, and two transaction log backups.
> The first of log backups is corrupt.
> So I have to make a restore with the complete backup and only the
> second log backup.
> Is it possible?
> Thanks.
>

Restore sql server database

I did one complete backup, and two transaction log backups.
The first of log backups is corrupt.
So I have to make a restore with the complete backup and only the
second log backup.
Is it possible?
Thanks.Hi,
if any of u r log backup is corrupt u cant restore subsequential log back
files.
Amol Lembhe
"giminera@.libero.it" wrote:

> I did one complete backup, and two transaction log backups.
> The first of log backups is corrupt.
> So I have to make a restore with the complete backup and only the
> second log backup.
> Is it possible?
> Thanks.
>

Restore SQL master database from a file

Hello,

I need to restore the Master DB in my SQL 2k server (with sp3). The
problem is that i don't have SQL backups of this file I only have
general backup of the entire c:\ drive i made using NTBackup. So i
tried all I know and succesfully run the rebuildm utility. Now when I
try to restore it from the general backup (not SQL backup) using
"restore database master from gen_backup" i got error: "The file on
device 'gen_backup' is not a valid Microsoft Tape Format backup set."
RESTORE DATABASE is terminating abnormally.
Any ideas how I can restore it?<Moti.Ba@.gmail.com> wrote in message
news:1105264654.916229.258480@.c13g2000cwb.googlegr oups.com...
> Hello,
> I need to restore the Master DB in my SQL 2k server (with sp3). The
> problem is that i don't have SQL backups of this file I only have
> general backup of the entire c:\ drive i made using NTBackup. So i
> tried all I know and succesfully run the rebuildm utility. Now when I
> try to restore it from the general backup (not SQL backup) using
> "restore database master from gen_backup" i got error: "The file on
> device 'gen_backup' is not a valid Microsoft Tape Format backup set."
> RESTORE DATABASE is terminating abnormally.
> Any ideas how I can restore it?

Is this the same problem you had a few months ago?

http://groups.google.co.uk/groups?h...news.bluewin.ch

If you have the master.mdf and mastlog.ldf files (if you can restore them
from your NTBackup backup set), then you can try the process I described
earlier, although I haven't tried it myself, so I can't say for sure that it
will work.

Whatever happens, you need to set up proper MSSQL backups to make sure you
don't have this problem again - you can use the maintenance plan wizard to
get started quickly. You should also check out the Books Online section on
"Backing Up and Restoring Databases".

Simon|||(Moti.Ba@.gmail.com) writes:
> I need to restore the Master DB in my SQL 2k server (with sp3). The
> problem is that i don't have SQL backups of this file I only have
> general backup of the entire c:\ drive i made using NTBackup. So i
> tried all I know and succesfully run the rebuildm utility. Now when I
> try to restore it from the general backup (not SQL backup) using
> "restore database master from gen_backup" i got error: "The file on
> device 'gen_backup' is not a valid Microsoft Tape Format backup set."
> RESTORE DATABASE is terminating abnormally.
> Any ideas how I can restore it?

You cannot use the RESTORE command in SQL Server to read something
from a NT Backup, as far as I know. You first need to restore the
master.mdf and master.ldf from backup with NT Backup.

However, it is far from certain that the files are usuable, because if
you backup database files with NT backup, there is no guarantee that they
are transactionally consistent. You can get all sorts of rubbish. That is,
if SQL Server was running when you ran NT backup. If SQL Server wasn't
running the backups should be OK.

There are somewhat better odds with master than other database, because
there is little update traffic in that database, and the database is small.

But nevertheless, before you actually try to use it as a master database,
just try to attach it as a regular user database first and run DBCC CHECKDB
on it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Restore SQL 2000 Tables from Transaction Log Files

I inadvertently deleted related records from one table when I deleted anothe
r table. I am not sure how often the server admin backups the data or how h
e is doing it. I thought that I read somewhere that one could do that from
the log files. Can someone
please help me with this?
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,

Restore SQL 2000 Tables from Transaction Log Files

I inadvertently deleted related records from one table when I deleted another table. I am not sure how often the server admin backups the data or how he is doing it. I thought that I read somewhere that one could do that from the log files. Can someone please help me with this
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,

Restore Set has a Negative File Size

Any ideas what would cause a file size to be negative?
We ship backups to our dev server, and they usually take an hour or so, but
they have recently been running for around 4 hours.
The first file in the restore set has a negative size. What's up with that?
Screenshot: http://home.comcast.net/~mcahrenfamily/SNAG-0008.gifUI overflow perhaps? What does RESTORE HEADERONLY and RESTORE FILELISTONLY s
ay?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JM" <JM@.nospam.com> wrote in message news:OYFELykqFHA.2604@.TK2MSFTNGP14.phx.gbl...agreen">
> Any ideas what would cause a file size to be negative?
> We ship backups to our dev server, and they usually take an hour or so, bu
t
> they have recently been running for around 4 hours.
> The first file in the restore set has a negative size. What's up with tha
t?
> Screenshot: http://home.comcast.net/~mcahrenfamily/SNAG-0008.gif
>

Restore Set has a Negative File Size

Any ideas what would cause a file size to be negative?
We ship backups to our dev server, and they usually take an hour or so, but
they have recently been running for around 4 hours.
The first file in the restore set has a negative size. What's up with that?
Screenshot: http://home.comcast.net/~mcahrenfamily/SNAG-0008.gif
UI overflow perhaps? What does RESTORE HEADERONLY and RESTORE FILELISTONLY say?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JM" <JM@.nospam.com> wrote in message news:OYFELykqFHA.2604@.TK2MSFTNGP14.phx.gbl...
> Any ideas what would cause a file size to be negative?
> We ship backups to our dev server, and they usually take an hour or so, but
> they have recently been running for around 4 hours.
> The first file in the restore set has a negative size. What's up with that?
> Screenshot: http://home.comcast.net/~mcahrenfamily/SNAG-0008.gif
>

Restore Set has a Negative File Size

Any ideas what would cause a file size to be negative?
We ship backups to our dev server, and they usually take an hour or so, but
they have recently been running for around 4 hours.
The first file in the restore set has a negative size. What's up with that?
Screenshot: http://home.comcast.net/~mcahrenfamily/SNAG-0008.gifUI overflow perhaps? What does RESTORE HEADERONLY and RESTORE FILELISTONLY say?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JM" <JM@.nospam.com> wrote in message news:OYFELykqFHA.2604@.TK2MSFTNGP14.phx.gbl...
> Any ideas what would cause a file size to be negative?
> We ship backups to our dev server, and they usually take an hour or so, but
> they have recently been running for around 4 hours.
> The first file in the restore set has a negative size. What's up with that?
> Screenshot: http://home.comcast.net/~mcahrenfamily/SNAG-0008.gif
>

Restore Problem

MSSQL 2000 SP3A

I'm trying to restore a database with the following commands.

restore database Test
from disk = 'D:\backups\9pm.bak'
WITH RECOVERY,

move 'DEV_Data' to 'c:\Program Files\Microsoft SQL
Server\MSSQL\data\Test_Data.MDF',
move 'DEV_Log' to 'c:\Program Files\Microsoft SQL
Server\MSSQL\data\Test_log.LDF'

The database doesn't exist on the restore server destination.

The size of the bak file is 681 mb.

The restore process never finishes or at least I've canceled after 30
minutes.

I do see the mdf file being created at 811 mbs.

I also see a log file for the database being created, its 17gb. I have 30
gbs of disk space when I initiate the restore.

Is this normal?Terri (terri@.cybernets.com) writes:
> MSSQL 2000 SP3A
> I'm trying to restore a database with the following commands.
> restore database Test
> from disk = 'D:\backups\9pm.bak'
> WITH RECOVERY,
> move 'DEV_Data' to 'c:\Program Files\Microsoft SQL
> Server\MSSQL\data\Test_Data.MDF',
> move 'DEV_Log' to 'c:\Program Files\Microsoft SQL
> Server\MSSQL\data\Test_log.LDF'
> The database doesn't exist on the restore server destination.
> The size of the bak file is 681 mb.
> The restore process never finishes or at least I've canceled after 30
> minutes.
> I do see the mdf file being created at 811 mbs.
> I also see a log file for the database being created, its 17gb. I have 30
> gbs of disk space when I initiate the restore.
> Is this normal?

17 GB of disk could take some time to initiate, so I would restart
the restore, and leave it running.

Once you are up, you should probably take action to shrink the
transaction log. Exactly how do to this, depends on your requirements
for up-to-the-point recovery.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Restore points available

Hi group,
I have a SQL 2000 database with a backup schedule as so:
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
With the way I have things setup now, if I choose to restore the database
from Enterprise manager, the restore points that I have to select from go
all the way back to when these backups were setup last year. Considering
that there is a backup every five minutes, and this has been going on for
months, there are pretty many options listed...
What I would like to have as my options is just a list of the past seven
days and all the times that backups ran during those seven days. To get it
this way, I believe what I have to do is add another step in the backup that
would run prior to the first incremental log backup that would initialize
the .bak files for that day. ? So, it would be more like:
Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server forgets
last Monday's backups existed.
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
Does this make sense? If so, can anyone tell me how I can do this? Would I
just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
In the BOL, it states:
INIT : Specifies that all backup sets should be overwritten, but preserves
the media header. If INIT is specified, any existing backup set data on that
device is overwritten.
Will this clear out the history of all these backups so that when I choose
to do a restore from Enterprise Manager, it doesn't take half an hour to
list all the restore points? (This half hour isn't the reason that I want
to change this, btw. I want to change it because it's setup wrong, imo.)
Thanks,
Ray at workA couple of points here.
First, the frequency of log backups has nothing to do with restore points.
As long as log continuity is unbroken, you can restore to any point in time
you have backup files for.
Second, there is a sp_delete_backuphistory procedure to clean out old unused
backup jobs so you can keep the size of the backup list down to a managable
level.
These tools should give you a few more options.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OZz%23$59%23DHA.2552@.TK2MSFTNGP09.phx.gbl...
> Hi group,
> I have a SQL 2000 database with a backup schedule as so:
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
> With the way I have things setup now, if I choose to restore the database
> from Enterprise manager, the restore points that I have to select from go
> all the way back to when these backups were setup last year. Considering
> that there is a backup every five minutes, and this has been going on for
> months, there are pretty many options listed...
> What I would like to have as my options is just a list of the past seven
> days and all the times that backups ran during those seven days. To get
it
> this way, I believe what I have to do is add another step in the backup
that
> would run prior to the first incremental log backup that would initialize
> the .bak files for that day. ? So, it would be more like:
>
> Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server
forgets
> last Monday's backups existed.
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
>
> Does this make sense? If so, can anyone tell me how I can do this? Would
I
> just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
> In the BOL, it states:
> INIT : Specifies that all backup sets should be overwritten, but preserves
> the media header. If INIT is specified, any existing backup set data on
that
> device is overwritten.
> Will this clear out the history of all these backups so that when I choose
> to do a restore from Enterprise Manager, it doesn't take half an hour to
> list all the restore points? (This half hour isn't the reason that I want
> to change this, btw. I want to change it because it's setup wrong, imo.)
> Thanks,
> Ray at work
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> A couple of points here.
> First, the frequency of log backups has nothing to do with restore points.
> As long as log continuity is unbroken, you can restore to any point in
time
> you have backup files for.
This makes sense. Thank you.
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
I suppose that I was under the impression that there would be an implied
deletion of backup history if I were "INIT'ing" my backup devices properly.
If this is not the case, what I will do is add a step that uses
sp_delete_backuphistory to my jobs.
Thanks Geoff.
Ray at work|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
My execution of this SP has been running for over seven minutes. I get
nervous when I have to do anything that I don't know about like this.
Whoever had the brilliant idea of tacking DBA onto my job title should be
fired. For some reason, "knowing some things about Access" apparently
qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
concerned, what this really adds to my title is "fraud." I'm just thinking
out loud for the sake of distracting myself. Ignore this post.
--
Ray at work
Microsoft ASP MVP|||You might want to brush up on the training or polish the resume. DBA
without training is a complicated way of saying 'scapegoat'.
FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
middle, it is OK. Find the oldest entry and let it chew a few days off at a
time.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:%23EqOwf%23%23DHA.1464@.tk2msftngp13.phx.gbl...
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> >
> > Second, there is a sp_delete_backuphistory procedure to clean out old
> unused
> > backup jobs so you can keep the size of the backup list down to a
> managable
> > level.
> My execution of this SP has been running for over seven minutes. I get
> nervous when I have to do anything that I don't know about like this.
> Whoever had the brilliant idea of tacking DBA onto my job title should be
> fired. For some reason, "knowing some things about Access" apparently
> qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
> concerned, what this really adds to my title is "fraud." I'm just
thinking
> out loud for the sake of distracting myself. Ignore this post.
> --
> Ray at work
> Microsoft ASP MVP
>
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OCF1Dx%23%23DHA.1548@.TK2MSFTNGP12.phx.gbl...
> You might want to brush up on the training or polish the resume. DBA
> without training is a complicated way of saying 'scapegoat'.
The most I'd want on my resume, as of now, is just something that indicates
that I have a small amount of SQL Server knowedge. I'd like to continue
learning more about it, but only for my own personal interest and to make
things easier on myself. But, this DBA thing, I am just so wrong for it.
I'm probably more qualified to be a neurosurgeon.
> FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
> middle, it is OK. Find the oldest entry and let it chew a few days off at
a
> time.
This I will do. Thanks again Geoff.
--
Ray at home
Microsoft ASP MVP

Restore points available

Hi group,
I have a SQL 2000 database with a backup schedule as so:
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
With the way I have things setup now, if I choose to restore the database
from Enterprise manager, the restore points that I have to select from go
all the way back to when these backups were setup last year. Considering
that there is a backup every five minutes, and this has been going on for
months, there are pretty many options listed...
What I would like to have as my options is just a list of the past seven
days and all the times that backups ran during those seven days. To get it
this way, I believe what I have to do is add another step in the backup that
would run prior to the first incremental log backup that would initialize
the .bak files for that day. ? So, it would be more like:
Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server forgets
last Monday's backups existed.
Monday: 7 AM - 10 PM: incremental log backups every five minutes to
MondayFilename.bak
Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
Same for Tuesday through Sunday, for a total of 14 .bak files.
Does this make sense? If so, can anyone tell me how I can do this? Would I
just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
In the BOL, it states:
INIT : Specifies that all backup sets should be overwritten, but preserves
the media header. If INIT is specified, any existing backup set data on that
device is overwritten.
Will this clear out the history of all these backups so that when I choose
to do a restore from Enterprise Manager, it doesn't take half an hour to
list all the restore points? (This half hour isn't the reason that I want
to change this, btw. I want to change it because it's setup wrong, imo.)
Thanks,
Ray at workA couple of points here.
First, the frequency of log backups has nothing to do with restore points.
As long as log continuity is unbroken, you can restore to any point in time
you have backup files for.
Second, there is a sp_delete_backuphistory procedure to clean out old unused
backup jobs so you can keep the size of the backup list down to a managable
level.
These tools should give you a few more options.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OZz%23$59%23DHA.2552@.TK2MSFTNGP09.phx.gbl...
> Hi group,
> I have a SQL 2000 database with a backup schedule as so:
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
> With the way I have things setup now, if I choose to restore the database
> from Enterprise manager, the restore points that I have to select from go
> all the way back to when these backups were setup last year. Considering
> that there is a backup every five minutes, and this has been going on for
> months, there are pretty many options listed...
> What I would like to have as my options is just a list of the past seven
> days and all the times that backups ran during those seven days. To get
it
> this way, I believe what I have to do is add another step in the backup
that
> would run prior to the first incremental log backup that would initialize
> the .bak files for that day. ? So, it would be more like:
>
> Monday: 6:30 AM: Initialize Monday*.bak and make it so SQL Server
forgets
> last Monday's backups existed.
> Monday: 7 AM - 10 PM: incremental log backups every five minutes to
> MondayFilename.bak
> Monday: 11 PM: Full DB backup to MondayFullBackupFilename.bak
> Same for Tuesday through Sunday, for a total of 14 .bak files.
>
> Does this make sense? If so, can anyone tell me how I can do this? Would
I
> just do a BACKUP LOG|DATABASE WITH INIT as its own job at 6:30 AM?
> In the BOL, it states:
> INIT : Specifies that all backup sets should be overwritten, but preserves
> the media header. If INIT is specified, any existing backup set data on
that
> device is overwritten.
> Will this clear out the history of all these backups so that when I choose
> to do a restore from Enterprise Manager, it doesn't take half an hour to
> list all the restore points? (This half hour isn't the reason that I want
> to change this, btw. I want to change it because it's setup wrong, imo.)
> Thanks,
> Ray at work
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
> A couple of points here.
> First, the frequency of log backups has nothing to do with restore points.
> As long as log continuity is unbroken, you can restore to any point in
time
> you have backup files for.
This makes sense. Thank you.

> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
I suppose that I was under the impression that there would be an implied
deletion of backup history if I were "INIT'ing" my backup devices properly.
If this is not the case, what I will do is add a step that uses
sp_delete_backuphistory to my jobs.
Thanks Geoff.
Ray at work|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...

> Second, there is a sp_delete_backuphistory procedure to clean out old
unused
> backup jobs so you can keep the size of the backup list down to a
managable
> level.
My execution of this SP has been running for over seven minutes. I get
nervous when I have to do anything that I don't know about like this.
Whoever had the brilliant idea of tacking DBA onto my job title should be
fired. For some reason, "knowing some things about Access" apparently
qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
concerned, what this really adds to my title is "fraud." I'm just thinking
out loud for the sake of distracting myself. Ignore this post.
Ray at work
Microsoft ASP MVP|||You might want to brush up on the training or polish the resume. DBA
without training is a complicated way of saying 'scapegoat'.
FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
middle, it is OK. Find the oldest entry and let it chew a few days off at a
time.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:%23EqOwf%23%23DHA.1464@.tk2msftngp13.phx.gbl...
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uFyWM%239%23DHA.624@.TK2MSFTNGP11.phx.gbl...
>
> unused
> managable
> My execution of this SP has been running for over seven minutes. I get
> nervous when I have to do anything that I don't know about like this.
> Whoever had the brilliant idea of tacking DBA onto my job title should be
> fired. For some reason, "knowing some things about Access" apparently
> qualified me to be a SQL Server DBA, in the eyes of others. As far as I'm
> concerned, what this really adds to my title is "fraud." I'm just
thinking
> out loud for the sake of distracting myself. Ignore this post.
> --
> Ray at work
> Microsoft ASP MVP
>
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OCF1Dx%23%23DHA.1548@.TK2MSFTNGP12.phx.gbl...
> You might want to brush up on the training or polish the resume. DBA
> without training is a complicated way of saying 'scapegoat'.
The most I'd want on my resume, as of now, is just something that indicates
that I have a small amount of SQL Server knowedge. I'd like to continue
learning more about it, but only for my own personal interest and to make
things easier on myself. But, this DBA thing, I am just so wrong for it.
I'm probably more qualified to be a neurosurgeon.

> FYI, sp_delete_backuphistory runs very slowly. If you kill it in the
> middle, it is OK. Find the oldest entry and let it chew a few days off at
a
> time.
This I will do. Thanks again Geoff.
Ray at home
Microsoft ASP MVP

restore MS SQL2000 DB using log files and no backups?

Hello!
I have the serious problem and can't find any ways to solve it. So, greatly
appreciate for any help!
I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
Transaction Log of this Database. Could anybody show me anyway how to
restore a database, using only transaction log file?
Thanks in forvard!
DenisIt is impossible to restore a database with only logs.
"Denis" <denis_vb@.pisem.net> wrote in message
news:bvoe2s$uap$1@.sky.kavkaz.elektra.ru...
> Hello!
> I have the serious problem and can't find any ways to solve it. So,
greatly
> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>|||If what you describe is true, then your database is toast.
Log backups alone are only of value if you have a full backup to restore
from first.
Jim
"Denis" <denis_vb@.pisem.net> wrote in message
news:bvoe2s$uap$1@.sky.kavkaz.elektra.ru...
> Hello!
> I have the serious problem and can't find any ways to solve it. So,
greatly
> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>

restore MS SQL2000 DB using log files and no backups?

Hello!
I have the serious problem and can't find any ways to solve it. So, greatly
appreciate for any help!
I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
Transaction Log of this Database. Could anybody show me anyway how to
restore a database, using only transaction log file?
Thanks in forvard!
DenisIt is impossible to restore a database with only logs.
"Denis" <denis_vb@.pisem.net> wrote in message
news:bvoe2s$uap$1@.sky.kavkaz.elektra.ru...

quote:

---

> Hello!
> I have the serious problem and can't find any ways to solve it. So,

---

greatly

quote:

---

> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>

---

|||If what you describe is true, then your database is toast.
Log backups alone are only of value if you have a full backup to restore
from first.
Jim
"Denis" <denis_vb@.pisem.net> wrote in message
news:bvoe2s$uap$1@.sky.kavkaz.elektra.ru...

quote:

---

> Hello!
> I have the serious problem and can't find any ways to solve it. So,

---

greatly

quote:

---

> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>

---