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 Deleted Columns

I just deleted a few columns from a database that I am in the process of
moving. I totally screwed up and didn't dts the data in the columns over to
the new one. Is there any way to restore the data?
Thanks,
Drew"Drew" <dlaing@.NOswvtc.state.va.SPAMus> wrote in message
news:uGxwwqYdEHA.3796@.TK2MSFTNGP10.phx.gbl...
> I just deleted a few columns from a database that I am in the process of
> moving. I totally screwed up and didn't dts the data in the columns over
to
> the new one. Is there any way to restore the data?
Restore from your last good backup. You have one, right?
Steve|||Got it... I just wasn't sure how to do it... I figured it out...
Thanks,
Drew
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:%23NqhtMZdEHA.3632@.TK2MSFTNGP11.phx.gbl...
> "Drew" <dlaing@.NOswvtc.state.va.SPAMus> wrote in message
> news:uGxwwqYdEHA.3796@.TK2MSFTNGP10.phx.gbl...
> > I just deleted a few columns from a database that I am in the process of
> > moving. I totally screwed up and didn't dts the data in the columns
over
> to
> > the new one. Is there any way to restore the data?
> Restore from your last good backup. You have one, right?
> Steve
>

Restoring Databases to New Servers

I only have one Server running SQL. Daily I am performing full SQL backups
of the databases and moving them to tape offsite. If my the Server is
destroyed I will obtain a new machine. load SQL Sever and intend to restore
the backups off tape. I am told there is no clean way to do this because of
the GUID problems. Does anyone know of a clean way to do this or can direct
me to instructions as to how to do it?
Thanks.
Hi,
No issues at all. All you have to do is:-
1. Install SQL Server with same service pack as old
2. Restore Master database
3. Restore MSDB Database
4. Now Restore the user database one by one. If you want to apply
transaction log backups then use NORECOVERY option along with Restore
Database/
Restore Log command
Since you are restoring the MASTER database as well.. automatically Logins
and users will be syncronized.
Thanks
Hari
SQL Server MVP
"KED Florida" <KED Florida@.discussions.microsoft.com> wrote in message
news:0CCDB4D9-144C-4FB4-A345-141286B73983@.microsoft.com...
>I only have one Server running SQL. Daily I am performing full SQL backups
> of the databases and moving them to tape offsite. If my the Server is
> destroyed I will obtain a new machine. load SQL Sever and intend to
> restore
> the backups off tape. I am told there is no clean way to do this because
> of
> the GUID problems. Does anyone know of a clean way to do this or can
> direct
> me to instructions as to how to do it?
> Thanks.

Restoring Databases to New Servers

I only have one Server running SQL. Daily I am performing full SQL backups
of the databases and moving them to tape offsite. If my the Server is
destroyed I will obtain a new machine. load SQL Sever and intend to restore
the backups off tape. I am told there is no clean way to do this because of
the GUID problems. Does anyone know of a clean way to do this or can direct
me to instructions as to how to do it?
Thanks.Hi,
No issues at all. All you have to do is:-
1. Install SQL Server with same service pack as old
2. Restore Master database
3. Restore MSDB Database
4. Now Restore the user database one by one. If you want to apply
transaction log backups then use NORECOVERY option along with Restore
Database/
Restore Log command
Since you are restoring the MASTER database as well.. automatically Logins
and users will be syncronized.
Thanks
Hari
SQL Server MVP
"KED Florida" <KED Florida@.discussions.microsoft.com> wrote in message
news:0CCDB4D9-144C-4FB4-A345-141286B73983@.microsoft.com...
>I only have one Server running SQL. Daily I am performing full SQL backups
> of the databases and moving them to tape offsite. If my the Server is
> destroyed I will obtain a new machine. load SQL Sever and intend to
> restore
> the backups off tape. I am told there is no clean way to do this because
> of
> the GUID problems. Does anyone know of a clean way to do this or can
> direct
> me to instructions as to how to do it?
> Thanks.

Restoring Databases to New Servers

I only have one Server running SQL. Daily I am performing full SQL backups
of the databases and moving them to tape offsite. If my the Server is
destroyed I will obtain a new machine. load SQL Sever and intend to restore
the backups off tape. I am told there is no clean way to do this because of
the GUID problems. Does anyone know of a clean way to do this or can direct
me to instructions as to how to do it?
Thanks.Hi,
No issues at all. All you have to do is:-
1. Install SQL Server with same service pack as old
2. Restore Master database
3. Restore MSDB Database
4. Now Restore the user database one by one. If you want to apply
transaction log backups then use NORECOVERY option along with Restore
Database/
Restore Log command
Since you are restoring the MASTER database as well.. automatically Logins
and users will be syncronized.
Thanks
Hari
SQL Server MVP
"KED Florida" <KED Florida@.discussions.microsoft.com> wrote in message
news:0CCDB4D9-144C-4FB4-A345-141286B73983@.microsoft.com...
>I only have one Server running SQL. Daily I am performing full SQL backups
> of the databases and moving them to tape offsite. If my the Server is
> destroyed I will obtain a new machine. load SQL Sever and intend to
> restore
> the backups off tape. I am told there is no clean way to do this because
> of
> the GUID problems. Does anyone know of a clean way to do this or can
> direct
> me to instructions as to how to do it?
> Thanks.