Restore mdf file: location

Showing posts with label location. Show all posts

Friday, March 30, 2012

Restore db in cluster environment

Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
Your T: drive have not been made a dependency of SQL Server. You need to do
the following:
1) Make the SQL Server resource offline.
2) Move the T: drive resource into the SQL group if it is not already
there.
3) Bring up the properties of the SQL Server resource.
4) Make the T: drive a dependency of SQL Server.
5) Bring the SQL group online.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"mecn" <mecn2002@.yahoo.com> wrote in message
news:eybFs1luHHA.4916@.TK2MSFTNGP04.phx.gbl...
Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
|||Got it, Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23A3R85luHHA.536@.TK2MSFTNGP06.phx.gbl...
> Your T: drive have not been made a dependency of SQL Server. You need to
> do
> the following:
> 1) Make the SQL Server resource offline.
> 2) Move the T: drive resource into the SQL group if it is not already
> there.
> 3) Bring up the properties of the SQL Server resource.
> 4) Make the T: drive a dependency of SQL Server.
> 5) Bring the SQL group online.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eybFs1luHHA.4916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I got this error when I move the log file to a diff location in the
> cluster
> environment.
> Any ideas and solution?
> Thanks
>
> Msg 5184, Level 16, State 2, Line 1
> Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
> formatted files on which the cluster resource of the server has a
> dependency
> can be used.
>
>

restore db from device in sql2005

Need to restore the db from network location.
After I click restore from device. The select path box is grayed out. I
could not type the network path.
(I know i could restore it in query analyzer by using restore code.)
Any ideas?
ThanksDid you press the ... button to the right of that text box?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mecn" <mecn2002@.yahoo.com> wrote in message news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...
> Need to restore the db from network location.
> After I click restore from device. The select path box is grayed out. I
> could not type the network path.
> (I know i could restore it in query analyzer by using restore code.)
> Any ideas?
> Thanks
>|||I did, Just let you select local divers only
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23mgSe1B9GHA.3916@.TK2MSFTNGP04.phx.gbl...
> Did you press the ... button to the right of that text box?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...
>> Need to restore the db from network location.
>> After I click restore from device. The select path box is grayed out. I
>> could not type the network path.
>> (I know i could restore it in query analyzer by using restore code.)
>> Any ideas?
>> Thanks|||mecn wrote:
> I did, Just let you select local divers only
>
Correct, you can't point/click to a network path, but you can TYPE the
path into the filename field on that same window...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The problem is I could not. The box is grayed out.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I couldn't type. Is it sort of permission issue?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||sorry. you are saying that I could type the network path in to the file name
box?
i.e. \\network\servover\dir\abc.bak?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't think taht you could type the path and file name into filename box.
Filename box is only for file name like abc.bak. right?!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I am sorry, you are right!!!! I could type the network path into file name
box and it works--THANK YOU VERY MUCH!!!@.!!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||mecn wrote:
> I am sorry, you are right!!!! I could type the network path into file name
> box and it works--THANK YOU VERY MUCH!!!@.!!
>
Great... Now that you've accomplished that, can you explain why this
was easier than just issuing the RESTORE DATABASE command in a query window?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

restore db from device in sql2005

Need to restore the db from network location.
After I click restore from device. The select path box is grayed out. I
could not type the network path.
(I know i could restore it in query analyzer by using restore code.)
Any ideas?
Thanks
Did you press the ... button to the right of that text box?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mecn" <mecn2002@.yahoo.com> wrote in message news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...
> Need to restore the db from network location.
> After I click restore from device. The select path box is grayed out. I
> could not type the network path.
> (I know i could restore it in query analyzer by using restore code.)
> Any ideas?
> Thanks
>
|||I did, Just let you select local divers only
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23mgSe1B9GHA.3916@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> Did you press the ... button to the right of that text box?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...
|||mecn wrote:
> I did, Just let you select local divers only
>
Correct, you can't point/click to a network path, but you can TYPE the
path into the filename field on that same window...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The problem is I could not. The box is grayed out.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||I couldn't type. Is it sort of permission issue?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||sorry. you are saying that I could type the network path in to the file name
box?
i.e. \\network\servover\dir\abc.bak?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||I don't think taht you could type the path and file name into filename box.
Filename box is only for file name like abc.bak. right?!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||I am sorry, you are right!!!! I could type the network path into file name
box and it works--THANK YOU VERY MUCH!!!@.!!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||mecn wrote:
> I am sorry, you are right!!!! I could type the network path into file name
> box and it works--THANK YOU VERY MUCH!!!@.!!
>
Great... Now that you've accomplished that, can you explain why this
was easier than just issuing the RESTORE DATABASE command in a query window?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

