TSQL - Script for moving databases to a new location - 0 views
started by Sergey Leifer on 21 Sep 10
started by Sergey Leifer on 21 Sep 10
Start a New Topic » « Back to the MS SQL Server and Analysis Services group
declare @SqlInstanceName varchar(128),
@SqlServiceName varchar(128),
@Cmd varchar(8000),
@TempDbLoc varchar(255),
@SysDbLoc varchar(255),
@UserDbDataLoc varchar(255),
@UserDbLogLoc varchar(255)
--sp_helpdb 'DBA'
--select * from master..sysaltfiles
--select cast(serverproperty('instancename') as varchar(128))
select @TempDbLoc = 'T:SQL_TempDb'
,@SysDbLoc = 'D:SQL_Data'
,@UserDbDataLoc = 'D:SQL_Data'
,@UserDbLogLoc = 'L:SQL_Logs'
--service name
if cast(serverproperty('instancename') as varchar(128)) is null
begin
set @SqlServiceName = 'MSSQLSERVER'
end
else
begin
set @SqlServiceName = 'MSSQL$' + upper(cast(serverproperty('instancename') as varchar(128)))
end
/********************************************************************************/
--print parameters
select
'--Server: ' + cast(serverproperty('ServerName') as varchar(128))
select
'--@TempDbLoc: ' + @TempDbLoc +
'
--@SysDbLoc: ' + @SysDbLoc +
'
--@UserDbDataLoc: ' + @UserDbDataLoc +
'
--@UserDbLogLoc: ' + @UserDbLogLoc
/********************************************************************************/
--tempdb
select '--Tempdb: '
union all
select '--Old file location: ' + ltrim(rtrim(name)) + ' - ' + ltrim(rtrim([filename]))
--select *
from master..sysaltfiles
where [dbid] = db_id(N'tempdb')
union all
select 'alter database [' + db_name([dbid]) + ']
modify file (NAME = ' + ltrim(rtrim(name)) + ', filename = ''' + ltrim(rtrim(@TempDbLoc + right([filename], patindex('%%', reverse([filename]))))) + ''');
go
'
from master..sysaltfiles
where [dbid] = db_id(N'tempdb');
/********************************************************************************/
--system dbs
select '--System Dbs: '
union all
select '--Old file location: ' + name + ' - ' + [filename]
from master..sysaltfiles
where [dbid] in (
db_id(N'msdb'),
db_id(N'model'),
db_id(N'distribution')
)
union all
select 'alter database [' + db_name([dbid]) + ']
modify file (NAME = ' + ltrim(rtrim(name)) + ', filename = ''' + ltrim(rtrim(@SysDbLoc + right([filename], patindex('%%', reverse([filename]))))) + ''');
go
'
from master..sysaltfiles
where [dbid] in (
db_id(N'msdb'),
db_id(N'model'),
db_id(N'distribution')
);
/********************************************************************************/
--master db
select '--master (startup parameters): '
union all
select
'--Current location:
-- ' + ltrim(rtrim([filename])) +
'
--New location:
-- ' + ltrim(rtrim(@SysDbLoc + right([filename], patindex('%%', reverse([filename]))))) as NewFileLoc
from master..sysaltfiles
where [dbid] = db_id(N'master');
/********************************************************************************/
--resource db (2005 only)
if (@@microsoftversion / 0x1000000) < 10
begin
select '--Resource db (2005 only): '
union all
select '--1. Stop the service;
'
union all
select '--2. Run "NET START ' + @SqlServiceName + ' /f /T3608"
'
union all
select '--3. Move resource db files to the master db location: ' + @SysDbLoc + '\n'
union all
select '--4. alter database mssqlsystemresource modify file (name = data, filename = ''' + @SysDbLoc + '' + 'mssqlsystemresource.mdf'');
'
union all
select '--5. alter database mssqlsystemresource modify file (name = log, filename = ''' + @SysDbLoc + '' + 'mssqlsystemresource.ldf'');
'
union all
select '--6. alter database mssqlsystemresource set read_only;
'
union all
select '--7. Run "NET STOP ' + @SqlServiceName + '"
'
union all
select '--8. Run "NET START ' + @SqlServiceName + '"
'
end
/********************************************************************************/
--detach user dbs
select '--Detach user dbs: '
union all
select
'alter database [' + name + ']
set single_user with rollback immediate
exec sp_detach_db ''' + name + ''', ''true'';
go
'
from master..sysdatabases
where [name] not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
/********************************************************************************/
--old user db location
--select '--Old user db location'
--union all
--select '/*'
--union all
--select db_name([dbid]) + ': ' + [filename]
--from master..sysaltfiles
--where db_name([dbid]) not in (
-- N'master',
-- N'msdb',
-- N'model',
-- N'distribution',
-- N'tempdb'
--)
--and [fileid] <> 65537
--union all
--select '*/'
--attach user dbs
if (@@microsoftversion / 0x1000000) < 9
begin
select Cmd as [--Attach user dbs]
from (
select [dbid] * 1000 + 0 as RId,
'exec sp_attach_db
@dbname = ''' + db_name([dbid]) + '''' as Cmd
from sysdatabases
where name not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
union all
select [dbid] * 1000 + max([fileid]) + 1 as RId,
'go
'
from master..sysaltfiles
where db_name([dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and [fileid] <> 65537
group by [dbid]
union all
select sf.[dbid] * 1000 + sf.[fileid] as RId,
' --' + sf.[filename] +
'
,@filename' + cast(sf.[fileid] as varchar(10)) + ' = ''' + case
when sf.[status] & 64 = 64 then @UserDbLogLoc +
case when sff.files_cnt > 2 then '' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%%', reverse(sf.[filename])))
else @UserDbDataLoc +
case when sff.files_cnt > 2 then '' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%%', reverse(sf.[filename])))
end + ''''
from master..sysaltfiles sf
inner join (
select [dbid]
,count(fileid) as files_cnt
from master..sysaltfiles
group by [dbid]
) sff
on sf.[dbid] = sff.[dbid]
where db_name(sf.[dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and sf.[fileid] <> 65537
) q
order by RId
end --> SQL 2000
else
begin
select Cmd as [--Attach user dbs]
from (
select [dbid] * 1000 + 0 as RId,
'create database [' + db_name([dbid]) + ']
on' as Cmd
from master..sysdatabases
where name not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
union all
select [dbid] * 1000 + max([fileid]) + 1 as RId,
'
for attach
go
'
from master..sysaltfiles
where db_name([dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and [fileid] <> 65537
group by [dbid]
union all
select sf.[dbid] * 1000 + sf.[fileid] as RId,
' --' + sf.[filename] +
'
' + case sf.[fileid] when 1 then '' else ',' end +
'(filename = ''' + case
when sf.[status] & 64 = 64 then @UserDbLogLoc +
case when sff.files_cnt > 2 then '' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%%', reverse(sf.[filename])))
else @UserDbDataLoc +
case when sff.files_cnt > 2 then '' + db_name(sf.[dbid]) else '' end +
right(sf.[filename], patindex('%%', reverse(sf.[filename])))
end + ''')'
from master..sysaltfiles sf
inner join (
select [dbid]
,count(fileid) as files_cnt
from master..sysaltfiles
group by [dbid]
) sff
on sf.[dbid] = sff.[dbid]
where db_name(sf.[dbid]) not in (
N'master',
N'msdb',
N'model',
N'distribution',
N'tempdb'
)
and sf.[fileid] <> 65537
) q
order by RId
end --> SQL 2005 and higher
set nocount off