Can we restore database to a specific folder

26 Aug 2005 9:51 AM

ad

I want to resotre a databse from a backup file like:
restore database Health from disk='c:\Health.bak' WITH REPLACE
The Health database did not exist in my SQLServer before restore.
After restore, it will add a .mdf in some folder.
How can I assign a folder to store the .mdf when restore?

26 Aug 2005 10:31 AM

Mike Hodgson

Create the database first with CREATE DATABASE
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp>
placing the files wherever you want them and then restore over the top
of that DB specifying the WITH MOVE option in the RESTORE statement (and
possibly the REPLACE option too depending on what you name your DB when
you create it). Something like:

    create database Health
        on     (name = 'Health_data', filename = 'D:\SQLData\Health.mdf')
        log on (name = 'Health_log', filename = 'E:\SQLLogs\Health.ldf')
    go

    restore database Health from disk = 'C:\Health.bak' with
        move 'Health_data' to 'D:\SQLData\Health.mdf',
        move 'Health_log' to 'E:\SQLLogs\Health.ldf'
    go

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

ad wrote:

Show quote

>I want to resotre a databse from a backup file like:
> restore database Health from disk='c:\Health.bak' WITH REPLACE
>The Health database did not exist in my SQLServer before restore.
>After restore, it will add a .mdf in some folder.
>How can I assign a folder to store the .mdf when restore?
>
>
>
>
>
>

26 Aug 2005 10:46 AM

Tibor Karaszi

Hi Mike,

You don't need to create the database before a restore. If the database doesn't exist, it will be
created by the RESTORE command.

