Restoring/moving model?

Hi
I want to alter Model so that its transaction logs and
datafiles are on different drives,so that new dbs created
on this server get the same configuration.
When I try to restore Model with a with_move option it
says I can't do that.
How can I acheive what I want?
Would remaning Model, and the creating a new db called
Model in the config I want work?Have a look at this old post :-
http://groups.google.co.uk/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=co4joN3eCHA.1308%40cpmsftngxa08
--
HTH
Ryan Waight, MCDBA, MCSE
"felix" <felix@.hotmail.com> wrote in message
news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
> Hi
> I want to alter Model so that its transaction logs and
> datafiles are on different drives,so that new dbs created
> on this server get the same configuration.
> When I try to restore Model with a with_move option it
> says I can't do that.
> How can I acheive what I want?
> Would remaning Model, and the creating a new db called
> Model in the config I want work?
>|||To move the model database, SQL Server must be started
with trace flag 3608 so that
it does not recover any database except
the master.
NOTE: You will not be able to access any user databases at
this time.
You should not perform any operations
other than the steps below while using
this trace flag.
To add trace flag 3608 as a SQL Server startup
parameter:
After adding trace flag 3608, perform the following
steps:
1. Stop and restart SQL Server.
2. Detach the model database as follows:
use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from D:\Mssql7
\Data to
E:\Sqldata(or any other drives).
4. Reattach the model database as follows:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\mod
ellog.ldf'
go
--e:\ or any other drives
5. Remove the -T3608 trace flag from the startup
parameters box in the
Enterprise Manager.
6. Stop and restart SQL Server. You can verify the change
in
file locations using
sp_helpfile:
use model
go
sp_helpfile
go
Koohyar
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>Hi
>I want to alter Model so that its transaction logs and
>datafiles are on different drives,so that new dbs created
>on this server get the same configuration.
>When I try to restore Model with a with_move option it
>says I can't do that.
>How can I acheive what I want?
>Would remaning Model, and the creating a new db called
>Model in the config I want work?
>.
>|||To add to the other responses, the default location of new database data
and log files is not determined by the model database file locations.
The default file locations can be specified via Enterprise Manager under
server properties --> database settings.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"felix" <felix@.hotmail.com> wrote in message
news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
> Hi
> I want to alter Model so that its transaction logs and
> datafiles are on different drives,so that new dbs created
> on this server get the same configuration.
> When I try to restore Model with a with_move option it
> says I can't do that.
> How can I acheive what I want?
> Would remaning Model, and the creating a new db called
> Model in the config I want work?
>|||Thanks Dan,
Much more simple than I thought then! Your suggestion
worked a treat.
Felix
>--Original Message--
>To add to the other responses, the default location of
new database data
>and log files is not determined by the model database
file locations.
>The default file locations can be specified via
Enterprise Manager under
>server properties --> database settings.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"felix" <felix@.hotmail.com> wrote in message
>news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
>> Hi
>> I want to alter Model so that its transaction logs and
>> datafiles are on different drives,so that new dbs
created
>> on this server get the same configuration.
>> When I try to restore Model with a with_move option it
>> says I can't do that.
>> How can I acheive what I want?
>> Would remaning Model, and the creating a new db called
>> Model in the config I want work?
>
>.
>

restoring to a mirrored database

