Restore database to new location

On our SQL 2000, we need to restore an old version of a running database,
because the developer needs the old version temporarily.
I plan to:
1) Create new database in Enterprise Manger.
2) Choose restore from new database,
3) point to old version on tape (or in file system).
3) Rename database as part of restore confg.
Being unused to SQL administration, I have to ask you: Is this all there's
to it?
(I'm afraid that the old version will mess things up for the new, running
version.)
Thank you,
/JSLNo need to create the database first, it is created by the restore process. It is probably easier to
use Query Analyzer and the RESTORE command instead of using Enterprise Manager. It is harder to
communicate how to drive a GUI properly compared to sending a RESTORE command to look at. If you
decide to use RESTORE from QA, use the MOVE option to specify the desired location and file names
for the database files (assuming you don't want to overwrite the file that the current database is
using) and also just put in the name you want to new database to have.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:06B12366-416C-48C5-A677-72DF602CEFC1@.microsoft.com...
> On our SQL 2000, we need to restore an old version of a running database,
> because the developer needs the old version temporarily.
> I plan to:
> 1) Create new database in Enterprise Manger.
> 2) Choose restore from new database,
> 3) point to old version on tape (or in file system).
> 3) Rename database as part of restore confg.
> Being unused to SQL administration, I have to ask you: Is this all there's
> to it?
> (I'm afraid that the old version will mess things up for the new, running
> version.)
> Thank you,
> /JSL|||Thank you.
Do you think it would be easier for me as an SQL rookie to use QA than
Enterprise Manager...? -- I attended a course some time ago, but that's all
SQL "experience" I've got.
I'm googling about this, but it seems hard to find an easy to follow guide
on this.
/JSL
"Tibor Karaszi" wrote:
> No need to create the database first, it is created by the restore process. It is probably easier to
> use Query Analyzer and the RESTORE command instead of using Enterprise Manager. It is harder to
> communicate how to drive a GUI properly compared to sending a RESTORE command to look at. If you
> decide to use RESTORE from QA, use the MOVE option to specify the desired location and file names
> for the database files (assuming you don't want to overwrite the file that the current database is
> using) and also just put in the name you want to new database to have.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:06B12366-416C-48C5-A677-72DF602CEFC1@.microsoft.com...
> > On our SQL 2000, we need to restore an old version of a running database,
> > because the developer needs the old version temporarily.
> >
> > I plan to:
> > 1) Create new database in Enterprise Manger.
> > 2) Choose restore from new database,
> > 3) point to old version on tape (or in file system).
> > 3) Rename database as part of restore confg.
> >
> > Being unused to SQL administration, I have to ask you: Is this all there's
> > to it?
> >
> > (I'm afraid that the old version will mess things up for the new, running
> > version.)
> >
> > Thank you,
> > /JSL
>|||The advantage using the command directly is that you can read about it in Books Online and make sure
you understand what the options mean. You can also post the proposed command you are about to
execute here and we can comment on that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:1831BF54-37C8-413D-8501-1D50FDB6307D@.microsoft.com...
> Thank you.
> Do you think it would be easier for me as an SQL rookie to use QA than
> Enterprise Manager...? -- I attended a course some time ago, but that's all
> SQL "experience" I've got.
> I'm googling about this, but it seems hard to find an easy to follow guide
> on this.
> /JSL
> "Tibor Karaszi" wrote:
>> No need to create the database first, it is created by the restore process. It is probably easier
>> to
>> use Query Analyzer and the RESTORE command instead of using Enterprise Manager. It is harder to
>> communicate how to drive a GUI properly compared to sending a RESTORE command to look at. If you
>> decide to use RESTORE from QA, use the MOVE option to specify the desired location and file names
>> for the database files (assuming you don't want to overwrite the file that the current database
>> is
>> using) and also just put in the name you want to new database to have.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "JSL" <JSL@.discussions.microsoft.com> wrote in message
>> news:06B12366-416C-48C5-A677-72DF602CEFC1@.microsoft.com...
>> > On our SQL 2000, we need to restore an old version of a running database,
>> > because the developer needs the old version temporarily.
>> >
>> > I plan to:
>> > 1) Create new database in Enterprise Manger.
>> > 2) Choose restore from new database,
>> > 3) point to old version on tape (or in file system).
>> > 3) Rename database as part of restore confg.
>> >
>> > Being unused to SQL administration, I have to ask you: Is this all there's
>> > to it?
>> >
>> > (I'm afraid that the old version will mess things up for the new, running
>> > version.)
>> >
>> > Thank you,
>> > /JSL
>>|||Thank you. I'll get back to you.
"Tibor Karaszi" wrote:
> The advantage using the command directly is that you can read about it in Books Online and make sure
> you understand what the options mean. You can also post the proposed command you are about to
> execute here and we can comment on that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:1831BF54-37C8-413D-8501-1D50FDB6307D@.microsoft.com...
> > Thank you.
> > Do you think it would be easier for me as an SQL rookie to use QA than
> > Enterprise Manager...? -- I attended a course some time ago, but that's all
> > SQL "experience" I've got.
> > I'm googling about this, but it seems hard to find an easy to follow guide
> > on this.
> >
> > /JSL
> >
> > "Tibor Karaszi" wrote:
> >
> >> No need to create the database first, it is created by the restore process. It is probably easier
> >> to
> >> use Query Analyzer and the RESTORE command instead of using Enterprise Manager. It is harder to
> >> communicate how to drive a GUI properly compared to sending a RESTORE command to look at. If you
> >> decide to use RESTORE from QA, use the MOVE option to specify the desired location and file names
> >> for the database files (assuming you don't want to overwrite the file that the current database
> >> is
> >> using) and also just put in the name you want to new database to have.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> >> news:06B12366-416C-48C5-A677-72DF602CEFC1@.microsoft.com...
> >> > On our SQL 2000, we need to restore an old version of a running database,
> >> > because the developer needs the old version temporarily.
> >> >
> >> > I plan to:
> >> > 1) Create new database in Enterprise Manger.
> >> > 2) Choose restore from new database,
> >> > 3) point to old version on tape (or in file system).
> >> > 3) Rename database as part of restore confg.
> >> >
> >> > Being unused to SQL administration, I have to ask you: Is this all there's
> >> > to it?
> >> >
> >> > (I'm afraid that the old version will mess things up for the new, running
> >> > version.)
> >> >
> >> > Thank you,
> >> > /JSL
> >>
> >>
>|||JSL - here is an example of a RESTORE command issued in QA vs. EM.
Note that there are more parameters available to customize the restore
process, but I think this will do what you're attempting to do...
RESTORE DATABASE testdatabase
FROM DISK = 'c:\databasebackups\testdatabase_dump.bak'
WITH
MOVE 'testdatabase' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdatabase_data.mdf',
MOVE 'testdatabase_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdatabase_log.ldf'
This restores a backup file called "testdatabase_dump.bak", which is
located at c:\databasebackups\ on the server you're restoring on. The
database files will end up at c:\Program Files\Microsoft SQL
Server\MSSQL\Data\ on the new server.
The MOVE is needed in case on the original server the files were stored
at another path, or were named a different name - for instance, if the
database files were at d:\dbfiles\whatever.mdf and
d:\dbfiles\whatever.ldf. This script also assumes that the logical
name of the data file & log file is "testdatabase" and
"testdatabase_log", respectively. You can get the logical names, and
former paths from the backup file without performing a backup by
issuing the following command in QA (I believe)...
RESTORE HEADERONLY FROM 'c:\databasebackups\testdatabase_dump.bak'
JSL wrote:
> Thank you. I'll get back to you.
> "Tibor Karaszi" wrote:
> > The advantage using the command directly is that you can read about it in Books Online and make sure
> > you understand what the options mean. You can also post the proposed command you are about to
> > execute here and we can comment on that.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "JSL" <JSL@.discussions.microsoft.com> wrote in message
> > news:1831BF54-37C8-413D-8501-1D50FDB6307D@.microsoft.com...
> > > Thank you.
> > > Do you think it would be easier for me as an SQL rookie to use QA than
> > > Enterprise Manager...? -- I attended a course some time ago, but that's all
> > > SQL "experience" I've got.
> > > I'm googling about this, but it seems hard to find an easy to follow guide
> > > on this.
> > >
> > > /JSL
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> No need to create the database first, it is created by the restore process. It is probably easier
> > >> to
> > >> use Query Analyzer and the RESTORE command instead of using Enterprise Manager. It is harder to
> > >> communicate how to drive a GUI properly compared to sending a RESTORE command to look at. If you
> > >> decide to use RESTORE from QA, use the MOVE option to specify the desired location and file names
> > >> for the database files (assuming you don't want to overwrite the file that the current database
> > >> is
> > >> using) and also just put in the name you want to new database to have.
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > >>
> > >>
> > >> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> > >> news:06B12366-416C-48C5-A677-72DF602CEFC1@.microsoft.com...
> > >> > On our SQL 2000, we need to restore an old version of a running database,
> > >> > because the developer needs the old version temporarily.
> > >> >
> > >> > I plan to:
> > >> > 1) Create new database in Enterprise Manger.
> > >> > 2) Choose restore from new database,
> > >> > 3) point to old version on tape (or in file system).
> > >> > 3) Rename database as part of restore confg.
> > >> >
> > >> > Being unused to SQL administration, I have to ask you: Is this all there's
> > >> > to it?
> > >> >
> > >> > (I'm afraid that the old version will mess things up for the new, running
> > >> > version.)
> > >> >
> > >> > Thank you,
> > >> > /JSL
> > >>
> > >>
> >
> >|||Most important when restoring in Enterprise Manager or via QA - make sure you
give the temp database a different name.
"Corey Bunch" wrote:
> JSL - here is an example of a RESTORE command issued in QA vs. EM.
> Note that there are more parameters available to customize the restore
> process, but I think this will do what you're attempting to do...
> RESTORE DATABASE testdatabase
> FROM DISK = 'c:\databasebackups\testdatabase_dump.bak'
> WITH
> MOVE 'testdatabase' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdatabase_data.mdf',
> MOVE 'testdatabase_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdatabase_log.ldf'
>
> This restores a backup file called "testdatabase_dump.bak", which is
> located at c:\databasebackups\ on the server you're restoring on. The
> database files will end up at c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ on the new server.
> The MOVE is needed in case on the original server the files were stored
> at another path, or were named a different name - for instance, if the
> database files were at d:\dbfiles\whatever.mdf and
> d:\dbfiles\whatever.ldf. This script also assumes that the logical
> name of the data file & log file is "testdatabase" and
> "testdatabase_log", respectively. You can get the logical names, and
> former paths from the backup file without performing a backup by
> issuing the following command in QA (I believe)...
> RESTORE HEADERONLY FROM 'c:\databasebackups\testdatabase_dump.bak'
>
> JSL wrote:
> > Thank you. I'll get back to you.
> >
> > "Tibor Karaszi" wrote:
> >
> > > The advantage using the command directly is that you can read about it in Books Online and make sure
> > > you understand what the options mean. You can also post the proposed command you are about to
> > > execute here and we can comment on that.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > > Blog: http://solidqualitylearning.com/blogs/tibor/
> > >
> > >
> > > "JSL" <JSL@.discussions.microsoft.com> wrote in message
> > > news:1831BF54-37C8-413D-8501-1D50FDB6307D@.microsoft.com...
> > > > Thank you.
> > > > Do you think it would be easier for me as an SQL rookie to use QA than
> > > > Enterprise Manager...? -- I attended a course some time ago, but that's all
> > > > SQL "experience" I've got.
> > > > I'm googling about this, but it seems hard to find an easy to follow guide
> > > > on this.
> > > >
> > > > /JSL
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > >> No need to create the database first, it is created by the restore process. It is probably easier
> > > >> to
> > > >> use Query Analyzer and the RESTORE command instead of using Enterprise Manager. It is harder to
> > > >> communicate how to drive a GUI properly compared to sending a RESTORE command to look at. If you
> > > >> decide to use RESTORE from QA, use the MOVE option to specify the desired location and file names
> > > >> for the database files (assuming you don't want to overwrite the file that the current database
> > > >> is
> > > >> using) and also just put in the name you want to new database to have.
> > > >>
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> http://www.karaszi.com/sqlserver/default.asp
> > > >> http://www.solidqualitylearning.com/
> > > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > > >>
> > > >>
> > > >> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> > > >> news:06B12366-416C-48C5-A677-72DF602CEFC1@.microsoft.com...
> > > >> > On our SQL 2000, we need to restore an old version of a running database,
> > > >> > because the developer needs the old version temporarily.
> > > >> >
> > > >> > I plan to:
> > > >> > 1) Create new database in Enterprise Manger.
> > > >> > 2) Choose restore from new database,
> > > >> > 3) point to old version on tape (or in file system).
> > > >> > 3) Rename database as part of restore confg.
> > > >> >
> > > >> > Being unused to SQL administration, I have to ask you: Is this all there's
> > > >> > to it?
> > > >> >
> > > >> > (I'm afraid that the old version will mess things up for the new, running
> > > >> > version.)
> > > >> >
> > > >> > Thank you,
> > > >> > /JSL
> > > >>
> > > >>
> > >
> > >
>|||Yes - forgot this point. If you're restoring to a different machine,
then of course the database names can be different, but if restoring to
the same machine, then new name is quite necessary.
brimhj wrote:
> Most important when restoring in Enterprise Manager or via QA - make sure you
> give the temp database a different name.
> "Corey Bunch" wrote:
> > JSL - here is an example of a RESTORE command issued in QA vs. EM.
> > Note that there are more parameters available to customize the restore
> > process, but I think this will do what you're attempting to do...
> >
> > RESTORE DATABASE testdatabase
> > FROM DISK = 'c:\databasebackups\testdatabase_dump.bak'
> > WITH
> > MOVE 'testdatabase' TO 'c:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdatabase_data.mdf',
> > MOVE 'testdatabase_log' TO 'c:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdatabase_log.ldf'
> >
> >
> > This restores a backup file called "testdatabase_dump.bak", which is
> > located at c:\databasebackups\ on the server you're restoring on. The
> > database files will end up at c:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\ on the new server.
> >
> > The MOVE is needed in case on the original server the files were stored
> > at another path, or were named a different name - for instance, if the
> > database files were at d:\dbfiles\whatever.mdf and
> > d:\dbfiles\whatever.ldf. This script also assumes that the logical
> > name of the data file & log file is "testdatabase" and
> > "testdatabase_log", respectively. You can get the logical names, and
> > former paths from the backup file without performing a backup by
> > issuing the following command in QA (I believe)...
> >
> > RESTORE HEADERONLY FROM 'c:\databasebackups\testdatabase_dump.bak'
> >
> >
> >
> > JSL wrote:
> > > Thank you. I'll get back to you.
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > The advantage using the command directly is that you can read about it in Books Online and make sure
> > > > you understand what the options mean. You can also post the proposed command you are about to
> > > > execute here and we can comment on that.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > > Blog: http://solidqualitylearning.com/blogs/tibor/
> > > >
> > > >
> > > > "JSL" <JSL@.discussions.microsoft.com> wrote in message
> > > > news:1831BF54-37C8-413D-8501-1D50FDB6307D@.microsoft.com...
> > > > > Thank you.
> > > > > Do you think it would be easier for me as an SQL rookie to use QA than
> > > > > Enterprise Manager...? -- I attended a course some time ago, but that's all
> > > > > SQL "experience" I've got.
> > > > > I'm googling about this, but it seems hard to find an easy to follow guide
> > > > > on this.
> > > > >
> > > > > /JSL
> > > > >
> > > > > "Tibor Karaszi" wrote:
> > > > >
> > > > >> No need to create the database first, it is created by the restore process. It is probably easier
> > > > >> to
> > > > >> use Query Analyzer and the RESTORE command instead of using Enterprise Manager. It is harder to
> > > > >> communicate how to drive a GUI properly compared to sending a RESTORE command to look at. If you
> > > > >> decide to use RESTORE from QA, use the MOVE option to specify the desired location and file names
> > > > >> for the database files (assuming you don't want to overwrite the file that the current database
> > > > >> is
> > > > >> using) and also just put in the name you want to new database to have.
> > > > >>
> > > > >> --
> > > > >> Tibor Karaszi, SQL Server MVP
> > > > >> http://www.karaszi.com/sqlserver/default.asp
> > > > >> http://www.solidqualitylearning.com/
> > > > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > > > >>
> > > > >>
> > > > >> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> > > > >> news:06B12366-416C-48C5-A677-72DF602CEFC1@.microsoft.com...
> > > > >> > On our SQL 2000, we need to restore an old version of a running database,
> > > > >> > because the developer needs the old version temporarily.
> > > > >> >
> > > > >> > I plan to:
> > > > >> > 1) Create new database in Enterprise Manger.
> > > > >> > 2) Choose restore from new database,
> > > > >> > 3) point to old version on tape (or in file system).
> > > > >> > 3) Rename database as part of restore confg.
> > > > >> >
> > > > >> > Being unused to SQL administration, I have to ask you: Is this all there's
> > > > >> > to it?
> > > > >> >
> > > > >> > (I'm afraid that the old version will mess things up for the new, running
> > > > >> > version.)
> > > > >> >
> > > > >> > Thank you,
> > > > >> > /JSL
> > > > >>
> > > > >>
> > > >
> > > >
> >
> >