Restore DB to Point in Time

I have a group of folks that are Peoplesoft Developers, of which the
back-end is a 65 GB SQL Server database. They run test procedures and
processes against this database, but then it is a pretty common practice for
them to ask the DBAs to restore to an old backup so they can run more tests
after modifying their code.
Is there an easier way to do this? Restoring a 65 GB database is rather
daunting and time consuming. Wouldn't there be a way to not commit a
timeframe of activity (transaction log, for example), ultimately returning
the database back to a previous point in time?
I hope this makes sense, and that someone has come across this scenario in
the past.
Thanks for any advice,
AllenIf you are running SQL Server 2005 this is a perfect situation for
database snapshots. This effectively creates a read-only copy of the
database, and you can revert the database back to the state at the
time of the snapshot.
Roy Harvey
Beacon Falls, CT
On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
<allen.mcguire@.gmail.com.invalid> wrote:

>I have a group of folks that are Peoplesoft Developers, of which the
>back-end is a 65 GB SQL Server database. They run test procedures and
>processes against this database, but then it is a pretty common practice fo
r
>them to ask the DBAs to restore to an old backup so they can run more tests
>after modifying their code.
>Is there an easier way to do this? Restoring a 65 GB database is rather
>daunting and time consuming. Wouldn't there be a way to not commit a
>timeframe of activity (transaction log, for example), ultimately returning
>the database back to a previous point in time?
>I hope this makes sense, and that someone has come across this scenario in
>the past.
>Thanks for any advice,
>Allen
>|||Unfortunately in this instance I'm using SQL 2000. Any ideas for SQL 2000?
What about getting copies of the databases at the point in time they
continually want to restore to, and then attach to them when I need to
revert back to the older version (after deleting the modified ones of
course)?
Still thinking about this, but there has to be a more efficient way to
revert back to a previous state without having to restore a 65 GB
database...
Would it be
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:9qgsb2td3nc7csfk6rogastm6vohds7iq4@.
4ax.com...[vbcol=seagreen]
> If you are running SQL Server 2005 this is a perfect situation for
> database snapshots. This effectively creates a read-only copy of the
> database, and you can revert the database back to the state at the
> time of the snapshot.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
> <allen.mcguire@.gmail.com.invalid> wrote:
>|||Yes you can use sp_detach copy a file to another location or rename it in
the same folder and then sp_attach one file for them to play with.
If they need to revert just sp_attach the renamed file.
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:eDh9NH0qGHA.5008@.TK2MSFTNGP05.phx.gbl...
> Unfortunately in this instance I'm using SQL 2000. Any ideas for SQL
> 2000?
> What about getting copies of the databases at the point in time they
> continually want to restore to, and then attach to them when I need to
> revert back to the older version (after deleting the modified ones of
> course)?
> Still thinking about this, but there has to be a more efficient way to
> revert back to a previous state without having to restore a 65 GB
> database...
> Would it be
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:9qgsb2td3nc7csfk6rogastm6vohds7iq4@.
4ax.com...
>|||Awesome - thanks for confirming my suspicions ;-)
That will work great considering this continual restore process takes about
4-6 hours.
"alex sadykov" <alexsadykov@.gmail.com> wrote in message
news:%23JJKkE1qGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Yes you can use sp_detach copy a file to another location or rename it in
> the same folder and then sp_attach one file for them to play with.
> If they need to revert just sp_attach the renamed file.
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:eDh9NH0qGHA.5008@.TK2MSFTNGP05.phx.gbl...
>|||A McGuire wrote:
> Awesome - thanks for confirming my suspicions ;-)
> That will work great considering this continual restore process takes abou
t
> 4-6 hours.
> "alex sadykov" <alexsadykov@.gmail.com> wrote in message
> news:%23JJKkE1qGHA.4684@.TK2MSFTNGP05.phx.gbl...
>
Just keep in mind that it also takes a while to copy a 65GB database
file and a log file in order to use sp_attach_db. I'm also wondering why
it takes 4-6 hours to restore a 65 GB database. Our biggest database
which is a 110 GB database file and a 65 GB log file can be restored in
less than 1 hour. If it's because you have a slow server/disk system,
the copy/sp_attach_db solution will take quite a while as well.
Often I find that it's not that much faster to copy the files and attach
them compared to doing a RESTORE. The part that takes time is for the OS
to create the files on disk and that's more or less the same with both
solutions.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||On my way home last night that notion came to me - copying the 65 GB file
took a few hours. I'm not saving a whole lot of time... unfortunately.
I don't deal at all with the hardware - in fact I've never seen the
servers - so I'm not quite sure what they are.
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:efL%23In8qGHA.4508@.TK2MSFTNGP04.phx.gbl...
>A McGuire wrote:
> Just keep in mind that it also takes a while to copy a 65GB database file
> and a log file in order to use sp_attach_db. I'm also wondering why it
> takes 4-6 hours to restore a 65 GB database. Our biggest database which is
> a 110 GB database file and a 65 GB log file can be restored in less than
> 1 hour. If it's because you have a slow server/disk system, the
> copy/sp_attach_db solution will take quite a while as well.
> Often I find that it's not that much faster to copy the files and attach
> them compared to doing a RESTORE. The part that takes time is for the OS
> to create the files on disk and that's more or less the same with both
> solutions.
>
> --
> Regards
> Steen Schlter Persson
> Databaseadministrator / Systemadministrator|||On Thu, 20 Jul 2006 11:41:41 -0500, "A McGuire"
<allen.mcguire@.gmail.com.invalid> wrote:

