How to Copy the Schemas & Statistics of Multiple Databases without Data via TSQL

With the service pack 2 of SQL Server 2014, you can create a copy of a user database without the actual data. i.e., you would be creating just the schema and statistics of your source database. In this article, we will learn how to quickly create clone of multiple user databases

A copy needed for investigation:

To find the root cause of a performance problem, you might need a copy of your existing database. In such cases, you can use the following query to create copies of multiple SQL Server databases.

The query given below creates a table-valued function in your master database. This function will help you to split a delimited string. We would be using this function to split and get individual database names.

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split_String_Old]
(
 @dbs NVARCHAR(4000),
 @dl NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
 WITH tbl1(v_start,v_end)
 AS(
   SELECT 0 AS v_start, CHARINDEX(@dl,@dbs) AS v_end
   UNION ALL
   SELECT v_end+1, CHARINDEX(@dl,@dbs,v_end+1)
   FROM tbl1
   WHERE v_end > 0
 )
 SELECT 'S.NO' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
   'Database' = SUBSTRING(@dbs,v_start,COALESCE(NULLIF(v_end,0),LEN(@dbs)+1)-v_start)
 FROM tbl1
)

GO

Before executing the query mentioned below, update the variable dbcontainer with correct database names separated by a semicolon. By default, the clone command would create a copy of your SQL Server database in read only mode. So after creating a copy this query would also execute Alter database command to convert the copy of a database in to read write mode. Please do note that these database copies should be purely used for diagnostic and troubleshooting purposes and not as a production SQL Server database. Microsoft recommends to detach these database copies from the server soon after they are created. Please do note, databases created using this method will not help you in SQL recovery.

declare @dbcontainer varchar(1000)
set @dbcontainer = 'Database1;Database2' -- list database(s) here. Separate multiple databases by ;
declare @dbname varchar(200)
Declare cur1 cursor for
Select [database] from [dbo].[Split_String_Old] (@dbcontainer,';')
open cur1
fetch next from cur1 into @dbname
while @@FETCH_STATUS = 0 
begin
declare @sql as Varchar(1000)
set @sql = 'DBCC CLONEDATABASE (''' + @dbname + ''', '''+ @dbname + '_Clone_Database'')'
print @sql
EXEC (@sql)
set @sql = 'ALTER DATABASE [' + @dbname + '_Clone_Database] SET READ_WRITE WITH NO_WAIT'
print @sql
EXEC (@sql)
fetch next from cur1 into @dbname
end
close cur1
deallocate cur1

The following query will help you to identify databases that are just a clone.

Create Table T1
(
dbname varchar(200), isclone sql_variant
)
declare @dbname varchar(200)
Declare cur1 cursor for
Select [name] from [dbo].[sysdatabases]
open cur1
fetch next from cur1 into @dbname
while @@FETCH_STATUS = 0 
begin
declare @sql as Varchar(1000)
set @sql = 'INSERT INTO T1 SELECT ''' + @dbname + ''' , DATABASEPROPERTYEX(''' + @dbname + ''', ''isClone'')'
print @sql
EXEC (@sql)
fetch next from cur1 into @dbname
end
close cur1
deallocate cur1
select * from T1 where isclone = 1
DRop table T1

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including pst repair and excel recovery software products. For more information visit www.datanumen.com