?? Restoring a BAK -- Do I Need the Schema ??

10 May 2007 2:50 PM

Alan Foxmore

Hello everyone,

I'm new to SQL Server 2005. I'm using the Express version.

A co-worker sent me a BAK file which I need to restore. My question is, do I
need to have the database structure already in place before I can do the
restore? I was wondering if there's a way to simply restore the BAK file
into an empty database and have underlying tables/sprocs automatically
created as part of the restore process. Is this possible?

Thanks very much.

10 May 2007 3:18 PM

MSDN

Yes. You can either just restore the bak file and let it create what it
needs or you can

1. Create empty, new database with the same name
2. Do a backup of the same name as your backup
3. Put the co-worker's backup in place of the backup in 2
4. Restore the backup and tell it to overwrite an existing DB.

Alan Foxmore wrote:
Show quote

> Hello everyone,
>
> I'm new to SQL Server 2005. I'm using the Express version.
>
> A co-worker sent me a BAK file which I need to restore. My question is, do I
> need to have the database structure already in place before I can do the
> restore? I was wondering if there's a way to simply restore the BAK file
> into an empty database and have underlying tables/sprocs automatically
> created as part of the restore process. Is this possible?
>
> Thanks very much.
>
>

10 May 2007 4:22 PM

Tibor Karaszi

Actually, step 1 to 3 are unnecessary and a waste of time. The restore process will create the
database for you.

You might need to investigate what files were used for the original database (RESTORE HEADERONLY and
RESTORE FILELISTONLY) and then specify new physical filenames when you restore (using the MOVE
option of the RESTORE command).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quote

"MSDN" <Grunt@newsgroup.nospam> wrote in message news:eAc3EaxkHHA.492@TK2MSFTNGP04.phx.gbl...
> Yes. You can either just restore the bak file and let it create what it needs or you can
>
> 1. Create empty, new database with the same name
> 2. Do a backup of the same name as your backup
> 3. Put the co-worker's backup in place of the backup in 2
> 4. Restore the backup and tell it to overwrite an existing DB.
>
>
> Alan Foxmore wrote:
>> Hello everyone,
>>
>> I'm new to SQL Server 2005. I'm using the Express version.
>>
>> A co-worker sent me a BAK file which I need to restore. My question is, do I need to have the
>> database structure already in place before I can do the restore? I was wondering if there's a way
>> to simply restore the BAK file into an empty database and have underlying tables/sprocs
>> automatically created as part of the restore process. Is this possible?
>>
>> Thanks very much.

10 May 2007 5:34 PM

MSDN

I always wondered about that. I've taken to just restoring the file as I
suggested. However, when I inherited the SQL stuff that was the
technique the current admin used and recommended. It didn't make much
sense to me but at the time he new much more than I did <G>.

Tibor Karaszi wrote:
Show quote

> Actually, step 1 to 3 are unnecessary and a waste of time. The restore
> process will create the database for you.
>
> You might need to investigate what files were used for the original
> database (RESTORE HEADERONLY and RESTORE FILELISTONLY) and then specify
> new physical filenames when you restore (using the MOVE option of the
> RESTORE command).
>

10 May 2007 5:44 PM

Tibor Karaszi

Perhaps that person had worked with the old architecture (6.5 and older), where you actually *had*
to create the database first?
Show quote

:-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quote

"MSDN" <Grunt@newsgroup.nospam> wrote in message news:%23e7CJmykHHA.568@TK2MSFTNGP02.phx.gbl...
>I always wondered about that. I've taken to just restoring the file as I suggested. However, when
>I inherited the SQL stuff that was the technique the current admin used and recommended. It didn't
>make much sense to me but at the time he new much more than I did <G>.
>
>
>
> Tibor Karaszi wrote:
>> Actually, step 1 to 3 are unnecessary and a waste of time. The restore process will create the
>> database for you.
>>
>> You might need to investigate what files were used for the original database (RESTORE HEADERONLY
>> and RESTORE FILELISTONLY) and then specify new physical filenames when you restore (using the
>> MOVE option of the RESTORE command).
>>

10 May 2007 5:54 PM

MSDN

That would explain it. I picked it up when I migrated a SQL 7 to SQL
2000. He did have 6.5 or earlier experience. Thanks.

Tibor Karaszi wrote:
Show quote

> Perhaps that person had worked with the old architecture (6.5 and
> older), where you actually *had* to create the database first?
> :-)
>

10 May 2007 6:06 PM

Alan Foxmore

Thank you all.... It works now. I had to change to the account under which
the service was running to the Local Account.

Thanks again.

Show quote

"Alan Foxmore" <afoxm***@yahoo.com> wrote in message
news:upPyLKxkHHA.4112@TK2MSFTNGP04.phx.gbl...
> Hello everyone,
>
> I'm new to SQL Server 2005. I'm using the Express version.
>
> A co-worker sent me a BAK file which I need to restore. My question is, do
> I need to have the database structure already in place before I can do the
> restore? I was wondering if there's a way to simply restore the BAK file
> into an empty database and have underlying tables/sprocs automatically
> created as part of the restore process. Is this possible?
>
> Thanks very much.
>