>On my way home last night that notion came to me - copying the 65 GB file
>took a few hours. I'm not saving a whole lot of time... unfortunately.
To use file copy you need a master "saved" copy on hand, plus the copy
currently attached and in use. If you have space for a third copy you
might be able to save time. Don't wait for them to ask for the
database to be restored. Copy the master version using different file
names, so that when they do ask you just have to detach the active
copy and attach to the one already in place. Then delete the files
you just detached and replace them with fresh copies from the master
set right away.
Which, as a read it, is probably confusing.
Roy|||I did get a copy of the restored .MDF (detatch, copy, attach), so I'm good
there. Before they only had the .BAK files from early May, for example, so
my only option this time around was a restore. Only problem is that the
.MDF is in a neighboring folder, not the same one, which means a multi-hour
copy. I suppose I could rename it as you suggest and put it in the same
folder. That will save me the copy time.
I assume you suggest the third copy so when I go to 'restore' next time
(drop current, attach to .MDF) that I have an additional free .MDF of the
snapshot handy that won't be in use. The current would get dropped, one of
the copies would be put to use (attached to), and I would just make another
copy of the 3rd - this would be the procedure each time.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:pngvb2psrs8hg44hh15vlsjl3hqfsn2ilr@.
4ax.com...
> On Thu, 20 Jul 2006 11:41:41 -0500, "A McGuire"
> <allen.mcguire@.gmail.com.invalid> wrote:
>
> To use file copy you need a master "saved" copy on hand, plus the copy
> currently attached and in use. If you have space for a third copy you
> might be able to save time. Don't wait for them to ask for the
> database to be restored. Copy the master version using different file
> names, so that when they do ask you just have to detach the active
> copy and attach to the one already in place. Then delete the files
> you just detached and replace them with fresh copies from the master
> set right away.
> Which, as a read it, is probably confusing.
> Roysql

Restore DB to Point in Time

