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

This is the 1st time I have had to restore to an alternate location.
I followed the threads from other posts (and BOL) but am having no luck.
I have a prod database called DATA. I need to restore an older backup to
retrieve info BUT to another database data_tl (it is not created).
In QA, I ran this against Master but...
RESTORE FILELISTONLY
FROM DISK = 'F:\Microsoft SQL Server\MSSQL\backup\data011504.bak'
RESTORE DATABASE data_t1
FROM DISK = 'F:\Microsoft SQL Server\MSSQL\backup\data011504.bak'
WITH MOVE 'data_t1_Data' To 'F:\Microsoft SQL
Server\MSSQL\data\data_1_data.mdf',
MOVE 'data_t1_Log' To 'F:\Microsoft SQL Server\MSSQL\data\data_1_log.ldf'
with the following error msg.
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 3
Logical file 'data_t1_Data' is not part of database 'data_t1'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Obviously I am missing something in my syntax but can't see it for the life
of me.
TIA,
Susan Simpsonwhat returned when you run this line:
RESTORE FILELISTONLY FROM DISK = 'F:\Microsoft SQL
Server\MSSQL\backup\data011504.bak'
?
"Susan Simpson" <ssimpson@.infospectruminc.com> wrote in message
news:ujGtgva8DHA.2560@.TK2MSFTNGP09.phx.gbl...
> This is the 1st time I have had to restore to an alternate location.
> I followed the threads from other posts (and BOL) but am having no luck.
> I have a prod database called DATA. I need to restore an older backup to
> retrieve info BUT to another database data_tl (it is not created).
> In QA, I ran this against Master but...
> RESTORE FILELISTONLY
> FROM DISK = 'F:\Microsoft SQL Server\MSSQL\backup\data011504.bak'
> RESTORE DATABASE data_t1
> FROM DISK = 'F:\Microsoft SQL Server\MSSQL\backup\data011504.bak'
> WITH MOVE 'data_t1_Data' To 'F:\Microsoft SQL
> Server\MSSQL\data\data_1_data.mdf',
> MOVE 'data_t1_Log' To 'F:\Microsoft SQL
Server\MSSQL\data\data_1_log.ldf'
> with the following error msg.
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 3
> Logical file 'data_t1_Data' is not part of database 'data_t1'. Use RESTORE
> FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Obviously I am missing something in my syntax but can't see it for the
life
> of me.
> TIA,
> Susan Simpson
>
>

restore database to new database

This is the 1st time I have had to restore to an alternate location.
I followed the threads from other posts (and BOL) but am having no luck.
I have a prod database called DATA. I need to restore an older backup to
retrieve info BUT to another database data_tl (it is not created).
In QA, I ran this against Master but...
RESTORE FILELISTONLY
FROM DISK = 'F:\Microsoft SQL Server\MSSQL\backup\data011504.bak'
RESTORE DATABASE data_t1
FROM DISK = 'F:\Microsoft SQL Server\MSSQL\backup\data011504.bak'
WITH MOVE 'data_t1_Data' To 'F:\Microsoft SQL
Server\MSSQL\data\data_1_data.mdf',
MOVE 'data_t1_Log' To 'F:\Microsoft SQL Server\MSSQL\data\data_1_log.ldf'
with the following error msg.
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 3
Logical file 'data_t1_Data' is not part of database 'data_t1'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Obviously I am missing something in my syntax but can't see it for the life
of me.
TIA,
Susan Simpsonwhat returned when you run this line:
RESTORE FILELISTONLY FROM DISK = 'F:\Microsoft SQL
Server\MSSQL\backup\data011504.bak'
?
"Susan Simpson" <ssimpson@.infospectruminc.com> wrote in message
news:ujGtgva8DHA.2560@.TK2MSFTNGP09.phx.gbl...
> This is the 1st time I have had to restore to an alternate location.
> I followed the threads from other posts (and BOL) but am having no luck.
> I have a prod database called DATA. I need to restore an older backup to
> retrieve info BUT to another database data_tl (it is not created).
> In QA, I ran this against Master but...
> RESTORE FILELISTONLY
> FROM DISK = 'F:\Microsoft SQL Server\MSSQL\backup\data011504.bak'
> RESTORE DATABASE data_t1
> FROM DISK = 'F:\Microsoft SQL Server\MSSQL\backup\data011504.bak'
> WITH MOVE 'data_t1_Data' To 'F:\Microsoft SQL
> Server\MSSQL\data\data_1_data.mdf',
> MOVE 'data_t1_Log' To 'F:\Microsoft SQL
Server\MSSQL\data\data_1_log.ldf'
> with the following error msg.
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 3
> Logical file 'data_t1_Data' is not part of database 'data_t1'. Use RESTORE
> FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Obviously I am missing something in my syntax but can't see it for the
life
> of me.
> TIA,
> Susan Simpson
>
>

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

Hello, everyone:
Every time to restore database, I copy the database backup file to local hard drive. Is is possible to restore database over network? Is other tools need to install? Thanks
YTZIt should work, but I wouldn't recommend it. if the network goes down in the middle of the restore, you would probably have to start over.

it's more reliable to copy the backup locally if you have enough space.

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo

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