restore db from device in sql2005

Need to restore the db from network location.
After I click restore from device. The select path box is grayed out. I
could not type the network path.
(I know i could restore it in query analyzer by using restore code.)
Any ideas?
ThanksDid you press the ... button to the right of that text box?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mecn" <mecn2002@.yahoo.com> wrote in message news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> Need to restore the db from network location.
> After I click restore from device. The select path box is grayed out. I
> could not type the network path.
> (I know i could restore it in query analyzer by using restore code.)
> Any ideas?
> Thanks
>[/vbcol]|||I did, Just let you select local divers only
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23mgSe1B9GHA.3916@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> Did you press the ... button to the right of that text box?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...|||mecn wrote:
> I did, Just let you select local divers only
>
Correct, you can't point/click to a network path, but you can TYPE the
path into the filename field on that same window...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The problem is I could not. The box is grayed out.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I couldn't type. Is it sort of permission issue?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||sorry. you are saying that I could type the network path in to the file name
box?
i.e. \\network\servover\dir\abc.bak?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't think taht you could type the path and file name into filename box.
Filename box is only for file name like abc.bak. right?!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I am sorry, you are right!!!! I could type the network path into file name
box and it works--THANK YOU VERY MUCH!!!@.!!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||mecn wrote:
> I am sorry, you are right!!!! I could type the network path into file name
> box and it works--THANK YOU VERY MUCH!!!@.!!
>
Great... Now that you've accomplished that, can you explain why this
was easier than just issuing the RESTORE DATABASE command in a query window?
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

Wednesday, March 28, 2012

Restore database...schedule

SQL Server Enterprise Edition
Microsoft Windows NT - 5.2 (3790)
Product version 8.00.760 (SP3)
1. I am moving two databases to different location (Both ldf and mdf files
because of size) on the same server. Can I automate the process and schedule
to run it at midnight. When I try to create a restore task using enterprise
manager it does not give me a option to schedule the task.
2. While performing same task(restore), few services on another server (Non
sql server) needs to be stopped, once restore is complete needs to be started
again. How to automate (start and stop services).
--
ontario, canadaProbably better to detach the database(s), move the files, thn re-attach
Check out sp_detach_db and sp_attach_db in Books Online. You can write a
few simple T-SQL statements and schedule these as a SQL job for the Agent to
run
Step 1 - sp_detach...etc.
Step 2 - xp_cmdshell 'copy the files...'
Step 3 - sp_attach_db
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:63CA1294-582D-47BF-9508-65E44EDF74C6@.microsoft.com...
> SQL Server Enterprise Edition
> Microsoft Windows NT - 5.2 (3790)
> Product version 8.00.760 (SP3)
> 1. I am moving two databases to different location (Both ldf and mdf files
> because of size) on the same server. Can I automate the process and
> schedule
> to run it at midnight. When I try to create a restore task using
> enterprise
> manager it does not give me a option to schedule the task.
> 2. While performing same task(restore), few services on another server
> (Non
> sql server) needs to be stopped, once restore is complete needs to be
> started
> again. How to automate (start and stop services).
> --
> ontario, canada|||Can I schedule database restore. I have schedules non sql services to stop
at desired time.
--
ontario, canada
"Kevin3NF" wrote:
> Probably better to detach the database(s), move the files, thn re-attach
> Check out sp_detach_db and sp_attach_db in Books Online. You can write a
> few simple T-SQL statements and schedule these as a SQL job for the Agent to
> run
> Step 1 - sp_detach...etc.
> Step 2 - xp_cmdshell 'copy the files...'
> Step 3 - sp_attach_db
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:63CA1294-582D-47BF-9508-65E44EDF74C6@.microsoft.com...
> >
> > SQL Server Enterprise Edition
> > Microsoft Windows NT - 5.2 (3790)
> > Product version 8.00.760 (SP3)
> >
> > 1. I am moving two databases to different location (Both ldf and mdf files
> > because of size) on the same server. Can I automate the process and
> > schedule
> > to run it at midnight. When I try to create a restore task using
> > enterprise
> > manager it does not give me a option to schedule the task.
> >
> > 2. While performing same task(restore), few services on another server
> > (Non
> > sql server) needs to be stopped, once restore is complete needs to be
> > started
> > again. How to automate (start and stop services).
> > --
> > ontario, canada
>
>|||You can. Create a job with a RESTORE Database statement in it...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:4B5A046B-5A3F-4FB7-8FED-D4319B59DF81@.microsoft.com...
> Can I schedule database restore. I have schedules non sql services to
> stop
> at desired time.
> --
> ontario, canada
>
> "Kevin3NF" wrote:
>> Probably better to detach the database(s), move the files, thn re-attach
>> Check out sp_detach_db and sp_attach_db in Books Online. You can write a
>> few simple T-SQL statements and schedule these as a SQL job for the Agent
>> to
>> run
>> Step 1 - sp_detach...etc.
>> Step 2 - xp_cmdshell 'copy the files...'
>> Step 3 - sp_attach_db
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:63CA1294-582D-47BF-9508-65E44EDF74C6@.microsoft.com...
>> >
>> > SQL Server Enterprise Edition
>> > Microsoft Windows NT - 5.2 (3790)
>> > Product version 8.00.760 (SP3)
>> >
>> > 1. I am moving two databases to different location (Both ldf and mdf
>> > files
>> > because of size) on the same server. Can I automate the process and
>> > schedule
>> > to run it at midnight. When I try to create a restore task using
>> > enterprise
>> > manager it does not give me a option to schedule the task.
>> >
>> > 2. While performing same task(restore), few services on another server
>> > (Non
>> > sql server) needs to be stopped, once restore is complete needs to be
>> > started
>> > again. How to automate (start and stop services).
>> > --
>> > ontario, canada
>>