I have a group of folks that are Peoplesoft Developers, of which the
back-end is a 65 GB SQL Server database. They run test procedures and
processes against this database, but then it is a pretty common practice for
them to ask the DBAs to restore to an old backup so they can run more tests
after modifying their code.
Is there an easier way to do this? Restoring a 65 GB database is rather
daunting and time consuming. Wouldn't there be a way to not commit a
timeframe of activity (transaction log, for example), ultimately returning
the database back to a previous point in time?
I hope this makes sense, and that someone has come across this scenario in
the past.
Thanks for any advice,
AllenIf you are running SQL Server 2005 this is a perfect situation for
database snapshots. This effectively creates a read-only copy of the
database, and you can revert the database back to the state at the
time of the snapshot.
Roy Harvey
Beacon Falls, CT
On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
<allen.mcguire@.gmail.com.invalid> wrote:
>I have a group of folks that are Peoplesoft Developers, of which the
>back-end is a 65 GB SQL Server database. They run test procedures and
>processes against this database, but then it is a pretty common practice for
>them to ask the DBAs to restore to an old backup so they can run more tests
>after modifying their code.
>Is there an easier way to do this? Restoring a 65 GB database is rather
>daunting and time consuming. Wouldn't there be a way to not commit a
>timeframe of activity (transaction log, for example), ultimately returning
>the database back to a previous point in time?
>I hope this makes sense, and that someone has come across this scenario in
>the past.
>Thanks for any advice,
>Allen
>|||Unfortunately in this instance I'm using SQL 2000. Any ideas for SQL 2000?
What about getting copies of the databases at the point in time they
continually want to restore to, and then attach to them when I need to
revert back to the older version (after deleting the modified ones of
course)?
Still thinking about this, but there has to be a more efficient way to
revert back to a previous state without having to restore a 65 GB
database...
Would it be
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:9qgsb2td3nc7csfk6rogastm6vohds7iq4@.4ax.com...
> If you are running SQL Server 2005 this is a perfect situation for
> database snapshots. This effectively creates a read-only copy of the
> database, and you can revert the database back to the state at the
> time of the snapshot.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
> <allen.mcguire@.gmail.com.invalid> wrote:
>>I have a group of folks that are Peoplesoft Developers, of which the
>>back-end is a 65 GB SQL Server database. They run test procedures and
>>processes against this database, but then it is a pretty common practice
>>for
>>them to ask the DBAs to restore to an old backup so they can run more
>>tests
>>after modifying their code.
>>Is there an easier way to do this? Restoring a 65 GB database is rather
>>daunting and time consuming. Wouldn't there be a way to not commit a
>>timeframe of activity (transaction log, for example), ultimately returning
>>the database back to a previous point in time?
>>I hope this makes sense, and that someone has come across this scenario in
>>the past.
>>Thanks for any advice,
>>Allen|||Yes you can use sp_detach copy a file to another location or rename it in
the same folder and then sp_attach one file for them to play with.
If they need to revert just sp_attach the renamed file.
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:eDh9NH0qGHA.5008@.TK2MSFTNGP05.phx.gbl...
> Unfortunately in this instance I'm using SQL 2000. Any ideas for SQL
> 2000?
> What about getting copies of the databases at the point in time they
> continually want to restore to, and then attach to them when I need to
> revert back to the older version (after deleting the modified ones of
> course)?
> Still thinking about this, but there has to be a more efficient way to
> revert back to a previous state without having to restore a 65 GB
> database...
> Would it be
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:9qgsb2td3nc7csfk6rogastm6vohds7iq4@.4ax.com...
>> If you are running SQL Server 2005 this is a perfect situation for
>> database snapshots. This effectively creates a read-only copy of the
>> database, and you can revert the database back to the state at the
>> time of the snapshot.
>> Roy Harvey
>> Beacon Falls, CT
>> On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
>> <allen.mcguire@.gmail.com.invalid> wrote:
>>I have a group of folks that are Peoplesoft Developers, of which the
>>back-end is a 65 GB SQL Server database. They run test procedures and
>>processes against this database, but then it is a pretty common practice
>>for
>>them to ask the DBAs to restore to an old backup so they can run more
>>tests
>>after modifying their code.
>>Is there an easier way to do this? Restoring a 65 GB database is rather
>>daunting and time consuming. Wouldn't there be a way to not commit a
>>timeframe of activity (transaction log, for example), ultimately
>>returning
>>the database back to a previous point in time?
>>I hope this makes sense, and that someone has come across this scenario
>>in
>>the past.
>>Thanks for any advice,
>>Allen
>|||Awesome - thanks for confirming my suspicions ;-)
That will work great considering this continual restore process takes about
4-6 hours.
"alex sadykov" <alexsadykov@.gmail.com> wrote in message
news:%23JJKkE1qGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Yes you can use sp_detach copy a file to another location or rename it in
> the same folder and then sp_attach one file for them to play with.
> If they need to revert just sp_attach the renamed file.
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:eDh9NH0qGHA.5008@.TK2MSFTNGP05.phx.gbl...
>> Unfortunately in this instance I'm using SQL 2000. Any ideas for SQL
>> 2000?
>> What about getting copies of the databases at the point in time they
>> continually want to restore to, and then attach to them when I need to
>> revert back to the older version (after deleting the modified ones of
>> course)?
>> Still thinking about this, but there has to be a more efficient way to
>> revert back to a previous state without having to restore a 65 GB
>> database...
>> Would it be
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:9qgsb2td3nc7csfk6rogastm6vohds7iq4@.4ax.com...
>> If you are running SQL Server 2005 this is a perfect situation for
>> database snapshots. This effectively creates a read-only copy of the
>> database, and you can revert the database back to the state at the
>> time of the snapshot.
>> Roy Harvey
>> Beacon Falls, CT
>> On Wed, 19 Jul 2006 08:58:37 -0500, "A McGuire"
>> <allen.mcguire@.gmail.com.invalid> wrote:
>>I have a group of folks that are Peoplesoft Developers, of which the
>>back-end is a 65 GB SQL Server database. They run test procedures and
>>processes against this database, but then it is a pretty common practice
>>for
>>them to ask the DBAs to restore to an old backup so they can run more
>>tests
>>after modifying their code.
>>Is there an easier way to do this? Restoring a 65 GB database is rather
>>daunting and time consuming. Wouldn't there be a way to not commit a
>>timeframe of activity (transaction log, for example), ultimately
>>returning
>>the database back to a previous point in time?
>>I hope this makes sense, and that someone has come across this scenario
>>in
>>the past.
>>Thanks for any advice,
>>Allen
>>
>|||A McGuire wrote:
> Awesome - thanks for confirming my suspicions ;-)
> That will work great considering this continual restore process takes about
> 4-6 hours.
> "alex sadykov" <alexsadykov@.gmail.com> wrote in message
> news:%23JJKkE1qGHA.4684@.TK2MSFTNGP05.phx.gbl...
>
Just keep in mind that it also takes a while to copy a 65GB database
file and a log file in order to use sp_attach_db. I'm also wondering why
it takes 4-6 hours to restore a 65 GB database. Our biggest database
which is a 110 GB database file and a 65 GB log file can be restored in
less than 1½ hour. If it's because you have a slow server/disk system,
the copy/sp_attach_db solution will take quite a while as well.
Often I find that it's not that much faster to copy the files and attach
them compared to doing a RESTORE. The part that takes time is for the OS
to create the files on disk and that's more or less the same with both
solutions.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||On my way home last night that notion came to me - copying the 65 GB file
took a few hours. I'm not saving a whole lot of time... unfortunately.
I don't deal at all with the hardware - in fact I've never seen the
servers - so I'm not quite sure what they are.
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:efL%23In8qGHA.4508@.TK2MSFTNGP04.phx.gbl...
>A McGuire wrote:
>> Awesome - thanks for confirming my suspicions ;-)
>> That will work great considering this continual restore process takes
>> about 4-6 hours.
>> "alex sadykov" <alexsadykov@.gmail.com> wrote in message
>> news:%23JJKkE1qGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Just keep in mind that it also takes a while to copy a 65GB database file
> and a log file in order to use sp_attach_db. I'm also wondering why it
> takes 4-6 hours to restore a 65 GB database. Our biggest database which is
> a 110 GB database file and a 65 GB log file can be restored in less than
> 1½ hour. If it's because you have a slow server/disk system, the
> copy/sp_attach_db solution will take quite a while as well.
> Often I find that it's not that much faster to copy the files and attach
> them compared to doing a RESTORE. The part that takes time is for the OS
> to create the files on disk and that's more or less the same with both
> solutions.
>
> --
> Regards
> Steen Schlüter Persson
> Databaseadministrator / Systemadministrator|||On Thu, 20 Jul 2006 11:41:41 -0500, "A McGuire"
<allen.mcguire@.gmail.com.invalid> wrote:
>On my way home last night that notion came to me - copying the 65 GB file
>took a few hours. I'm not saving a whole lot of time... unfortunately.
To use file copy you need a master "saved" copy on hand, plus the copy
currently attached and in use. If you have space for a third copy you
might be able to save time. Don't wait for them to ask for the
database to be restored. Copy the master version using different file
names, so that when they do ask you just have to detach the active
copy and attach to the one already in place. Then delete the files
you just detached and replace them with fresh copies from the master
set right away.
Which, as a read it, is probably confusing.
Roy|||I did get a copy of the restored .MDF (detatch, copy, attach), so I'm good
there. Before they only had the .BAK files from early May, for example, so
my only option this time around was a restore. Only problem is that the
.MDF is in a neighboring folder, not the same one, which means a multi-hour
copy. I suppose I could rename it as you suggest and put it in the same
folder. That will save me the copy time.
I assume you suggest the third copy so when I go to 'restore' next time
(drop current, attach to .MDF) that I have an additional free .MDF of the
snapshot handy that won't be in use. The current would get dropped, one of
the copies would be put to use (attached to), and I would just make another
copy of the 3rd - this would be the procedure each time.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:pngvb2psrs8hg44hh15vlsjl3hqfsn2ilr@.4ax.com...
> On Thu, 20 Jul 2006 11:41:41 -0500, "A McGuire"
> <allen.mcguire@.gmail.com.invalid> wrote:
>>On my way home last night that notion came to me - copying the 65 GB file
>>took a few hours. I'm not saving a whole lot of time... unfortunately.
> To use file copy you need a master "saved" copy on hand, plus the copy
> currently attached and in use. If you have space for a third copy you
> might be able to save time. Don't wait for them to ask for the
> database to be restored. Copy the master version using different file
> names, so that when they do ask you just have to detach the active
> copy and attach to the one already in place. Then delete the files
> you just detached and replace them with fresh copies from the master
> set right away.
> Which, as a read it, is probably confusing.
> Roy

