hi,
in my event log I have the alarm Backup failed to complete command
sp_executesql;1
The point is that I didn't find any scheduled backup jobs and I didn't find
any *.bak file relative to the DB mentioned in the alarm. (I can see the DB
file name in the text window at the bottom of the detail in Windows event
log )
I suspect that it's hidden in a stored procedure or somewhere else. How can
I find it?
I have two DB on the system pointed by this alarm and they're quite large 20
& 25gig so if a backup file was create I think I wouldn't have difficulty
to find them.
I didn't find any infos on the net about this alarm so if someone can give
me some the help me to search deeper.
Regards
MarcDo you have any third party products installed on the server, that perform
database backups? Worth checking under Start > Programs
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:SfKFe.92489$mv2.1552703@.weber.videotron.net...
hi,
in my event log I have the alarm Backup failed to complete command
sp_executesql;1
The point is that I didn't find any scheduled backup jobs and I didn't find
any *.bak file relative to the DB mentioned in the alarm. (I can see the DB
file name in the text window at the bottom of the detail in Windows event
log )
I suspect that it's hidden in a stored procedure or somewhere else. How can
I find it?
I have two DB on the system pointed by this alarm and they're quite large 20
& 25gig so if a backup file was create I think I wouldn't have difficulty
to find them.
I didn't find any infos on the net about this alarm so if someone can give
me some the help me to search deeper.
Regards
Marc
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Backup devices (named pipes)
I hope someone can at least point me in the right direction on this one.
I've written some procs and created appropriate tables to replicate logshipping without incurring the added expense of Enterprise, just for the GUI.
I'm now looking for ways to extend/enhance this functionality. Currently I use a commandline compression app (bzip2) to compress the tran log backup after it's been written to disk, and although it works, I was thinking it would be nice if I could capture the backup stream on the fly (before it get's written to disk) and compress it before writing to disk. I've found several products that do this, but almost no documentation on the actual process of capturing the backup output.
Through a little sleuthing, I was able to create a device with sp_addumpdevice using a named pipe, and a small .Net app that was to read from that pipe, but I'm running into a problem. For some reason, all I get is the header information, then SQL server seems to expect a response from me and if I don't respond within a certain timeframe (let alone that I have no idea what to send it) it closes the pipe and the backup operation fails with a device write error, listing OS error 232(pipe being closed) in the logs.
Any assistance that someone could provide would be greatly appreciated.
Thanks in advance for taking the time to read this gobbledy-gook!
That being the case, are there any resources relevant to this situation in SQL2000?
Also, I apologize if this is posted in the wrong forum, but as I wasn't able to find any SQL2000 forums, this seemed the most appropriate place to pose my question.
Thanks again for your help!
|||
I've written some procs and created appropriate tables to replicate logshipping without incurring the added expense of Enterprise, just for the GUI.
I'm now looking for ways to extend/enhance this functionality. Currently I use a commandline compression app (bzip2) to compress the tran log backup after it's been written to disk, and although it works, I was thinking it would be nice if I could capture the backup stream on the fly (before it get's written to disk) and compress it before writing to disk. I've found several products that do this, but almost no documentation on the actual process of capturing the backup output.
Through a little sleuthing, I was able to create a device with sp_addumpdevice using a named pipe, and a small .Net app that was to read from that pipe, but I'm running into a problem. For some reason, all I get is the header information, then SQL server seems to expect a response from me and if I don't respond within a certain timeframe (let alone that I have no idea what to send it) it closes the pipe and the backup operation fails with a device write error, listing OS error 232(pipe being closed) in the logs.
Any assistance that someone could provide would be greatly appreciated.
Thanks in advance for taking the time to read this gobbledy-gook!
You can write a VDI (Virtual Device Interface) application to get access to the backup data.
Named pipe backups have been deprecated since sql7, and are removed in sql2005.
You can download doc and samples for VDI here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&DisplayLang=en
|||Thanks very much for the quick reply Steve. Unfortunately, I won't be able to utilize sql2005 in a production environment for at least another year. We have many legacy systems that interface with SQL2000 via batch loads, DTS and a vagary of third party interfaces (Liant Relativity for example) and until testing is complete company wide, policy is that SQL2005 not be used.That being the case, are there any resources relevant to this situation in SQL2000?
Also, I apologize if this is posted in the wrong forum, but as I wasn't able to find any SQL2000 forums, this seemed the most appropriate place to pose my question.
Thanks again for your help!
|||
You can use VDI since sql7, so that should do the trick for you.
The VDI has not changed since sql2000, so our newer 2005 spec is mostly just clarifications. Any VDI app written for sql2000 (or sql7) should work without changes on sql2000 or sql2005.
I strongly advise you against writing a named pipe backup application. The reason is that such an app will not be compatible with sql2005.
Of course you are free to pass the VDI stream on via your own named pipes if you really want to.
This forum is good for any sql questions.
Cheers,
Tuesday, March 20, 2012
Backup Device Location
Is it possible to point a backup device to a network location? I have a mapp
ed drive on my SQL Server to a share on another server (Q: drive). The backu
p device points to that Q drive. However, everytime I run the backup, it say
s it cannot reach the speci
fied location. The drive is mapped and the administrator is logged onto the
server, so there should not be a rights issue.
-BrianBrian,
You are better to use UNC names \\server\share\etc instead of mapped =
drive letters. Mapped drives are local to the account that mapped them, =
and therefore may not be visible to the account that the SQl Server =
service is running under. You need to check what account is being used =
for the MSSQLSERVER service and ensure that account has the necessary =
access rights to the remote server. Who is logged on at the other =
machine will make no difference.
Mike John
"Brian" <anonymous@.discussions.microsoft.com> wrote in message =
news:69161F98-4357-4CD0-900E-A229A9917C95@.microsoft.com...
> Is it possible to point a backup device to a network location? I have =
a mapped drive on my SQL Server to a share on another server (Q: drive). =
The backup device points to that Q drive. However, everytime I run the =
backup, it says it cannot reach the specified location. The drive is =
mapped and the administrator is logged onto the server, so there should =
not be a rights issue.
>=20
> -Brian|||Try using a UNC name instead of a mapped drive letter ie
backup database master to disk = '\\server2\backupshare\master.mak' with
init
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:69161F98-4357-4CD0-900E-A229A9917C95@.microsoft.com...
> Is it possible to point a backup device to a network location? I have a
mapped drive on my SQL Server to a share on another server (Q: drive). The
backup device points to that Q drive. However, everytime I run the backup,
it says it cannot reach the specified location. The drive is mapped and the
administrator is logged onto the server, so there should not be a rights
issue.
> -Brian|||The UNC path worked. Thanks.sql
ed drive on my SQL Server to a share on another server (Q: drive). The backu
p device points to that Q drive. However, everytime I run the backup, it say
s it cannot reach the speci
fied location. The drive is mapped and the administrator is logged onto the
server, so there should not be a rights issue.
-BrianBrian,
You are better to use UNC names \\server\share\etc instead of mapped =
drive letters. Mapped drives are local to the account that mapped them, =
and therefore may not be visible to the account that the SQl Server =
service is running under. You need to check what account is being used =
for the MSSQLSERVER service and ensure that account has the necessary =
access rights to the remote server. Who is logged on at the other =
machine will make no difference.
Mike John
"Brian" <anonymous@.discussions.microsoft.com> wrote in message =
news:69161F98-4357-4CD0-900E-A229A9917C95@.microsoft.com...
> Is it possible to point a backup device to a network location? I have =
a mapped drive on my SQL Server to a share on another server (Q: drive). =
The backup device points to that Q drive. However, everytime I run the =
backup, it says it cannot reach the specified location. The drive is =
mapped and the administrator is logged onto the server, so there should =
not be a rights issue.
>=20
> -Brian|||Try using a UNC name instead of a mapped drive letter ie
backup database master to disk = '\\server2\backupshare\master.mak' with
init
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:69161F98-4357-4CD0-900E-A229A9917C95@.microsoft.com...
> Is it possible to point a backup device to a network location? I have a
mapped drive on my SQL Server to a share on another server (Q: drive). The
backup device points to that Q drive. However, everytime I run the backup,
it says it cannot reach the specified location. The drive is mapped and the
administrator is logged onto the server, so there should not be a rights
issue.
> -Brian|||The UNC path worked. Thanks.sql
Sunday, March 11, 2012
Backup Database ?
Does the backup database command truncate the transaction log to the point
of the backup?
--
nivekNo
You must backup the log separately.
If you're using Simple Recovery model, use the backup log... with
truncate_only and then dbcc shrinkfile
to release disk back to the OS.
"nivek" <eckart_612@.hotmail.com> wrote in message
news:rI2dnf7GAuFAeqPfRVn-rQ@.centurytel.net...
> Does the backup database command truncate the transaction log to the point
> of the backup?
> --
> nivek
>
>
>
of the backup?
--
nivekNo
You must backup the log separately.
If you're using Simple Recovery model, use the backup log... with
truncate_only and then dbcc shrinkfile
to release disk back to the OS.
"nivek" <eckart_612@.hotmail.com> wrote in message
news:rI2dnf7GAuFAeqPfRVn-rQ@.centurytel.net...
> Does the backup database command truncate the transaction log to the point
> of the backup?
> --
> nivek
>
>
>
Wednesday, March 7, 2012
backup and restore trouble.
Hello,
We want to do a restore to a point of time.
With recovery mode on full.
Can one do a restore to a point of time before the last full backup
with a transaction log backup made after the full backup ?
Sequence.
(full recovery mode, SQL-server 7. I think.).
a. Somewere in the past a full backup is made.
b. An error is made.
c. A full backup is made.
d. A transaction log backup is made.
We want to restore to a point in time just before
the error (b.) is made. Is this possible ?
(I have got the BOL (from 7) and inside from Kalen but
can not find the anwsers there).
Ben Brugman.> We want to do a restore to a point of time.
> With recovery mode on full.
> Sequence.
> (full recovery mode, SQL-server 7. I think.).
There is no FULL recovery mode in SQL 7. In SQL 7 and earlier versions,
the 'trunc. log on chkpt.' database option turned off is similar.
> a. Somewere in the past a full backup is made.
> b. An error is made.
> c. A full backup is made.
> d. A transaction log backup is made.
> We want to restore to a point in time just before
> the error (b.) is made. Is this possible ?
You can perform point-in-time recovery as follows:
1) Restore full database backup from step 'a' WITH NORECOVERY
2) Restore transaction log backup from step 'd' WITH STOPAT (time
before step 'b' error) and RECOVERY
Note that if you have any other log backups between 'a' and 'b', these
will need to be applied (WITH NORECOVERY) before step '2' above.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"ben brugman" <ben@.niethier.nl> wrote in message
news:bkhf32$mlv$1@.reader10.wxs.nl...
> Hello,
> We want to do a restore to a point of time.
> With recovery mode on full.
> Can one do a restore to a point of time before the last full backup
> with a transaction log backup made after the full backup ?
> Sequence.
> (full recovery mode, SQL-server 7. I think.).
> a. Somewere in the past a full backup is made.
> b. An error is made.
> c. A full backup is made.
> d. A transaction log backup is made.
> We want to restore to a point in time just before
> the error (b.) is made. Is this possible ?
> (I have got the BOL (from 7) and inside from Kalen but
> can not find the anwsers there).
> Ben Brugman.
>|||Greg,
I too have been having problems with the restore to a point in time
and your sample script was useful in trying to come to grips with
this. All this seems to do though , is to restore the datbase to the
state it was in when it was backed up to a_bak1.bak.
I have managed to write a script to add 4 records to the database ,
then do a full backup to a_bak1, then add a few more records then do
another full backup and a transaction log backup. I now want to
restore to a point where I have only entered the first two records,
but all that seems to happen is that I am restored to the point of the
first full backup.
I have attached my script (which is heavily based on the one you
originally posted).
What am I doing wrong ?...it's been driving me mad for days !!
All help gratefully received .
Kind Regards,
Nigel
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> Hi Ben.
> Are you sure you're on SQL 7.0? The term "full recovery" only came along
> with SQL 2000...
> Anyway, this should be simple - restore the last good full backup without
> recovering, then apply the transaction log & stopat the appropriate time.
> Below is a demo script that should work on both 7.0 & 2000, whichever you're
> using. Follow it carefully & you should be able to see that this is
> possible. Note that the table t1 is populated with a row, which is then
> deleted between the two full backups. By performing a normal full database
> restore without recovery, then a log restore with recovery, you can get the
> point in time you're after.
> set nocount on
> go
> use master
> go
> create database a
> on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> go
> use a
> go
> create table t1 (c1 int)
> create table t2 (restoretime varchar(26))
> go
> /* insert a row into t1. we'll expect to see this row again after restore,
> despite it being deleted before full backup 2 */
> insert into t1 values (1)
> go
> use master
> go
> /* your full backup from whenever */
> backup database a to disk='c:\a_bak1.bak'
> go
> use a
> go
> /* insert a time into t2 that can be read as a stopat time accross batches
> */
> insert into t2 values (convert(varchar(26), getdate(), 9))
> go
> /* delay one minute */
> waitfor delay '00:01:00'
> go
> /* delete the row from t1. This represents the mistake we want to recover
> before.. */
> delete from t1
> go
> use master
> go
> /* your secondary, post mistake full backup */
> backup database a to disk='c:\a_bak2.bak'
> go
> /* your log backup */
> backup log a to disk='c:\a_bak3.bak'
> go
> /* we restore to point in time captured in t2. I'm only using t2 so we could
> record that point in time accross batches for the purposes of this example
> script */
> use a
> declare @.restoretime varchar(26)
> select @.restoretime = min(restoretime) from t2
> use master
> restore database a from disk='c:\a_bak1.bak' with norecovery
> restore log a from disk='c:\a_bak3.bak' with recovery, stopat = @.restoretime
> go
> use a
> go
> /* prove that the deleted row from t1 is restored */
> select * from t1
> go
> /* clean up */
> use master
> go
> drop database a
> go
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:bkhf32$mlv$1@.reader10.wxs.nl...
> > Hello,
> >
> > We want to do a restore to a point of time.
> >
> > With recovery mode on full.
> > Can one do a restore to a point of time before the last full backup
> > with a transaction log backup made after the full backup ?
> >
> > Sequence.
> >
> > (full recovery mode, SQL-server 7. I think.).
> > a. Somewere in the past a full backup is made.
> > b. An error is made.
> > c. A full backup is made.
> > d. A transaction log backup is made.
> >
> > We want to restore to a point in time just before
> > the error (b.) is made. Is this possible ?
> > (I have got the BOL (from 7) and inside from Kalen but
> > can not find the anwsers there).
> >
> > Ben Brugman.
> >
> >|||Hi Nigel.
There's no attachment to your post. I'm not sure, but I think these might be
getting dropped by the news-servers at the moment, so please re-post with
your script in the body of your post & I'll look at the script & try to work
it out for you..
Don't email it to me as I have a strong filter on my email & you're not in
my address book, so you won't get through..
Regards,
Greg Linwood
SQL Server MVP
"NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
news:26210f56.0310080734.53626df5@.posting.google.com...
> Greg,
> I too have been having problems with the restore to a point in time
> and your sample script was useful in trying to come to grips with
> this. All this seems to do though , is to restore the datbase to the
> state it was in when it was backed up to a_bak1.bak.
> I have managed to write a script to add 4 records to the database ,
> then do a full backup to a_bak1, then add a few more records then do
> another full backup and a transaction log backup. I now want to
> restore to a point where I have only entered the first two records,
> but all that seems to happen is that I am restored to the point of the
> first full backup.
> I have attached my script (which is heavily based on the one you
> originally posted).
> What am I doing wrong ?...it's been driving me mad for days !!
> All help gratefully received .
>
> Kind Regards,
> Nigel
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > Hi Ben.
> >
> > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > with SQL 2000...
> >
> > Anyway, this should be simple - restore the last good full backup
without
> > recovering, then apply the transaction log & stopat the appropriate
time.
> > Below is a demo script that should work on both 7.0 & 2000, whichever
you're
> > using. Follow it carefully & you should be able to see that this is
> > possible. Note that the table t1 is populated with a row, which is then
> > deleted between the two full backups. By performing a normal full
database
> > restore without recovery, then a log restore with recovery, you can get
the
> > point in time you're after.
> >
> > set nocount on
> > go
> > use master
> > go
> > create database a
> > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > go
> > use a
> > go
> > create table t1 (c1 int)
> > create table t2 (restoretime varchar(26))
> > go
> > /* insert a row into t1. we'll expect to see this row again after
restore,
> > despite it being deleted before full backup 2 */
> > insert into t1 values (1)
> > go
> > use master
> > go
> > /* your full backup from whenever */
> > backup database a to disk='c:\a_bak1.bak'
> > go
> > use a
> > go
> > /* insert a time into t2 that can be read as a stopat time accross
batches
> > */
> > insert into t2 values (convert(varchar(26), getdate(), 9))
> > go
> > /* delay one minute */
> > waitfor delay '00:01:00'
> > go
> > /* delete the row from t1. This represents the mistake we want to
recover
> > before.. */
> > delete from t1
> > go
> > use master
> > go
> > /* your secondary, post mistake full backup */
> > backup database a to disk='c:\a_bak2.bak'
> > go
> > /* your log backup */
> > backup log a to disk='c:\a_bak3.bak'
> > go
> > /* we restore to point in time captured in t2. I'm only using t2 so we
could
> > record that point in time accross batches for the purposes of this
example
> > script */
> > use a
> > declare @.restoretime varchar(26)
> > select @.restoretime = min(restoretime) from t2
> > use master
> > restore database a from disk='c:\a_bak1.bak' with norecovery
> > restore log a from disk='c:\a_bak3.bak' with recovery, stopat =@.restoretime
> > go
> > use a
> > go
> > /* prove that the deleted row from t1 is restored */
> > select * from t1
> > go
> > /* clean up */
> > use master
> > go
> > drop database a
> > go
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > Hello,
> > >
> > > We want to do a restore to a point of time.
> > >
> > > With recovery mode on full.
> > > Can one do a restore to a point of time before the last full backup
> > > with a transaction log backup made after the full backup ?
> > >
> > > Sequence.
> > >
> > > (full recovery mode, SQL-server 7. I think.).
> > > a. Somewere in the past a full backup is made.
> > > b. An error is made.
> > > c. A full backup is made.
> > > d. A transaction log backup is made.
> > >
> > > We want to restore to a point in time just before
> > > the error (b.) is made. Is this possible ?
> > > (I have got the BOL (from 7) and inside from Kalen but
> > > can not find the anwsers there).
> > >
> > > Ben Brugman.
> > >
> > >|||Hi Greg,
The script is as follows..I know its a terrible script, I'm having to
learn as I go with this..
---
set nocount on
go
use master
create database a
on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
go
use a
go
create table t1 (c1 int, restoretime varchar(26))
create table t2 (restoretime varchar (26))
go
backup database a to disk='c:\a_bak0.bak'
go
insert into t1 values(1 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(2 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(3 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(4 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
use master
go
backup database a to disk='c:\a_bak1.bak'
go
use a
go
insert into t1 values(5 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
go
waitfor delay '00:00:05'
insert into t1 values(6 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
go
waitfor delay '00:00:05'
go
use master
go
backup database a to disk='c:\a_bak2.bak'
go
backup log a to disk='c:\a_bak3.bak'
go
use a
go
----
Basically I'm trying to prove to myself that I can decide to restore
back to any particular transaction, which I assume is what restore to
point in time is all about.
I think I understood your original example script, but as far as i
could see there was no need to use the transaction log as the database
would be put back to the desired point in time simply by restore the
first full backup.
Basically I'm trying to prove to myself that I can decide to restore
back to any particular transaction, which I assume is what restore to
point in time is all about.
What I am attempting to do is try to restore to the point just after
the 2nd or 3rd transaction.
Thanks for your interest in my problem, most appreciated..and thanks
for the quick response.
Nigel
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> Hi Nigel.
> There's no attachment to your post. I'm not sure, but I think these might be
> getting dropped by the news-servers at the moment, so please re-post with
> your script in the body of your post & I'll look at the script & try to work
> it out for you..
> Don't email it to me as I have a strong filter on my email & you're not in
> my address book, so you won't get through..
> Regards,
> Greg Linwood
> SQL Server MVP
> "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> news:26210f56.0310080734.53626df5@.posting.google.com...
> > Greg,
> >
> > I too have been having problems with the restore to a point in time
> > and your sample script was useful in trying to come to grips with
> > this. All this seems to do though , is to restore the datbase to the
> > state it was in when it was backed up to a_bak1.bak.
> >
> > I have managed to write a script to add 4 records to the database ,
> > then do a full backup to a_bak1, then add a few more records then do
> > another full backup and a transaction log backup. I now want to
> > restore to a point where I have only entered the first two records,
> > but all that seems to happen is that I am restored to the point of the
> > first full backup.
> > I have attached my script (which is heavily based on the one you
> > originally posted).
> >
> > What am I doing wrong ?...it's been driving me mad for days !!
> >
> > All help gratefully received .
> >
> >
> > Kind Regards,
> >
> > Nigel
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > Hi Ben.
> > >
> > > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > > with SQL 2000...
> > >
> > > Anyway, this should be simple - restore the last good full backup
> without
> > > recovering, then apply the transaction log & stopat the appropriate
> time.
> > > Below is a demo script that should work on both 7.0 & 2000, whichever
> you're
> > > using. Follow it carefully & you should be able to see that this is
> > > possible. Note that the table t1 is populated with a row, which is then
> > > deleted between the two full backups. By performing a normal full
> database
> > > restore without recovery, then a log restore with recovery, you can get
> the
> > > point in time you're after.
> > >
> > > set nocount on
> > > go
> > > use master
> > > go
> > > create database a
> > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > > go
> > > use a
> > > go
> > > create table t1 (c1 int)
> > > create table t2 (restoretime varchar(26))
> > > go
> > > /* insert a row into t1. we'll expect to see this row again after
> restore,
> > > despite it being deleted before full backup 2 */
> > > insert into t1 values (1)
> > > go
> > > use master
> > > go
> > > /* your full backup from whenever */
> > > backup database a to disk='c:\a_bak1.bak'
> > > go
> > > use a
> > > go
> > > /* insert a time into t2 that can be read as a stopat time accross
> batches
> > > */
> > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > go
> > > /* delay one minute */
> > > waitfor delay '00:01:00'
> > > go
> > > /* delete the row from t1. This represents the mistake we want to
> recover
> > > before.. */
> > > delete from t1
> > > go
> > > use master
> > > go
> > > /* your secondary, post mistake full backup */
> > > backup database a to disk='c:\a_bak2.bak'
> > > go
> > > /* your log backup */
> > > backup log a to disk='c:\a_bak3.bak'
> > > go
> > > /* we restore to point in time captured in t2. I'm only using t2 so we
> could
> > > record that point in time accross batches for the purposes of this
> example
> > > script */
> > > use a
> > > declare @.restoretime varchar(26)
> > > select @.restoretime = min(restoretime) from t2
> > > use master
> > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => @.restoretime
> > > go
> > > use a
> > > go
> > > /* prove that the deleted row from t1 is restored */
> > > select * from t1
> > > go
> > > /* clean up */
> > > use master
> > > go
> > > drop database a
> > > go
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > Hello,
> > > >
> > > > We want to do a restore to a point of time.
> > > >
> > > > With recovery mode on full.
> > > > Can one do a restore to a point of time before the last full backup
> > > > with a transaction log backup made after the full backup ?
> > > >
> > > > Sequence.
> > > >
> > > > (full recovery mode, SQL-server 7. I think.).
> > > > a. Somewere in the past a full backup is made.
> > > > b. An error is made.
> > > > c. A full backup is made.
> > > > d. A transaction log backup is made.
> > > >
> > > > We want to restore to a point in time just before
> > > > the error (b.) is made. Is this possible ?
> > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > can not find the anwsers there).
> > > >
> > > > Ben Brugman.
> > > >
> > > >|||Hi Greg,
Please ignore my last post. I have sorted out the problems with the
restore to a point in time. I have noticed however that when I try to
restore to a time which is later than the last entry in the
transaction log, the database is left in a loading state. I am using
SQL Server 2000 with SP3A installed, and according to MS knowledgebase
article 319697 this particular issue was fixed with SP3. It also
states that the problem was when using Enterprise Manager to carry out
the restore. I am using a script. Have you come across this problem
even when SP3 has been installed ?
Kind Regards,
Nigel
Nigel_Stallard@.hotmail.com (NIgel Stallard) wrote in message news:<26210f56.0310090259.664b6871@.posting.google.com>...
> Hi Greg,
> The script is as follows..I know its a terrible script, I'm having to
> learn as I go with this..
> ---
> set nocount on
> go
> use master
> create database a
> on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
> log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
> go
> use a
> go
> create table t1 (c1 int, restoretime varchar(26))
> create table t2 (restoretime varchar (26))
> go
> backup database a to disk='c:\a_bak0.bak'
> go
> insert into t1 values(1 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(2 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(3 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(4 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> use master
> go
> backup database a to disk='c:\a_bak1.bak'
> go
> use a
> go
> insert into t1 values(5 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> go
> waitfor delay '00:00:05'
> insert into t1 values(6 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> go
> waitfor delay '00:00:05'
> go
> use master
> go
> backup database a to disk='c:\a_bak2.bak'
> go
> backup log a to disk='c:\a_bak3.bak'
> go
> use a
> go
> ----
> Basically I'm trying to prove to myself that I can decide to restore
> back to any particular transaction, which I assume is what restore to
> point in time is all about.
> I think I understood your original example script, but as far as i
> could see there was no need to use the transaction log as the database
> would be put back to the desired point in time simply by restore the
> first full backup.
> Basically I'm trying to prove to myself that I can decide to restore
> back to any particular transaction, which I assume is what restore to
> point in time is all about.
> What I am attempting to do is try to restore to the point just after
> the 2nd or 3rd transaction.
> Thanks for your interest in my problem, most appreciated..and thanks
> for the quick response.
> Nigel
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> > Hi Nigel.
> >
> > There's no attachment to your post. I'm not sure, but I think these might be
> > getting dropped by the news-servers at the moment, so please re-post with
> > your script in the body of your post & I'll look at the script & try to work
> > it out for you..
> >
> > Don't email it to me as I have a strong filter on my email & you're not in
> > my address book, so you won't get through..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> > news:26210f56.0310080734.53626df5@.posting.google.com...
> > > Greg,
> > >
> > > I too have been having problems with the restore to a point in time
> > > and your sample script was useful in trying to come to grips with
> > > this. All this seems to do though , is to restore the datbase to the
> > > state it was in when it was backed up to a_bak1.bak.
> > >
> > > I have managed to write a script to add 4 records to the database ,
> > > then do a full backup to a_bak1, then add a few more records then do
> > > another full backup and a transaction log backup. I now want to
> > > restore to a point where I have only entered the first two records,
> > > but all that seems to happen is that I am restored to the point of the
> > > first full backup.
> > > I have attached my script (which is heavily based on the one you
> > > originally posted).
> > >
> > > What am I doing wrong ?...it's been driving me mad for days !!
> > >
> > > All help gratefully received .
> > >
> > >
> > > Kind Regards,
> > >
> > > Nigel
> > >
> > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > > Hi Ben.
> > > >
> > > > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > > > with SQL 2000...
> > > >
> > > > Anyway, this should be simple - restore the last good full backup
> without
> > > > recovering, then apply the transaction log & stopat the appropriate
> time.
> > > > Below is a demo script that should work on both 7.0 & 2000, whichever
> you're
> > > > using. Follow it carefully & you should be able to see that this is
> > > > possible. Note that the table t1 is populated with a row, which is then
> > > > deleted between the two full backups. By performing a normal full
> database
> > > > restore without recovery, then a log restore with recovery, you can get
> the
> > > > point in time you're after.
> > > >
> > > > set nocount on
> > > > go
> > > > use master
> > > > go
> > > > create database a
> > > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > > > go
> > > > use a
> > > > go
> > > > create table t1 (c1 int)
> > > > create table t2 (restoretime varchar(26))
> > > > go
> > > > /* insert a row into t1. we'll expect to see this row again after
> restore,
> > > > despite it being deleted before full backup 2 */
> > > > insert into t1 values (1)
> > > > go
> > > > use master
> > > > go
> > > > /* your full backup from whenever */
> > > > backup database a to disk='c:\a_bak1.bak'
> > > > go
> > > > use a
> > > > go
> > > > /* insert a time into t2 that can be read as a stopat time accross
> batches
> > > > */
> > > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > > go
> > > > /* delay one minute */
> > > > waitfor delay '00:01:00'
> > > > go
> > > > /* delete the row from t1. This represents the mistake we want to
> recover
> > > > before.. */
> > > > delete from t1
> > > > go
> > > > use master
> > > > go
> > > > /* your secondary, post mistake full backup */
> > > > backup database a to disk='c:\a_bak2.bak'
> > > > go
> > > > /* your log backup */
> > > > backup log a to disk='c:\a_bak3.bak'
> > > > go
> > > > /* we restore to point in time captured in t2. I'm only using t2 so we
> could
> > > > record that point in time accross batches for the purposes of this
> example
> > > > script */
> > > > use a
> > > > declare @.restoretime varchar(26)
> > > > select @.restoretime = min(restoretime) from t2
> > > > use master
> > > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => @.restoretime
> > > > go
> > > > use a
> > > > go
> > > > /* prove that the deleted row from t1 is restored */
> > > > select * from t1
> > > > go
> > > > /* clean up */
> > > > use master
> > > > go
> > > > drop database a
> > > > go
> > > >
> > > > HTH
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > > Hello,
> > > > >
> > > > > We want to do a restore to a point of time.
> > > > >
> > > > > With recovery mode on full.
> > > > > Can one do a restore to a point of time before the last full backup
> > > > > with a transaction log backup made after the full backup ?
> > > > >
> > > > > Sequence.
> > > > >
> > > > > (full recovery mode, SQL-server 7. I think.).
> > > > > a. Somewere in the past a full backup is made.
> > > > > b. An error is made.
> > > > > c. A full backup is made.
> > > > > d. A transaction log backup is made.
> > > > >
> > > > > We want to restore to a point in time just before
> > > > > the error (b.) is made. Is this possible ?
> > > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > > can not find the anwsers there).
> > > > >
> > > > > Ben Brugman.
> > > > >
> > > > >|||I tried some restores to a point in time. What I experienced was
that I could not restore to before the first transaction log backup.
Could be that I did not use the correct procedure, but I tried
several way to restore to some points in time all times after
the first transaction log backup were possible none before.
My solution is do a transaction backup immediatly after you have
created a database. Then there is no problem.
Could be that your problem is similar ?
Can you confirm the above ?
Thanks for sharing your knowledge,
ben brugman
"NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
news:26210f56.0310090757.2a60601a@.posting.google.com...
> Hi Greg,
> Please ignore my last post. I have sorted out the problems with the
> restore to a point in time. I have noticed however that when I try to
> restore to a time which is later than the last entry in the
> transaction log, the database is left in a loading state. I am using
> SQL Server 2000 with SP3A installed, and according to MS knowledgebase
> article 319697 this particular issue was fixed with SP3. It also
> states that the problem was when using Enterprise Manager to carry out
> the restore. I am using a script. Have you come across this problem
> even when SP3 has been installed ?
> Kind Regards,
> Nigel
> Nigel_Stallard@.hotmail.com (NIgel Stallard) wrote in message
news:<26210f56.0310090259.664b6871@.posting.google.com>...
> > Hi Greg,
> >
> > The script is as follows..I know its a terrible script, I'm having to
> > learn as I go with this..
> > ---
> > set nocount on
> > go
> > use master
> > create database a
> > on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
> > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
> > go
> > use a
> > go
> > create table t1 (c1 int, restoretime varchar(26))
> > create table t2 (restoretime varchar (26))
> > go
> > backup database a to disk='c:\a_bak0.bak'
> > go
> > insert into t1 values(1 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(2 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(3 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(4 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > use master
> > go
> > backup database a to disk='c:\a_bak1.bak'
> > go
> > use a
> > go
> > insert into t1 values(5 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > go
> > waitfor delay '00:00:05'
> > insert into t1 values(6 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > go
> > waitfor delay '00:00:05'
> > go
> > use master
> > go
> > backup database a to disk='c:\a_bak2.bak'
> > go
> > backup log a to disk='c:\a_bak3.bak'
> > go
> > use a
> > go
> ----
> >
> > Basically I'm trying to prove to myself that I can decide to restore
> > back to any particular transaction, which I assume is what restore to
> > point in time is all about.
> >
> > I think I understood your original example script, but as far as i
> > could see there was no need to use the transaction log as the database
> > would be put back to the desired point in time simply by restore the
> > first full backup.
> >
> > Basically I'm trying to prove to myself that I can decide to restore
> > back to any particular transaction, which I assume is what restore to
> > point in time is all about.
> > What I am attempting to do is try to restore to the point just after
> > the 2nd or 3rd transaction.
> >
> > Thanks for your interest in my problem, most appreciated..and thanks
> > for the quick response.
> >
> > Nigel
> >
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> > > Hi Nigel.
> > >
> > > There's no attachment to your post. I'm not sure, but I think these
might be
> > > getting dropped by the news-servers at the moment, so please re-post
with
> > > your script in the body of your post & I'll look at the script & try
to work
> > > it out for you..
> > >
> > > Don't email it to me as I have a strong filter on my email & you're
not in
> > > my address book, so you won't get through..
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> > > news:26210f56.0310080734.53626df5@.posting.google.com...
> > > > Greg,
> > > >
> > > > I too have been having problems with the restore to a point in time
> > > > and your sample script was useful in trying to come to grips with
> > > > this. All this seems to do though , is to restore the datbase to the
> > > > state it was in when it was backed up to a_bak1.bak.
> > > >
> > > > I have managed to write a script to add 4 records to the database ,
> > > > then do a full backup to a_bak1, then add a few more records then do
> > > > another full backup and a transaction log backup. I now want to
> > > > restore to a point where I have only entered the first two records,
> > > > but all that seems to happen is that I am restored to the point of
the
> > > > first full backup.
> > > > I have attached my script (which is heavily based on the one you
> > > > originally posted).
> > > >
> > > > What am I doing wrong ?...it's been driving me mad for days !!
> > > >
> > > > All help gratefully received .
> > > >
> > > >
> > > > Kind Regards,
> > > >
> > > > Nigel
> > > >
> > > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > > > Hi Ben.
> > > > >
> > > > > Are you sure you're on SQL 7.0? The term "full recovery" only came
along
> > > > > with SQL 2000...
> > > > >
> > > > > Anyway, this should be simple - restore the last good full backup
> > without
> > > > > recovering, then apply the transaction log & stopat the
appropriate
> > time.
> > > > > Below is a demo script that should work on both 7.0 & 2000,
whichever
> > you're
> > > > > using. Follow it carefully & you should be able to see that this
is
> > > > > possible. Note that the table t1 is populated with a row, which is
then
> > > > > deleted between the two full backups. By performing a normal full
> > database
> > > > > restore without recovery, then a log restore with recovery, you
can get
> > the
> > > > > point in time you're after.
> > > > >
> > > > > set nocount on
> > > > > go
> > > > > use master
> > > > > go
> > > > > create database a
> > > > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb,
filegrowth=1mb )
> > > > > go
> > > > > use a
> > > > > go
> > > > > create table t1 (c1 int)
> > > > > create table t2 (restoretime varchar(26))
> > > > > go
> > > > > /* insert a row into t1. we'll expect to see this row again after
> > restore,
> > > > > despite it being deleted before full backup 2 */
> > > > > insert into t1 values (1)
> > > > > go
> > > > > use master
> > > > > go
> > > > > /* your full backup from whenever */
> > > > > backup database a to disk='c:\a_bak1.bak'
> > > > > go
> > > > > use a
> > > > > go
> > > > > /* insert a time into t2 that can be read as a stopat time accross
> > batches
> > > > > */
> > > > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > > > go
> > > > > /* delay one minute */
> > > > > waitfor delay '00:01:00'
> > > > > go
> > > > > /* delete the row from t1. This represents the mistake we want to
> > recover
> > > > > before.. */
> > > > > delete from t1
> > > > > go
> > > > > use master
> > > > > go
> > > > > /* your secondary, post mistake full backup */
> > > > > backup database a to disk='c:\a_bak2.bak'
> > > > > go
> > > > > /* your log backup */
> > > > > backup log a to disk='c:\a_bak3.bak'
> > > > > go
> > > > > /* we restore to point in time captured in t2. I'm only using t2
so we
> > could
> > > > > record that point in time accross batches for the purposes of this
> > example
> > > > > script */
> > > > > use a
> > > > > declare @.restoretime varchar(26)
> > > > > select @.restoretime = min(restoretime) from t2
> > > > > use master
> > > > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => > @.restoretime
> > > > > go
> > > > > use a
> > > > > go
> > > > > /* prove that the deleted row from t1 is restored */
> > > > > select * from t1
> > > > > go
> > > > > /* clean up */
> > > > > use master
> > > > > go
> > > > > drop database a
> > > > > go
> > > > >
> > > > > HTH
> > > > >
> > > > > Regards,
> > > > > Greg Linwood
> > > > > SQL Server MVP
> > > > >
> > > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > > > Hello,
> > > > > >
> > > > > > We want to do a restore to a point of time.
> > > > > >
> > > > > > With recovery mode on full.
> > > > > > Can one do a restore to a point of time before the last full
backup
> > > > > > with a transaction log backup made after the full backup ?
> > > > > >
> > > > > > Sequence.
> > > > > >
> > > > > > (full recovery mode, SQL-server 7. I think.).
> > > > > > a. Somewere in the past a full backup is made.
> > > > > > b. An error is made.
> > > > > > c. A full backup is made.
> > > > > > d. A transaction log backup is made.
> > > > > >
> > > > > > We want to restore to a point in time just before
> > > > > > the error (b.) is made. Is this possible ?
> > > > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > > > can not find the anwsers there).
> > > > > >
> > > > > > Ben Brugman.
> > > > > >
> > > > > >
We want to do a restore to a point of time.
With recovery mode on full.
Can one do a restore to a point of time before the last full backup
with a transaction log backup made after the full backup ?
Sequence.
(full recovery mode, SQL-server 7. I think.).
a. Somewere in the past a full backup is made.
b. An error is made.
c. A full backup is made.
d. A transaction log backup is made.
We want to restore to a point in time just before
the error (b.) is made. Is this possible ?
(I have got the BOL (from 7) and inside from Kalen but
can not find the anwsers there).
Ben Brugman.> We want to do a restore to a point of time.
> With recovery mode on full.
> Sequence.
> (full recovery mode, SQL-server 7. I think.).
There is no FULL recovery mode in SQL 7. In SQL 7 and earlier versions,
the 'trunc. log on chkpt.' database option turned off is similar.
> a. Somewere in the past a full backup is made.
> b. An error is made.
> c. A full backup is made.
> d. A transaction log backup is made.
> We want to restore to a point in time just before
> the error (b.) is made. Is this possible ?
You can perform point-in-time recovery as follows:
1) Restore full database backup from step 'a' WITH NORECOVERY
2) Restore transaction log backup from step 'd' WITH STOPAT (time
before step 'b' error) and RECOVERY
Note that if you have any other log backups between 'a' and 'b', these
will need to be applied (WITH NORECOVERY) before step '2' above.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"ben brugman" <ben@.niethier.nl> wrote in message
news:bkhf32$mlv$1@.reader10.wxs.nl...
> Hello,
> We want to do a restore to a point of time.
> With recovery mode on full.
> Can one do a restore to a point of time before the last full backup
> with a transaction log backup made after the full backup ?
> Sequence.
> (full recovery mode, SQL-server 7. I think.).
> a. Somewere in the past a full backup is made.
> b. An error is made.
> c. A full backup is made.
> d. A transaction log backup is made.
> We want to restore to a point in time just before
> the error (b.) is made. Is this possible ?
> (I have got the BOL (from 7) and inside from Kalen but
> can not find the anwsers there).
> Ben Brugman.
>|||Greg,
I too have been having problems with the restore to a point in time
and your sample script was useful in trying to come to grips with
this. All this seems to do though , is to restore the datbase to the
state it was in when it was backed up to a_bak1.bak.
I have managed to write a script to add 4 records to the database ,
then do a full backup to a_bak1, then add a few more records then do
another full backup and a transaction log backup. I now want to
restore to a point where I have only entered the first two records,
but all that seems to happen is that I am restored to the point of the
first full backup.
I have attached my script (which is heavily based on the one you
originally posted).
What am I doing wrong ?...it's been driving me mad for days !!
All help gratefully received .
Kind Regards,
Nigel
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> Hi Ben.
> Are you sure you're on SQL 7.0? The term "full recovery" only came along
> with SQL 2000...
> Anyway, this should be simple - restore the last good full backup without
> recovering, then apply the transaction log & stopat the appropriate time.
> Below is a demo script that should work on both 7.0 & 2000, whichever you're
> using. Follow it carefully & you should be able to see that this is
> possible. Note that the table t1 is populated with a row, which is then
> deleted between the two full backups. By performing a normal full database
> restore without recovery, then a log restore with recovery, you can get the
> point in time you're after.
> set nocount on
> go
> use master
> go
> create database a
> on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> go
> use a
> go
> create table t1 (c1 int)
> create table t2 (restoretime varchar(26))
> go
> /* insert a row into t1. we'll expect to see this row again after restore,
> despite it being deleted before full backup 2 */
> insert into t1 values (1)
> go
> use master
> go
> /* your full backup from whenever */
> backup database a to disk='c:\a_bak1.bak'
> go
> use a
> go
> /* insert a time into t2 that can be read as a stopat time accross batches
> */
> insert into t2 values (convert(varchar(26), getdate(), 9))
> go
> /* delay one minute */
> waitfor delay '00:01:00'
> go
> /* delete the row from t1. This represents the mistake we want to recover
> before.. */
> delete from t1
> go
> use master
> go
> /* your secondary, post mistake full backup */
> backup database a to disk='c:\a_bak2.bak'
> go
> /* your log backup */
> backup log a to disk='c:\a_bak3.bak'
> go
> /* we restore to point in time captured in t2. I'm only using t2 so we could
> record that point in time accross batches for the purposes of this example
> script */
> use a
> declare @.restoretime varchar(26)
> select @.restoretime = min(restoretime) from t2
> use master
> restore database a from disk='c:\a_bak1.bak' with norecovery
> restore log a from disk='c:\a_bak3.bak' with recovery, stopat = @.restoretime
> go
> use a
> go
> /* prove that the deleted row from t1 is restored */
> select * from t1
> go
> /* clean up */
> use master
> go
> drop database a
> go
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:bkhf32$mlv$1@.reader10.wxs.nl...
> > Hello,
> >
> > We want to do a restore to a point of time.
> >
> > With recovery mode on full.
> > Can one do a restore to a point of time before the last full backup
> > with a transaction log backup made after the full backup ?
> >
> > Sequence.
> >
> > (full recovery mode, SQL-server 7. I think.).
> > a. Somewere in the past a full backup is made.
> > b. An error is made.
> > c. A full backup is made.
> > d. A transaction log backup is made.
> >
> > We want to restore to a point in time just before
> > the error (b.) is made. Is this possible ?
> > (I have got the BOL (from 7) and inside from Kalen but
> > can not find the anwsers there).
> >
> > Ben Brugman.
> >
> >|||Hi Nigel.
There's no attachment to your post. I'm not sure, but I think these might be
getting dropped by the news-servers at the moment, so please re-post with
your script in the body of your post & I'll look at the script & try to work
it out for you..
Don't email it to me as I have a strong filter on my email & you're not in
my address book, so you won't get through..
Regards,
Greg Linwood
SQL Server MVP
"NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
news:26210f56.0310080734.53626df5@.posting.google.com...
> Greg,
> I too have been having problems with the restore to a point in time
> and your sample script was useful in trying to come to grips with
> this. All this seems to do though , is to restore the datbase to the
> state it was in when it was backed up to a_bak1.bak.
> I have managed to write a script to add 4 records to the database ,
> then do a full backup to a_bak1, then add a few more records then do
> another full backup and a transaction log backup. I now want to
> restore to a point where I have only entered the first two records,
> but all that seems to happen is that I am restored to the point of the
> first full backup.
> I have attached my script (which is heavily based on the one you
> originally posted).
> What am I doing wrong ?...it's been driving me mad for days !!
> All help gratefully received .
>
> Kind Regards,
> Nigel
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > Hi Ben.
> >
> > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > with SQL 2000...
> >
> > Anyway, this should be simple - restore the last good full backup
without
> > recovering, then apply the transaction log & stopat the appropriate
time.
> > Below is a demo script that should work on both 7.0 & 2000, whichever
you're
> > using. Follow it carefully & you should be able to see that this is
> > possible. Note that the table t1 is populated with a row, which is then
> > deleted between the two full backups. By performing a normal full
database
> > restore without recovery, then a log restore with recovery, you can get
the
> > point in time you're after.
> >
> > set nocount on
> > go
> > use master
> > go
> > create database a
> > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > go
> > use a
> > go
> > create table t1 (c1 int)
> > create table t2 (restoretime varchar(26))
> > go
> > /* insert a row into t1. we'll expect to see this row again after
restore,
> > despite it being deleted before full backup 2 */
> > insert into t1 values (1)
> > go
> > use master
> > go
> > /* your full backup from whenever */
> > backup database a to disk='c:\a_bak1.bak'
> > go
> > use a
> > go
> > /* insert a time into t2 that can be read as a stopat time accross
batches
> > */
> > insert into t2 values (convert(varchar(26), getdate(), 9))
> > go
> > /* delay one minute */
> > waitfor delay '00:01:00'
> > go
> > /* delete the row from t1. This represents the mistake we want to
recover
> > before.. */
> > delete from t1
> > go
> > use master
> > go
> > /* your secondary, post mistake full backup */
> > backup database a to disk='c:\a_bak2.bak'
> > go
> > /* your log backup */
> > backup log a to disk='c:\a_bak3.bak'
> > go
> > /* we restore to point in time captured in t2. I'm only using t2 so we
could
> > record that point in time accross batches for the purposes of this
example
> > script */
> > use a
> > declare @.restoretime varchar(26)
> > select @.restoretime = min(restoretime) from t2
> > use master
> > restore database a from disk='c:\a_bak1.bak' with norecovery
> > restore log a from disk='c:\a_bak3.bak' with recovery, stopat =@.restoretime
> > go
> > use a
> > go
> > /* prove that the deleted row from t1 is restored */
> > select * from t1
> > go
> > /* clean up */
> > use master
> > go
> > drop database a
> > go
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > Hello,
> > >
> > > We want to do a restore to a point of time.
> > >
> > > With recovery mode on full.
> > > Can one do a restore to a point of time before the last full backup
> > > with a transaction log backup made after the full backup ?
> > >
> > > Sequence.
> > >
> > > (full recovery mode, SQL-server 7. I think.).
> > > a. Somewere in the past a full backup is made.
> > > b. An error is made.
> > > c. A full backup is made.
> > > d. A transaction log backup is made.
> > >
> > > We want to restore to a point in time just before
> > > the error (b.) is made. Is this possible ?
> > > (I have got the BOL (from 7) and inside from Kalen but
> > > can not find the anwsers there).
> > >
> > > Ben Brugman.
> > >
> > >|||Hi Greg,
The script is as follows..I know its a terrible script, I'm having to
learn as I go with this..
---
set nocount on
go
use master
create database a
on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
go
use a
go
create table t1 (c1 int, restoretime varchar(26))
create table t2 (restoretime varchar (26))
go
backup database a to disk='c:\a_bak0.bak'
go
insert into t1 values(1 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(2 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(3 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(4 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
use master
go
backup database a to disk='c:\a_bak1.bak'
go
use a
go
insert into t1 values(5 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
go
waitfor delay '00:00:05'
insert into t1 values(6 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
go
waitfor delay '00:00:05'
go
use master
go
backup database a to disk='c:\a_bak2.bak'
go
backup log a to disk='c:\a_bak3.bak'
go
use a
go
----
Basically I'm trying to prove to myself that I can decide to restore
back to any particular transaction, which I assume is what restore to
point in time is all about.
I think I understood your original example script, but as far as i
could see there was no need to use the transaction log as the database
would be put back to the desired point in time simply by restore the
first full backup.
Basically I'm trying to prove to myself that I can decide to restore
back to any particular transaction, which I assume is what restore to
point in time is all about.
What I am attempting to do is try to restore to the point just after
the 2nd or 3rd transaction.
Thanks for your interest in my problem, most appreciated..and thanks
for the quick response.
Nigel
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> Hi Nigel.
> There's no attachment to your post. I'm not sure, but I think these might be
> getting dropped by the news-servers at the moment, so please re-post with
> your script in the body of your post & I'll look at the script & try to work
> it out for you..
> Don't email it to me as I have a strong filter on my email & you're not in
> my address book, so you won't get through..
> Regards,
> Greg Linwood
> SQL Server MVP
> "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> news:26210f56.0310080734.53626df5@.posting.google.com...
> > Greg,
> >
> > I too have been having problems with the restore to a point in time
> > and your sample script was useful in trying to come to grips with
> > this. All this seems to do though , is to restore the datbase to the
> > state it was in when it was backed up to a_bak1.bak.
> >
> > I have managed to write a script to add 4 records to the database ,
> > then do a full backup to a_bak1, then add a few more records then do
> > another full backup and a transaction log backup. I now want to
> > restore to a point where I have only entered the first two records,
> > but all that seems to happen is that I am restored to the point of the
> > first full backup.
> > I have attached my script (which is heavily based on the one you
> > originally posted).
> >
> > What am I doing wrong ?...it's been driving me mad for days !!
> >
> > All help gratefully received .
> >
> >
> > Kind Regards,
> >
> > Nigel
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > Hi Ben.
> > >
> > > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > > with SQL 2000...
> > >
> > > Anyway, this should be simple - restore the last good full backup
> without
> > > recovering, then apply the transaction log & stopat the appropriate
> time.
> > > Below is a demo script that should work on both 7.0 & 2000, whichever
> you're
> > > using. Follow it carefully & you should be able to see that this is
> > > possible. Note that the table t1 is populated with a row, which is then
> > > deleted between the two full backups. By performing a normal full
> database
> > > restore without recovery, then a log restore with recovery, you can get
> the
> > > point in time you're after.
> > >
> > > set nocount on
> > > go
> > > use master
> > > go
> > > create database a
> > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > > go
> > > use a
> > > go
> > > create table t1 (c1 int)
> > > create table t2 (restoretime varchar(26))
> > > go
> > > /* insert a row into t1. we'll expect to see this row again after
> restore,
> > > despite it being deleted before full backup 2 */
> > > insert into t1 values (1)
> > > go
> > > use master
> > > go
> > > /* your full backup from whenever */
> > > backup database a to disk='c:\a_bak1.bak'
> > > go
> > > use a
> > > go
> > > /* insert a time into t2 that can be read as a stopat time accross
> batches
> > > */
> > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > go
> > > /* delay one minute */
> > > waitfor delay '00:01:00'
> > > go
> > > /* delete the row from t1. This represents the mistake we want to
> recover
> > > before.. */
> > > delete from t1
> > > go
> > > use master
> > > go
> > > /* your secondary, post mistake full backup */
> > > backup database a to disk='c:\a_bak2.bak'
> > > go
> > > /* your log backup */
> > > backup log a to disk='c:\a_bak3.bak'
> > > go
> > > /* we restore to point in time captured in t2. I'm only using t2 so we
> could
> > > record that point in time accross batches for the purposes of this
> example
> > > script */
> > > use a
> > > declare @.restoretime varchar(26)
> > > select @.restoretime = min(restoretime) from t2
> > > use master
> > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => @.restoretime
> > > go
> > > use a
> > > go
> > > /* prove that the deleted row from t1 is restored */
> > > select * from t1
> > > go
> > > /* clean up */
> > > use master
> > > go
> > > drop database a
> > > go
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > Hello,
> > > >
> > > > We want to do a restore to a point of time.
> > > >
> > > > With recovery mode on full.
> > > > Can one do a restore to a point of time before the last full backup
> > > > with a transaction log backup made after the full backup ?
> > > >
> > > > Sequence.
> > > >
> > > > (full recovery mode, SQL-server 7. I think.).
> > > > a. Somewere in the past a full backup is made.
> > > > b. An error is made.
> > > > c. A full backup is made.
> > > > d. A transaction log backup is made.
> > > >
> > > > We want to restore to a point in time just before
> > > > the error (b.) is made. Is this possible ?
> > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > can not find the anwsers there).
> > > >
> > > > Ben Brugman.
> > > >
> > > >|||Hi Greg,
Please ignore my last post. I have sorted out the problems with the
restore to a point in time. I have noticed however that when I try to
restore to a time which is later than the last entry in the
transaction log, the database is left in a loading state. I am using
SQL Server 2000 with SP3A installed, and according to MS knowledgebase
article 319697 this particular issue was fixed with SP3. It also
states that the problem was when using Enterprise Manager to carry out
the restore. I am using a script. Have you come across this problem
even when SP3 has been installed ?
Kind Regards,
Nigel
Nigel_Stallard@.hotmail.com (NIgel Stallard) wrote in message news:<26210f56.0310090259.664b6871@.posting.google.com>...
> Hi Greg,
> The script is as follows..I know its a terrible script, I'm having to
> learn as I go with this..
> ---
> set nocount on
> go
> use master
> create database a
> on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
> log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
> go
> use a
> go
> create table t1 (c1 int, restoretime varchar(26))
> create table t2 (restoretime varchar (26))
> go
> backup database a to disk='c:\a_bak0.bak'
> go
> insert into t1 values(1 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(2 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(3 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(4 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> use master
> go
> backup database a to disk='c:\a_bak1.bak'
> go
> use a
> go
> insert into t1 values(5 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> go
> waitfor delay '00:00:05'
> insert into t1 values(6 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> go
> waitfor delay '00:00:05'
> go
> use master
> go
> backup database a to disk='c:\a_bak2.bak'
> go
> backup log a to disk='c:\a_bak3.bak'
> go
> use a
> go
> ----
> Basically I'm trying to prove to myself that I can decide to restore
> back to any particular transaction, which I assume is what restore to
> point in time is all about.
> I think I understood your original example script, but as far as i
> could see there was no need to use the transaction log as the database
> would be put back to the desired point in time simply by restore the
> first full backup.
> Basically I'm trying to prove to myself that I can decide to restore
> back to any particular transaction, which I assume is what restore to
> point in time is all about.
> What I am attempting to do is try to restore to the point just after
> the 2nd or 3rd transaction.
> Thanks for your interest in my problem, most appreciated..and thanks
> for the quick response.
> Nigel
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> > Hi Nigel.
> >
> > There's no attachment to your post. I'm not sure, but I think these might be
> > getting dropped by the news-servers at the moment, so please re-post with
> > your script in the body of your post & I'll look at the script & try to work
> > it out for you..
> >
> > Don't email it to me as I have a strong filter on my email & you're not in
> > my address book, so you won't get through..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> > news:26210f56.0310080734.53626df5@.posting.google.com...
> > > Greg,
> > >
> > > I too have been having problems with the restore to a point in time
> > > and your sample script was useful in trying to come to grips with
> > > this. All this seems to do though , is to restore the datbase to the
> > > state it was in when it was backed up to a_bak1.bak.
> > >
> > > I have managed to write a script to add 4 records to the database ,
> > > then do a full backup to a_bak1, then add a few more records then do
> > > another full backup and a transaction log backup. I now want to
> > > restore to a point where I have only entered the first two records,
> > > but all that seems to happen is that I am restored to the point of the
> > > first full backup.
> > > I have attached my script (which is heavily based on the one you
> > > originally posted).
> > >
> > > What am I doing wrong ?...it's been driving me mad for days !!
> > >
> > > All help gratefully received .
> > >
> > >
> > > Kind Regards,
> > >
> > > Nigel
> > >
> > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > > Hi Ben.
> > > >
> > > > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > > > with SQL 2000...
> > > >
> > > > Anyway, this should be simple - restore the last good full backup
> without
> > > > recovering, then apply the transaction log & stopat the appropriate
> time.
> > > > Below is a demo script that should work on both 7.0 & 2000, whichever
> you're
> > > > using. Follow it carefully & you should be able to see that this is
> > > > possible. Note that the table t1 is populated with a row, which is then
> > > > deleted between the two full backups. By performing a normal full
> database
> > > > restore without recovery, then a log restore with recovery, you can get
> the
> > > > point in time you're after.
> > > >
> > > > set nocount on
> > > > go
> > > > use master
> > > > go
> > > > create database a
> > > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > > > go
> > > > use a
> > > > go
> > > > create table t1 (c1 int)
> > > > create table t2 (restoretime varchar(26))
> > > > go
> > > > /* insert a row into t1. we'll expect to see this row again after
> restore,
> > > > despite it being deleted before full backup 2 */
> > > > insert into t1 values (1)
> > > > go
> > > > use master
> > > > go
> > > > /* your full backup from whenever */
> > > > backup database a to disk='c:\a_bak1.bak'
> > > > go
> > > > use a
> > > > go
> > > > /* insert a time into t2 that can be read as a stopat time accross
> batches
> > > > */
> > > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > > go
> > > > /* delay one minute */
> > > > waitfor delay '00:01:00'
> > > > go
> > > > /* delete the row from t1. This represents the mistake we want to
> recover
> > > > before.. */
> > > > delete from t1
> > > > go
> > > > use master
> > > > go
> > > > /* your secondary, post mistake full backup */
> > > > backup database a to disk='c:\a_bak2.bak'
> > > > go
> > > > /* your log backup */
> > > > backup log a to disk='c:\a_bak3.bak'
> > > > go
> > > > /* we restore to point in time captured in t2. I'm only using t2 so we
> could
> > > > record that point in time accross batches for the purposes of this
> example
> > > > script */
> > > > use a
> > > > declare @.restoretime varchar(26)
> > > > select @.restoretime = min(restoretime) from t2
> > > > use master
> > > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => @.restoretime
> > > > go
> > > > use a
> > > > go
> > > > /* prove that the deleted row from t1 is restored */
> > > > select * from t1
> > > > go
> > > > /* clean up */
> > > > use master
> > > > go
> > > > drop database a
> > > > go
> > > >
> > > > HTH
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > > Hello,
> > > > >
> > > > > We want to do a restore to a point of time.
> > > > >
> > > > > With recovery mode on full.
> > > > > Can one do a restore to a point of time before the last full backup
> > > > > with a transaction log backup made after the full backup ?
> > > > >
> > > > > Sequence.
> > > > >
> > > > > (full recovery mode, SQL-server 7. I think.).
> > > > > a. Somewere in the past a full backup is made.
> > > > > b. An error is made.
> > > > > c. A full backup is made.
> > > > > d. A transaction log backup is made.
> > > > >
> > > > > We want to restore to a point in time just before
> > > > > the error (b.) is made. Is this possible ?
> > > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > > can not find the anwsers there).
> > > > >
> > > > > Ben Brugman.
> > > > >
> > > > >|||I tried some restores to a point in time. What I experienced was
that I could not restore to before the first transaction log backup.
Could be that I did not use the correct procedure, but I tried
several way to restore to some points in time all times after
the first transaction log backup were possible none before.
My solution is do a transaction backup immediatly after you have
created a database. Then there is no problem.
Could be that your problem is similar ?
Can you confirm the above ?
Thanks for sharing your knowledge,
ben brugman
"NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
news:26210f56.0310090757.2a60601a@.posting.google.com...
> Hi Greg,
> Please ignore my last post. I have sorted out the problems with the
> restore to a point in time. I have noticed however that when I try to
> restore to a time which is later than the last entry in the
> transaction log, the database is left in a loading state. I am using
> SQL Server 2000 with SP3A installed, and according to MS knowledgebase
> article 319697 this particular issue was fixed with SP3. It also
> states that the problem was when using Enterprise Manager to carry out
> the restore. I am using a script. Have you come across this problem
> even when SP3 has been installed ?
> Kind Regards,
> Nigel
> Nigel_Stallard@.hotmail.com (NIgel Stallard) wrote in message
news:<26210f56.0310090259.664b6871@.posting.google.com>...
> > Hi Greg,
> >
> > The script is as follows..I know its a terrible script, I'm having to
> > learn as I go with this..
> > ---
> > set nocount on
> > go
> > use master
> > create database a
> > on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
> > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
> > go
> > use a
> > go
> > create table t1 (c1 int, restoretime varchar(26))
> > create table t2 (restoretime varchar (26))
> > go
> > backup database a to disk='c:\a_bak0.bak'
> > go
> > insert into t1 values(1 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(2 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(3 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(4 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > use master
> > go
> > backup database a to disk='c:\a_bak1.bak'
> > go
> > use a
> > go
> > insert into t1 values(5 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > go
> > waitfor delay '00:00:05'
> > insert into t1 values(6 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > go
> > waitfor delay '00:00:05'
> > go
> > use master
> > go
> > backup database a to disk='c:\a_bak2.bak'
> > go
> > backup log a to disk='c:\a_bak3.bak'
> > go
> > use a
> > go
> ----
> >
> > Basically I'm trying to prove to myself that I can decide to restore
> > back to any particular transaction, which I assume is what restore to
> > point in time is all about.
> >
> > I think I understood your original example script, but as far as i
> > could see there was no need to use the transaction log as the database
> > would be put back to the desired point in time simply by restore the
> > first full backup.
> >
> > Basically I'm trying to prove to myself that I can decide to restore
> > back to any particular transaction, which I assume is what restore to
> > point in time is all about.
> > What I am attempting to do is try to restore to the point just after
> > the 2nd or 3rd transaction.
> >
> > Thanks for your interest in my problem, most appreciated..and thanks
> > for the quick response.
> >
> > Nigel
> >
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> > > Hi Nigel.
> > >
> > > There's no attachment to your post. I'm not sure, but I think these
might be
> > > getting dropped by the news-servers at the moment, so please re-post
with
> > > your script in the body of your post & I'll look at the script & try
to work
> > > it out for you..
> > >
> > > Don't email it to me as I have a strong filter on my email & you're
not in
> > > my address book, so you won't get through..
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> > > news:26210f56.0310080734.53626df5@.posting.google.com...
> > > > Greg,
> > > >
> > > > I too have been having problems with the restore to a point in time
> > > > and your sample script was useful in trying to come to grips with
> > > > this. All this seems to do though , is to restore the datbase to the
> > > > state it was in when it was backed up to a_bak1.bak.
> > > >
> > > > I have managed to write a script to add 4 records to the database ,
> > > > then do a full backup to a_bak1, then add a few more records then do
> > > > another full backup and a transaction log backup. I now want to
> > > > restore to a point where I have only entered the first two records,
> > > > but all that seems to happen is that I am restored to the point of
the
> > > > first full backup.
> > > > I have attached my script (which is heavily based on the one you
> > > > originally posted).
> > > >
> > > > What am I doing wrong ?...it's been driving me mad for days !!
> > > >
> > > > All help gratefully received .
> > > >
> > > >
> > > > Kind Regards,
> > > >
> > > > Nigel
> > > >
> > > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > > > Hi Ben.
> > > > >
> > > > > Are you sure you're on SQL 7.0? The term "full recovery" only came
along
> > > > > with SQL 2000...
> > > > >
> > > > > Anyway, this should be simple - restore the last good full backup
> > without
> > > > > recovering, then apply the transaction log & stopat the
appropriate
> > time.
> > > > > Below is a demo script that should work on both 7.0 & 2000,
whichever
> > you're
> > > > > using. Follow it carefully & you should be able to see that this
is
> > > > > possible. Note that the table t1 is populated with a row, which is
then
> > > > > deleted between the two full backups. By performing a normal full
> > database
> > > > > restore without recovery, then a log restore with recovery, you
can get
> > the
> > > > > point in time you're after.
> > > > >
> > > > > set nocount on
> > > > > go
> > > > > use master
> > > > > go
> > > > > create database a
> > > > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb,
filegrowth=1mb )
> > > > > go
> > > > > use a
> > > > > go
> > > > > create table t1 (c1 int)
> > > > > create table t2 (restoretime varchar(26))
> > > > > go
> > > > > /* insert a row into t1. we'll expect to see this row again after
> > restore,
> > > > > despite it being deleted before full backup 2 */
> > > > > insert into t1 values (1)
> > > > > go
> > > > > use master
> > > > > go
> > > > > /* your full backup from whenever */
> > > > > backup database a to disk='c:\a_bak1.bak'
> > > > > go
> > > > > use a
> > > > > go
> > > > > /* insert a time into t2 that can be read as a stopat time accross
> > batches
> > > > > */
> > > > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > > > go
> > > > > /* delay one minute */
> > > > > waitfor delay '00:01:00'
> > > > > go
> > > > > /* delete the row from t1. This represents the mistake we want to
> > recover
> > > > > before.. */
> > > > > delete from t1
> > > > > go
> > > > > use master
> > > > > go
> > > > > /* your secondary, post mistake full backup */
> > > > > backup database a to disk='c:\a_bak2.bak'
> > > > > go
> > > > > /* your log backup */
> > > > > backup log a to disk='c:\a_bak3.bak'
> > > > > go
> > > > > /* we restore to point in time captured in t2. I'm only using t2
so we
> > could
> > > > > record that point in time accross batches for the purposes of this
> > example
> > > > > script */
> > > > > use a
> > > > > declare @.restoretime varchar(26)
> > > > > select @.restoretime = min(restoretime) from t2
> > > > > use master
> > > > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => > @.restoretime
> > > > > go
> > > > > use a
> > > > > go
> > > > > /* prove that the deleted row from t1 is restored */
> > > > > select * from t1
> > > > > go
> > > > > /* clean up */
> > > > > use master
> > > > > go
> > > > > drop database a
> > > > > go
> > > > >
> > > > > HTH
> > > > >
> > > > > Regards,
> > > > > Greg Linwood
> > > > > SQL Server MVP
> > > > >
> > > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > > > Hello,
> > > > > >
> > > > > > We want to do a restore to a point of time.
> > > > > >
> > > > > > With recovery mode on full.
> > > > > > Can one do a restore to a point of time before the last full
backup
> > > > > > with a transaction log backup made after the full backup ?
> > > > > >
> > > > > > Sequence.
> > > > > >
> > > > > > (full recovery mode, SQL-server 7. I think.).
> > > > > > a. Somewere in the past a full backup is made.
> > > > > > b. An error is made.
> > > > > > c. A full backup is made.
> > > > > > d. A transaction log backup is made.
> > > > > >
> > > > > > We want to restore to a point in time just before
> > > > > > the error (b.) is made. Is this possible ?
> > > > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > > > can not find the anwsers there).
> > > > > >
> > > > > > Ben Brugman.
> > > > > >
> > > > > >
Tuesday, February 14, 2012
backup
I am not a sql server DBA, so please be gentle.
To se tthe scene. We are using hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transac
tion log backups (backup
of changes). When you restore, you do the most recent db backup and then the
subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL
Server so that SQL
server considers this a db backup. Because this will allow you to do log bac
kups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups wi
th above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably suppor
ted as a "VDI device"
(SQL Server terminology).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that dis
k
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can ge
t
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and trans
action log backups (backup
> of changes). When you restore, you do the most recent db backup and then t
he subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to S
QL Server so that SQL
> server considers this a db backup. Because this will allow you to do log b
ackups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups
with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supp
orted as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>
>|||You have to read the documentation with your SAN for how to do restore. Or p
erhaps someone who have
used those products might jump in here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...[vbcol=seagreen]
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support),
in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear
in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would ge
t
> me back to a point in time?
> "Tibor Karaszi" wrote:
>
To se tthe scene. We are using hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transac
tion log backups (backup
of changes). When you restore, you do the most recent db backup and then the
subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL
Server so that SQL
server considers this a db backup. Because this will allow you to do log bac
kups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups wi
th above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably suppor
ted as a "VDI device"
(SQL Server terminology).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that dis
k
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can ge
t
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and trans
action log backups (backup
> of changes). When you restore, you do the most recent db backup and then t
he subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to S
QL Server so that SQL
> server considers this a db backup. Because this will allow you to do log b
ackups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups
with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supp
orted as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>
>|||You have to read the documentation with your SAN for how to do restore. Or p
erhaps someone who have
used those products might jump in here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...[vbcol=seagreen]
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support),
in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear
in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would ge
t
> me back to a point in time?
> "Tibor Karaszi" wrote:
>
backup
Hello,
OK. At this point, I'm almost convinced that something went wrong
during the install process for SQL 2000. No matter what I try, I can
not get SQl to listen on port 1433. I clearly see that named pipes and
tcp is set for the network setting. Port 1433 is the default.
I did a backup of all my databases (to c:\dbbackup) and am going to do
an un-install, reboot, then re-install SQL server.
Is there anything I'm forgetting?Nothing in the errorlog? Of some other service is using 1433, then you'd get
an error in the
errorlog...
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Won Lee" <noemail@.nospam.com> wrote in message news:ujvVGKm6DHA.2576@.TK2MSFTNGP11.phx.gbl...[QUOT
E]
> Hello,
> OK. At this point, I'm almost convinced that something went wrong
> during the install process for SQL 2000. No matter what I try, I can
> not get SQl to listen on port 1433. I clearly see that named pipes and
> tcp is set for the network setting. Port 1433 is the default.
> I did a backup of all my databases (to c:\dbbackup) and am going to do
> an un-install, reboot, then re-install SQL server.
> Is there anything I'm forgetting?
>[/QUOTE]|||Tibor,
Thanks for trying to help me out.
THis is the full cut and paste of my error log.
2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
(Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
Corporation.
2004-02-03 09:28:32.10 server All rights reserved.
2004-02-03 09:28:32.10 server Server Process ID is 1688.
2004-02-03 09:28:32.10 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-02-03 09:28:32.15 server SQL Server is starting at priority
class 'normal'(2 CPUs detected).
2004-02-03 09:28:32.95 server SQL Server configured for thread mode
processing.
2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
Lock Blocks, [5000] Lock Owner Blocks.
2004-02-03 09:28:33.01 server Attempting to initialize Distributed
Transaction Coordinator.
2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
Named Pipes.
2004-02-03 09:28:38.12 server SQL Server is ready for client connections
2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
2004-02-03 09:28:41.39 spid2 Recovery complete.
2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
I should see SQL server listening on Shared Memory, TCP, Named Pipes.
I'm missing the TCP in my error log though.
netstat -an shows that I'm not listening on port 1443.
C:\Documents and Settings\wlee>netstat -an
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1034 *:*
UDP 0.0.0.0:1435 *:*
UDP 0.0.0.0:4500 *:*
UDP 127.0.0.1:123 *:*
UDP 192.168.1.202:123 *:*
UDP 192.168.1.202:137 *:*
UDP 192.168.1.202:138 *:*
C:\Documents and Settings\wlee>
I tried to change it to other ports via the Network Utility but I still
can't see the TCP in the error log.
Tibor Karaszi wrote:
2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
...
2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check
the registry so there
isn't anything strange in the registry entry where this configuration is hel
d (compare against a
healthy SQL Server).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Won Lee" <noemail@.nospam.com> wrote in message news:uFrXSkm6DHA.696@.tk2msftngp13.phx.gbl...[QUOTE
]
> Tibor,
> Thanks for trying to help me out.
> THis is the full cut and paste of my error log.
>
> 2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
> (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
> Corporation.
> 2004-02-03 09:28:32.10 server All rights reserved.
> 2004-02-03 09:28:32.10 server Server Process ID is 1688.
> 2004-02-03 09:28:32.10 server Logging SQL Server messages in file
> 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
> 2004-02-03 09:28:32.15 server SQL Server is starting at priority
> class 'normal'(2 CPUs detected).
> 2004-02-03 09:28:32.95 server SQL Server configured for thread mode
> processing.
> 2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
> Lock Blocks, [5000] Lock Owner Blocks.
> 2004-02-03 09:28:33.01 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
> 2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
> 2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
> 2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
> 2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
> 2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
> 2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
> 2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
> 2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
> Named Pipes.
> 2004-02-03 09:28:38.12 server SQL Server is ready for client connection
s
> 2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
> 2004-02-03 09:28:41.39 spid2 Recovery complete.
> 2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
> I should see SQL server listening on Shared Memory, TCP, Named Pipes.
> I'm missing the TCP in my error log though.
> netstat -an shows that I'm not listening on port 1443.
> C:\Documents and Settings\wlee>netstat -an
> Active Connections
> Proto Local Address Foreign Address State
> TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
> TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
> TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
> TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
> UDP 0.0.0.0:445 *:*
> UDP 0.0.0.0:500 *:*
> UDP 0.0.0.0:1034 *:*
> UDP 0.0.0.0:1435 *:*
> UDP 0.0.0.0:4500 *:*
> UDP 127.0.0.1:123 *:*
> UDP 192.168.1.202:123 *:*
> UDP 192.168.1.202:137 *:*
> UDP 192.168.1.202:138 *:*
> C:\Documents and Settings\wlee>
>
> I tried to change it to other ports via the Network Utility but I still
> can't see the TCP in the error log.
>
>
>
> Tibor Karaszi wrote:
>
>[/QUOTE]|||Tibor,
What entry should I search for?
Tibor Karaszi wrote:
Does this make you think that something is wrong with the SQL server and
not the OS?|||You have to do some investigation yourself and compare a healthy and the pro
blematic machine. Here's
a place to start:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketNet
Lib
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Won Lee" <noemail@.nospam.com> wrote in message news:e3BY9tm6DHA.2568@.TK2MSFTNGP10.phx.gbl...[QUOT
E]
> Tibor,
> What entry should I search for?
>
> Tibor Karaszi wrote:
>
there
OK. At this point, I'm almost convinced that something went wrong
during the install process for SQL 2000. No matter what I try, I can
not get SQl to listen on port 1433. I clearly see that named pipes and
tcp is set for the network setting. Port 1433 is the default.
I did a backup of all my databases (to c:\dbbackup) and am going to do
an un-install, reboot, then re-install SQL server.
Is there anything I'm forgetting?Nothing in the errorlog? Of some other service is using 1433, then you'd get
an error in the
errorlog...
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Won Lee" <noemail@.nospam.com> wrote in message news:ujvVGKm6DHA.2576@.TK2MSFTNGP11.phx.gbl...[QUOT
E]
> Hello,
> OK. At this point, I'm almost convinced that something went wrong
> during the install process for SQL 2000. No matter what I try, I can
> not get SQl to listen on port 1433. I clearly see that named pipes and
> tcp is set for the network setting. Port 1433 is the default.
> I did a backup of all my databases (to c:\dbbackup) and am going to do
> an un-install, reboot, then re-install SQL server.
> Is there anything I'm forgetting?
>[/QUOTE]|||Tibor,
Thanks for trying to help me out.
THis is the full cut and paste of my error log.
2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
(Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
Corporation.
2004-02-03 09:28:32.10 server All rights reserved.
2004-02-03 09:28:32.10 server Server Process ID is 1688.
2004-02-03 09:28:32.10 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-02-03 09:28:32.15 server SQL Server is starting at priority
class 'normal'(2 CPUs detected).
2004-02-03 09:28:32.95 server SQL Server configured for thread mode
processing.
2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
Lock Blocks, [5000] Lock Owner Blocks.
2004-02-03 09:28:33.01 server Attempting to initialize Distributed
Transaction Coordinator.
2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
Named Pipes.
2004-02-03 09:28:38.12 server SQL Server is ready for client connections
2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
2004-02-03 09:28:41.39 spid2 Recovery complete.
2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
I should see SQL server listening on Shared Memory, TCP, Named Pipes.
I'm missing the TCP in my error log though.
netstat -an shows that I'm not listening on port 1443.
C:\Documents and Settings\wlee>netstat -an
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1034 *:*
UDP 0.0.0.0:1435 *:*
UDP 0.0.0.0:4500 *:*
UDP 127.0.0.1:123 *:*
UDP 192.168.1.202:123 *:*
UDP 192.168.1.202:137 *:*
UDP 192.168.1.202:138 *:*
C:\Documents and Settings\wlee>
I tried to change it to other ports via the Network Utility but I still
can't see the TCP in the error log.
Tibor Karaszi wrote:
quote:|||Yes, my sever has entries such as:
> Nothing in the errorlog? Of some other service is using 1433, then you'd g
et an error in the
> errorlog...
>
2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
...
2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check
the registry so there
isn't anything strange in the registry entry where this configuration is hel
d (compare against a
healthy SQL Server).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Won Lee" <noemail@.nospam.com> wrote in message news:uFrXSkm6DHA.696@.tk2msftngp13.phx.gbl...[QUOTE
]
> Tibor,
> Thanks for trying to help me out.
> THis is the full cut and paste of my error log.
>
> 2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
> (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
> Corporation.
> 2004-02-03 09:28:32.10 server All rights reserved.
> 2004-02-03 09:28:32.10 server Server Process ID is 1688.
> 2004-02-03 09:28:32.10 server Logging SQL Server messages in file
> 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
> 2004-02-03 09:28:32.15 server SQL Server is starting at priority
> class 'normal'(2 CPUs detected).
> 2004-02-03 09:28:32.95 server SQL Server configured for thread mode
> processing.
> 2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
> Lock Blocks, [5000] Lock Owner Blocks.
> 2004-02-03 09:28:33.01 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
> 2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
> 2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
> 2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
> 2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
> 2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
> 2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
> 2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
> 2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
> Named Pipes.
> 2004-02-03 09:28:38.12 server SQL Server is ready for client connection
s
> 2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
> 2004-02-03 09:28:41.39 spid2 Recovery complete.
> 2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
> I should see SQL server listening on Shared Memory, TCP, Named Pipes.
> I'm missing the TCP in my error log though.
> netstat -an shows that I'm not listening on port 1443.
> C:\Documents and Settings\wlee>netstat -an
> Active Connections
> Proto Local Address Foreign Address State
> TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
> TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
> TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
> TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
> UDP 0.0.0.0:445 *:*
> UDP 0.0.0.0:500 *:*
> UDP 0.0.0.0:1034 *:*
> UDP 0.0.0.0:1435 *:*
> UDP 0.0.0.0:4500 *:*
> UDP 127.0.0.1:123 *:*
> UDP 192.168.1.202:123 *:*
> UDP 192.168.1.202:137 *:*
> UDP 192.168.1.202:138 *:*
> C:\Documents and Settings\wlee>
>
> I tried to change it to other ports via the Network Utility but I still
> can't see the TCP in the error log.
>
>
>
> Tibor Karaszi wrote:
>
>[/QUOTE]|||Tibor,
What entry should I search for?
Tibor Karaszi wrote:
quote:|||Tibor,
> Yes, my sever has entries such as:
> 2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
> ...
> 2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
> Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd che
ck the registry so there
> isn't anything strange in the registry entry where this configuration is h
eld (compare against a
> healthy SQL Server).
>
Does this make you think that something is wrong with the SQL server and
not the OS?|||You have to do some investigation yourself and compare a healthy and the pro
blematic machine. Here's
a place to start:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketNet
Lib
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Won Lee" <noemail@.nospam.com> wrote in message news:e3BY9tm6DHA.2568@.TK2MSFTNGP10.phx.gbl...[QUOT
E]
> Tibor,
> What entry should I search for?
>
> Tibor Karaszi wrote:
>
there
quote:
>
backup
I am not a sql server DBA, so please be gentle.
To se tthe scene. We are using Hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.
Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
server considers this a db backup. Because this will allow you to do log backups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups with above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
(SQL Server terminology).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using Hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can get
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.
|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
> of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
> server considers this a db backup. Because this will allow you to do log backups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>
>
|||You have to read the documentation with your SAN for how to do restore. Or perhaps someone who have
used those products might jump in here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...[vbcol=seagreen]
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support), in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
> me back to a point in time?
> "Tibor Karaszi" wrote:
To se tthe scene. We are using Hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.
Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
server considers this a db backup. Because this will allow you to do log backups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups with above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
(SQL Server terminology).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using Hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can get
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.
|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
> of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
> server considers this a db backup. Because this will allow you to do log backups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>
>
|||You have to read the documentation with your SAN for how to do restore. Or perhaps someone who have
used those products might jump in here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...[vbcol=seagreen]
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support), in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
> me back to a point in time?
> "Tibor Karaszi" wrote:
Backup
I want to backup a database every day, but i want that at some point (ex.
when there are 5 bakupsets) it replaces the previous backups, one at a time.
Can you help me'You may want to take a look at the BACKUP DATABASE command help file.
With the RETAINDAYS option, you will be able to do just that.
A stored procedure running daily as a job will then be all you need.|||A lot of us prefer to use a different file for each backup. You don't want
to take a chance of one mistake or problem corrupting the whole set of
backups you have. Tara Duggan has a script written for backups. You can
set the number of retain days, so it sounds exactly like what you're looking
for. Here is the link:
http://weblogs.sqlteam.com/tarad/ar...08/04/1876.aspx
"Marfig" <marfig@.gmail.com> wrote in message
news:1110558250.287530.240470@.f14g2000cwb.googlegroups.com...
> You may want to take a look at the BACKUP DATABASE command help file.
> With the RETAINDAYS option, you will be able to do just that.
> A stored procedure running daily as a job will then be all you need.
>
when there are 5 bakupsets) it replaces the previous backups, one at a time.
Can you help me'You may want to take a look at the BACKUP DATABASE command help file.
With the RETAINDAYS option, you will be able to do just that.
A stored procedure running daily as a job will then be all you need.|||A lot of us prefer to use a different file for each backup. You don't want
to take a chance of one mistake or problem corrupting the whole set of
backups you have. Tara Duggan has a script written for backups. You can
set the number of retain days, so it sounds exactly like what you're looking
for. Here is the link:
http://weblogs.sqlteam.com/tarad/ar...08/04/1876.aspx
"Marfig" <marfig@.gmail.com> wrote in message
news:1110558250.287530.240470@.f14g2000cwb.googlegroups.com...
> You may want to take a look at the BACKUP DATABASE command help file.
> With the RETAINDAYS option, you will be able to do just that.
> A stored procedure running daily as a job will then be all you need.
>
Sunday, February 12, 2012
backup
Hello,
OK. At this point, I'm almost convinced that something went wrong
during the install process for SQL 2000. No matter what I try, I can
not get SQl to listen on port 1433. I clearly see that named pipes and
tcp is set for the network setting. Port 1433 is the default.
I did a backup of all my databases (to c:\dbbackup) and am going to do
an un-install, reboot, then re-install SQL server.
Is there anything I'm forgetting?Nothing in the errorlog? Of some other service is using 1433, then you'd get an error in the
errorlog...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Won Lee" <noemail@.nospam.com> wrote in message news:ujvVGKm6DHA.2576@.TK2MSFTNGP11.phx.gbl...
> Hello,
> OK. At this point, I'm almost convinced that something went wrong
> during the install process for SQL 2000. No matter what I try, I can
> not get SQl to listen on port 1433. I clearly see that named pipes and
> tcp is set for the network setting. Port 1433 is the default.
> I did a backup of all my databases (to c:\dbbackup) and am going to do
> an un-install, reboot, then re-install SQL server.
> Is there anything I'm forgetting?
>|||Tibor,
Thanks for trying to help me out.
THis is the full cut and paste of my error log.
2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
(Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
Corporation.
2004-02-03 09:28:32.10 server All rights reserved.
2004-02-03 09:28:32.10 server Server Process ID is 1688.
2004-02-03 09:28:32.10 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-02-03 09:28:32.15 server SQL Server is starting at priority
class 'normal'(2 CPUs detected).
2004-02-03 09:28:32.95 server SQL Server configured for thread mode
processing.
2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
Lock Blocks, [5000] Lock Owner Blocks.
2004-02-03 09:28:33.01 server Attempting to initialize Distributed
Transaction Coordinator.
2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
Named Pipes.
2004-02-03 09:28:38.12 server SQL Server is ready for client connections
2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
2004-02-03 09:28:41.39 spid2 Recovery complete.
2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
I should see SQL server listening on Shared Memory, TCP, Named Pipes.
I'm missing the TCP in my error log though.
netstat -an shows that I'm not listening on port 1443.
C:\Documents and Settings\wlee>netstat -an
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1034 *:*
UDP 0.0.0.0:1435 *:*
UDP 0.0.0.0:4500 *:*
UDP 127.0.0.1:123 *:*
UDP 192.168.1.202:123 *:*
UDP 192.168.1.202:137 *:*
UDP 192.168.1.202:138 *:*
C:\Documents and Settings\wlee>
I tried to change it to other ports via the Network Utility but I still
can't see the TCP in the error log.
Tibor Karaszi wrote:
> Nothing in the errorlog? Of some other service is using 1433, then you'd get an error in the
> errorlog...
>|||Yes, my sever has entries such as:
2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
...
2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check the registry so there
isn't anything strange in the registry entry where this configuration is held (compare against a
healthy SQL Server).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Won Lee" <noemail@.nospam.com> wrote in message news:uFrXSkm6DHA.696@.tk2msftngp13.phx.gbl...
> Tibor,
> Thanks for trying to help me out.
> THis is the full cut and paste of my error log.
>
> 2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
> (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
> Corporation.
> 2004-02-03 09:28:32.10 server All rights reserved.
> 2004-02-03 09:28:32.10 server Server Process ID is 1688.
> 2004-02-03 09:28:32.10 server Logging SQL Server messages in file
> 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
> 2004-02-03 09:28:32.15 server SQL Server is starting at priority
> class 'normal'(2 CPUs detected).
> 2004-02-03 09:28:32.95 server SQL Server configured for thread mode
> processing.
> 2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
> Lock Blocks, [5000] Lock Owner Blocks.
> 2004-02-03 09:28:33.01 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
> 2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
> 2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
> 2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
> 2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
> 2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
> 2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
> 2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
> 2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
> Named Pipes.
> 2004-02-03 09:28:38.12 server SQL Server is ready for client connections
> 2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
> 2004-02-03 09:28:41.39 spid2 Recovery complete.
> 2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
> I should see SQL server listening on Shared Memory, TCP, Named Pipes.
> I'm missing the TCP in my error log though.
> netstat -an shows that I'm not listening on port 1443.
> C:\Documents and Settings\wlee>netstat -an
> Active Connections
> Proto Local Address Foreign Address State
> TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
> TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
> TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
> TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
> UDP 0.0.0.0:445 *:*
> UDP 0.0.0.0:500 *:*
> UDP 0.0.0.0:1034 *:*
> UDP 0.0.0.0:1435 *:*
> UDP 0.0.0.0:4500 *:*
> UDP 127.0.0.1:123 *:*
> UDP 192.168.1.202:123 *:*
> UDP 192.168.1.202:137 *:*
> UDP 192.168.1.202:138 *:*
> C:\Documents and Settings\wlee>
>
> I tried to change it to other ports via the Network Utility but I still
> can't see the TCP in the error log.
>
>
>
> Tibor Karaszi wrote:
> > Nothing in the errorlog? Of some other service is using 1433, then you'd get an error in the
> > errorlog...
> >
>|||Tibor,
What entry should I search for?
Tibor Karaszi wrote:
> Yes, my sever has entries such as:
> 2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
> ...
> 2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
> Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check the registry so there
> isn't anything strange in the registry entry where this configuration is held (compare against a
> healthy SQL Server).
>|||Tibor,
Does this make you think that something is wrong with the SQL server and
not the OS?|||You have to do some investigation yourself and compare a healthy and the problematic machine. Here's
a place to start:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Won Lee" <noemail@.nospam.com> wrote in message news:e3BY9tm6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> Tibor,
> What entry should I search for?
>
> Tibor Karaszi wrote:
> > Yes, my sever has entries such as:
> >
> > 2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
> > ...
> > 2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
> >
> > Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check the registry so
there
> > isn't anything strange in the registry entry where this configuration is held (compare against a
> > healthy SQL Server).
> >
>
OK. At this point, I'm almost convinced that something went wrong
during the install process for SQL 2000. No matter what I try, I can
not get SQl to listen on port 1433. I clearly see that named pipes and
tcp is set for the network setting. Port 1433 is the default.
I did a backup of all my databases (to c:\dbbackup) and am going to do
an un-install, reboot, then re-install SQL server.
Is there anything I'm forgetting?Nothing in the errorlog? Of some other service is using 1433, then you'd get an error in the
errorlog...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Won Lee" <noemail@.nospam.com> wrote in message news:ujvVGKm6DHA.2576@.TK2MSFTNGP11.phx.gbl...
> Hello,
> OK. At this point, I'm almost convinced that something went wrong
> during the install process for SQL 2000. No matter what I try, I can
> not get SQl to listen on port 1433. I clearly see that named pipes and
> tcp is set for the network setting. Port 1433 is the default.
> I did a backup of all my databases (to c:\dbbackup) and am going to do
> an un-install, reboot, then re-install SQL server.
> Is there anything I'm forgetting?
>|||Tibor,
Thanks for trying to help me out.
THis is the full cut and paste of my error log.
2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
(Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
Corporation.
2004-02-03 09:28:32.10 server All rights reserved.
2004-02-03 09:28:32.10 server Server Process ID is 1688.
2004-02-03 09:28:32.10 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-02-03 09:28:32.15 server SQL Server is starting at priority
class 'normal'(2 CPUs detected).
2004-02-03 09:28:32.95 server SQL Server configured for thread mode
processing.
2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
Lock Blocks, [5000] Lock Owner Blocks.
2004-02-03 09:28:33.01 server Attempting to initialize Distributed
Transaction Coordinator.
2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
Named Pipes.
2004-02-03 09:28:38.12 server SQL Server is ready for client connections
2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
2004-02-03 09:28:41.39 spid2 Recovery complete.
2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
I should see SQL server listening on Shared Memory, TCP, Named Pipes.
I'm missing the TCP in my error log though.
netstat -an shows that I'm not listening on port 1443.
C:\Documents and Settings\wlee>netstat -an
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1034 *:*
UDP 0.0.0.0:1435 *:*
UDP 0.0.0.0:4500 *:*
UDP 127.0.0.1:123 *:*
UDP 192.168.1.202:123 *:*
UDP 192.168.1.202:137 *:*
UDP 192.168.1.202:138 *:*
C:\Documents and Settings\wlee>
I tried to change it to other ports via the Network Utility but I still
can't see the TCP in the error log.
Tibor Karaszi wrote:
> Nothing in the errorlog? Of some other service is using 1433, then you'd get an error in the
> errorlog...
>|||Yes, my sever has entries such as:
2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
...
2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check the registry so there
isn't anything strange in the registry entry where this configuration is held (compare against a
healthy SQL Server).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Won Lee" <noemail@.nospam.com> wrote in message news:uFrXSkm6DHA.696@.tk2msftngp13.phx.gbl...
> Tibor,
> Thanks for trying to help me out.
> THis is the full cut and paste of my error log.
>
> 2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
> (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
> Corporation.
> 2004-02-03 09:28:32.10 server All rights reserved.
> 2004-02-03 09:28:32.10 server Server Process ID is 1688.
> 2004-02-03 09:28:32.10 server Logging SQL Server messages in file
> 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
> 2004-02-03 09:28:32.15 server SQL Server is starting at priority
> class 'normal'(2 CPUs detected).
> 2004-02-03 09:28:32.95 server SQL Server configured for thread mode
> processing.
> 2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
> Lock Blocks, [5000] Lock Owner Blocks.
> 2004-02-03 09:28:33.01 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
> 2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
> 2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
> 2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
> 2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
> 2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
> 2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
> 2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
> 2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
> Named Pipes.
> 2004-02-03 09:28:38.12 server SQL Server is ready for client connections
> 2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
> 2004-02-03 09:28:41.39 spid2 Recovery complete.
> 2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
> I should see SQL server listening on Shared Memory, TCP, Named Pipes.
> I'm missing the TCP in my error log though.
> netstat -an shows that I'm not listening on port 1443.
> C:\Documents and Settings\wlee>netstat -an
> Active Connections
> Proto Local Address Foreign Address State
> TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
> TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
> TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
> TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
> UDP 0.0.0.0:445 *:*
> UDP 0.0.0.0:500 *:*
> UDP 0.0.0.0:1034 *:*
> UDP 0.0.0.0:1435 *:*
> UDP 0.0.0.0:4500 *:*
> UDP 127.0.0.1:123 *:*
> UDP 192.168.1.202:123 *:*
> UDP 192.168.1.202:137 *:*
> UDP 192.168.1.202:138 *:*
> C:\Documents and Settings\wlee>
>
> I tried to change it to other ports via the Network Utility but I still
> can't see the TCP in the error log.
>
>
>
> Tibor Karaszi wrote:
> > Nothing in the errorlog? Of some other service is using 1433, then you'd get an error in the
> > errorlog...
> >
>|||Tibor,
What entry should I search for?
Tibor Karaszi wrote:
> Yes, my sever has entries such as:
> 2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
> ...
> 2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
> Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check the registry so there
> isn't anything strange in the registry entry where this configuration is held (compare against a
> healthy SQL Server).
>|||Tibor,
Does this make you think that something is wrong with the SQL server and
not the OS?|||You have to do some investigation yourself and compare a healthy and the problematic machine. Here's
a place to start:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Won Lee" <noemail@.nospam.com> wrote in message news:e3BY9tm6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> Tibor,
> What entry should I search for?
>
> Tibor Karaszi wrote:
> > Yes, my sever has entries such as:
> >
> > 2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
> > ...
> > 2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
> >
> > Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check the registry so
there
> > isn't anything strange in the registry entry where this configuration is held (compare against a
> > healthy SQL Server).
> >
>
backup
I am not a sql server DBA, so please be gentle.
To se tthe scene. We are using Hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
server considers this a db backup. Because this will allow you to do log backups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups with above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
(SQL Server terminology).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using Hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can get
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
> of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
> server considers this a db backup. Because this will allow you to do log backups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
> >I am not a sql server DBA, so please be gentle.
> >
> > To se tthe scene. We are using Hardware plication , and point in time copy
> > technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> > Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> > to a backup server, and then start the SQL DB up.
> >
> > Bearing this in mind is it possible to roll forward/backup using the logs,
> > post a restore proceedure? Is it possible to get back to a point in time
> > during the day when the DB may of corrupted, or lost data? I know I can get
> > back to the point when the Business Copy was taken, but unsure of the
> > flexability within SQL 2000. I know this is possible on Oracle DB's.
>
>|||You have to read the documentation with your SAN for how to do restore. Or perhaps someone who have
used those products might jump in here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support), in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
> me back to a point in time?
> "Tibor Karaszi" wrote:
>> Disregarding the SAN for a moment:
>> In SQL Server you basically have a database backup (full backup) and transaction log backups
>> (backup
>> of changes). When you restore, you do the most recent db backup and then the subsequent log
>> backups-
>> For a log backup, you have a STOPAT parameter to stop at desired time.
>> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
>> server considers this a db backup. Because this will allow you to do log backups. So you can now
>> use
>> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
>> STOPAT.
>> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
>> (SQL Server terminology).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "visaya" <visaya@.discussions.microsoft.com> wrote in message
>> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>> >I am not a sql server DBA, so please be gentle.
>> >
>> > To se tthe scene. We are using Hardware plication , and point in time copy
>> > technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
>> > Backup). For backup we shut the DB down, split the BC Disk, mount that disk
>> > to a backup server, and then start the SQL DB up.
>> >
>> > Bearing this in mind is it possible to roll forward/backup using the logs,
>> > post a restore proceedure? Is it possible to get back to a point in time
>> > during the day when the DB may of corrupted, or lost data? I know I can get
>> > back to the point when the Business Copy was taken, but unsure of the
>> > flexability within SQL 2000. I know this is possible on Oracle DB's.
>>
To se tthe scene. We are using Hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
server considers this a db backup. Because this will allow you to do log backups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups with above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
(SQL Server terminology).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using Hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can get
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
> of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
> server considers this a db backup. Because this will allow you to do log backups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
> >I am not a sql server DBA, so please be gentle.
> >
> > To se tthe scene. We are using Hardware plication , and point in time copy
> > technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> > Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> > to a backup server, and then start the SQL DB up.
> >
> > Bearing this in mind is it possible to roll forward/backup using the logs,
> > post a restore proceedure? Is it possible to get back to a point in time
> > during the day when the DB may of corrupted, or lost data? I know I can get
> > back to the point when the Business Copy was taken, but unsure of the
> > flexability within SQL 2000. I know this is possible on Oracle DB's.
>
>|||You have to read the documentation with your SAN for how to do restore. Or perhaps someone who have
used those products might jump in here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support), in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
> me back to a point in time?
> "Tibor Karaszi" wrote:
>> Disregarding the SAN for a moment:
>> In SQL Server you basically have a database backup (full backup) and transaction log backups
>> (backup
>> of changes). When you restore, you do the most recent db backup and then the subsequent log
>> backups-
>> For a log backup, you have a STOPAT parameter to stop at desired time.
>> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
>> server considers this a db backup. Because this will allow you to do log backups. So you can now
>> use
>> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
>> STOPAT.
>> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
>> (SQL Server terminology).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "visaya" <visaya@.discussions.microsoft.com> wrote in message
>> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>> >I am not a sql server DBA, so please be gentle.
>> >
>> > To se tthe scene. We are using Hardware plication , and point in time copy
>> > technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
>> > Backup). For backup we shut the DB down, split the BC Disk, mount that disk
>> > to a backup server, and then start the SQL DB up.
>> >
>> > Bearing this in mind is it possible to roll forward/backup using the logs,
>> > post a restore proceedure? Is it possible to get back to a point in time
>> > during the day when the DB may of corrupted, or lost data? I know I can get
>> > back to the point when the Business Copy was taken, but unsure of the
>> > flexability within SQL 2000. I know this is possible on Oracle DB's.
>>
Subscribe to:
Posts (Atom)