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
Leave a Reply