Month: October 2014

I kept getting an odd error message …

Msg 3634, Level 16, State 1, Line 3
The operating system returned the error ’32(The process cannot access the file because it is being used by another process.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘E:\Website.mdf’.
blah blah blah

… while executing this code (simplified) …

RESTORE DATABASE [Website]
FROM DISK = N'E:\backups\Website.bak' WITH FILE = 1,
MOVE N'Website' TO N'D:\Website.mdf',
MOVE N'Website_Indexes' TO N'D:\Website_1.ndf',
MOVE N'Website_Customers' TO N'D:\Website_2.ndf'
MOVE N'Website_log' TO N'D:\Website_3.ldf',
NOUNLOAD, REPLACE, STATS = 1
GO

The solution was to use a double-backslash in the path like so …

RESTORE DATABASE [Website]
FROM DISK = N'E:\\backups\Website.bak' WITH FILE = 1,
MOVE N'Website' TO N'D:\Website.mdf',
MOVE N'Website_Indexes' TO N'D:\Website_1.ndf',
MOVE N'Website_Customers' TO N'D:\Website_2.ndf'
MOVE N'Website_log' TO N'D:\Website_3.ldf',
NOUNLOAD, REPLACE, STATS = 1
GO

To stop “Business Intelligence Development Studio” crashing when you click CALCULATIONS just remove / re-install BIDS.

Further to my last – here is my script to empty (using the very fast TRUNCATE command) all the tables that are not referenced by a foreign-key. Then in (2) attempt to delete the contents of those remaining (This fails for tables that are referenced by non-empty tables). Step-3 has another go at those that failed, hopefully more successfully now the referencing tables are empty.

-- EmptyDatabase.sql

-- 1) TRUNCATE child & independent tables

-- capture non-parent table names
SELECT SCHEMA_name(schema_id) [schema], [name] INTO #tables
FROM sys.tables
where name NOT IN (SELECT OBJECT_NAME(referenced_object_id) FROM sys.foreign_keys)
ORDER BY name

-- loop through list truncating tables
DECLARE @sql VARCHAR(300)
WHILE (SELECT COUNT(*) FROM #tables) > 0
BEGIN
	SELECT TOP 1 @sql = [schema] + '.[' + [name] + ']' FROM #tables
	DELETE FROM #tables WHERE [schema] + '.[' + [name] + ']' = @sql
	SELECT @sql = 'truncate table ' + @sql
	EXEC(@sql)
END

DROP TABLE #tables


-- 2) DELETE parents of child tables

--list all parent-tables
SELECT OBJECT_NAME(referenced_object_id) pname into #ptables
FROM sys.foreign_keys
GO

-- loop through list DELETING tables
DECLARE @sql VARCHAR(300)
WHILE (SELECT COUNT(*) FROM #ptables) > 0
BEGIN
	SELECT TOP 1 @sql = [pname] FROM #ptables
	DELETE FROM #ptables WHERE [pname] = @sql
	SET @sql = 'delete ' + @sql
	exec(@sql)
END

drop table #ptables


-- 3) DELETE parents of (now-empty) parent tables

--list all parent-tables
SELECT OBJECT_NAME(referenced_object_id) pname into #ptables2
FROM sys.foreign_keys
GO

-- loop through list DELETING tables
DECLARE @sql VARCHAR(300)
WHILE (SELECT COUNT(*) FROM #ptables2) > 0
BEGIN
	SELECT TOP 1 @sql = [pname] FROM #ptables2
	DELETE FROM #ptables2 WHERE [pname] = @sql
	SET @sql = 'delete ' + @sql
	exec(@sql)
END

drop table #ptables2

In use, this reduced a database of [965,849,336] total rows of data to just [10,860] rows in less than two seconds. Which after shrinking (this was a copy of a production-database on a dev-server set to Simple-mode), shrunk it from [822000.50 MB] to [65100.13 MB].

I think I deserve a beer!

As part of my project to empty a database I wanted to TRUNCATE tables without Foreign Keys. Here’s a neat script I have not seen elsewhere, that lists the tables that are referenced my at-least one Foreign Key (that therefore could not be truncated)

-- ParentTables.sql
SELECT OBJECT_NAME(referenced_object_id) AS TablesRefdByFKs
FROM sys.foreign_keys

And just for interest here’s the script to list tables that are not parents and may or maybe not children …

-- NonParentTables.sql
SELECT name NonParentTables
FROM sys.tables
where name NOT IN (SELECT OBJECT_NAME(referenced_object_id) FROM sys.foreign_keys)
ORDER BY name