"Boxington Headmaker" wrote:

>
> "SQLpro [MVP]" <broua***@club-internet.fr> wrote in message
> news:OVJwz2bSGHA.5924@TK2MSFTNGP09.phx.gbl...
> > Boxington Headmaker a écrit :
> > sp_detach is a SQL Server stored procedure thant can just dettach the
> > files of the database.
> > The syntax is simple and you will find it with examples in MS SQL Server
> > help.
>
> Okay, thanks.
>
> >
> > in SQL Server Entreprise Manager you can obtain the SQL Script for all
> > objects.
> > On the database clic right, go to all task / SQL script and check
> > connexion/user
> > this will write the script.
> >
> > To pass the script just open wuery analyser, paste it and execute.
> >
>
> Thanks.
>
> >
> > no !!!! ;-) In french A+ mean good by !!!!
> >
> >
> > By !
> >
>
> Au Revoir :-)
> Box
>
>
>

"Boxington Headmaker" wrote:

> I'm running SQL 2000 fully service packed, on Windows 2000, also fully
> patched.
>
> The databases, log files and several Gb of backups are all stored on a
> compressed partition of a raid 5 array.  The partition is showing severe
> fragmentation >90% file frags., so much so that defrag refuses to run
> properly even though there is over 60% free space on the drive
> (kbid=294950).  The Raid array also holds a system volume which is not
> fragmented, and has some free space.
>
> My plan to fix the problem is as follows:
>
> 1) Backup all the existing databases from Enterprise manager to the system
> volume.
> 2) Shut down SQL server, and the Agent.
> 3) Copy all the database files (ldf mdf etc.) to the system volume.
> 4) Backup to tape the datafiles, and the backups (plus some older ones.)
> 5) Reformat the volume, removing compression.
> 6) Recreate the file structure.
> 7) Copy the database files, and backups back to the fresh volume.
> 8) Restart SQL Server
>
> Does this seem sensible?
>
> My only concern is that in deleting and recreating the file structure I will
> lose the security permissions.  SQL would have been installed under the
> Admin account, do I need to add any special access rights?
>
> Many thanks,
> Box
>
>
>

"Boxington Headmaker" wrote:

> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:9D62436B-7BA3-4783-8A92-F2C6C0894224@microsoft.com...
> > Hi
> >
> > Using a compressed volume for SQL Server is not recommended. If this was
> > not
> > compressed you could have defragged the disc whilst SQL Server was not
> > running without going through the process of backing up/restoring.
>
> That's one of the reasons that I'm formating, not only to lose the
> fragmentation, but also the compression.
>
> > You should take a SQL Server backup and copy them onto tape for all
> > databases before you start.
>
> Will do.
>
> > You may then want to consider detaching each user database after that and
> > re-backing up the master database. This would allow you to re-attach the
> > databases in a different location, so you have the option of changing the
> > directory structures. If it is only mean only the minimum number of
> > (system)
> > databases are available when you bring the system online. Make sure you
> > re-attach the database so the get the same Ids as previously.
>
> The files will be going back to their original locations.
>
> > You may want to think about moving tempdb and the system databases to
> > different spindles if you have the option, hope fully your log files are
> > on a
> > different spindles.
>
> Unfortunatley not, the server only has one drive, it a raid 5 array of 5 x
> 75Gb disks, giving me 300Gb.  Its partitioned into 2 volumes, one for the
> system files, and one for the databases and backups (these are staged here,
> and moved to tape backup and offsite wan backup).
>
> > If you will need to make sure that the service account that runs SQL
> > Server
> > has read/write permissions to the database files and enough to get there!
>
> How would I check which service account SQL is using?
>
> > John
>
> Many thanks for your input,
> Box
>
>
>