Restore database...schedule

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

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

Restore database to default location on anohter server?

I have a database called "test_db" that has been backed up with the
command:
BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
I want to restore it to another Sql Server and call it
'Test_Restored_DB', as well as save it as a different physical file
name. So I copy the file over to the new server into the folder
c:\temp\ and then execute the command:
RESTORE DATABASE Test_Restored_DB
FROM DISK = 'c:\temp\test_db.bak'
WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
This works fine. But what I really want to do is restore the database
to the default location on Sql Server 2005, i.e. typically "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
hard-coding this path in. This is because I am assuming that some Sql
Servers may be configured differently and have their default data
directory located in another folder (I have no control on the
configuration of various sql servers where this will be run). Is there
a way to restore a database to another server, give it a new name,
change its physical file name, and have it placed in the default data
directory without specifying the data directory? Or perhaps there is a
stored procedure (not xp_cmdshell) or some other t-sql code that will
tell me where the default data directory is located?
Thanks for any feedback,
MarcusHi,
I have never tried that but this is what SQL Server does in the background
to find these locations
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
@.SmoDefaultFile OUTPUT
print @.SmoDefaultFile
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog
OUTPUT
print @.SmoDefaultLog
declare @.SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @.SmoRoot OUTPUT
print @.SmoRoot
Ben Nevarez, MCDBA, OCP
Database Administrator
"Marcus" wrote:
> I have a database called "test_db" that has been backed up with the
> command:
> BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> I want to restore it to another Sql Server and call it
> 'Test_Restored_DB', as well as save it as a different physical file
> name. So I copy the file over to the new server into the folder
> c:\temp\ and then execute the command:
> RESTORE DATABASE Test_Restored_DB
> FROM DISK = 'c:\temp\test_db.bak'
> WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
> MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> This works fine. But what I really want to do is restore the database
> to the default location on Sql Server 2005, i.e. typically "C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> hard-coding this path in. This is because I am assuming that some Sql
> Servers may be configured differently and have their default data
> directory located in another folder (I have no control on the
> configuration of various sql servers where this will be run). Is there
> a way to restore a database to another server, give it a new name,
> change its physical file name, and have it placed in the default data
> directory without specifying the data directory? Or perhaps there is a
> stored procedure (not xp_cmdshell) or some other t-sql code that will
> tell me where the default data directory is located?
> Thanks for any feedback,
> Marcus
>|||Excellent, thanks, Ben! With a little bit more googling of
xp_instance_regread lead me to this page which more stuff that I could
use:
http://www.sqldev.net/misc/SQLLocationFunctions.htm
Cheers,
Marcus
Ben Nevarez wrote:
> Hi,
> I have never tried that but this is what SQL Server does in the background
> to find these locations
> declare @.SmoDefaultFile nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
> @.SmoDefaultFile OUTPUT
> print @.SmoDefaultFile
> declare @.SmoDefaultLog nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog
> OUTPUT
> print @.SmoDefaultLog
> declare @.SmoRoot nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @.SmoRoot OUTPUT
> print @.SmoRoot
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Marcus" wrote:
> > I have a database called "test_db" that has been backed up with the
> > command:
> >
> > BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> >
> > I want to restore it to another Sql Server and call it
> > 'Test_Restored_DB', as well as save it as a different physical file
> > name. So I copy the file over to the new server into the folder
> > c:\temp\ and then execute the command:
> >
> > RESTORE DATABASE Test_Restored_DB
> > FROM DISK = 'c:\temp\test_db.bak'
> > WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
> > MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> >
> > This works fine. But what I really want to do is restore the database
> > to the default location on Sql Server 2005, i.e. typically "C:\Program
> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> > hard-coding this path in. This is because I am assuming that some Sql
> > Servers may be configured differently and have their default data
> > directory located in another folder (I have no control on the
> > configuration of various sql servers where this will be run). Is there
> > a way to restore a database to another server, give it a new name,
> > change its physical file name, and have it placed in the default data
> > directory without specifying the data directory? Or perhaps there is a
> > stored procedure (not xp_cmdshell) or some other t-sql code that will
> > tell me where the default data directory is located?
> >
> > Thanks for any feedback,
> > Marcus
> >
> >sql

