Hi all,
I have a query connecting users to my products by and user groups and
today I added a nonclustered index to my table the one that maps users
to user groups (added a nonclusted index to both userID and UserGroups
in ASC order). About 6 hours later our CPU usage went from .01 - .10%
to 100+%? So I removed it and disable some parts of my SQL to
improve the speed. What's happening here? Please help? Would
removing the index change my table back to it's previous index'
please helpppppppppppppppp...
Thx
MStill UPDATE STATISTICS.
"mazdotnet" <maflatoun@.gmail.com> wrote in message
news:1194993911.116978.257150@.v2g2000hsf.googlegroups.com...
> Hi all,
> I have a query connecting users to my products by and user groups and
> today I added a nonclustered index to my table the one that maps users
> to user groups (added a nonclusted index to both userID and UserGroups
> in ASC order). About 6 hours later our CPU usage went from .01 - .10%
> to 100+%? So I removed it and disable some parts of my SQL to
> improve the speed. What's happening here? Please help? Would
> removing the index change my table back to it's previous index'
> please helpppppppppppppppp...
>
> Thx
> M
>|||Can you show us the query + info about the indexes?
"mazdotnet" <maflatoun@.gmail.com> wrote in message
news:1194993911.116978.257150@.v2g2000hsf.googlegroups.com...
> Hi all,
> I have a query connecting users to my products by and user groups and
> today I added a nonclustered index to my table the one that maps users
> to user groups (added a nonclusted index to both userID and UserGroups
> in ASC order). About 6 hours later our CPU usage went from .01 - .10%
> to 100+%? So I removed it and disable some parts of my SQL to
> improve the speed. What's happening here? Please help? Would
> removing the index change my table back to it's previous index'
> please helpppppppppppppppp...
>
> Thx
> M
>
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Please help > Problem restoring database
We have 2 database on a production server running sql express 2005, the first one is 200mb and the other one is 1.3gig.
We make backup under sql management studio using option full.
When we try to recover database on another server the 200mb database restore perfectly, the 1.3 gig
always fail with the following message :
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The file "database_name" failed to initialize correctly. Examine the error logs for more details. (Microsoft.SqlServer.Express.Smo)
We tried dbcc checkdb and everything seems alright.
I updated the statistics, no change.
I switched the recovery model from full to simple, no change.
I tried using "restore database from disk='' with norecovery, same error message
We tried stopping the production server then copying the file to the other server and attaching and it still fail with the following message :
An error occured when attaching the database(s). >
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database_Data.MDF". Operating system error 8: "8(error not found)". (Microsoft SQL Server, Error: 5120)
We dont know what to try anymore, the goal is to be sure our backup are alright....
Please helppppp ! thank you.
sorry for my bad english...
Ok, I uninstalled avg antivirus, then free my disk drive to have 10gig free.
restored my backup and it completed successfully !!!!
thank you for your help rmiaosql
Monday, March 26, 2012
Please help - Transaction Log not shrinking!
Hi,
My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
yesterday). DBCC Reindex was issued against all tables as part of
monthly DB maintenance. Since then I have performed a full DB backup,
have performed log backups, then performed log backup with truncate
only option, then reissued the DBCC Shrinkfile command and tried to
shrink the log file via SSMS - nothing happens. The transaction log
file is still over 25 GBs.
This is in SQL Server 2005. I've even changed the recovery model to
Simple, issued the DBCC Shrinkfile - no change, and then switched back
to FULL - still no change.
What am I missing here?First read about backup, and recovery model in Books Online. Then check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups.com...
> Hi,
> My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> yesterday). DBCC Reindex was issued against all tables as part of
> monthly DB maintenance. Since then I have performed a full DB backup,
> have performed log backups, then performed log backup with truncate
> only option, then reissued the DBCC Shrinkfile command and tried to
> shrink the log file via SSMS - nothing happens. The transaction log
> file is still over 25 GBs.
> This is in SQL Server 2005. I've even changed the recovery model to
> Simple, issued the DBCC Shrinkfile - no change, and then switched back
> to FULL - still no change.
> What am I missing here?
>|||Try this:
USE MASTER
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
GO
USE YourDatabase
DBCC SHRINKFILE (YourDatabase _log, 1000)
This should shrink the log file to 1GB
Ozcan
"Tibor Karaszi" wrote:
> First read about backup, and recovery model in Books Online. Then check out
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups.com...
> > Hi,
> >
> > My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> > yesterday). DBCC Reindex was issued against all tables as part of
> > monthly DB maintenance. Since then I have performed a full DB backup,
> > have performed log backups, then performed log backup with truncate
> > only option, then reissued the DBCC Shrinkfile command and tried to
> > shrink the log file via SSMS - nothing happens. The transaction log
> > file is still over 25 GBs.
> >
> > This is in SQL Server 2005. I've even changed the recovery model to
> > Simple, issued the DBCC Shrinkfile - no change, and then switched back
> > to FULL - still no change.
> >
> > What am I missing here?
> >
>
My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
yesterday). DBCC Reindex was issued against all tables as part of
monthly DB maintenance. Since then I have performed a full DB backup,
have performed log backups, then performed log backup with truncate
only option, then reissued the DBCC Shrinkfile command and tried to
shrink the log file via SSMS - nothing happens. The transaction log
file is still over 25 GBs.
This is in SQL Server 2005. I've even changed the recovery model to
Simple, issued the DBCC Shrinkfile - no change, and then switched back
to FULL - still no change.
What am I missing here?First read about backup, and recovery model in Books Online. Then check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups.com...
> Hi,
> My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> yesterday). DBCC Reindex was issued against all tables as part of
> monthly DB maintenance. Since then I have performed a full DB backup,
> have performed log backups, then performed log backup with truncate
> only option, then reissued the DBCC Shrinkfile command and tried to
> shrink the log file via SSMS - nothing happens. The transaction log
> file is still over 25 GBs.
> This is in SQL Server 2005. I've even changed the recovery model to
> Simple, issued the DBCC Shrinkfile - no change, and then switched back
> to FULL - still no change.
> What am I missing here?
>|||Try this:
USE MASTER
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
GO
USE YourDatabase
DBCC SHRINKFILE (YourDatabase _log, 1000)
This should shrink the log file to 1GB
Ozcan
"Tibor Karaszi" wrote:
> First read about backup, and recovery model in Books Online. Then check out
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups.com...
> > Hi,
> >
> > My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> > yesterday). DBCC Reindex was issued against all tables as part of
> > monthly DB maintenance. Since then I have performed a full DB backup,
> > have performed log backups, then performed log backup with truncate
> > only option, then reissued the DBCC Shrinkfile command and tried to
> > shrink the log file via SSMS - nothing happens. The transaction log
> > file is still over 25 GBs.
> >
> > This is in SQL Server 2005. I've even changed the recovery model to
> > Simple, issued the DBCC Shrinkfile - no change, and then switched back
> > to FULL - still no change.
> >
> > What am I missing here?
> >
>
Please help - Transaction Log not shrinking!
Hi,
My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
yesterday). DBCC Reindex was issued against all tables as part of
monthly DB maintenance. Since then I have performed a full DB backup,
have performed log backups, then performed log backup with truncate
only option, then reissued the DBCC Shrinkfile command and tried to
shrink the log file via SSMS - nothing happens. The transaction log
file is still over 25 GBs.
This is in SQL Server 2005. I've even changed the recovery model to
Simple, issued the DBCC Shrinkfile - no change, and then switched back
to FULL - still no change.
What am I missing here?First read about backup, and recovery model in Books Online. Then check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups
.com...
> Hi,
> My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> yesterday). DBCC Reindex was issued against all tables as part of
> monthly DB maintenance. Since then I have performed a full DB backup,
> have performed log backups, then performed log backup with truncate
> only option, then reissued the DBCC Shrinkfile command and tried to
> shrink the log file via SSMS - nothing happens. The transaction log
> file is still over 25 GBs.
> This is in SQL Server 2005. I've even changed the recovery model to
> Simple, issued the DBCC Shrinkfile - no change, and then switched back
> to FULL - still no change.
> What am I missing here?
>|||First read about backup, and recovery model in Books Online. Then check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups
.com...
> Hi,
> My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> yesterday). DBCC Reindex was issued against all tables as part of
> monthly DB maintenance. Since then I have performed a full DB backup,
> have performed log backups, then performed log backup with truncate
> only option, then reissued the DBCC Shrinkfile command and tried to
> shrink the log file via SSMS - nothing happens. The transaction log
> file is still over 25 GBs.
> This is in SQL Server 2005. I've even changed the recovery model to
> Simple, issued the DBCC Shrinkfile - no change, and then switched back
> to FULL - still no change.
> What am I missing here?
>|||Try this:
USE MASTER
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
GO
USE YourDatabase
DBCC SHRINKFILE (YourDatabase _log, 1000)
This should shrink the log file to 1GB
Ozcan
"Tibor Karaszi" wrote:
> First read about backup, and recovery model in Books Online. Then check ou
t
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g20
00cwx.googlegroups.com...
>|||Try this:
USE MASTER
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
GO
USE YourDatabase
DBCC SHRINKFILE (YourDatabase _log, 1000)
This should shrink the log file to 1GB
Ozcan
"Tibor Karaszi" wrote:
> First read about backup, and recovery model in Books Online. Then check ou
t
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g20
00cwx.googlegroups.com...
>sql
My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
yesterday). DBCC Reindex was issued against all tables as part of
monthly DB maintenance. Since then I have performed a full DB backup,
have performed log backups, then performed log backup with truncate
only option, then reissued the DBCC Shrinkfile command and tried to
shrink the log file via SSMS - nothing happens. The transaction log
file is still over 25 GBs.
This is in SQL Server 2005. I've even changed the recovery model to
Simple, issued the DBCC Shrinkfile - no change, and then switched back
to FULL - still no change.
What am I missing here?First read about backup, and recovery model in Books Online. Then check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups
.com...
> Hi,
> My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> yesterday). DBCC Reindex was issued against all tables as part of
> monthly DB maintenance. Since then I have performed a full DB backup,
> have performed log backups, then performed log backup with truncate
> only option, then reissued the DBCC Shrinkfile command and tried to
> shrink the log file via SSMS - nothing happens. The transaction log
> file is still over 25 GBs.
> This is in SQL Server 2005. I've even changed the recovery model to
> Simple, issued the DBCC Shrinkfile - no change, and then switched back
> to FULL - still no change.
> What am I missing here?
>|||First read about backup, and recovery model in Books Online. Then check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g2000cwx.googlegroups
.com...
> Hi,
> My production DB transaction log has grown to 25 GBs. (It was <8 Gbs
> yesterday). DBCC Reindex was issued against all tables as part of
> monthly DB maintenance. Since then I have performed a full DB backup,
> have performed log backups, then performed log backup with truncate
> only option, then reissued the DBCC Shrinkfile command and tried to
> shrink the log file via SSMS - nothing happens. The transaction log
> file is still over 25 GBs.
> This is in SQL Server 2005. I've even changed the recovery model to
> Simple, issued the DBCC Shrinkfile - no change, and then switched back
> to FULL - still no change.
> What am I missing here?
>|||Try this:
USE MASTER
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
GO
USE YourDatabase
DBCC SHRINKFILE (YourDatabase _log, 1000)
This should shrink the log file to 1GB
Ozcan
"Tibor Karaszi" wrote:
> First read about backup, and recovery model in Books Online. Then check ou
t
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g20
00cwx.googlegroups.com...
>|||Try this:
USE MASTER
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
GO
USE YourDatabase
DBCC SHRINKFILE (YourDatabase _log, 1000)
This should shrink the log file to 1GB
Ozcan
"Tibor Karaszi" wrote:
> First read about backup, and recovery model in Books Online. Then check ou
t
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <sqlQuest@.gmail.com> wrote in message news:1151589347.687402.206610@.x69g20
00cwx.googlegroups.com...
>sql
Wednesday, March 21, 2012
Please answer my queries for fresh Installation
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
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
Monday, March 12, 2012
Placement of datafiles and log files
This is my first sql server app so I'm pretty ignorant.
In my production environment I have a server with 4
processors and 4 drives and is part of a SANS network. 2
drives are mirrorred to be c: and two drives are mirrorred
to be d:, both RAID1. I also have another drive (T:)
available for my app. I have my datafiles for this app on
the t: drive which is RAID5 and I have my log files on
drive d:. Is this the recommended practice. I know that
I read that I need to separate my data files and log
files. Please advise.
Thanks,
EdieThat seems like a reasonable setup. To be able to give more precise
suggestions, we'd need I/O metrics for the current config. I suggest you
keep it like it is unless you have performance problems and are willing to
spend some time on the issue.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Edie Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
> This is my first sql server app so I'm pretty ignorant.
> In my production environment I have a server with 4
> processors and 4 drives and is part of a SANS network. 2
> drives are mirrorred to be c: and two drives are mirrorred
> to be d:, both RAID1. I also have another drive (T:)
> available for my app. I have my datafiles for this app on
> the t: drive which is RAID5 and I have my log files on
> drive d:. Is this the recommended practice. I know that
> I read that I need to separate my data files and log
> files. Please advise.
> Thanks,
> Edie|||Thanks. If I were to add more databases to that instances
of sql server, would it be a good practice to put all my
log files on that one D Drive?
>--Original Message--
>That seems like a reasonable setup. To be able to give
more precise
>suggestions, we'd need I/O metrics for the current
config. I suggest you
>keep it like it is unless you have performance problems
and are willing to
>spend some time on the issue.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Edie Richardson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
>> This is my first sql server app so I'm pretty ignorant.
>> In my production environment I have a server with 4
>> processors and 4 drives and is part of a SANS network.
2
>> drives are mirrorred to be c: and two drives are
mirrorred
>> to be d:, both RAID1. I also have another drive (T:)
>> available for my app. I have my datafiles for this app
on
>> the t: drive which is RAID5 and I have my log files on
>> drive d:. Is this the recommended practice. I know
that
>> I read that I need to separate my data files and log
>> files. Please advise.
>> Thanks,
>> Edie
>
>.
>|||Yes, I think so. Ideally, each database should have it's on drive for the
log, but sometimes that is not practical. Isolating other stuff from the
drive where you have a number of databases log files is still better than
mix 'n match, IMO.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:000301c3c011$770047f0$a501280a@.phx.gbl...
> Thanks. If I were to add more databases to that instances
> of sql server, would it be a good practice to put all my
> log files on that one D Drive?
> >--Original Message--
> >That seems like a reasonable setup. To be able to give
> more precise
> >suggestions, we'd need I/O metrics for the current
> config. I suggest you
> >keep it like it is unless you have performance problems
> and are willing to
> >spend some time on the issue.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Edie Richardson" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
> >> This is my first sql server app so I'm pretty ignorant.
> >> In my production environment I have a server with 4
> >> processors and 4 drives and is part of a SANS network.
> 2
> >> drives are mirrorred to be c: and two drives are
> mirrorred
> >> to be d:, both RAID1. I also have another drive (T:)
> >> available for my app. I have my datafiles for this app
> on
> >> the t: drive which is RAID5 and I have my log files on
> >> drive d:. Is this the recommended practice. I know
> that
> >> I read that I need to separate my data files and log
> >> files. Please advise.
> >>
> >> Thanks,
> >> Edie
> >
> >
> >.
> >
In my production environment I have a server with 4
processors and 4 drives and is part of a SANS network. 2
drives are mirrorred to be c: and two drives are mirrorred
to be d:, both RAID1. I also have another drive (T:)
available for my app. I have my datafiles for this app on
the t: drive which is RAID5 and I have my log files on
drive d:. Is this the recommended practice. I know that
I read that I need to separate my data files and log
files. Please advise.
Thanks,
EdieThat seems like a reasonable setup. To be able to give more precise
suggestions, we'd need I/O metrics for the current config. I suggest you
keep it like it is unless you have performance problems and are willing to
spend some time on the issue.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Edie Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
> This is my first sql server app so I'm pretty ignorant.
> In my production environment I have a server with 4
> processors and 4 drives and is part of a SANS network. 2
> drives are mirrorred to be c: and two drives are mirrorred
> to be d:, both RAID1. I also have another drive (T:)
> available for my app. I have my datafiles for this app on
> the t: drive which is RAID5 and I have my log files on
> drive d:. Is this the recommended practice. I know that
> I read that I need to separate my data files and log
> files. Please advise.
> Thanks,
> Edie|||Thanks. If I were to add more databases to that instances
of sql server, would it be a good practice to put all my
log files on that one D Drive?
>--Original Message--
>That seems like a reasonable setup. To be able to give
more precise
>suggestions, we'd need I/O metrics for the current
config. I suggest you
>keep it like it is unless you have performance problems
and are willing to
>spend some time on the issue.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Edie Richardson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
>> This is my first sql server app so I'm pretty ignorant.
>> In my production environment I have a server with 4
>> processors and 4 drives and is part of a SANS network.
2
>> drives are mirrorred to be c: and two drives are
mirrorred
>> to be d:, both RAID1. I also have another drive (T:)
>> available for my app. I have my datafiles for this app
on
>> the t: drive which is RAID5 and I have my log files on
>> drive d:. Is this the recommended practice. I know
that
>> I read that I need to separate my data files and log
>> files. Please advise.
>> Thanks,
>> Edie
>
>.
>|||Yes, I think so. Ideally, each database should have it's on drive for the
log, but sometimes that is not practical. Isolating other stuff from the
drive where you have a number of databases log files is still better than
mix 'n match, IMO.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:000301c3c011$770047f0$a501280a@.phx.gbl...
> Thanks. If I were to add more databases to that instances
> of sql server, would it be a good practice to put all my
> log files on that one D Drive?
> >--Original Message--
> >That seems like a reasonable setup. To be able to give
> more precise
> >suggestions, we'd need I/O metrics for the current
> config. I suggest you
> >keep it like it is unless you have performance problems
> and are willing to
> >spend some time on the issue.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Edie Richardson" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
> >> This is my first sql server app so I'm pretty ignorant.
> >> In my production environment I have a server with 4
> >> processors and 4 drives and is part of a SANS network.
> 2
> >> drives are mirrorred to be c: and two drives are
> mirrorred
> >> to be d:, both RAID1. I also have another drive (T:)
> >> available for my app. I have my datafiles for this app
> on
> >> the t: drive which is RAID5 and I have my log files on
> >> drive d:. Is this the recommended practice. I know
> that
> >> I read that I need to separate my data files and log
> >> files. Please advise.
> >>
> >> Thanks,
> >> Edie
> >
> >
> >.
> >
placement of Analysis service? on cluster or lone server?
Hi, we have an active/passive cluster which hosts our production database.
Transactional replication is being made from the production to a 'reporting'
database residing on a lone server. We will need to install Analysis service
for the construction of cubes.
I was wondering where Analysis service should be installed?
Is is ok to install it on the lone server and have it use the data from the
reporting database (which is filled via transactional replication)? Will
Analysis service have a problem with this based on the fact that it is
reading a read only database which doesnt have primary keys in the tables?
OR, should analysis service be installed on the cluster? Is it cluster
aware?
At this stage we are thinking of only running the cubes at night to generate
the data for export to reporting service.
Would it then be a good idea to install reporting service on this same lone
server?
Any insight would be most appreciated!
Cheers, john
If that is the case, you can't possibly be running transactional
replication.
The "reporting" database is LOGICALLY read-only, not physically read-only.
This database also MUST have all of the primary keys in place on the tables,
since it is impossible to replicate a table with transactional replication
if the table does not have a primary key. This primary key exists at both
the publisher and the subscriber.
Analysis Services is not going to write to your tables when building cubes,
so it does not care if the tables are physically read-only, logically
read-only, or read-write.
Where you place AS is entirely up to you. You can install it on the
stand-alone server and you can install it in the cluster. The question you
need to ask is what level of availability do you need for AS. If it needs
to be available as much as possible, even through a hardware failure, then
it should be installed in the cluster. If not, it should be installed on
the stand-alone machine.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"john clarke" <jclarke@.nospam.com> wrote in message
news:%23QyI71JDGHA.688@.TK2MSFTNGP11.phx.gbl...
> Hi, we have an active/passive cluster which hosts our production database.
> Transactional replication is being made from the production to a
> 'reporting'
> database residing on a lone server. We will need to install Analysis
> service
> for the construction of cubes.
> I was wondering where Analysis service should be installed?
> Is is ok to install it on the lone server and have it use the data from
> the
> reporting database (which is filled via transactional replication)? Will
> Analysis service have a problem with this based on the fact that it is
> reading a read only database which doesnt have primary keys in the tables?
> OR, should analysis service be installed on the cluster? Is it cluster
> aware?
> At this stage we are thinking of only running the cubes at night to
> generate
> the data for export to reporting service.
> Would it then be a good idea to install reporting service on this same
> lone
> server?
> Any insight would be most appreciated!
> Cheers, john
>
>
Transactional replication is being made from the production to a 'reporting'
database residing on a lone server. We will need to install Analysis service
for the construction of cubes.
I was wondering where Analysis service should be installed?
Is is ok to install it on the lone server and have it use the data from the
reporting database (which is filled via transactional replication)? Will
Analysis service have a problem with this based on the fact that it is
reading a read only database which doesnt have primary keys in the tables?
OR, should analysis service be installed on the cluster? Is it cluster
aware?
At this stage we are thinking of only running the cubes at night to generate
the data for export to reporting service.
Would it then be a good idea to install reporting service on this same lone
server?
Any insight would be most appreciated!
Cheers, john
If that is the case, you can't possibly be running transactional
replication.
The "reporting" database is LOGICALLY read-only, not physically read-only.
This database also MUST have all of the primary keys in place on the tables,
since it is impossible to replicate a table with transactional replication
if the table does not have a primary key. This primary key exists at both
the publisher and the subscriber.
Analysis Services is not going to write to your tables when building cubes,
so it does not care if the tables are physically read-only, logically
read-only, or read-write.
Where you place AS is entirely up to you. You can install it on the
stand-alone server and you can install it in the cluster. The question you
need to ask is what level of availability do you need for AS. If it needs
to be available as much as possible, even through a hardware failure, then
it should be installed in the cluster. If not, it should be installed on
the stand-alone machine.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"john clarke" <jclarke@.nospam.com> wrote in message
news:%23QyI71JDGHA.688@.TK2MSFTNGP11.phx.gbl...
> Hi, we have an active/passive cluster which hosts our production database.
> Transactional replication is being made from the production to a
> 'reporting'
> database residing on a lone server. We will need to install Analysis
> service
> for the construction of cubes.
> I was wondering where Analysis service should be installed?
> Is is ok to install it on the lone server and have it use the data from
> the
> reporting database (which is filled via transactional replication)? Will
> Analysis service have a problem with this based on the fact that it is
> reading a read only database which doesnt have primary keys in the tables?
> OR, should analysis service be installed on the cluster? Is it cluster
> aware?
> At this stage we are thinking of only running the cubes at night to
> generate
> the data for export to reporting service.
> Would it then be a good idea to install reporting service on this same
> lone
> server?
> Any insight would be most appreciated!
> Cheers, john
>
>
Subscribe to:
Posts (Atom)