What's the best way to create a working copy of a SQL Server database?

Question

I have a SQL database that I am currently converting from an Access database. One of the features of the Access database is to 'Copy DB' and is used when working in a 'dev' site - it copies all of the production data by physically copying the production file into the dev site. In this way, all production data and structure and queries and everything is duplicated into the dev version. This only needs to be done occasionally when we are testing why something is occurring and we don't want to play in production data; not rare, but not frequent either - perhaps once or twice a month. I'm wondering what have other people done to accomplish this when working in SQL?

I'm thinking that I could do a DB backup followed by a restore to the DEV version, but I don't want this backup to interfere with normal backup processes. Is there any way to do this from one DB straight to another instead of going to the file system, and have the backup seem like it never happened (i.e. the REAL backup will still back up all items that really needed to be backed up)?

What other options are there? I have SQL Compare and SQL Data Compare from Red Gate, but I need to expose this functionality to certain users (with high privs and access to the DEV site), and they don't have it.

Answer 1

You can restore a database directly from another database.

If you're using SQL Management Studio, select "From database" instead of "From device" in the Restore Database dialog box.

Answer 2

We do an on-demand backup of the production data, and then restore the backup on the dev machine.

Answer 3

You generally want to restore a whole database from a backup. Trying to do it directly from a live running prod database could cause locking issues for your users. You can do this using SSIS, but it is neither a simple nor quick thing to set up properly.

Another possibility is if you can turn off prod for a time being (only if you have a time period when users are not inthe database). Then detach the database. Detach the dev database and delete it. Copy the file to the dev server and attach both databases again. This can be faster than a restore, but it is a rare environment now that doesn't have 24 hour data access on production.

Incidentally it is very much preferable to have dev and prod on separate servers.

And if you are restoring to dev, you need to make sure any dev changes that have not yet been committed to prod are scripted, so they can be run immediately after the restore. It's best if you script any and all database changes and store them in source control. That makes it easier to do this.

Answer 4

Ok well after looking around a bit, I've come to the conclusion that I do have to go thru the file system, but there is a way to do a backup/restore without affecting the normal backup process by using 'Copy Only' mode. Here's script to do it:

BACKUP DATABASE [ProductionDB] 
TO DISK = N'D:\ProductionDBToDevTransfer.bak' 
WITH
 COPY_ONLY, 
 NOFORMAT, 
 INIT,  
 NAME = N'DB-Full Backup', 
 SKIP, 
 NOREWIND, 
 NOUNLOAD, 
 STATS = 10

RESTORE DATABASE [DevDB] 
FROM DISK = N'D:\ProductionDBToDevTransfer.bak' 
WITH 
 FILE = 1,  
 MOVE N'ProductionDB' TO N'D:\Microsoft\SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DevDB.mdf',  
 MOVE N'ProductionDB_log' TO N'D:\Microsoft\SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DevDB_log.ldf',  
 NOUNLOAD,  
 REPLACE,  
 STATS = 10

Take particular note of the MOVE statements in the RESTORE command... by default, the RESTORE will restore files to the originally backed up physical files, NOT the Dev DB files, despite the fact that you are restoring to the DEV db... I ALMOST found out the hard way, when I did the restore, and SSMS complained that the files were in use by ANOTHER db... OMG, how non-intuitive.

Answer 5

The script above works, but doesn't change the logical file name for the copied server. So, if you try and run it again to reverse the process it will fail in the MOVE statements.

I modified the script a little and came up with the following which seems to work for me. I'm new to this, so be careful!

DECLARE @SOURCEDB nvarchar(100)
DECLARE @SOURCEDBLOG nvarchar(100)
DECLARE @DESTINATIONDB nvarchar(100)
DECLARE @DESTINATIONDBLOG nvarchar(100)
DECLARE @BACKUPDIR nvarchar(100)
DECLARE @BACKUPFILE nvarchar(100)
DECLARE @BACKUPNAME nvarchar(100)
DECLARE @SQLDATADIR nvarchar(100)
DECLARE @SQLDATABACKUPFILE nvarchar(100)
DECLARE @SQLDATABACKUPLOGFILE nvarchar(100)

--CHANGE THESE VALUES TO MATCH YOUR SYSTEM
SET @SOURCEDB = N'test'
SET @DESTINATIONDB = N'test-backup'
SET @BACKUPDIR = N'C:\SHARED\'
SET @SQLDATADIR = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'

--CALCULATED VALUES
SET @SOURCEDBLOG = @SOURCEDB + N'_log'
SET @DESTINATIONDBLOG = @DESTINATIONDB + N'_log'
SET @BACKUPFILE = @BACKUPDIR + @SOURCEDB + N'-to-' + @DESTINATIONDB + N'.bak'
SET @BACKUPNAME = @SOURCEDB + N'-Full Backup'
SET @SQLDATABACKUPFILE = @SQLDATADIR + @DESTINATIONDB + N'.mdf'
SET @SQLDATABACKUPLOGFILE = @SQLDATADIR + @DESTINATIONDBLOG + N'.ldf'

--BACKUP THE DATABASE
BACKUP DATABASE @SOURCEDB
TO DISK = @BACKUPFILE
WITH
        COPY_ONLY, 
        NOFORMAT, 
        INIT,  
        NAME = @BACKUPNAME, 
        SKIP, 
        NOREWIND, 
        NOUNLOAD, 
        STATS = 10

--RESTORE THE BACKUP TO THE NEW DATABASE NAME
RESTORE DATABASE @DESTINATIONDB 
FROM DISK = @BACKUPFILE
WITH 
        FILE = 1,  
        MOVE @SOURCEDB TO @SQLDATABACKUPFILE,  
        MOVE @SOURCEDBLOG TO @SQLDATABACKUPLOGFILE,  
        NOUNLOAD,  
        REPLACE,  
        STATS = 10

--UPDATE THE LOGICAL FILE NAMES
DECLARE @TEMPLATE varchar(500)
DECLARE @SCRIPT varchar(500)
SET @TEMPLATE = N'ALTER DATABASE [{DBNAME}] MODIFY FILE (NAME = [{OLD}], NEWNAME = [{NEW}])'
SET @SCRIPT = REPLACE(REPLACE(REPLACE(@TEMPLATE, '{DBNAME}', @DESTINATIONDB),'{OLD}',@SOURCEDB),'{NEW}',@DESTINATIONDB)
EXEC(@SCRIPT)
SET @SCRIPT = REPLACE(REPLACE(REPLACE(@TEMPLATE, '{DBNAME}', @DESTINATIONDB),'{OLD}',@SOURCEDBLOG),'{NEW}',@DESTINATIONDBLOG)
EXEC(@SCRIPT)