restore db from network drive

All of our database servers are doing backups and saving the .bak files out
on a network share. With SQL 2005, how can I point to that share and restore
the database in SQL 2005 environment?
When I do 'restore database --> From Device --> File; when I click the 'ADD;
button, I only see my local drives on the computer. How can I map to my
network share and point to my restore file on the network?Use UNC names: \\servername\sharename\foldername\file.ext. The
account doing the backups - which is to say the account under which
SQL Server is running - needs appropriate rights to the share.
Roy Harvey
Beacon Falls, CT
On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
wrote:

>All of our database servers are doing backups and saving the .bak files out
>on a network share. With SQL 2005, how can I point to that share and restor
e
>the database in SQL 2005 environment?
>When I do 'restore database --> From Device --> File; when I click the 'ADD
;
>button, I only see my local drives on the computer. How can I map to my
>network share and point to my restore file on the network?
>|||I've tried the UNC path and I get 'network path not found' message
Beacon falls, ct, your in my nekc of the woods.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:km4b63p0q6hco968a82ngn955tgse4eg47@.
4ax.com...[vbcol=seagreen]
> Use UNC names: \\servername\sharename\foldername\file.ext. The
> account doing the backups - which is to say the account under which
> SQL Server is running - needs appropriate rights to the share.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>|||Hello,
In the management studio; go to query window and use RESTORE DATABASE
command. Make sure that you start sql server service using
a domain account which has access to remote share.
RESTORE DATABASE <DBNAME> FROM DISK='\\Servername\Share\filename.bak' with
stats=5
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:%23xEIwJ5pHHA.1244@.TK2MSFTNGP04.phx.gbl...
> I've tried the UNC path and I get 'network path not found' message
> Beacon falls, ct, your in my nekc of the woods.
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:km4b63p0q6hco968a82ngn955tgse4eg47@.
4ax.com...
>|||On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
wrote:

>I've tried the UNC path and I get 'network path not found' message
Perhaps it is an access rights problem. A partial test can be done
using xp_cmdshell:
EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'

>Beacon falls, ct, your in my nekc of the woods.
Cool! If you ever need more hands-on help remember I'm around!
Hari's advice to use a query window is excellent, by the way.
Roy Harvey
Beacon Falls, CT|||Ok, I think I got it, but now I'm getting this message:
'the media set has 2 media families but only 1 are provided. All members
must be provided.'
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:89eb631lpobifjd281tdpma4q57sct745h@.
4ax.com...
> On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>
> Perhaps it is an access rights problem. A partial test can be done
> using xp_cmdshell:
> EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>
> Cool! If you ever need more hands-on help remember I'm around!
> Hari's advice to use a query window is excellent, by the way.
> Roy Harvey
> Beacon Falls, CT|||Hello,
Did you strip the backup into multiple files? Can you execute RESTORE
LABELONLY (see books online) and see how many files are associated with the
backup.
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Ok, I think I got it, but now I'm getting this message:
> 'the media set has 2 media families but only 1 are provided. All members
> must be provided.'
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:89eb631lpobifjd281tdpma4q57sct745h@.
4ax.com...
>|||No, one file. and now I can't even backup the SQL 2000 database version to
do a restore from.
This is driving me nuts.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Ov2b29pHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Did you strip the backup into multiple files? Can you execute RESTORE
> LABELONLY (see books online) and see how many files are associated with
> the backup.
> Thanks
> Hari
> "Mike" <Mike@.community.nospam> wrote in message
> news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
>sql

restore db from network drive

