question

I am relatively new to SQL Server, so I apologize in advance if the answer to this is obvious to more seasoned eyes.

I have a stored procedure that copies a database using the backup/restore method. It works fine, but I was testing to see if it would overwrite the target database, in case it was ran twice with the same parms. I have an exists test for the target database, but being paranoid, I commented that out and tried running it twice with the same parms.

It overwrites the target database. So I looked up the syntax of the RESTORE DATABASE command looking for a 'DO NOT REPLACE' option, but it seems that IS the default, and you need to specify if you want to allow the command to overwrite an existing database.

This are the backup and restore commands generated by the sp:

BACKUP DATABASE test TO DISK = 'Z:\Backup\3_year_and_Delete\backup_test.dat'

RESTORE DATABASE test01 
FROM DISK = 'Z:\Backup\3_year_and_Delete\backup_test.dat' 
WITH MOVE 'test' TO 'X:\xxx\Data001\test01.mdf',  
MOVE 'test_log' TO 'Y:\xxx\Data001\test01.ldf', FILE = 9

I would assume from my research that if database test01 exists, this command should fail, but it works fine.

Here is the entire script:

    /*******  CODE ADDED BY DBA-Mark McNary  *******/
USE UMB_DBA
GO
print '**** BEGIN DBA DOCUMENTATION ****'
print 'Running on SQL Server:' 
print @@servername
print 'Running at:'
print GETDATE() 
print 'Current database is:'
print DB_NAME()
print '**** END DBA DOCUMENTATION ****'
/******* END OF CODE ADDED BY DBA ********/

/****** Object:  StoredProcedure [dbo].[dbasp_copy_db]    
                 Script Date: 03/01/2010 07:43:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Mark McNary
-- Create date: 2/25/2010
-- Description: script to copy an existing database to a new database
--              with the same name_YYYYMMDD.
-- =============================================
ALTER PROCEDURE [dbo].[dbasp_copy_db] 
    -- @source_db is the source database
    -- @target is the name of the new database
    @source_db varchar(50) = ' ' ,
    @target_db varchar(50) = ' ' 
AS

DECLARE @RestoreDatFile   nvarchar(2000)
DECLARE @RestoreDatDir    nvarchar(2000)
DECLARE @RestoreLogFile   nvarchar(2000)
DECLARE @RestoreLogDir    nvarchar(2000)
DECLARE @BackupFile    nvarchar(2000)
DECLARE @BackupPath    nvarchar(2000)
DECLARE @BackupDir     nvarchar(2000)
DECLARE @DB            nvarchar(200)
DECLARE @TestDB        nvarchar(200)
DECLARE @query         nvarchar(2000)
DECLARE @DataFile      nvarchar(2000)
DECLARE @LogFile       nvarchar(2000)


BEGIN
-- Housekeeping stuff 

-- the original database (use 'SET @DB = NULL' to disable backup)
SET @DB = @source_db
SET @TestDB = @target_db

-- the backup filename
SET @BackupDir = 'Z:\Backup\3_year_and_Delete'
SET @BackupFile = @BackupDir + '\backup_' + @DB + '.dat'

-- the new database name
-- the new database files without .mdf/.ldf
SET @RestoreDatDir = 'X:\xxx\Data001\'
SET @RestoreLogDir = 'Y:\xxx\Data001\'
SET @RestoreDatFile = @RestoreDatDir + @TestDB
SET @RestoreLogFile = @RestoreLogDir + @TestDB

SET @DataFile = @RestoreDatFile + '.mdf'

SET @LogFile = @RestoreLogFile + '.ldf'

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
--  SET NOCOUNT ON;

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

-- This tests for the existance if the source database.  If it soes not exist, then 
-- the script stops and a message is returned to the user
IF not EXISTS(SELECT * FROM [master].sys.sysdatabases WHERE name = @DB)
BEGIN
    print 'The source database does not exist.  Please re-submit the job with a different source database'
    return 4
END

-- This tests for the existance if the target database.  If it already exists, then 
-- the script stops and a message is returned to the user
--IF EXISTS(SELECT * FROM [master].sys.sysdatabases WHERE name = @TestDB)
--BEGIN
--    --SET @query = 'DROP DATABASE ' + @TestDB
--    --EXEC (@query)
--    print 'The target database already exists.  Please re-submit the job with a different target database'
--    return 4
--END

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

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

DECLARE @Data nvarchar(500)
DECLARE @Log  nvarchar(500)
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoretemp
(
 LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId INT
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes INT
,SourceBlockSize INT
,FilegroupId INT
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly INT
,IsPresent INT 
,TDEthumbprint INT 
)
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)
    print @query
    EXEC (@query)
END

end

GO