detach sql database

[bn2hunt]

I have been working on this for a day or two and have so far been unable to find any help on google. I need to detach a lsit of databases about 40 of them, copy them to a new location so they can be archived.

Can someone point me in the right direction to get started with this project. I would prefer to figure it out myself but I'm coming up on the deadline next week so its time to ask for some help.

I havn't come up with any code yet. Everyting I try either I'm missing dll becuase it can't create a control or when I use wmi to connect to the database server I am getting a null errorl.



Thanks

Dan

bn2hunt

"Born to hunt forced to work

 

[Roadki11]

Well i dont have a vb solution for this but this is how i have handled it in the past.

1st way is a simple backup using transact sql statements in a batch file. Nice thing about this way is it can be done with people attached to the database.

@echo off
REM intranet database backup
REM Last Modified on 03/15/2005
REM By: DJT

@echo Setting Directories....
SET SQLLOCAT=C:\Program Files\Microsoft SQL Server\MSSQL\SQL_Backup

@echo Backing Up Intranet Database....
osql -U sa -P yourpassword -Q "BACKUP DATABASE intranet TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\SQL_Backup\intranet.bak' WITH INIT"

@echo Creating 5 day rotation for intranet database
copy /Y "%SQLLOCAT%\intranet4.bak" "%SQLLOCAT%\intranet5.bak"
copy /Y "%SQLLOCAT%\intranet3.bak" "%SQLLOCAT%\intranet4.bak"
copy /Y "%SQLLOCAT%\intranet2.bak" "%SQLLOCAT%\intranet3.bak"
copy /Y "%SQLLOCAT%\intranet1.bak" "%SQLLOCAT%\intranet2.bak"
copy /Y "%SQLLOCAT%\intranet.bak" "%SQLLOCAT%\intranet1.bak"

To restore the database from a .bak use the following code:

@echo off
REM intranet database restore
REM Last Modified on 03/15/2005
REM By: DJT

@echo Restoring Database....
osql -U sa -P yourpassword -Q "RESTORE DATABASE intranet FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\SQL_Backup\intranet.bak' WITH REPLACE"

2nd way is more inline with your detach request. This way is slightly more complicated and requires you kill any connections to the db before you can detach it. I did this with a combination of a .bat file and 2 .sql scripts.

First you need to kill the users, to do this create a new stored procedure in the master db.

CREATE PROCEDURE kill_database_users @arg_dbname sysname with recompile
AS
-- kills all the users in a particular database
declare @a_spid	smallint
declare @msg	varchar(255)
declare @a_dbid	int
select
	@a_dbid = sdb.dbid
from	master..sysdatabases sdb
where	sdb.name = @arg_dbname
declare db_users insensitive cursor for
select
	sp.spid
from	master..sysprocesses sp
where	sp.dbid = @a_dbid
open db_users
fetch next from db_users into @a_spid
while @@fetch_status = 0
	begin
	select @msg = 'kill '+convert(char(5),@a_spid)
	print @msg
	execute (@msg)
	fetch next from db_users into @a_spid
	end
close db_users
deallocate db_users

GO

The following is the .sql script file to kill the users and detach the db.

use master
go
kill_database_users intranet
go
EXEC sp_detach_db 'intranet'
go
exit

This .sql script will reattach the db after you copy it to where ever you want it.

use master
go
EXEC sp_attach_db @dbname = N'intranet', @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\intranet.mdf', @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\intranet.ldf'
go
exit

This is the batch file that ties it all to gether.

@echo off
@echo MSDE SQL Database Backup
@echo Last Modified on 03/25/04
@echo By: DJT

@echo Setting Directories....
SET SQLSCRIPT=C:\Program Files\Microsoft SQL Server\MSSQL\SQLScripts
SET SQLLOCAT=C:\Program Files\Microsoft SQL Server\MSSQL\data
SET SQLBACKUP=C:\Program Files\Microsoft SQL Server\MSSQL\DB_Backups

@echo Connect to SQL Server and execute detach script....
osql -U sa -P yourpassword -i "%SQLSCRIPT%\detach.sql"

@echo Done Detaching Databases.

@echo Copy databases....
copy /Y "%SQLLOCAT%\intranet.mdf" "%SQLBACKUP%\intranet.mdf"
copy /Y "%SQLLOCAT%\intranet.ldf" "%SQLBACKUP%\intranet.ldf"

@echo Connect to SQL Server and execute reattach script....
osql -U sa -P yourpassword -i "%SQLSCRIPT%\attach.sql"

I would just throw the .bat file into a scheduled task.

This stuff was used on MS SQL 2000 and MSDE 2000 havent tried against MS SQL 2005 but it should work.

Hope this helps,

RoadKi11