All of our database servers are doing backups and saving the .bak files out
on a network share. With SQL 2005, how can I point to that share and restore
the database in SQL 2005 environment?
When I do 'restore database --> From Device --> File; when I click the 'ADD;
button, I only see my local drives on the computer. How can I map to my
network share and point to my restore file on the network?Use UNC names: \\servername\sharename\foldername\file.ext. The
account doing the backups - which is to say the account under which
SQL Server is running - needs appropriate rights to the share.
Roy Harvey
Beacon Falls, CT
On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
wrote:
>All of our database servers are doing backups and saving the .bak files out
>on a network share. With SQL 2005, how can I point to that share and restore
>the database in SQL 2005 environment?
>When I do 'restore database --> From Device --> File; when I click the 'ADD;
>button, I only see my local drives on the computer. How can I map to my
>network share and point to my restore file on the network?
>|||I've tried the UNC path and I get 'network path not found' message
Beacon falls, ct, your in my nekc of the woods.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:km4b63p0q6hco968a82ngn955tgse4eg47@.4ax.com...
> Use UNC names: \\servername\sharename\foldername\file.ext. The
> account doing the backups - which is to say the account under which
> SQL Server is running - needs appropriate rights to the share.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>>All of our database servers are doing backups and saving the .bak files
>>out
>>on a network share. With SQL 2005, how can I point to that share and
>>restore
>>the database in SQL 2005 environment?
>>When I do 'restore database --> From Device --> File; when I click the
>>'ADD;
>>button, I only see my local drives on the computer. How can I map to my
>>network share and point to my restore file on the network?
>>|||Hello,
In the management studio; go to query window and use RESTORE DATABASE
command. Make sure that you start sql server service using
a domain account which has access to remote share.
RESTORE DATABASE <DBNAME> FROM DISK='\\Servername\Share\filename.bak' with
stats=5
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:%23xEIwJ5pHHA.1244@.TK2MSFTNGP04.phx.gbl...
> I've tried the UNC path and I get 'network path not found' message
> Beacon falls, ct, your in my nekc of the woods.
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:km4b63p0q6hco968a82ngn955tgse4eg47@.4ax.com...
>> Use UNC names: \\servername\sharename\foldername\file.ext. The
>> account doing the backups - which is to say the account under which
>> SQL Server is running - needs appropriate rights to the share.
>> Roy Harvey
>> Beacon Falls, CT
>> On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
>> wrote:
>>All of our database servers are doing backups and saving the .bak files
>>out
>>on a network share. With SQL 2005, how can I point to that share and
>>restore
>>the database in SQL 2005 environment?
>>When I do 'restore database --> From Device --> File; when I click the
>>'ADD;
>>button, I only see my local drives on the computer. How can I map to my
>>network share and point to my restore file on the network?
>>
>|||On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
wrote:
>I've tried the UNC path and I get 'network path not found' message
Perhaps it is an access rights problem. A partial test can be done
using xp_cmdshell:
EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>Beacon falls, ct, your in my nekc of the woods.
Cool! If you ever need more hands-on help remember I'm around!
Hari's advice to use a query window is excellent, by the way.
Roy Harvey
Beacon Falls, CT|||Ok, I think I got it, but now I'm getting this message:
'the media set has 2 media families but only 1 are provided. All members
must be provided.'
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:89eb631lpobifjd281tdpma4q57sct745h@.4ax.com...
> On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>>I've tried the UNC path and I get 'network path not found' message
> Perhaps it is an access rights problem. A partial test can be done
> using xp_cmdshell:
> EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>>Beacon falls, ct, your in my nekc of the woods.
> Cool! If you ever need more hands-on help remember I'm around!
> Hari's advice to use a query window is excellent, by the way.
> Roy Harvey
> Beacon Falls, CT|||Hello,
Did you strip the backup into multiple files? Can you execute RESTORE
LABELONLY (see books online) and see how many files are associated with the
backup.
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Ok, I think I got it, but now I'm getting this message:
> 'the media set has 2 media families but only 1 are provided. All members
> must be provided.'
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:89eb631lpobifjd281tdpma4q57sct745h@.4ax.com...
>> On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
>> wrote:
>>I've tried the UNC path and I get 'network path not found' message
>> Perhaps it is an access rights problem. A partial test can be done
>> using xp_cmdshell:
>> EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>>Beacon falls, ct, your in my nekc of the woods.
>> Cool! If you ever need more hands-on help remember I'm around!
>> Hari's advice to use a query window is excellent, by the way.
>> Roy Harvey
>> Beacon Falls, CT
>|||No, one file. and now I can't even backup the SQL 2000 database version to
do a restore from.
This is driving me nuts.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Ov2b29pHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Did you strip the backup into multiple files? Can you execute RESTORE
> LABELONLY (see books online) and see how many files are associated with
> the backup.
> Thanks
> Hari
> "Mike" <Mike@.community.nospam> wrote in message
> news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
>> Ok, I think I got it, but now I'm getting this message:
>> 'the media set has 2 media families but only 1 are provided. All members
>> must be provided.'
>>
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:89eb631lpobifjd281tdpma4q57sct745h@.4ax.com...
>> On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
>> wrote:
>>I've tried the UNC path and I get 'network path not found' message
>> Perhaps it is an access rights problem. A partial test can be done
>> using xp_cmdshell:
>> EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>>Beacon falls, ct, your in my nekc of the woods.
>> Cool! If you ever need more hands-on help remember I'm around!
>> Hari's advice to use a query window is excellent, by the way.
>> Roy Harvey
>> Beacon Falls, CT
>>
>

Restore db and logs from device to a point in time

When restoring a database and log files from a device the point in time
option disappears. How do I restore from a device to a point in time? Is
there an easy way to restore multiple logs from a device? It seems like
doing them one at a time is the only option and it's quite a pain.I recommend that you familiar yourself with the RESTORE command so you can
do this from Query Analyzer.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"David Morrison" <me@.nospam.com> wrote in message
news:uof2WquuDHA.1596@.TK2MSFTNGP10.phx.gbl...
> When restoring a database and log files from a device the point in time
> option disappears. How do I restore from a device to a point in time? Is
> there an easy way to restore multiple logs from a device? It seems like
> doing them one at a time is the only option and it's quite a pain.
>

Restore database to a certain point of time.

I will make it simpler to look...

I have DB1 - as backup for day 1
LOg1 as backup of logs

T1 T2 T3 T4 T5 ...some transaction on day 2

Now i backup again
DB2
Log2