Restore database to default location on anohter server?

I have a database called "test_db" that has been backed up with the
command:
BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
I want to restore it to another Sql Server and call it
'Test_Restored_DB', as well as save it as a different physical file
name. So I copy the file over to the new server into the folder
c:\temp\ and then execute the command:
RESTORE DATABASE Test_Restored_DB
FROM DISK = 'c:\temp\test_db.bak'
WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_D
B.mdf',
MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
This works fine. But what I really want to do is restore the database
to the default location on Sql Server 2005, i.e. typically "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
hard-coding this path in. This is because I am assuming that some Sql
Servers may be configured differently and have their default data
directory located in another folder (I have no control on the
configuration of various sql servers where this will be run). Is there
a way to restore a database to another server, give it a new name,
change its physical file name, and have it placed in the default data
directory without specifying the data directory? Or perhaps there is a
stored procedure (not xp_cmdshell) or some other t-sql code that will
tell me where the default data directory is located?
Thanks for any feedback,
MarcusHi,
I have never tried that but this is what SQL Server does in the background
to find these locations
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultData',
@.SmoDefaultFile OUTPUT
print @.SmoDefaultFile
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultLog', @.SmoDefaultLog
OUTPUT
print @.SmoDefaultLog
declare @.SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup',
N'SQLPath', @.SmoRoot OUTPUT
print @.SmoRoot
Ben Nevarez, MCDBA, OCP
Database Administrator
"Marcus" wrote:

> I have a database called "test_db" that has been backed up with the
> command:
> BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> I want to restore it to another Sql Server and call it
> 'Test_Restored_DB', as well as save it as a different physical file
> name. So I copy the file over to the new server into the folder
> c:\temp\ and then execute the command:
> RESTORE DATABASE Test_Restored_DB
> FROM DISK = 'c:\temp\test_db.bak'
> WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_D
B.mdf',
> MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> This works fine. But what I really want to do is restore the database
> to the default location on Sql Server 2005, i.e. typically "C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> hard-coding this path in. This is because I am assuming that some Sql
> Servers may be configured differently and have their default data
> directory located in another folder (I have no control on the
> configuration of various sql servers where this will be run). Is there
> a way to restore a database to another server, give it a new name,
> change its physical file name, and have it placed in the default data
> directory without specifying the data directory? Or perhaps there is a
> stored procedure (not xp_cmdshell) or some other t-sql code that will
> tell me where the default data directory is located?
> Thanks for any feedback,
> Marcus
>|||Excellent, thanks, Ben! With a little bit more googling of
xp_instance_regread lead me to this page which more stuff that I could
use:
http://www.sqldev.net/misc/SQLLocationFunctions.htm
Cheers,
Marcus
Ben Nevarez wrote:[vbcol=seagreen]
> Hi,
> I have never tried that but this is what SQL Server does in the background
> to find these locations
> declare @.SmoDefaultFile nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultData',
> @.SmoDefaultFile OUTPUT
> print @.SmoDefaultFile
> declare @.SmoDefaultLog nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultLog', @.SmoDefaultL
og
> OUTPUT
> print @.SmoDefaultLog
> declare @.SmoRoot nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'SOFTWARE\Microsoft\MSSQLServer\Setup',
N'SQLPath', @.SmoRoot OUTPUT
> print @.SmoRoot
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Marcus" wrote:
>

Friday, March 23, 2012

Restore database goes to error

Hello everyone,
I'm just restoring database with the ordinary way. But one of my database
reports an error:
"Location: execsql.cpp:230
Expression: exception != se_OK
SPID: 52
Process ID: 572"
SQL Command looks like this:
"USE MASTER RESTORE DATABASE [SourceDB] FROM DISK = 'C:\Backup.bak' WITH
REPLACE
,MOVE 'OldName' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\MDFFileName.mdf'
,MOVE 'OldName_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\MDFFileName_log.LDF'"
Any ideas?
Ilia
Hello,
Is this SQL 2000? Can you try restoring the database into a different SQL
Server machine and try if it is working. Just see the size of the database
also. MSDE can support
only a max of 2 GB MDF size.
Thanks
Hari
"ilia" <ilia@.discussions.microsoft.com> wrote in message
news:70D680CF-C9B9-44BA-BC39-93C2724FEBB7@.microsoft.com...
> Hello everyone,
> I'm just restoring database with the ordinary way. But one of my database
> reports an error:
> "Location: execsql.cpp:230
> Expression: exception != se_OK
> SPID: 52
> Process ID: 572"
> SQL Command looks like this:
> "USE MASTER RESTORE DATABASE [SourceDB] FROM DISK = 'C:\Backup.bak' WITH
> REPLACE
> ,MOVE 'OldName' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\MDFFileName.mdf'
> ,MOVE 'OldName_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\MDFFileName_log.LDF'"
> Any ideas?
> Ilia

Wednesday, March 21, 2012

Restore Database Dialog Shows Old Backup File Location

