Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
ChristofferYou can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.
> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
>> You can view the physical files from the backup using
>> restore filelistonly.
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>> If you need to have the files in
>> another location, use the move option in the restore script
>> to specify the physical file locations.
>No, You cannot use the move option with system databases.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
>> Yes you can use with move. You can follow the example in the
>> following knowledge base article:
>> http://support.microsoft.com/?id=304692
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
Christoffer
You can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.

> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.
|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com.. .
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>No, You cannot use the move option with system databases.
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.
|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com.. .
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.
|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
ChristofferYou can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.
4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.

> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.
4ax.com...
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>
>No, You cannot use the move option with system databases.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.
4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.
4ax.com...
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.
4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring databases on SQL Server 2000 while trying not to restore to the wrong database

Experts,
Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a couple
of development teams using databases on the same SQL Server. While they can
backup their databases, they cannot restore their own databases. I have
noticed that the SQL server role overwrite the DB roles on each user
database. Users have to have DB creator permission in order to restore the
existing database. However, this role also grants users to ability to
restore DB to other users database's. Is there any way around this, so that
I could give my development users rights to restore THEIR OWN database but
not right to restore database created by someone else?
--
SpinHi
You could do this by letting them have their own instance, but then you may
as well get copies of developer edition for them!
Why do you not trust them?
John
"Spin" wrote:
> Experts,
> Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a couple
> of development teams using databases on the same SQL Server. While they can
> backup their databases, they cannot restore their own databases. I have
> noticed that the SQL server role overwrite the DB roles on each user
> database. Users have to have DB creator permission in order to restore the
> existing database. However, this role also grants users to ability to
> restore DB to other users database's. Is there any way around this, so that
> I could give my development users rights to restore THEIR OWN database but
> not right to restore database created by someone else?
> --
> Spin
>
>|||I do not want them accidentally restoring onto someone else's database.
--
Spin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:30C46FD5-B979-4B35-AEAE-0B6885F9EA57@.microsoft.com...
> Hi
> You could do this by letting them have their own instance, but then you
> may
> as well get copies of developer edition for them!
> Why do you not trust them?
> John
>
> "Spin" wrote:
>> Experts,
>> Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a
>> couple
>> of development teams using databases on the same SQL Server. While they
>> can
>> backup their databases, they cannot restore their own databases. I have
>> noticed that the SQL server role overwrite the DB roles on each user
>> database. Users have to have DB creator permission in order to restore
>> the
>> existing database. However, this role also grants users to ability to
>> restore DB to other users database's. Is there any way around this, so
>> that
>> I could give my development users rights to restore THEIR OWN database
>> but
>> not right to restore database created by someone else?
>> --
>> Spin
>>|||Hi
Proper use of a source code contol system, and a regular backup regime for
you development server would reduce the potential for loss if this does
occur. Maybe you should allocate the privileges to more senior members of the
team only?
John
"Spin" wrote:
> I do not want them accidentally restoring onto someone else's database.
> --
> Spin
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:30C46FD5-B979-4B35-AEAE-0B6885F9EA57@.microsoft.com...
> > Hi
> >
> > You could do this by letting them have their own instance, but then you
> > may
> > as well get copies of developer edition for them!
> >
> > Why do you not trust them?
> >
> > John
> >
> >
> > "Spin" wrote:
> >
> >> Experts,
> >>
> >> Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a
> >> couple
> >> of development teams using databases on the same SQL Server. While they
> >> can
> >> backup their databases, they cannot restore their own databases. I have
> >> noticed that the SQL server role overwrite the DB roles on each user
> >> database. Users have to have DB creator permission in order to restore
> >> the
> >> existing database. However, this role also grants users to ability to
> >> restore DB to other users database's. Is there any way around this, so
> >> that
> >> I could give my development users rights to restore THEIR OWN database
> >> but
> >> not right to restore database created by someone else?
> >>
> >> --
> >> Spin
> >>
> >>
> >>
>
>

Restoring database to a different name and location

Can someone please tell me what's wrong with my script below? I am trying to restore a database backup to a different database name and location.

RESTORE DATABASE test
FROM 'C:\mybackup.bak'
WITH MOVE 'h_data' TO 'C:\Program Files\SQL2000\MSSQL\Data\Test_data.mdf'

Thanks

SHKWhat error are you getting?

I do the following

ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TaxReconDB_Prod
FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
WITH MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Data.MDF'
, MOVE 'TaxReconDB_Log' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Log.LDF'
, REPLACE|||The error said that I have a syntax error on line 2.

I tried

FROM DISK = 'C:\Mybackup.bak"

and I got an error "Cannot open backup device", which makes sense because the backup is not from a disk.

then I tried

FROM FILE = 'C:\MyBackup.bak"

and I got a syntax error.

Help. I am not familiar with restoring database at all.

Thanks

SHK|||The following is correct:

FROM DISK = 'C:\Mybackup.bak"

As you are getting "Cannot open backup device", looks like you have mispelled the file name or missed a blank or something...check again|||Check the ending double quote:::
FROM DISK = 'C:\Mybackup.bak"|||I double checked and the spelling is correct.

It is a single quote at the end of the FROM DISK.

I am out of ideas.

SHK|||Ok, do this

RESTORE FILELISTONLY FROM DISK = 'C:\...'

btw where is the back up file physically at?

What happend when you run

master..xp_cmdshell 'DIR C:\*.bak'

What do you see?|||I got it to work.

I copied the backup from the network to my C drive and wanted to restore the backup to my MSDE 2000. I was trying to run the script from the network when I got all those errors. I was playing around and figured that maybe I could try to run the script locally and there it ran.
I guess the script failed because the backup was on my C drive and I tried to run it from the network server.

Thanks for all the suggestions and helps.

SHK