When I tried to run the command
alter database Press_Ganey_Associates_MSCRM set single_user with
rollback after 30
on a mirrored database, I got the following error.
Msg 1468, Level 16, State 1, Line 1
The operation cannot be performed on database "myDB" because it is
involved in a database mirroring session.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
When restoring to a mirrored database, do I need to pause the
mirroring, or stop it? Once restored, do I simply resume or set on?Are you attempting the restore on the principal or on the mirror?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JOH" <JOHolloway@.gmail.com> wrote in message
news:af10aac4-fae7-4a64-baf8-9592cf7571f7@.b64g2000hsa.googlegroups.com...
> When I tried to run the command
> alter database Press_Ganey_Associates_MSCRM set single_user with
> rollback after 30
> on a mirrored database, I got the following error.
> Msg 1468, Level 16, State 1, Line 1
> The operation cannot be performed on database "myDB" because it is
> involved in a database mirroring session.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> When restoring to a mirrored database, do I need to pause the
> mirroring, or stop it? Once restored, do I simply resume or set on?|||On Apr 26, 2:37 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Are you attempting the restore on the principal or on the mirror?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "JOH" <JOHollo...@.gmail.com> wrote in message
> news:af10aac4-fae7-4a64-baf8-9592cf7571f7@.b64g2000hsa.googlegroups.com...
> > When I tried to run the command
> > alter database Press_Ganey_Associates_MSCRM set single_user with
> > rollback after 30
> > on a mirrored database, I got the following error.
> > Msg 1468, Level 16, State 1, Line 1
> > The operation cannot be performed on database "myDB" because it is
> > involved in a database mirroring session.
> > Msg 5069, Level 16, State 1, Line 1
> > ALTER DATABASE statement failed.
> > When restoring to a mirrored database, do I need to pause the
> > mirroring, or stop it? Once restored, do I simply resume or set on?
On the principal.|||You can't restore the principal, this would break the mirroring anyway, so you have to stop
mirroring, do the restore and then re do the mirroring setup. See for instance:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f28f0fb8-c18a-4fc6-96ac-2e7a8ff189cd.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JOH" <JOHolloway@.gmail.com> wrote in message
news:952f4616-d7c5-4c60-9ffb-2a334cd4b28e@.27g2000hsf.googlegroups.com...
> On Apr 26, 2:37 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Are you attempting the restore on the principal or on the mirror?
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "JOH" <JOHollo...@.gmail.com> wrote in message
>> news:af10aac4-fae7-4a64-baf8-9592cf7571f7@.b64g2000hsa.googlegroups.com...
>> > When I tried to run the command
>> > alter database Press_Ganey_Associates_MSCRM set single_user with
>> > rollback after 30
>> > on a mirrored database, I got the following error.
>> > Msg 1468, Level 16, State 1, Line 1
>> > The operation cannot be performed on database "myDB" because it is
>> > involved in a database mirroring session.
>> > Msg 5069, Level 16, State 1, Line 1
>> > ALTER DATABASE statement failed.
>> > When restoring to a mirrored database, do I need to pause the
>> > mirroring, or stop it? Once restored, do I simply resume or set on?
> On the principal.|||On Apr 27, 2:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You can't restore the principal, this would break the mirroring anyway, so you have to stop
> mirroring, do the restore and then re do the mirroring setup. See for instance:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f28f0fb8-c18a-4fc6-96ac-2e7a8ff189cd.htm
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "JOH" <JOHollo...@.gmail.com> wrote in message
> news:952f4616-d7c5-4c60-9ffb-2a334cd4b28e@.27g2000hsf.googlegroups.com...
> > On Apr 26, 2:37 am, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> Are you attempting the restore on the principal or on the mirror?
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "JOH" <JOHollo...@.gmail.com> wrote in message
> >>news:af10aac4-fae7-4a64-baf8-9592cf7571f7@.b64g2000hsa.googlegroups.com...
> >> > When I tried to run the command
> >> > alter database Press_Ganey_Associates_MSCRM set single_user with
> >> > rollback after 30
> >> > on a mirrored database, I got the following error.
> >> > Msg 1468, Level 16, State 1, Line 1
> >> > The operation cannot be performed on database "myDB" because it is
> >> > involved in a database mirroring session.
> >> > Msg 5069, Level 16, State 1, Line 1
> >> > ALTER DATABASE statement failed.
> >> > When restoring to a mirrored database, do I need to pause the
> >> > mirroring, or stop it? Once restored, do I simply resume or set on?
> > On the principal.
Thank you for the link. I've now read through the BOL on mirroring,
and will experiment with test instances.