If you do create it first, just to then restore over it, you are probably wasting time. Chances are
that you don't create the database with same size and logical file names for each file. This means
that you have to use the REPLACE option, which mean that SQL Server internally deletes the database
(as it didn't have the correct disk file structure) and then create it again.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message
news:OP9zlliqFHA.544@TK2MSFTNGP11.phx.gbl...
> Create the database first with CREATE DATABASE
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp>
> placing the files wherever you want them and then restore over the top
> of that DB specifying the WITH MOVE option in the RESTORE statement (and
> possibly the REPLACE option too depending on what you name your DB when
> you create it). Something like:
>
>    create database Health
>        on     (name = 'Health_data', filename = 'D:\SQLData\Health.mdf')
>        log on (name = 'Health_log', filename = 'E:\SQLLogs\Health.ldf')
>    go
>
>    restore database Health from disk = 'C:\Health.bak' with
>        move 'Health_data' to 'D:\SQLData\Health.mdf',
>        move 'Health_log' to 'E:\SQLLogs\Health.ldf'
>    go
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>
> ad wrote:
>
>>I want to resotre a databse from a backup file like:
>>    restore database Health from disk='c:\Health.bak' WITH REPLACE
>>The Health database did not exist in my SQLServer before restore.
>>After restore, it will add a .mdf in some folder.
>>How can I assign a folder to store the .mdf when restore?
>>
>>
>>
>>
>>
>>
>

26 Aug 2005 11:17 AM

Mike Hodgson

I'm tired - it's late at night. That's my excuse. ;)

Thanks for the info Tibor. Now that you mention it I recall observing
that before. So just that restore statement on its own would do the
trick? That is:

    restore database Health from disk = 'C:\Health.bak' with
        move 'Health_data' to 'D:\SQLData\Health.mdf',
        move 'Health_log' to 'E:\SQLLogs\Health.ldf'

....assuming those were the new locations you wanted your physical files
to go (and assuming Health_data & Health_log were the logical filenames
of the data & log files respectively).

<yawn> time to go home & go to bed.

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

Tibor Karaszi wrote:

Show quote

> Hi Mike,
>
> You don't need to create the database before a restore. If the
> database doesn't exist, it will be created by the RESTORE command.
>
> If you do create it first, just to then restore over it, you are
> probably wasting time. Chances are that you don't create the database
> with same size and logical file names for each file. This means that
> you have to use the REPLACE option, which mean that SQL Server
> internally deletes the database (as it didn't have the correct disk
> file structure) and then create it again.
>

26 Aug 2005 12:34 PM

ad

I tested, it's ok, thanks.
But I have a question.
I thought it need to restore with replace, like
restore database Health
from disk = 'd:\health1.bak'
with replace,
move 'Health_log' to 'd:\health_log.ldf',
move 'Health_dat' to 'd:\health_data.mdf'

But I find whether with replace or not, they are all OK.

If we the .mdf is not exist, and no move clause, it must use "with
replace",

but now "with move", why it no need "with replace" any more?

"Mike Hodgson" <mike.hodgson@mallesons.nospam.com>

???????:%23IfrA$iqFHA.2***@TK2MSFTNGP14.phx.gbl...
Show quote

> I'm tired - it's late at night. That's my excuse. ;)
>
> Thanks for the info Tibor. Now that you mention it I recall observing
> that before. So just that restore statement on its own would do the
> trick? That is:
>
>    restore database Health from disk = 'C:\Health.bak' with
>        move 'Health_data' to 'D:\SQLData\Health.mdf',
>        move 'Health_log' to 'E:\SQLLogs\Health.ldf'
>
> ...assuming those were the new locations you wanted your physical files
> to go (and assuming Health_data & Health_log were the logical filenames
> of the data & log files respectively).
>
> <yawn> time to go home & go to bed.
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>
> Tibor Karaszi wrote:
>
>> Hi Mike,
>>
>> You don't need to create the database before a restore. If the
>> database doesn't exist, it will be created by the RESTORE command.
>>
>> If you do create it first, just to then restore over it, you are
>> probably wasting time. Chances are that you don't create the database
>> with same size and logical file names for each file. This means that
>> you have to use the REPLACE option, which mean that SQL Server
>> internally deletes the database (as it didn't have the correct disk
>> file structure) and then create it again.
>>
>

26 Aug 2005 3:47 PM

Tibor Karaszi

> So just that restore statement on its own would do the
> trick?

Yep. Restore does it all. But if the "safety check" comes into play, it will fail. Safety check will
alert if:

The database name specified in the restore command already exist, and the file layout between the
backup and the existing db doesn't match (you might restore the incorrect db by mistake)

Some of the files that the database to be created for you by the restore process already exist. SQL
Server will not overwrite them

For these types of situation, the safety check will kick in and give you an error message. In this
case, you need to specify the REPLACE option for the restore command.

I often see people creating a database before restore. They don't creating with same file layout as
the backup specifies. Safety check kicks in, restore gives error. So you try again using REPLACE
option. All this a waste of time, as you didn't have to create the database in the first place :-).

Sleep well! :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message
news:%23IfrA$iqFHA.2076@TK2MSFTNGP14.phx.gbl...
> I'm tired - it's late at night. That's my excuse. ;)
>
> Thanks for the info Tibor. Now that you mention it I recall observing
> that before. So just that restore statement on its own would do the
> trick? That is:
>
>    restore database Health from disk = 'C:\Health.bak' with
>        move 'Health_data' to 'D:\SQLData\Health.mdf',
>        move 'Health_log' to 'E:\SQLLogs\Health.ldf'
>
> ...assuming those were the new locations you wanted your physical files
> to go (and assuming Health_data & Health_log were the logical filenames
> of the data & log files respectively).
>
> <yawn> time to go home & go to bed.
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>
> Tibor Karaszi wrote:
>
>> Hi Mike,
>>
>> You don't need to create the database before a restore. If the
>> database doesn't exist, it will be created by the RESTORE command.
>>
>> If you do create it first, just to then restore over it, you are
>> probably wasting time. Chances are that you don't create the database
>> with same size and logical file names for each file. This means that
>> you have to use the REPLACE option, which mean that SQL Server
>> internally deletes the database (as it didn't have the correct disk
>> file structure) and then create it again.
>>
>

26 Aug 2005 11:17 AM

GetGoing

hi there, this might help you

restore database Health
from disk = 'c:\health.bak'
with
move 'health_data' to 'c:\some folder\health_data.mdf',
move 'health_log' to 'c:\some folder\health_log.ldf'

keep going
Show quote

"ad" wrote:

> I want to resotre a databse from a backup file like:
> restore database Health from disk='c:\Health.bak' WITH REPLACE
> The Health database did not exist in my SQLServer before restore.
> After restore, it will add a .mdf in some folder.
> How can I assign a folder to store the .mdf when restore?
>
>
>
>
>