We're running SQL 7.0 (SP4,MS03-031) on Windows 2000. We recently
moved our database backups from the D:\ drive to the E:\ drive. In
the process, we dropped all of our old backup devices and created new
ones pointing to the new drive. The backups have been running
successfully for several weeks now; sending data to the new location.
The other day I needed to restore a database. While reviewing the
restore database dialog information, I noticed that the old path
(D:\)appeared under "Restore from" column. The other information;
type, backup set date and size are correct and apply to the backup
file on the E:\ drive. I attempted the restore and naturally received
an error.
I've checked sysdevices and found it has the correct location for the
backup files.
Any ideas why this could be and any suggestions on how to fix it?
Thanks!
Michael Brule
Database Administrator
The Phoenix CompaniesThe first 3 links should be relevant but the rest may also be of interest:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"michael brule" <michael.brule@.phoenixwm.com> wrote in message
news:55f7800f.0402181409.25c474c4@.posting.google.com...
> We're running SQL 7.0 (SP4,MS03-031) on Windows 2000. We recently
> moved our database backups from the D:\ drive to the E:\ drive. In
> the process, we dropped all of our old backup devices and created new
> ones pointing to the new drive. The backups have been running
> successfully for several weeks now; sending data to the new location.
> The other day I needed to restore a database. While reviewing the
> restore database dialog information, I noticed that the old path
> (D:\)appeared under "Restore from" column. The other information;
> type, backup set date and size are correct and apply to the backup
> file on the E:\ drive. I attempted the restore and naturally received
> an error.
> I've checked sysdevices and found it has the correct location for the
> backup files.
> Any ideas why this could be and any suggestions on how to fix it?
> Thanks!
> Michael Brule
> Database Administrator
> The Phoenix Companies|||I had the same problem before.
You can restore the database from the restore diaglog
panel by choosing "From device" rather than "database".
Fix the problem permanently is to delete the old backup
files on D:\ drive, the restore diaglog panel should show
correctly after the backup run on E:\ drive.|||Thanks for the replies, Andrew and Johnny!
Andrew: The information you provided seemed to concern issues
surrounding moving a database to a new location My question was about
moving database backup files. The databases remain in their original
location.
Johnny: Actually, we moved the backup directory to the new location.
Neither that directory nor any of its files remains in the original
location. Numerous backups have been run since.
Thanks!
Michael Brule
Database Administrator
The Phoenix Companies|||Some of the links refer to methods that can be used to move to a new server
or just a new location. You can either use RESTORE with the MOVE option or
sp_attach_db. Both are covered in there as well as BooksOnLine.
--
Andrew J. Kelly
SQL Server MVP
"michael brule" <michael.brule@.phoenixwm.com> wrote in message
news:55f7800f.0402190654.660f72e5@.posting.google.com...
> Thanks for the replies, Andrew and Johnny!
> Andrew: The information you provided seemed to concern issues
> surrounding moving a database to a new location My question was about
> moving database backup files. The databases remain in their original
> location.
> Johnny: Actually, we moved the backup directory to the new location.
> Neither that directory nor any of its files remains in the original
> location. Numerous backups have been run since.
> Thanks!
> Michael Brule
> Database Administrator
> The Phoenix Companies|||Sorry.
The files in the new location ie E:\ have to be deleted as
well.
Overwritten the existing backup files still showed the old
backup directory path in the restore panel. If you don't
want to delete all the backup files in one go, you may
delete the oldest backup files until all are recycled.

Friday, March 9, 2012

restore Back file to new server database

I've been reading articles on "how to restore a database to a new location",
"how to restore a database with a new name", etc.
What I need is a step by step on how to:
Using a preexisting database backup,
point it to a different server,
create that database on that different server,
apply the full back up,
do a "point in time" restore from the original database transaction log.
I thought I could do most ofthis but I am having NO luck in getting the
database to restore to a new database with the same name on a different
server.
How is this done?
It is easier if you post the TSQL commands you are executing. Basically, you:
On the originating server:
BACKUP DATABASE dbname
BACKUP LOG dbname
BACKUP LOG dbname
On the destination server:
RESTORE DATABASE dbname ... WITH NORECOVERY...
If the database exists and the database file structure doesn't match with the original, you need to
use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
RESTORE LOG dbname... WITH NORECOVERY...
RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
> I've been reading articles on "how to restore a database to a new location",
> "how to restore a database with a new name", etc.
> What I need is a step by step on how to:
> Using a preexisting database backup,
> point it to a different server,
> create that database on that different server,
> apply the full back up,
> do a "point in time" restore from the original database transaction log.
> I thought I could do most ofthis but I am having NO luck in getting the
> database to restore to a new database with the same name on a different
> server.
> How is this done?
|||There seems to be a couple of things going on that I'm not sure about;
The following is the way I have things typed, I'm confused with the "From"
peremeter. If this is the device I'm restoring from, is this than going to
be the literal name of the back up file?
I also get the following error:
"One or more of the options (stopat) are not supported for this statement"
my comments typed in the Query Analyzer;
RECOVER DATABASE Market
FROM ?
WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
"Tibor Karaszi" wrote:

