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