Copy DB to new drive (1 Viewer)

[FoFa]

For a SQL 2000 DB:
I would like a batch file I can schedule;
To disconnect any connection/s (or put it in single user mode, or whatever).
Dettached the DB
copy the DB and LOG files to a new drive
attached the DB from the new drive
verify it is back up

Anyone know how to do that?
I'll manually delete the old DB/LOG files after I am sure it is good.

 

[SQL_Hell]

Hi,

I would create a job with the following steps

step1. clear connection /detach

ALTER DATABASE <dbname>
SET SINGLE_USER
go
EXEC sp_detach_db 'dbname', 'true'

step2. copy files

EXEC xp_cmdshell 'copy c:\sql\db.mdf g:\sql\ ,
NO_OUTPUT

step3. attach db

EXEC sp_attach_db @dbname = N'dbname',
@filename1 = N'g:\sql\db.mdf',
@filename2 = N'g:\sql\db_log.ldf'
go
ALTER DATABASE <dbname>
SET multi_User


Obviously you will need to change file and db names to suit, but this should give you a good idea of what to do

 

[SQL_Hell]

Hi Fofa

Did this help at all?

 

[FoFa]

Have not had a chance to try it, most likely later this week will give it shot.
TKS