db copy script issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

db copy script issue

Author

Topic

mmcnary
Starting Member

16 Posts

Posted - 2010-03-03 : 14:01:19

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-03 : 14:48:15

In the if exists...
you have commented out the "return 4"
I think that is it.

No, you're never too old to Yak'n'Roll if you're too young to die.

mmcnary
Starting Member

16 Posts

Posted - 2010-03-03 : 15:15:36

Actually, the entire 'if exists' statement for the target db is commented out so I could test running the script twice with the same parms. God knows that never happens, right?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-03 : 16:06:47

Sorry but now I know what you meaned.
I think for such a (paranoid=greetings from ozzy

) test you should do the restore and then do some changes in the source db and then get a new backup and then do the test if the restore would replace your database...

No, you're never too old to Yak'n'Roll if you're too young to die.

mmcnary
Starting Member

16 Posts

Posted - 2010-03-03 : 16:37:39

That's what I did. These db's are empty, so I created a table in test, then ran the sp. Suddenly, there's a new table in test01.
So that's how I am sure that the existing target db is being overwritten.

I'm just not sure why. According to all of the dox I found, sql server should balk at creating a new db of the same name as an existing one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources