Problem:
Query_1:
select DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';' from sys.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('D','E')
Query_2:
Step 2:
If the database drive is full and you don't have the option to extend the drive, then you need to add another drive with a greater size. Then detach all databases, copy all your data and log files of database into new drive and attach the database
Lets us do in three steps:
Lets us consider, we have data file under D: drive and log files under E Drive. We need to move it I and J drive.
STEP 1: To list all the databases under the drive D and E
select DISTINCT DB_NAME(dbid) as database_name from sys.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('D','E')
Result:
database_name
master
model
msdb
tempdb
test1
test2
test3
It will list all the databases under the drive D and E
Now generate the query to detach all the database under the drive D and E.Query_1 will generate the query to detach all system tables as well. If you want to skip the system tables, use Query_2
select DISTINCT DB_NAME(dbid) as database_name from sys.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('D','E')
Result:
database_name
master
model
msdb
tempdb
test1
test2
test3
It will list all the databases under the drive D and E
Now generate the query to detach all the database under the drive D and E.Query_1 will generate the query to detach all system tables as well. If you want to skip the system tables, use Query_2
Query_1:
select DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';' from sys.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('D','E')
Query_2:
select DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';' from sys.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('D','E') AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')Result:
exec sp_detach_db 'test1';
exec sp_detach_db 'test10';
exec sp_detach_db 'test11';
exec sp_detach_db 'test12';
exec sp_detach_db 'test13';
exec sp_detach_db 'test10';
exec sp_detach_db 'test11';
exec sp_detach_db 'test12';
exec sp_detach_db 'test13';
The result for the query will be something like above, exec the above query to detach all the databases.
Step 2:
After detaching the databases, Copy all the mdf and ldf files from D,E drives to I and J drives
Step 3:
Generate the sql query to attach all the databases from new drive location:
-- Build the sp_attach_db:
-- In this example, I am assuming that only the drive letter changes except master database, not the whole
-- path of the files. You can modify this script according to your needs:
--It will create a temporary table and delete it after the execution of the command
SET NOCOUNT ON
DECLARE @cmd VARCHAR(MAX),
@dbname VARCHAR(200),
@prevdbname VARCHAR(200)
SELECT @cmd = '', @dbname = ';', @prevdbname = ''
CREATE TABLE #Attach
(Seq INT IDENTITY(1,1) PRIMARY KEY,
dbname SYSNAME NULL,
fileid INT NULL,
filename VARCHAR(1000) NULL,
TxtAttach VARCHAR( MAX) NULL
)
INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid,
CASE SUBSTRING(filename,1,1)
WHEN 'D' THEN 'I' + SUBSTRING(filename,2,LEN(filename))
WHEN 'E' THEN 'J' + SUBSTRING(filename,2,LEN(filename))
ELSE filename
END,
CONVERT(VARCHAR(MAX),'') AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM sys.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('D','E'))
AND DATABASEPROPERTYEX(DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master')
ORDER BY dbname, fileid,
CASE SUBSTRING(filename,1,1)
WHEN 'D' THEN 'I' + SUBSTRING(filename,2,LEN(filename))
WHEN 'E' THEN 'J' + SUBSTRING(filename,2,LEN(filename))
ELSE filename
END
UPDATE #Attach
SET @cmd = TxtAttach =
CASE WHEN dbname <> @prevdbname
THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''')
ELSE @cmd
END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''',
@prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
@dbname = dbname
FROM #Attach WITH (INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)
SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
GROUP BY dbname) AS x
DROP TABLE #Attach
GO
-- Build the sp_attach_db:
-- In this example, I am assuming that only the drive letter changes except master database, not the whole
-- path of the files. You can modify this script according to your needs:
--It will create a temporary table and delete it after the execution of the command
SET NOCOUNT ON
DECLARE @cmd VARCHAR(MAX),
@dbname VARCHAR(200),
@prevdbname VARCHAR(200)
SELECT @cmd = '', @dbname = ';', @prevdbname = ''
CREATE TABLE #Attach
(Seq INT IDENTITY(1,1) PRIMARY KEY,
dbname SYSNAME NULL,
fileid INT NULL,
filename VARCHAR(1000) NULL,
TxtAttach VARCHAR( MAX) NULL
)
INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid,
CASE SUBSTRING(filename,1,1)
WHEN 'D' THEN 'I' + SUBSTRING(filename,2,LEN(filename))
WHEN 'E' THEN 'J' + SUBSTRING(filename,2,LEN(filename))
ELSE filename
END,
CONVERT(VARCHAR(MAX),'') AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM sys.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('D','E'))
AND DATABASEPROPERTYEX(DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master')
ORDER BY dbname, fileid,
CASE SUBSTRING(filename,1,1)
WHEN 'D' THEN 'I' + SUBSTRING(filename,2,LEN(filename))
WHEN 'E' THEN 'J' + SUBSTRING(filename,2,LEN(filename))
ELSE filename
END
UPDATE #Attach
SET @cmd = TxtAttach =
CASE WHEN dbname <> @prevdbname
THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''')
ELSE @cmd
END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''',
@prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
@dbname = dbname
FROM #Attach WITH (INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)
SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
GROUP BY dbname) AS x
DROP TABLE #Attach
GO
Result:
exec sp_attach_db @dbname = N'model',@filename1=N'I:\MSSQL Server\SQL_PRIMARY\MSSQL13.SQL_PRIMARY\MSSQL\DATA\model.mdf',@filename2=N'I:\MSSQL Server\SQL_PRIMARY\MSSQL13.SQL_PRIMARY\MSSQL\DATA\modellog.ldf'
You can get the result something like above. verify the location and exec the query to attach the database from new data and log drives.
Thanks,
Sandy.