I want to restore the database till the point of transaction T3 say. I
know the time or i assume a certain time.
Is this possible ....i tried several options but hand in between for
some reason or the other. How can i achieve my solution. Is there some
extra parameter i will require or what...i am wondering now that it is
not at all possible. Please help.
RVG
If possible guys can you please mail me the sloution on
raj_chins@.rediffmail.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Rajesh Garg <raj_chins@.rediffmail.com> wrote in message news:<3f4c2fb2$0$62083$75868355@.news.frii.net>...
> I will make it simpler to look...
> I have DB1 - as backup for day 1
> LOg1 as backup of logs
> T1 T2 T3 T4 T5 ...some transaction on day 2
> Now i backup again
> DB2
> Log2
> I want to restore the database till the point of transaction T3 say. I
> know the time or i assume a certain time.
> Is this possible ....i tried several options but hand in between for
> some reason or the other. How can i achieve my solution. Is there some
> extra parameter i will require or what...i am wondering now that it is
> not at all possible. Please help.
> RVG
> If possible guys can you please mail me the sloution on
> raj_chins@.rediffmail.com
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Your description is a little confusing for two reasons - first, you
don't say what time each event is happening at, and second, it's not
clear if you are backing up your log regularly or only once each day.
My guess from your description is that you are doing this:

Day 1

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Day 2

09h00 - COMMIT txn1
10h00 - COMMIT txn2
11h00 - COMMIT txn3
12h00 - COMMIT txn4

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Let's say it's now 23h30 on day 2, and you want to recover your
database to 11h00 on day 2. If my assumptions are correct, then you
cannot recover to a point in time because you have no transaction log
backups during Day 2. To recover to a point in time, you first recover
a full backup, then recover your log backups, one after another. But
in your scenario you have no series of transaction backups. At the end
of Day 2 you can recover to Day 1 22h00 (full backup), Day 1 22h30
(full backup + log backup), Day 2 22h00 (full backup), or Day 2 22h30
(full backup + log backup).

To recover to a point in time, you need at least one transaction log
backup after 11h00:

Day 2

09h00 - COMMIT txn1
10h00 - COMMIT txn2
11h00 - COMMIT txn3
12h00 - COMMIT txn4

13h00 - BACKUP LOG...

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Now you can recover to 11h00: restore day 1 22h00 full + day 1 22h30
log + day 2 13h00 log, recovering only transactions before 11h00.

I may have misunderstood what your situation is - if so, then please
post a clear description of exactly what you are doing (in a similar
way to my example above). In the meantime, I suggest you review the
Books Online backup/restore section, in particular "Using Recovery
Models" and "Backup and Restore Operations".

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0308270354.789e8248@.posting.google.c om...
> Your description is a little confusing for two reasons - first, you
> don't say what time each event is happening at, and second, it's not
> clear if you are backing up your log regularly or only once each day.
> My guess from your description is that you are doing this:
> Day 1
> 22h00 - BACKUP DATABASE...
> 22h30 - BACKUP LOG...
> Day 2
> 09h00 - COMMIT txn1
> 10h00 - COMMIT txn2
> 11h00 - COMMIT txn3
> 12h00 - COMMIT txn4
> 22h00 - BACKUP DATABASE...
> 22h30 - BACKUP LOG...
> Let's say it's now 23h30 on day 2, and you want to recover your
> database to 11h00 on day 2. If my assumptions are correct, then you
> cannot recover to a point in time because you have no transaction log
> backups during Day 2. To recover to a point in time, you first recover
> a full backup, then recover your log backups, one after another. But
> in your scenario you have no series of transaction backups. At the end
> of Day 2 you can recover to Day 1 22h00 (full backup), Day 1 22h30
> (full backup + log backup), Day 2 22h00 (full backup), or Day 2 22h30
> (full backup + log backup).
> To recover to a point in time, you need at least one transaction log
> backup after 11h00:

This is not true (see my reply to Rajesh), a full backup does not truncate
the transaction log, so in your example it is possible to restore from the
first full backup and then use the two transaction logs, completely ignoring
the second full backup.

Ian.

Restore Database FROM MDF/LDF, then apply Transaction Log From POint of Failure? Is it pOs

If I have a database backup from sunday, and a failure occurs monday... Can the backup .mdf and .ldf files be attached, and the backup log after the point of failure be applied to them?

The problem I am having is it looks like you can only restore from a .bak file, and then apply the log at the point of failure. IT doesn't look like you can restore the .ldf/.mdf files, and then apply the backup log from the point of failure.

Can someone please help? I'm in desparate need of fixing this !

Thanks,
dp

You can restore from backup and apply your log files to recover up to failure unless it is a transactional database you can restore almost everything but you are also supposed to keep a DR(disaster recovery) copy of your database. Try the link below. Hope this helps.

http://www.sql-server-performance.com/log_explorer_spotlight.asp

sql

Restore at the point of failure

I m doing a test restore from my live to test db( to test restore at the
point of failure)
Below is my script :
-- Back up the currently active transaction log of Live DB
BACKUP LOG LIVEDB
TO disk = 'C:\Log2.TRN'
WITH NO_TRUNCATE
GO
-- Restore the database full backup from Live DB to Test DB
RESTORE DATABASE TESTDB
FROM DISK='C:\8A.M'
WITH NORECOVERY
GO
-- Restore the first transaction log backup of LIVEDB TO TESTDB
RESTORE LOG TESTDB
FROM DISK ='C:\LOG1.TRN'
WITH NORECOVERY
GO
-- Restore the final transaction log backup.(the last transaction log backup
)
RESTORE LOG TESTDB
FROM DISK ='C:\Log2.TRN'
WITH RECOVERY
GO
Somehow, I got the error as below:
Processed 1954 pages for database 'IMS', file 'Production60_Log' on file 1.
BACKUP LOG successfully processed 1954 pages in 0.432 seconds (37.037 MB/sec
).
Processed 88984 pages for database 'IERP60', file 'Production60_Data' on
file 1.
Processed 1 pages for database 'IERP60', file 'Production60_Log' on file 1.
RESTORE DATABASE successfully processed 88985 pages in 57.809 seconds
(12.609 MB/sec).
Server: Msg 4326, Level 16, State 1, Line 3
The log in this backup set terminates at LSN 42947000000004100001, which is
too early to apply to the database. A more recent log backup that includes
LSN 42947000000146100001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Processed 1954 pages for database 'IERP60', file 'Production60_Log' on file
1.
RESTORE LOG successfully processed 1954 pages in 2.127 seconds (7.522 MB/sec
).
I checked the transaction log that I applied is actually the first
transaction log backup of the day itself.Please note that my transaction log
backup date is 7 a.m and the full backup is 8a.m
Please advise
ThanksHi
If you use RESTORE HEADERONLY you will see the LSNs and therefore use the
correct file. You may find this discussion useful:
http://tinyurl.com/4kqkf
John
"sql fren" wrote:

