Sergey Leifer
Contents contributed and discussions participated by Sergey Leifer
Input Note - 0 views
-
set nocount on
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 + '\
'
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
SSAS performance counters - 0 views
-
SSAS performance counters
* Direct hit ratio - Ratio of cache direct hits to cache lookups, for the period between obtaining counter values. Higher is better.
* Evictions / sec - Rate of evictions from the cache. This is per partition per cube per database. Typically due to background cleaner. Could indicate memory issue is the cache is being excessively cleaned.
* Misses / sec - Rate of cache misses. High ratio indicates that queries are having to go to disk
* Current Connections - Current number of client connections established.
* Current user sessions - Current number of user sessions established.
* Total calculation covers - Total number of of calculation covering subcubes in MDX query plan. High value means that queries are being resolved cell by cell instead of block operations.
* Total cells calculated - Total number of cell properties calculated. High value means queries being resolved cell by cell.
* Total recomputes - Total number of cells recomputed due to error
* Memory Usage KB - Memory usage of the server process. Same as perfmon counter for ProcessPrivateBytes.
* Dimension queries / sec - high number means heavy trips to the storage engine
* Measure group queries / sec - high number means heavy trips to the storage engine
* Available Mbytes - Available MBytes is the amount of physical memory available to processes running on the computer
* Page Faults / sec - high page faults / sec could indicate inadequate memory. Counter has both hard and soft page faults. Soft page faults are okay. Hard page faults require disk access and can hinder performance.
* % Processor Time - > 80 % constant value indicates inadequate processor power
* Avg. Disk Queue Length - disk queues occur when the disk is busy reading or writing information. High disk queue lengths indicate inadequate disk performance. Rule of thumb is Avg. Disk Queue Length < 3 for an extended period.
Automating Analysis Services Deployment - 1 views
-
Automating build of Analysis Services Projects
As you may be aware, an Analysis Services project consists of the following files:
A project file (*.dwProj)
One or more Data sources (*.ds)
One or more Data source views (*.dsv)
A database file (*.database)
One or more cube files (*.cube) with their partition (*.partitions)
One or more dimension files (*.dim)
You can right click the project in Visual Studio and choose "build". What happens now seems to be a lot of called to DLL files executed by the integration between Visual Studio and the Analysis Services. The output of this build is the bin folder in your project directory. This folder contains a database file (*.asdatabase). The .asdatabase file is the one you want - you will see why later.
Unlike other projects - which you can build using their proper compiler (Example: CSC.EXE, VBC.EXE) - Analysis Services does not seem to have a compiler executable.
You could be tempted to run msbuild. Unlike most temptation this will result in disappointment - msbuild does not understand Analysis Services files.
The only way I have found to automate the building of Analysis Services projects is to invoke Visual Studio from the command line. Say you have a project called: MyAsProject.dwProj. To build it and obtain the asdatabase file you execute the following:
devenv MyAsProject.dwProj /rebuild Relase /project MyAsProject
So far so good - now you have automated the createion of the .asdatabase file.
You are not done yet. The asdatabase file provides the input to the Analysis Services Deployment Wizard. The spell cast by this wizard allows you to transform an asdatabase filen into an XML/A file. This XML/A file can be run on your deployment target - which will create the cube database.
From the command line the Deployment Wizard can be run in two modes.
The answer mode:
Microsoft.AnalysisServices.Deployment.exe /a
In this mode, you get to choose all the server specific settings and write them back to your bin directory. Check out the BOL documentation to find out exactly how this is implemented (You will find no defense for the naming of the executable in the documentation)
Once you have your answers stored (which you will only do once) you want to automate the creation of the XML/A file. Execute:
Microsoft.AnalysisServices.Deployment.exe MyAsProject.asdatabase /d /o:MyProject.xmla
Voila! You have your xmla file, which you can copy to your deployment target and run. By the way. xmla files can be run from the command line using ascmd.exe
T-SQL: disk free space alert - 0 views
SSAS performance counters - 0 views
SSAS performance counters - 0 views
-
SSAS performance counters
* Direct hit ratio - Ratio of cache direct hits to cache lookups, for the period between obtaining counter values. Higher is better.
* Evictions / sec - Rate of evictions from the cache. This is per partition per cube per database. Typically due to background cleaner. Could indicate memory issue is the cache is being excessively cleaned.
* Misses / sec - Rate of cache misses. High ratio indicates that queries are having to go to disk
* Current Connections - Current number of client connections established.
* Current user sessions - Current number of user sessions established.
* Total calculation covers - Total number of of calculation covering subcubes in MDX query plan. High value means that queries are being resolved cell by cell instead of block operations.
* Total cells calculated - Total number of cell properties calculated. High value means queries being resolved cell by cell.
* Total recomputes - Total number of cells recomputed due to error
* Memory Usage KB - Memory usage of the server process. Same as perfmon counter for Process\PrivateBytes.
* Dimension queries / sec - high number means heavy trips to the storage engine
* Measure group queries / sec - high number means heavy trips to the storage engine
* Available Mbytes - Available MBytes is the amount of physical memory available to processes running on the computer
* Page Faults / sec - high page faults / sec could indicate inadequate memory. Counter has both hard and soft page faults. Soft page faults are okay. Hard page faults require disk access and can hinder performance.
* % Processor Time - > 80 % constant value indicates inadequate processor power
* Avg. Disk Queue Length - disk queues occur when the disk is busy reading or writing information. High disk queue lengths indicate inadequate disk performance. Rule of thumb is Avg. Disk Queue Length < 3 for an extended period.
Automating Analysis Services Deployment - 0 views
-
Automating build of Analysis Services Projects
As you may be aware, an Analysis Services project consists of the following files:
A project file (*.dwProj)
One or more Data sources (*.ds)
One or more Data source views (*.dsv)
A database file (*.database)
One or more cube files (*.cube) with their partition (*.partitions)
One or more dimension files (*.dim)
You can right click the project in Visual Studio and choose "build". What happens now seems to be a lot of called to DLL files executed by the integration between Visual Studio and the Analysis Services. The output of this build is the bin folder in your project directory. This folder contains a database file (*.asdatabase). The .asdatabase file is the one you want - you will see why later.
Unlike other projects - which you can build using their proper compiler (Example: CSC.EXE, VBC.EXE) - Analysis Services does not seem to have a compiler executable.
You could be tempted to run msbuild. Unlike most temptation this will result in disappointment - msbuild does not understand Analysis Services files.
The only way I have found to automate the building of Analysis Services projects is to invoke Visual Studio from the command line. Say you have a project called: MyAsProject.dwProj. To build it and obtain the asdatabase file you execute the following:
devenv MyAsProject.dwProj /rebuild Relase /project MyAsProject
So far so good - now you have automated the createion of the .asdatabase file.
You are not done yet. The asdatabase file provides the input to the Analysis Services Deployment Wizard. The spell cast by this wizard allows you to transform an asdatabase filen into an XML/A file. This XML/A file can be run on your deployment target - which will create the cube database.
From the command line the Deployment Wizard can be run in two modes.
The answer mode:
Microsoft.AnalysisServices.Deployment.exe /a
In this mode, you get to choose all the server specific settings and write them back to your bin directory. Check out the BOL documentation to find out exactly how this is implemented (You will find no defense for the naming of the executable in the documentation)
Once you have your answers stored (which you will only do once) you want to automate the creation of the XML/A file. Execute:
Microsoft.AnalysisServices.Deployment.exe MyAsProject.asdatabase /d /o:MyProject.xmla
Voila! You have your xmla file, which you can copy to your deployment target and run. By the way. xmla files can be run from the command line using ascmd.exe
T-SQL: search by tags - 0 views
-
DECLARE @filter TABLE (id INT IDENTITY, data nvarchar(32))
insert into @filter (data) values ('sql,performance')
insert into @filter (data) values ('profiler')
insert into @filter (data) values ('table,performance,view')
DECLARE @blog TABLE (id INT IDENTITY, name nvarchar(20), tags nvarchar(20))
insert into @blog (name,tags) values ('post 1','sql,profiler,table')
insert into @blog (name,tags) values ('post 2','sql,performance')
insert into @blog (name,tags) values ('post 3','profilter')
insert into @blog (name,tags) values ('post 4','view,table')
;with cte
as( select n=1,
id,
tag=case charindex(',',tags)
when 0 then tags
else substring(tags,1,charindex(',',tags,1)-1) end ,
txt=substring(tags,charindex(',',tags,2)+1,len(tags))
from @blog
union all
select n=n+1,
id,
tag=case charindex(',',txt)
when 0 then txt
else substring(txt,1,charindex(',',txt,1)-1) end ,
txt=substring(txt,charindex(',',txt,2)+1,len(txt))
from cte
where txt!=tag
)
select
f.id,
f.data,
b.[name],
b.tags,
RELEVANCE=count(*)
from
cte join
@blog b on b.id=cte.id join
@filter f on charindex(','+cte.tag+',' , ','+f.data+',')>0
group by
f.id,
f.data,
b.[name],
b.tags
order by
id,
count(*)desc,
b.[name]
T-SQL: last day of the month, round dates - 0 views
-
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE())))
T-SQL: find SQL TCP port number - 0 views
-
--SQL 2000/2005 Version
set nocount on
go
DECLARE @SqlPort Nvarchar(10)
DECLARE @instance_name Nvarchar(30)
DECLARE @reg_key Nvarchar(500)
Declare @value_name Nvarchar(20)
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWAREMicrosoftMSSQLServerMSSQlServerSuperSocketNetLibTcp'
END
ELSE BEGIN
set @reg_key = 'SOFTWAREMicrosoftMicrosoft SQL Server' + @instance_name + 'MSSQLServerSuperSocketNetLibTcp'
END
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name='TcpPort',
@value=@SqlPort output
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port
END
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN
Create table #Port_2000 (value nvarchar(20),Data nVarchar(10))
insert into #Port_2000 exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLServerMSSQLServerSupersocketnetlib cp', 'tcpPort'
select @SqlPort = Data from #Port_2000
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port
drop table #Port_2000
END
T-SQL: disk free space alert - 0 views
-
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Disk Free Space on Drive E',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'.ROOTCIMV2',
@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 53687091200 AND TargetInstance.DeviceID = "E:"',
@job_id=N'06b9a31f-b6c6-4c42-8a30-8e67c7a0b6dd'
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Alert - Disk Space on disk E',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Alert - Disk Space on disk E',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'raiserror (''Disk space on drive E: falls below 50GB!'', 15, 1)',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
T-SQL: list files in folder - 1 views
-
create table #Files (
FullName nvarchar(255) null
)
insert #Files
exec master..xp_cmdshell 'DIR C:*.csv /B /S'
select right(FullName, patindex('%%', reverse(FullName)) - 1) as ShortFileName
,*
from #Files
where not FullName is null
drop table #Files
1 - 13 of 13
Showing 20▼ items per page
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