Hi Group,
For some reasons I have to pull the SQL Server Production
server out of network and reinstall from OS level. I have
few queries about Backing up the SQL server Databases and
choosing cluster size.
1. Do I need to Backup/detach the Master,MSDB databases
along with User database to restore again when SQL server
is installed again freshly?. MSDB just has two maintenance
plans running.I fell that I can just take the backups of
User databases as I have to restore them on fresh
installation.
2. I am creating three arrays on this server
C: RAID1 Array ( OS) - Array1
D: RAID1 (SQL Tran logs files)- Array2
D: RAID5E ( SQL Data files) - Array3
While installing SQLServer I will choose Array3 for SQL
system databases. How do i move the master.ldf msdb.ldf
model.ldf temp.ldf to Array2 ? does detach/attach work for
system databses?
3. I am choosing 8KB Cluster on NTFS instead of Default
4KB cluster, does this help in enhancing the access of 8K
SQL database pages in terms of I/O?
4. Are there any patches/fixes released after
SQLServer2000 Service Pack 3a?
OS Windows 2000 Server SP4
SQL Server 2000 Standard Edition
IBM x250 dual PIII Xeon 700MHz
4GB RAM
18GB for OS on server
1.4TB on IBM FAStT200 HA + EXP500 expansion.
Thanks for your patience
Regards
ChipHi,
1. This link details you the steps to move the system and user databases.
http://www.support.microsoft.com/?id=224071 Moving SQL Server
Databases to a New Location with Detach/Attach
It is always good to move the Master and MSDB databases to new server.
But it is a must that you should move master database
since it stores Server config, Logins permissions..If you are
notloading master all the database users chanin will be lost.
2. For Master database after starting sql server using trace flag "-T3608"
and use sp_detach_db and then copy to new drive and use
sp_attach_db to attach Master database. For Tempdb use ALTER DATBASE ..
MODIFY FILE option. Both methodologies are detailed
in the above link
3. This represent the smallest amount of disk space allocated to hold a
file.
I feel this might not reduce your I/O.
4. No, Sp3a is the latest service pack.
Thanks
Hari
MCDBA
"Chip" <Chipsin007@.yahoo.com> wrote in message
news:32b801c4290c$a0a01a90$a501280a@.phx.gbl...
> Hi Group,
> For some reasons I have to pull the SQL Server Production
> server out of network and reinstall from OS level. I have
> few queries about Backing up the SQL server Databases and
> choosing cluster size.
> 1. Do I need to Backup/detach the Master,MSDB databases
> along with User database to restore again when SQL server
> is installed again freshly?. MSDB just has two maintenance
> plans running.I fell that I can just take the backups of
> User databases as I have to restore them on fresh
> installation.
> 2. I am creating three arrays on this server
> C: RAID1 Array ( OS) - Array1
> D: RAID1 (SQL Tran logs files)- Array2
> D: RAID5E ( SQL Data files) - Array3
> While installing SQLServer I will choose Array3 for SQL
> system databases. How do i move the master.ldf msdb.ldf
> model.ldf temp.ldf to Array2 ? does detach/attach work for
> system databses?
> 3. I am choosing 8KB Cluster on NTFS instead of Default
> 4KB cluster, does this help in enhancing the access of 8K
> SQL database pages in terms of I/O?
> 4. Are there any patches/fixes released after
> SQLServer2000 Service Pack 3a?
> OS Windows 2000 Server SP4
> SQL Server 2000 Standard Edition
> IBM x250 dual PIII Xeon 700MHz
> 4GB RAM
> 18GB for OS on server
> 1.4TB on IBM FAStT200 HA + EXP500 expansion.
> Thanks for your patience
> Regards
> Chip|||Hi Hari,
Thanks for the help! one more doubt.. When I install SQL
Server by default it will create Master,MSDB . How do i
get rid of them and attach the last backed up master
(.mdf,.ldf), MSDB(.mdf,.ldf).
I guess I have to login in singleuser mode, detach them,
overwrite with the backed up files then attach?
Please give me step by step ( you are always awesome in
dealing step by step)
Sincerely
Chip
>--Original Message--
>Hi,
>1. This link details you the steps to move the system
and user databases.
> http://www.support.microsoft.com/?id=224071
Moving SQL Server
>Databases to a New Location with Detach/Attach
> It is always good to move the Master and MSDB
databases to new server.
>But it is a must that you should move master database
> since it stores Server config, Logins
permissions..If you are
>notloading master all the database users chanin will be
lost.
>2. For Master database after starting sql server using
trace flag "-T3608"
>and use sp_detach_db and then copy to new drive and use
> sp_attach_db to attach Master database. For Tempdb
use ALTER DATBASE ..
>MODIFY FILE option. Both methodologies are detailed
> in the above link
>3. This represent the smallest amount of disk space
allocated to hold a
>file.
> I feel this might not reduce your I/O.
>
>4. No, Sp3a is the latest service pack.
>Thanks
>Hari
>MCDBA
>
>"Chip" <Chipsin007@.yahoo.com> wrote in message
>news:32b801c4290c$a0a01a90$a501280a@.phx.gbl...
>> Hi Group,
>> For some reasons I have to pull the SQL Server
Production
>> server out of network and reinstall from OS level. I
have
>> few queries about Backing up the SQL server Databases
and
>> choosing cluster size.
>> 1. Do I need to Backup/detach the Master,MSDB databases
>> along with User database to restore again when SQL
server
>> is installed again freshly?. MSDB just has two
maintenance
>> plans running.I fell that I can just take the backups of
>> User databases as I have to restore them on fresh
>> installation.
>> 2. I am creating three arrays on this server
>> C: RAID1 Array ( OS) - Array1
>> D: RAID1 (SQL Tran logs files)- Array2
>> D: RAID5E ( SQL Data files) - Array3
>> While installing SQLServer I will choose Array3 for SQL
>> system databases. How do i move the master.ldf msdb.ldf
>> model.ldf temp.ldf to Array2 ? does detach/attach work
for
>> system databses?
>> 3. I am choosing 8KB Cluster on NTFS instead of Default
>> 4KB cluster, does this help in enhancing the access of
8K
>> SQL database pages in terms of I/O?
>> 4. Are there any patches/fixes released after
>> SQLServer2000 Service Pack 3a?
>> OS Windows 2000 Server SP4
>> SQL Server 2000 Standard Edition
>> IBM x250 dual PIII Xeon 700MHz
>> 4GB RAM
>> 18GB for OS on server
>> 1.4TB on IBM FAStT200 HA + EXP500 expansion.
>> Thanks for your patience
>> Regards
>> Chip
>
>.
>|||Moving databases and database files:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message news:34a801c4292b$29db0a90$a001280a@.phx.gbl...
> Hi Hari,
> Thanks for the help! one more doubt.. When I install SQL
> Server by default it will create Master,MSDB . How do i
> get rid of them and attach the last backed up master
> (.mdf,.ldf), MSDB(.mdf,.ldf).
> I guess I have to login in singleuser mode, detach them,
> overwrite with the backed up files then attach?
> Please give me step by step ( you are always awesome in
> dealing step by step)
> Sincerely
> Chip
> >--Original Message--
> >Hi,
> >
> >1. This link details you the steps to move the system
> and user databases.
> > http://www.support.microsoft.com/?id=224071
> Moving SQL Server
> >Databases to a New Location with Detach/Attach
> > It is always good to move the Master and MSDB
> databases to new server.
> >But it is a must that you should move master database
> > since it stores Server config, Logins
> permissions..If you are
> >notloading master all the database users chanin will be
> lost.
> >
> >2. For Master database after starting sql server using
> trace flag "-T3608"
> >and use sp_detach_db and then copy to new drive and use
> > sp_attach_db to attach Master database. For Tempdb
> use ALTER DATBASE ..
> >MODIFY FILE option. Both methodologies are detailed
> > in the above link
> >
> >3. This represent the smallest amount of disk space
> allocated to hold a
> >file.
> > I feel this might not reduce your I/O.
> >
> >
> >4. No, Sp3a is the latest service pack.
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >
> >"Chip" <Chipsin007@.yahoo.com> wrote in message
> >news:32b801c4290c$a0a01a90$a501280a@.phx.gbl...
> >> Hi Group,
> >>
> >> For some reasons I have to pull the SQL Server
> Production
> >> server out of network and reinstall from OS level. I
> have
> >> few queries about Backing up the SQL server Databases
> and
> >> choosing cluster size.
> >>
> >> 1. Do I need to Backup/detach the Master,MSDB databases
> >> along with User database to restore again when SQL
> server
> >> is installed again freshly?. MSDB just has two
> maintenance
> >> plans running.I fell that I can just take the backups of
> >> User databases as I have to restore them on fresh
> >> installation.
> >>
> >> 2. I am creating three arrays on this server
> >> C: RAID1 Array ( OS) - Array1
> >> D: RAID1 (SQL Tran logs files)- Array2
> >> D: RAID5E ( SQL Data files) - Array3
> >>
> >> While installing SQLServer I will choose Array3 for SQL
> >> system databases. How do i move the master.ldf msdb.ldf
> >> model.ldf temp.ldf to Array2 ? does detach/attach work
> for
> >> system databses?
> >>
> >> 3. I am choosing 8KB Cluster on NTFS instead of Default
> >> 4KB cluster, does this help in enhancing the access of
> 8K
> >> SQL database pages in terms of I/O?
> >>
> >> 4. Are there any patches/fixes released after
> >> SQLServer2000 Service Pack 3a?
> >>
> >> OS Windows 2000 Server SP4
> >> SQL Server 2000 Standard Edition
> >> IBM x250 dual PIII Xeon 700MHz
> >> 4GB RAM
> >> 18GB for OS on server
> >> 1.4TB on IBM FAStT200 HA + EXP500 expansion.
> >>
> >> Thanks for your patience
> >>
> >> Regards
> >>
> >> Chip
> >
> >
> >.
> >|||Hi Tibor,
I have all the links you posted. In fact I saved them from
one of your post. What I was asking is.. When I install a
SQL Server, It will create Master, MSDB databases. How do
I get rid of them to restore/attach the backed up Master &
MSDB .
Regards
Chip
>--Original Message--
>Moving databases and database files:
>
>Moving SQL Server Databases
>http://www.support.microsoft.com/?id=224071
>Moving Databases between Servers
>http://www.support.microsoft.com/?id=314546
>Using WITH MOVE in a Restore to a New Location with
Detach/Attach
>http://support.microsoft.com/?id=221465
>How To Transfer Logins and Passwords Between SQL Servers
>http://www.support.microsoft.com/?id=246133
>Mapping Logins & SIDs after a Restore
>http://www.support.microsoft.com/?id=298897
>Utility to map users to the correct login
>http://www.dbmaint.com/SyncSqlLogins.asp
>How to Resolve Permission Issues When a Database Is Moved
Between SQL Servers
>http://www.support.microsoft.com/?id=240872
>User Logon and/or Permission Errors After Restoring Dump
>http://www.support.microsoft.com/?id=168001
>Disaster Recovery Articles for SQL Server
>http://www.support.microsoft.com/?id=307775
>
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message news:34a801c4292b$29db0a90$a001280a@.phx.gbl...
>> Hi Hari,
>> Thanks for the help! one more doubt.. When I install SQL
>> Server by default it will create Master,MSDB . How do i
>> get rid of them and attach the last backed up master
>> (.mdf,.ldf), MSDB(.mdf,.ldf).
>> I guess I have to login in singleuser mode, detach them,
>> overwrite with the backed up files then attach?
>> Please give me step by step ( you are always awesome in
>> dealing step by step)
>> Sincerely
>> Chip
>> >--Original Message--
>> >Hi,
>> >
>> >1. This link details you the steps to move the system
>> and user databases.
>> > http://www.support.microsoft.com/?id=224071
>> Moving SQL Server
>> >Databases to a New Location with Detach/Attach
>> > It is always good to move the Master and MSDB
>> databases to new server.
>> >But it is a must that you should move master database
>> > since it stores Server config, Logins
>> permissions..If you are
>> >notloading master all the database users chanin will be
>> lost.
>> >
>> >2. For Master database after starting sql server using
>> trace flag "-T3608"
>> >and use sp_detach_db and then copy to new drive and use
>> > sp_attach_db to attach Master database. For Tempdb
>> use ALTER DATBASE ..
>> >MODIFY FILE option. Both methodologies are detailed
>> > in the above link
>> >
>> >3. This represent the smallest amount of disk space
>> allocated to hold a
>> >file.
>> > I feel this might not reduce your I/O.
>> >
>> >
>> >4. No, Sp3a is the latest service pack.
>> >
>> >Thanks
>> >Hari
>> >MCDBA
>> >
>> >
>> >
>> >"Chip" <Chipsin007@.yahoo.com> wrote in message
>> >news:32b801c4290c$a0a01a90$a501280a@.phx.gbl...
>> >> Hi Group,
>> >>
>> >> For some reasons I have to pull the SQL Server
>> Production
>> >> server out of network and reinstall from OS level. I
>> have
>> >> few queries about Backing up the SQL server Databases
>> and
>> >> choosing cluster size.
>> >>
>> >> 1. Do I need to Backup/detach the Master,MSDB
databases
>> >> along with User database to restore again when SQL
>> server
>> >> is installed again freshly?. MSDB just has two
>> maintenance
>> >> plans running.I fell that I can just take the
backups of
>> >> User databases as I have to restore them on fresh
>> >> installation.
>> >>
>> >> 2. I am creating three arrays on this server
>> >> C: RAID1 Array ( OS) - Array1
>> >> D: RAID1 (SQL Tran logs files)- Array2
>> >> D: RAID5E ( SQL Data files) - Array3
>> >>
>> >> While installing SQLServer I will choose Array3 for
SQL
>> >> system databases. How do i move the master.ldf
msdb.ldf
>> >> model.ldf temp.ldf to Array2 ? does detach/attach
work
>> for
>> >> system databses?
>> >>
>> >> 3. I am choosing 8KB Cluster on NTFS instead of
Default
>> >> 4KB cluster, does this help in enhancing the access
of
>> 8K
>> >> SQL database pages in terms of I/O?
>> >>
>> >> 4. Are there any patches/fixes released after
>> >> SQLServer2000 Service Pack 3a?
>> >>
>> >> OS Windows 2000 Server SP4
>> >> SQL Server 2000 Standard Edition
>> >> IBM x250 dual PIII Xeon 700MHz
>> >> 4GB RAM
>> >> 18GB for OS on server
>> >> 1.4TB on IBM FAStT200 HA + EXP500 expansion.
>> >>
>> >> Thanks for your patience
>> >>
>> >> Regards
>> >>
>> >> Chip
>> >
>> >
>> >.
>> >
>
>.
>|||That should be in the articles. You can restore MSDB as well as master. For master, you need to be in single
user mode in order to do a restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message news:3a2b01c429a2$ac8eb190$a001280a@.phx.gbl...
> Hi Tibor,
> I have all the links you posted. In fact I saved them from
> one of your post. What I was asking is.. When I install a
> SQL Server, It will create Master, MSDB databases. How do
> I get rid of them to restore/attach the backed up Master &
> MSDB .
> Regards
> Chip
> >--Original Message--
> >Moving databases and database files:
> >
> >
> >Moving SQL Server Databases
> >http://www.support.microsoft.com/?id=224071
> >
> >Moving Databases between Servers
> >http://www.support.microsoft.com/?id=314546
> >
> >Using WITH MOVE in a Restore to a New Location with
> Detach/Attach
> >http://support.microsoft.com/?id=221465
> >
> >How To Transfer Logins and Passwords Between SQL Servers
> >http://www.support.microsoft.com/?id=246133
> >
> >Mapping Logins & SIDs after a Restore
> >http://www.support.microsoft.com/?id=298897
> >
> >Utility to map users to the correct login
> >http://www.dbmaint.com/SyncSqlLogins.asp
> >
> >How to Resolve Permission Issues When a Database Is Moved
> Between SQL Servers
> >http://www.support.microsoft.com/?id=240872
> >
> >User Logon and/or Permission Errors After Restoring Dump
> >http://www.support.microsoft.com/?id=168001
> >
> >Disaster Recovery Articles for SQL Server
> >http://www.support.microsoft.com/?id=307775
> >
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >
> >
> >"Chip" <anonymous@.discussions.microsoft.com> wrote in
> message news:34a801c4292b$29db0a90$a001280a@.phx.gbl...
> >> Hi Hari,
> >>
> >> Thanks for the help! one more doubt.. When I install SQL
> >> Server by default it will create Master,MSDB . How do i
> >> get rid of them and attach the last backed up master
> >> (.mdf,.ldf), MSDB(.mdf,.ldf).
> >>
> >> I guess I have to login in singleuser mode, detach them,
> >> overwrite with the backed up files then attach?
> >>
> >> Please give me step by step ( you are always awesome in
> >> dealing step by step)
> >>
> >> Sincerely
> >>
> >> Chip
> >> >--Original Message--
> >> >Hi,
> >> >
> >> >1. This link details you the steps to move the system
> >> and user databases.
> >> > http://www.support.microsoft.com/?id=224071
> >> Moving SQL Server
> >> >Databases to a New Location with Detach/Attach
> >> > It is always good to move the Master and MSDB
> >> databases to new server.
> >> >But it is a must that you should move master database
> >> > since it stores Server config, Logins
> >> permissions..If you are
> >> >notloading master all the database users chanin will be
> >> lost.
> >> >
> >> >2. For Master database after starting sql server using
> >> trace flag "-T3608"
> >> >and use sp_detach_db and then copy to new drive and use
> >> > sp_attach_db to attach Master database. For Tempdb
> >> use ALTER DATBASE ..
> >> >MODIFY FILE option. Both methodologies are detailed
> >> > in the above link
> >> >
> >> >3. This represent the smallest amount of disk space
> >> allocated to hold a
> >> >file.
> >> > I feel this might not reduce your I/O.
> >> >
> >> >
> >> >4. No, Sp3a is the latest service pack.
> >> >
> >> >Thanks
> >> >Hari
> >> >MCDBA
> >> >
> >> >
> >> >
> >> >"Chip" <Chipsin007@.yahoo.com> wrote in message
> >> >news:32b801c4290c$a0a01a90$a501280a@.phx.gbl...
> >> >> Hi Group,
> >> >>
> >> >> For some reasons I have to pull the SQL Server
> >> Production
> >> >> server out of network and reinstall from OS level. I
> >> have
> >> >> few queries about Backing up the SQL server Databases
> >> and
> >> >> choosing cluster size.
> >> >>
> >> >> 1. Do I need to Backup/detach the Master,MSDB
> databases
> >> >> along with User database to restore again when SQL
> >> server
> >> >> is installed again freshly?. MSDB just has two
> >> maintenance
> >> >> plans running.I fell that I can just take the
> backups of
> >> >> User databases as I have to restore them on fresh
> >> >> installation.
> >> >>
> >> >> 2. I am creating three arrays on this server
> >> >> C: RAID1 Array ( OS) - Array1
> >> >> D: RAID1 (SQL Tran logs files)- Array2
> >> >> D: RAID5E ( SQL Data files) - Array3
> >> >>
> >> >> While installing SQLServer I will choose Array3 for
> SQL
> >> >> system databases. How do i move the master.ldf
> msdb.ldf
> >> >> model.ldf temp.ldf to Array2 ? does detach/attach
> work
> >> for
> >> >> system databses?
> >> >>
> >> >> 3. I am choosing 8KB Cluster on NTFS instead of
> Default
> >> >> 4KB cluster, does this help in enhancing the access
> of
> >> 8K
> >> >> SQL database pages in terms of I/O?
> >> >>
> >> >> 4. Are there any patches/fixes released after
> >> >> SQLServer2000 Service Pack 3a?
> >> >>
> >> >> OS Windows 2000 Server SP4
> >> >> SQL Server 2000 Standard Edition
> >> >> IBM x250 dual PIII Xeon 700MHz
> >> >> 4GB RAM
> >> >> 18GB for OS on server
> >> >> 1.4TB on IBM FAStT200 HA + EXP500 expansion.
> >> >>
> >> >> Thanks for your patience
> >> >>
> >> >> Regards
> >> >>
> >> >> Chip
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
sql