> I m doing a test restore from my live to test db( to test restore at the
> point of failure)
> Below is my script :
> -- Back up the currently active transaction log of Live DB
> BACKUP LOG LIVEDB
> TO disk = 'C:\Log2.TRN'
> WITH NO_TRUNCATE
> GO
> -- Restore the database full backup from Live DB to Test DB
> RESTORE DATABASE TESTDB
> FROM DISK='C:\8A.M'
> WITH NORECOVERY
> GO
> -- Restore the first transaction log backup of LIVEDB TO TESTDB
> RESTORE LOG TESTDB
> FROM DISK ='C:\LOG1.TRN'
> WITH NORECOVERY
> GO
> -- Restore the final transaction log backup.(the last transaction log back
up)
> RESTORE LOG TESTDB
> FROM DISK ='C:\Log2.TRN'
> WITH RECOVERY
> GO
> Somehow, I got the error as below:
> Processed 1954 pages for database 'IMS', file 'Production60_Log' on file 1
.
> BACKUP LOG successfully processed 1954 pages in 0.432 seconds (37.037 MB/s
ec).
> Processed 88984 pages for database 'IERP60', file 'Production60_Data' on
> file 1.
> Processed 1 pages for database 'IERP60', file 'Production60_Log' on file 1
.
> RESTORE DATABASE successfully processed 88985 pages in 57.809 seconds
> (12.609 MB/sec).
> Server: Msg 4326, Level 16, State 1, Line 3
> The log in this backup set terminates at LSN 42947000000004100001, which i
s
> too early to apply to the database. A more recent log backup that includes
> LSN 42947000000146100001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> Processed 1954 pages for database 'IERP60', file 'Production60_Log' on fil
e 1.
> RESTORE LOG successfully processed 1954 pages in 2.127 seconds (7.522 MB/s
ec).
>
> I checked the transaction log that I applied is actually the first
> transaction log backup of the day itself.Please note that my transaction l
og
> backup date is 7 a.m and the full backup is 8a.m
> Please advise
> Thanks
>|||Sorry, how to determine from the LSNs which db backup is the latest?
"John Bell" wrote:
> Hi
> If you use RESTORE HEADERONLY you will see the LSNs and therefore use the
> correct file. You may find this discussion useful:
> http://tinyurl.com/4kqkf
> John
>
> "sql fren" wrote:
>|||Hi
RESTORE HEADERONLY returns the first and last LSN. In your case you
want the backup where
FirstLSN <= 42947000000146100001 <= LastLSN.
John
sql fren wrote:
> Sorry, how to determine from the LSNs which db backup is the latest?
>
> "John Bell" wrote:
>
use the
at the
log backup)
on file 1.
(37.037 MB/sec).
'Production60_Data' on
on file 1.
seconds
42947000000004100001, which is
includes
'Production60_Log' on file 1.
(7.522 MB/sec).
first
transaction log|||It seems that you are trying to apply log backup that made before the full
backup .
C:\LOG1.TRN contain log backup that made before the full backup .(NOT OK)
C:\LOG2.TRN contain log backup that made after the full backup . (OK)
You can see all backup operation in msdb..backupset table.
select *
from msdb..backupset
where datbaseName = 'db_name'
order by backup_set_id
TLV
"sql fren" <sqlfren@.discussions.microsoft.com> wrote in message
news:7A7D973A-2FEB-4824-B557-07172AD86A39@.microsoft.com...
> Sorry, how to determine from the LSNs which db backup is the latest?
>
> "John Bell" wrote:
>

Restore a filegroup/file to a Point In Time (PIT)

Can i restore a single filegroup (or file) to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore of a specific filegroup. Or is it that everything has to be the same PIT as the PRIMARY.

tia

I'm not positive if you can restore a file to a specific point in time, but the database can do it, look: http://msdn2.microsoft.com/en-us/library/ms186858.aspx

|||Have a look at PIECEMEAL restore in BOoks online 2005 and also KBA http://support.microsoft.com/kb/281122 fyi.

Restore a filegroup/file to a Point In Time (PIT)

Can i restore a single filegroup (or file) to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore of a specific filegroup. Or is it that everything has to be the same PIT as the PRIMARY.

tia

I'm not positive if you can restore a file to a specific point in time, but the database can do it, look: http://msdn2.microsoft.com/en-us/library/ms186858.aspx

|||Have a look at PIECEMEAL restore in BOoks online 2005 and also KBA http://support.microsoft.com/kb/281122 fyi.

Restore a filegroup

Can i restore a single filegroup (or file) to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore of a specific filegroup.

The entire database has to be at the same point in time after a restore (of a specific filegroup).

Thanks

Sherry

|||Thank you. From what i have found out, that is correct. Too bad about that though. I'm sure the product will improve in that area one day.

Restore a filegroup

Can i restore a single filegroup (or file) to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore of a specific filegroup.

The entire database has to be at the same point in time after a restore (of a specific filegroup).

Thanks

