clone a sql table

table-clone.sql

--Written by Danny Gershman November 16, 2012
--For cloning a database for staging a release
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE wrench_Table_Clone
	@databaseName varchar(50),
	@restoredName varchar(50)
AS
BEGIN	
	DECLARE @filename varchar(255)
	DECLARE @timestamp varchar(20)
	DECLARE @sql nvarchar(max)
	DECLARE @path varchar(255)
	DECLARE @logicalName varchar(50)

	-- Configuration Parameters
	SET @logicalName = 'AMS'
	SET @databaseName = 'AMS-Danny'
	SET @path = 'd:\'
 
	-- Specify filename format
	SELECT @timestamp = CONVERT(VARCHAR(20),GETDATE(),112)
	SET @fileName = @path + @databaseName + '_' + @timestamp + '.BAK'  

	-- Backup database
	BACKUP DATABASE @databaseName TO DISK = @filename

	SET @sql = 'RESTORE FILELISTONLY FROM DISK = ''' + @filename + ''''
	EXEC sp_executesql @sql

	SET @sql = 'RESTORE DATABASE [' + @restoredName + ']'-- name of your newly restored db
	SET @sql = @sql + ' FROM  DISK = N''' + @filename + '''' -- same as step 1
	SET @sql = @sql + ' WITH  FILE = 1,'  
	SET @sql = @sql + ' MOVE ''' + @logicalName + ''' TO N''d:\' + @restoredName + '.mdf'','  -- db LogicalName and new db file path/name
	SET @sql = @sql + ' MOVE ''' + @logicalName + '_Log'' TO N''d:\' + @restoredName + '_1.ldf'',' -- log LogicalName and new db log path/name
	SET @sql = @sql + ' NOUNLOAD, STATS = 10'	

	EXEC sp_executesql @sql	
END