Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

Database Moves SPS2003

  Asked By: Larissa    Date: Apr 15    Category: Sharepoint    Views: 791

I'm running out of disk space and need to move data to a larger
partition just acquired.
I need to move the _PROF, _SERV, _SITE, _ConfigDB and 3 other content
databases to the larger partition. Is it as simple backing up the SQL
databases, stopping the SharePoint services, detaching the databases,
copy the database to the new partition then reattaching and starting
the services? I have no SQL DBA available and can't seem to find much
as far as moving to a new partition. Feedback please?



3 Answers Found

Answer #1    Answered By: Trevor Davis     Answered On: Apr 15

If you backup (using the SQL backup dumps to bak files), you can restore
those to files another partition.

Then delete the portal site and then restore the portal site with the files.
I don't think you really need the config db, unless you don't know your sps

Answer #2    Answered By: Kristie Hardy     Answered On: Apr 15

why are you running  out of space?
Are tranasction log files (.ldf) getting very large? I had that problem
initialy, where SITE transacttion log grew to 135 gigabytes...

Fortunately, I do have a DBA available. After consulting with him, he set up
jobs that backup logs 6 times a day and truncates them on a nightly job.

here is an example of something you can reference:
(google for "NOSKIP , STATS = 10, NOFORMAT" and you will get many sites)

and to put it more in perspective of SharePoint, on a nightly maintenance
job, syntax looks like this:

Transact-SQL script (TSQL):

exec xp_cmdshell 'del "E:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\CompaPor1_SITE.pre"' exec xp_cmdshell 'rename
"E:\Program Files\Microsoft sql  Server\MSSQL\BACKUP\CompaPor1_SITE.BAK"
CompaPor1_SITE.pre' BACKUP database  [CompaPor1_SITE] TO DISK = N'E:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\CompaPor1_SITE.BAK' WITH INIT ,
NOUNLOAD , NAME = N'Nightly Database Backups', NOSKIP , STATS = 10,

Also there is a Rename transaction log job that consists of 2 Steps
1 - Delete Old Transaction Logs syntax:

exec xp_cmdshell 'del "E:\Program Files\Microsoft SQL

2 - Rename Yesterdays Transaction Log Backups syntax:

exec xp_cmdshell 'rename "E:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\tlog*.BAK" tlog*.pre"'

on a "every 2 hours" maintenance jobs that include 4 steps for 4 core
SharePoint DBs syntax is:

BACKUP LOG [CompaPor1_PROF] TO DISK = N'E:\Program Files\Microsoft SQL
NAME = N'Transaction Log Backups - 10 AM', NOSKIP , STATS = 10, NOFORMAT

same syntax for 4 steps, with adjustments for the DB names.

This could be setup in Enterprise Manager - Management - Jobs.

Even if it is not your problem, this is certainly a best practice scenario
that is not really documented anywhere (that I could find) in SharePoint

Answer #3    Answered By: Shayla Mcbride     Answered On: Apr 15

I also moved the DB to the new partition, do like this:
-take the backup of sharepoint  via sharepoint backup utility (just in
case if anything goes wrong) also take backup of your SITE DB, SERV DB,
-stop all sharepoint services
-right click on one of the DB e.g. SITE DB and from properties menu see
its physical file existence remember it
-detach DB such as SITE DB, SERV DB, and PROF DB.
-go to that physical location where the data  files are located at
-select .mdf and .ldf files for SITE DB, SERV DB, and PROF DB and copy
them to the new partition. (DON'T cut these files until because
sometimes in moving  files get corrupted, and this can happen as these
files size would be too huge)
-go to the enterprise manager and attach those db again.
-now restart your machine, every sharepoint service would start
-after restart hit the server, and browse it would be done.

Didn't find what you were looking for? Find more on Database Moves SPS2003 Or get search suggestion and latest updates.