Sherry

|||Thank you. From what i have found out, that is correct. Too bad about that though. I'm sure the product will improve in that area one day.

Restore a database to point in time?

Hello All,
Id like to restore a database to point in time in another SQL Server. I hav
e
a database backup from Production.
I created a new database in Development SQL Server. How can I restore a
database to point in time?
Were on SQL Server 2000.
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1http://msdn.microsoft.com/library/d...>
kpc_6pv6.asp
David Portas
SQL Server MVP
--|||Hi,
The POINT-IN-TIME recovery will work only if the source database's Recovery
model is set to "FULL" and you should
have a full database backup + subsequent transaction log backups.
Steps:
1. Perform a transaction log backup of the original database (If you have
not performed one)
2. RESTORE DATABASE TEST1 FROM BBA (Give the correct backup file / device
name) WITH NORECOVERY
3. Restore the subsequent transaction log files in order of backup WITH
NORECOVERY option till the final transaction log file
4. In the the final transaction log restore mention WITH RECOVERY and
STOPAT='date and time'
Thanks
Hari
SQL Server MVP
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:519E7397BD1DE@.droptable.com...
> Hello All,
> I'd like to restore a database to point in time in another SQL Server. I
> have
> a database backup from Production.
> I created a new database in Development SQL Server. How can I restore a
> database to point in time?
> We're on SQL Server 2000.
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1

Restore a database to point in time?

Hello All,
Id like to restore a database to point in time in another SQL Server. I have
a database backup from Production.
I created a new database in Development SQL Server. How can I restore a
database to point in time?
Were on SQL Server 2000.
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
http://msdn.microsoft.com/library/de...ackpc_6pv6.asp
David Portas
SQL Server MVP
|||Hi,
The POINT-IN-TIME recovery will work only if the source database's Recovery
model is set to "FULL" and you should
have a full database backup + subsequent transaction log backups.
Steps:
1. Perform a transaction log backup of the original database (If you have
not performed one)
2. RESTORE DATABASE TEST1 FROM BBA (Give the correct backup file / device
name) WITH NORECOVERY
3. Restore the subsequent transaction log files in order of backup WITH
NORECOVERY option till the final transaction log file
4. In the the final transaction log restore mention WITH RECOVERY and
STOPAT='date and time'
Thanks
Hari
SQL Server MVP
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:519E7397BD1DE@.droptable.com...
> Hello All,
> I'd like to restore a database to point in time in another SQL Server. I
> have
> a database backup from Production.
> I created a new database in Development SQL Server. How can I restore a
> database to point in time?
> We're on SQL Server 2000.
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1

Restore a database to point in time?

Hello All,
I?d like to restore a database to point in time in another SQL Server. I have
a database backup from Production.
I created a new database in Development SQL Server. How can I restore a
database to point in time?
We?re on SQL Server 2000.
Thanks in advance,
Do.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6pv6.asp
--
David Portas
SQL Server MVP
--|||Hi,
The POINT-IN-TIME recovery will work only if the source database's Recovery
model is set to "FULL" and you should
have a full database backup + subsequent transaction log backups.
Steps:
1. Perform a transaction log backup of the original database (If you have
not performed one)
2. RESTORE DATABASE TEST1 FROM BBA (Give the correct backup file / device
name) WITH NORECOVERY
3. Restore the subsequent transaction log files in order of backup WITH
NORECOVERY option till the final transaction log file
4. In the the final transaction log restore mention WITH RECOVERY and
STOPAT='date and time'
Thanks
Hari
SQL Server MVP
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:519E7397BD1DE@.SQLMonster.com...
> Hello All,
> I'd like to restore a database to point in time in another SQL Server. I
> have
> a database backup from Production.
> I created a new database in Development SQL Server. How can I restore a
> database to point in time?
> We're on SQL Server 2000.
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1

restore a database to point in time

Should I need to restore a database to a specific point in
time, assuming full recovery model, are these steps
correct?
1. Back up Transactin Log
2. Restore Last Full backup and any differential backups
before the desired time.
3. Restore Transaction Log to specific point in time.
I am under the assumption that only tranasaction log
backups can be restored to a specific point in time, not
database backups, is this correct?Dan
You are correct that only transaction logs can restore to
a point in time.
If you are using differential backups, you only need to
restore the most recent one.
Your plan should work fine.
Regards
John|||Yes, you can only do point-in-time with transaction log restores.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:0d6d01c39e35$08002b70$a501280a@.phx.gbl...
Should I need to restore a database to a specific point in
time, assuming full recovery model, are these steps
correct?
1. Back up Transactin Log
2. Restore Last Full backup and any differential backups
before the desired time.
3. Restore Transaction Log to specific point in time.
I am under the assumption that only tranasaction log
backups can be restored to a specific point in time, not
database backups, is this correct?

RESTORE

Hi all,
Which tool can I use to RESTORE a database to a point in time 3 day before.
This database has only one FULL daily backup that overwrites the last one.
I´ve tried to use the STOPAT clause, but it didn´t work.
Thanks in advance
Adolfo JaborandyAdolfo Jaborandy wrote:
> Hi all,
> Which tool can I use to RESTORE a database to a point in time 3 day before.
> This database has only one FULL daily backup that overwrites the last one.
> I´ve tried to use the STOPAT clause, but it didn´t work.
> Thanks in advance
> Adolfo Jaborandy
>
Without transaction log backups, you can't... A full database backup is
a snapshot of the COMMITTED activity in the database. In order to
restore to a point in time, you need to start with a full backup that
was done prior to that point in time, and then you "replay" (by
restoring transaction log backups) all of the transactional activity
that has taken place since the full backup, stopping at the specific
point in time that you desire.
Tracy McKibben
MCDBA
http://www.realsqlguy.com