required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:

> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data file
s
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW.
So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do
I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSD
B
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making th
e
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over t
he
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>