use master
go
Alter database tempdb modify file (name = tempdev, filename = 'D:\data\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'D:\data\templog.ldf')
go
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 61440KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 61440KB )
GO
helpful article:
How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
Tuesday, April 28, 2009
Thursday, April 09, 2009
Rename MSSQL Server name
select @@servername
go
exec sp_dropserver 'CLSQL106'
go
sp_addserver 'CLSQL64', 'local'
go
Restart the server
go
exec sp_dropserver 'CLSQL106'
go
sp_addserver 'CLSQL64', 'local'
go
Restart the server
Wednesday, April 08, 2009
Database Permission Review
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
Monday, April 06, 2009
Parse XML file to read in SQL
DECLARE @hdoc int
DECLARE @doc nvarchar(max)
SelecT @doc = convert(nvarchar(max),xml_data) from XmlImportTest
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
--select @hdoc
SELECT *
FROM OPENXML (@hdoc, '/strings/enum')
with (name varchar(100) '@name')
SELECT *
FROM OPENXML (@hdoc, '/strings/enum/field')
with ( name varchar(100) '../@name',
val varchar(100) '@value',
txt varchar(100) '.')
-- Remove the internal representation.
exec sp_xml_removedocument @hdoc
DECLARE @doc nvarchar(max)
SelecT @doc = convert(nvarchar(max),xml_data) from XmlImportTest
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
--select @hdoc
SELECT *
FROM OPENXML (@hdoc, '/strings/enum')
with (name varchar(100) '@name')
SELECT *
FROM OPENXML (@hdoc, '/strings/enum/field')
with ( name varchar(100) '../@name',
val varchar(100) '@value',
txt varchar(100) '.')
-- Remove the internal representation.
exec sp_xml_removedocument @hdoc
Import XML data into a table
CREATE TABLE XmlImportTest(
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'C:\Documents and Settings\palexander\Desktop\Mingle Meta Data\black_strings.xml'
--dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('INSERT INTO XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'C:\Documents and Settings\palexander\Desktop\Mingle Meta Data\black_strings.xml'
--dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('INSERT INTO XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
Tuesday, March 31, 2009
Thursday, March 26, 2009
Detach all databases on a server
use master
go
exec master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use master''
print ''go''
print ''EXEC master.dbo.sp_detach_db @dbname = ''''?'''', @keepfulltextindexfile=N''''true''''''
print ''go''
end
'
go
go
exec master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use master''
print ''go''
print ''EXEC master.dbo.sp_detach_db @dbname = ''''?'''', @keepfulltextindexfile=N''''true''''''
print ''go''
end
'
go
Subscribe to:
Posts (Atom)