Re: Create a copy of an existing SQL Server database - Ireland .NET Developer Alliance

Re: Create a copy of an existing SQL Server database
SQL

Thread Starter: Paschal
Started:
Replies: 2

Ireland .NET Developer Alliance :: Forums » Technologies » SQL » Re: Create a copy of an existing SQL Server database

01 Nov 2004, 9:31

Paschal is not online. Last active: 27/04/2006 19:05:21

Paschal

Top 10 Posts

Joined on 27-10-2004
Posts 110

Create a copy of an existing SQL Server database

I was searching for an easy way to create a copy of an existing live database and put it on the same SQL Server in a test database. Because I didn't find a good stored procedure I build my own SQL. To use it I put the code to the master database as a stored procedure, now I can run one short command to get a copy!

USE master
GO

-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB varchar(200)
SET @DB = 'PcTopp'

-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'c:\pctopp\sqlserver\backup.dat'

-- the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = 'TestDB'

-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'c:\pctopp\sqlserver\backup'

-- ****************************************************************
-- no change below this line
-- ****************************************************************

DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '.mdf'

DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + '.ldf'

IF @DB IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)
END

-- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN
SET @query = 'DROP DATABASE ' + @TestDB
EXEC (@query)
END

RESTORE HEADERONLY FROM DISK = @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT

DECLARE @Data varchar(500)
DECLARE @Log varchar(500)

SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoretemp
(
LogicalName varchar(500),
PhysicalName varchar(500),
type varchar(10),
FilegroupName varchar(200),
size int,
maxsize bigint
)
INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'

PRINT @Data
PRINT @Log

TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

IF @File > 0
BEGIN
    SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
        ' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
        QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
    EXEC (@query)
END
GO

If someone has a better solution it would be nice to get a comment on this!

13 Jul 2005, 1:42

deepha is not online. Last active: 14/07/2005 12:04:47

deepha

Top 75 Posts

Joined on 07-13-2005
Posts 1

Re: Create a copy of an existing SQL Server database

hi
i have already sent u a msg.
this is something else which i hav tried...
but all codes give me the same error...
plzz can u tell me the problem...[:$]
thanku

----------------------------------------------------------------------

Declare
@query varchar(1000),
@file int,
@data varchar(500),
@log varchar(500),
@log2 varchar(500),
@filename varchar(100),
@year varchar(10),
@month varchar(10),
@day varchar(10)

select @year='2005'
select @month='03'
select @day='31'

BEGIN

  --Destroying the Temporary Device
  EXEC sp_dropdevice testbackup

  -- Create File Name
  SET @filename='F:\Database Backups\TIMS_SS_db_'+@year+@month+@day+'0200.BAK'

  select @filename

  -- take the backup file name from the user and create physical disk
  EXEC sp_addumpdevice 'disk','testbackup',@filename

  CREATE DATABASE a

-- To Extract the Mdf and Ldf File --
set @query = 'RESTORE FILELISTONLY FROM testbackup'

-- Taking the number of Row Affected by the above query
set @file = @@rowcount

  Create Table #restoretemp (logicalname varchar(500),physicalname varchar(500),type varchar(10),filegroupname varchar(200),size int,maxsize bigint)

  Insert #restoretemp exec(@query)

  select @data = logicalname from #restoretemp where type = 'D'
  select @log = logicalname from #restoretemp where type = 'L'
  select @log2 = logicalname from #restoretemp where physicalname like '%log2_Log%'

  if @file > 0
  BEGIN
   SET @query= 'restore database a From testbackup with replace, Move ' + quotename(@data, '''') + ' To ' + quotename ('c:\Program Files\Microsoft SQL Server\MSSQL\Data\a.mdf', '''') + ' , Move '+ quotename(@log,'''') + ' To ' + quotename('c:\Program Files\Microsoft SQL Server\MSSQL\Data\a_log.ldf','''')+ ', Move '+ quotename(@log2,'''') + ' To ' + quotename('c:\Program Files\Microsoft SQL Server\MSSQL\Data\a_log2_Log.ldf','''')+ ',File = ' + Convert(varchar, @file)
   EXEC(@query)
  END

  DROP TABLE #restoretemp

  --use master

END
GO

26 Apr 2006, 9:30

uswebpro is not online. Last active: 26/04/2006 23:10:34

uswebpro

Top 100 Posts

Joined on 04-26-2006
Posts 1

Re: Create a copy of an existing SQL Server database

is there a way I can called stroed proc to copy a "template" database on the fly and name it from a string I pass it?

Page 1 of 1 (3 items)

Ireland .NET Developer Alliance :: Forums » Technologies » SQL » Re: Create a copy of an existing SQL Server database