question

mmcnary avatar image
mmcnary asked

copy database script issue

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
restorecopy-database
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

When the REPLACE option is not specified, a safety check occurs. This prevents overwriting a different database by accident. The safety check ensures that the RESTORE DATABASE statement does not restore the database to the current server if the following conditions both exist:

The database named in the RESTORE statement already exists on the current server, and The database name is different from the database name recorded in the backup set.

http://msdn.microsoft.com/en-us/library/ms178615.aspx

If your database name is the same, then the restore can still occur without the overwrite. Both of the above conditions must exist for it to fail.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

when I issue this command: RESTORE HEADERONLY FROM DISK = 'Z:\Backup\3_year_and_Delete\backup_test.dat' the databasename in the resultset is test, which is different than the new db name. The db is one that currently exists.
0 Likes 0 ·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

I believe the answer to that part is that once you restore the database to a new name, it holds a tie to the old name in some way - thus the replace is unnecessary. IN my testing, I was able to restore a database using your script multiple times. The first created the new database and subsequent overwrote that database. However, I was never able to restore the database to a database that existed prior (to testing your script) and was of a different name.

Scenario Existing Databases TestA TestB Backup TestA and restore to Test1 - no problem (db did not exist on the first attempt) Backup TestA and restore to TestB - fails every time. TestB existed and was not a restore to a new database of a different name from the TestA backup set.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I duplicated your tests and I see that there seems to be a 'link' retained between the databases. I ran the script with test(existing) and test01(new), then I ran the script with test01(just created) and test02(new). Then I ran it with test(existing) and test02(existing) and it overwrote test02. Is there a way to break this link between the databases? I have the flexability to put into the script what ever would be required.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.