> It is easier if you post the TSQL commands you are executing. Basically, you:
> On the originating server:
> BACKUP DATABASE dbname
> BACKUP LOG dbname
> BACKUP LOG dbname
> On the destination server:
> RESTORE DATABASE dbname ... WITH NORECOVERY...
> If the database exists and the database file structure doesn't match with the original, you need to
> use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
> RESTORE LOG dbname... WITH NORECOVERY...
> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
>
|||FROM is either a predefined backupdevice (read about sp_addumpdevice):
RESTORE DATABASE Market
FROM myBackupDevice
WITH RECOVERY, STOPAT = '20051014 20:00'
Or a physical file name:
RESTORE DATABASE Market
FROM DISK = 'C:\myBackupDevice.BAK'
WITH RECOVERY, STOPAT = '20051014 20:00'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...[vbcol=seagreen]
> There seems to be a couple of things going on that I'm not sure about;
> The following is the way I have things typed, I'm confused with the "From"
> peremeter. If this is the device I'm restoring from, is this than going to
> be the literal name of the back up file?
> I also get the following error:
> "One or more of the options (stopat) are not supported for this statement"
> my comments typed in the Query Analyzer;
> RECOVER DATABASE Market
> FROM ?
> WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
> "Tibor Karaszi" wrote:
|||And if I wanted to restore from the transaction log, the following being the
appopriate syntex?
RESTORE LOG market
FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
WITH RECOVERY, STOPAT = '20050914 20:00'
"Tibor Karaszi" wrote:

> FROM is either a predefined backupdevice (read about sp_addumpdevice):
> RESTORE DATABASE Market
> FROM myBackupDevice
> WITH RECOVERY, STOPAT = '20051014 20:00'
>
> Or a physical file name:
> RESTORE DATABASE Market
> FROM DISK = 'C:\myBackupDevice.BAK'
> WITH RECOVERY, STOPAT = '20051014 20:00'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
>
|||Correct. I missed that in my prior post where I specified RESTORE DATABASE with STOPAT, which isn't
possible. STOPAT is only possible for LOG restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:8CCDAB6D-9C3D-4FCA-89B1-0D46A12983A7@.microsoft.com...[vbcol=seagreen]
> And if I wanted to restore from the transaction log, the following being the
> appopriate syntex?
> RESTORE LOG market
> FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
> WITH RECOVERY, STOPAT = '20050914 20:00'
> "Tibor Karaszi" wrote:
|||I may have bigger problems than what I thought. The file I was looking at is
evidently a copy of the original database from a few months ago, the
extensions was not visible and it was offering itself as a back up file was
why I thought it was a BAK.
It does not appear that a full backup has been done on this database prior
to a few days ago.
The problem is some time in the past 4 weeks some data has evaporated, the
thought was to do a STOPAT recovery with the transaction logs but if we don't
have a full back up prior to 4 weeks ago is this going to be possible?
"Tibor Karaszi" wrote:

> Correct. I missed that in my prior post where I specified RESTORE DATABASE with STOPAT, which isn't
> possible. STOPAT is only possible for LOG restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:8CCDAB6D-9C3D-4FCA-89B1-0D46A12983A7@.microsoft.com...
>
|||Are you saying that the earlier database backup if from a few days ago, and you want to do point in
time restore to an earlier time? If so, you can't. A log backup can only be applied on a database
backup. First restore database backup, then each log backup taken after that database backup,
optionally using STOPAT for the last log restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:B894C0C8-D6EB-4DE2-827F-9E9871A75C35@.microsoft.com...[vbcol=seagreen]
>I may have bigger problems than what I thought. The file I was looking at is
> evidently a copy of the original database from a few months ago, the
> extensions was not visible and it was offering itself as a back up file was
> why I thought it was a BAK.
> It does not appear that a full backup has been done on this database prior
> to a few days ago.
> The problem is some time in the past 4 weeks some data has evaporated, the
> thought was to do a STOPAT recovery with the transaction logs but if we don't
> have a full back up prior to 4 weeks ago is this going to be possible?
> "Tibor Karaszi" wrote:

Friday, March 30, 2012

Restore db in cluster environment

restore db from device in sql2005

restore db from device in sql2005

restore db from device in sql2005

Wednesday, March 28, 2012

Restore database...schedule

Restore database...schedule

Monday, March 26, 2012

Restore database to new location

Restore database to new location

Restore database to new location

restore database to new database

restore database to new database

Restore database to default location on anohter server?

Restore database to default location on anohter server?

Friday, March 23, 2012

Restore database goes to error

Wednesday, March 21, 2012

Restore Database Dialog Shows Old Backup File Location

Friday, March 9, 2012

restore Back file to new server database

Restore mdf file

Blog Archive

About Me