Restore mdf file: plan

Showing posts with label plan. Show all posts

Monday, March 26, 2012

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

Restore database to new location

Tuesday, February 21, 2012

Restore .BAK and incremental .TRN each day?

Hello,
Each morning, I have a plan fire at 2 AM that backs up my boss's
favorite database. I also have a 1 hour job that starts at 3 AM which
backs up the transaction log. The database is in FULL recovery and we
don't truncate the logs manually. I would like to know if anyone has
a script or suggestion for restoring the .TRN files all day long.
I can't spend $ on another product so TSQL is likely the answer. I do
not need the database operational at all times, as it is being
restored 'just in case'. I have read that restoring the .TRN files
WITH NORECOVERY is the best method as I don't want to lose anything.
Is it possible to do something like a JOB that uses XP_CMDSHELL to
list the .TRN files and just keeps restoring them? Or should I use an
external script completely? Also, what if the MASTER database
changes? I already looked into a DOS script that adds -m -c, restarts
MSSQL, restores the MASTER, removes the startup switches and then
restarts. Is that the right approach?
I just want to keep the two databases in sync, but cannot use
replication directly do to business rules. BTW, I am using an XCOPY
between the running MSSQL server and the 'just in case' one.
Any insight, TSQL, software recommendations or experiences would help.
Gracias,
TimTim,
What you are describing is referred to as Log Shipping. If you have
Enterprise edition of SQL Server you can use the maintenance wizard to set
this up. If you only have Std you can find scripts in the Resource Kit to
help with this. There is lots of information out there on this subject.
You can also do a google search for Log Shipping and I am sure you will find
some past posts that show some sample scripts.
--
Andrew J. Kelly
SQL Server MVP
"Tim" <SQLRESTORE@.CFAPOSTLE.COM> wrote in message
news:947a9f8a.0311211714.3f670726@.posting.google.com...
> Hello,
> Each morning, I have a plan fire at 2 AM that backs up my boss's
> favorite database. I also have a 1 hour job that starts at 3 AM which
> backs up the transaction log. The database is in FULL recovery and we
> don't truncate the logs manually. I would like to know if anyone has
> a script or suggestion for restoring the .TRN files all day long.
> I can't spend $ on another product so TSQL is likely the answer. I do
> not need the database operational at all times, as it is being
> restored 'just in case'. I have read that restoring the .TRN files
> WITH NORECOVERY is the best method as I don't want to lose anything.
> Is it possible to do something like a JOB that uses XP_CMDSHELL to
> list the .TRN files and just keeps restoring them? Or should I use an
> external script completely? Also, what if the MASTER database
> changes? I already looked into a DOS script that adds -m -c, restarts
> MSSQL, restores the MASTER, removes the startup switches and then
> restarts. Is that the right approach?
> I just want to keep the two databases in sync, but cannot use
> replication directly do to business rules. BTW, I am using an XCOPY
> between the running MSSQL server and the 'just in case' one.
> Any insight, TSQL, software recommendations or experiences would help.
> Gracias,
> Tim

Monday, March 26, 2012

Restore database to new location

Restore database to new location

Restore database to new location

Tuesday, February 21, 2012

Restore .BAK and incremental .TRN each day?

Restore mdf file

Blog Archive

About Me