Una din best practices ale SQL Server era sa separi mdf de ldf pe hard diskuri( vezi si SQL Server 2005 Best Practices Analyzer )
Asa ca a trebuit sa fac asa ceva pentru mai multe BD … si nu vroiam sa fac attach/detach manual si sa schimb manual locatia. Iar rezultatul este urmatorul script, care iti ia mdf-ul si il pune altundeva.
Observatii:
1 .Inchid toate conexiunile la BD brusc la rularea scriptului – fac SET READ_ONLY
2. Nu ia in considerare mai multe mdf-uri pentru o BD
3. Am preferat sa fac output la instructiuni decit sa le execut – pentru ca nu toata lumea are enabled xp_cmdshell ( si nici nu ar fi bine …) ca sa fac automat miscarea
4. Stiu, ar fi fost de preferat un Powershell –sau un programel C# – dar mi-ar fi luat mai mult.
Iata si scriptul(poate va foloseste)
DECLARE @where VARCHAR(MAX)
SET @where = '<cale fizica pe PC-ul unde se afla sql server>'
DECLARE @nameBD VARCHAR(MAX)
SET @nameBD = '<numele bazei de date>'
DECLARE @script NVARCHAR(MAX)
USE master
DECLARE @nameFile NVARCHAR(MAX),@PathFile NVARCHAR(MAX)
-- aflare doar nume master - teoretic, ar trebui facut un cursor...
SET @script = 'select @nameFile = name , @PathFile= filename from ' + @nameBD+ '..sysfiles
where filegroup_name(groupid) is not null'
--print @script
EXECUTE sp_executesql @script,N'@nameFile varchar(max) OUTPUT, @PathFile varchar(max) output',@nameFile output,@PathFile output
SET @script = 'ALTER DATABASE ' + @nameBD + ' SET READ_ONLY WITH ROLLBACK IMMEDIATE'
EXEC (@script)
SET @script = 'ALTER DATABASE ' + @nameBD + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@script)
SET @script = 'ALTER DATABASE ' + @nameBD + ' SET READ_WRITE WITH ROLLBACK IMMEDIATE'
EXEC (@script)
SET @script = 'ALTER DATABASE ' + @nameBD + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE'
EXEC (@script)
DECLARE @dateNow VARCHAR(20)
SET @dateNow = CONVERT(VARCHAR(20),GETDATE(),120)
SET @dateNow = REPLACE(@dateNow,'-','')
SET @dateNow = REPLACE(@dateNow,':','')
SET @dateNow = REPLACE(@dateNow,' ','')
SET @script = 'BACKUP DATABASE ' + @nameBD + ' TO DISK = ''' + @where + @nameBD + @dateNow + '.bak' + ''''
PRINT @script
SET @script = 'ALTER DATABASE ' + @nameBD + ' SET OFFLINE'
PRINT @script
SET @script = 'ALTER DATABASE ' + @nameBD + ' MODIFY FILE ( NAME = ' + @nameFile +
', FILENAME = ''' + @where + @nameFile + '.mdf' + '''' +' )'
PRINT @script
PRINT '--move physically the ' + @PathFile + ' to ' + @where + @nameFile + ' then go to this alter database'
PRINT ' or xp_cmdshell with move ...'
SET @script = ' -- ALTER DATABASE ' + @nameBD + ' SET ONLINE'
PRINT @script
Hi guys. Keep the good works comming. Help me! Need information about: The online someone funds have said, and their scenario is applying, online poker.. I found only this – [URL=http://www.martininsurance.us/Members/OnlinePoker/live-online-poker-game]live online poker game[/URL]. Online poker, regulatory gaming software grows up the second poker of commission group must be necessary and same. Hormone online gambling, online poker. With love :-(, Adah from Cuba.