Make 'Training' copy of database

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

SQL Server 2000 Forums

Transact-SQL (2000)

Make 'Training' copy of database

Author

Topic

mapage
Starting Member

9 Posts

Posted - 2005-07-20 : 18:00:28

I am trying to make a 'training' copy of a production database. The idea is that the system users will have data that is current up to yesterday to play with.

I currently have a TSQL script that runs every night that drops all of the tables in the training database and then INSERT's INTO the training database all of the data from the production database. The problem is that none of the contstraints go with it. To fix that, I have another script that also runs every night (as part of the same job) that adds the constraints to the tables that need them. The problem is that the constraints change and I don't want to have to futz with the TSQL every time a contraint changes.

So... The question is, is there a way to add the constraints from the production database to the training database in a better way? Or even better, is there a better way to make my training copy?

Thanks for the help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 18:02:25

Just backup the production database, then perform a restore using a different database name.

RESTORE DATABASE NewDBName
FROM DISK = 'E:\SomeFile.BAK'
WITH REPLACE, MOVE...

Tara

mapage
Starting Member

9 Posts

Posted - 2005-07-20 : 18:26:06

The filename is different every night. I guess it wouldn't be that difficult to figure out the filename...

Do I have any other options?

I wonder if I truncate the tables instead of dropping them and then insert into training select * from production instead of using select into...

Thanks for your quick reply!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 18:29:09

Backup/Restore is definitely the way to go. It'll certainly be the easiest and fastest.

Here's a query that I had in my toolbox that gets the last backup's filename for each of the user databases:

SELECT bs.database_name, MAX(bms.physical_device_name)
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name
WHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND
s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind')
GROUP BY bs.database_name

Tara

mapage
Starting Member

9 Posts

Posted - 2005-07-20 : 18:33:27

I was your 11000'th (and 11001'th) post! I feel so special!

Thanks again for the help. I will work on changing my code to restore a backup rather than copy the info directly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 18:36:12

quote:
Originally posted by mapage

I was your 11000'th (and 11001'th) post! I feel so special!

Cool! I didn't realize I was that close to 11k.

Tara

mapage
Starting Member

9 Posts

Posted - 2005-07-20 : 20:43:00

Okay... I don't understand your query enough to fix this myself, so I'm going to beg for more info...

This is returning the transaction log instead of the actual backup file. Can your script be modified to return files with a .bak extension instead of .trn? Or am I doing something wrong?

One other question, when I restore the backed up database over the old database, do I need to move the files or will it replace the old files? From what I read, it tries to keep the file names from the original backup instead of the taking the filenames of the database you are trying to scrub.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-21 : 12:21:51

Sorry, yes I had a bug in the query. Here you go:


SELECT bs.database_name, MAX(bmf.physical_device_name)
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name
WHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND
s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind') AND bs.type = 'D'
GROUP BY bs.database_name

On the restore, you use a different name for the database name. Then you'll also need to use the WITH MOVE command if you are restoring on the same server so you don't restore over the original MDF and LDF which would wipe out production. If you are doing this on a separate server, you only need WITH MOVE if you want to change the MDF or LDF file names or their locations (like for example if the production path doesn't exist on the other server). Here's an example RESTORE:

RESTORE DATABASE NewName
FROM DISK = 'F:\MSSQL\Backup\SomeFile.BAK'
WITH REPLACE, MOVE 'LogicalNameOfMDF' TO 'E:\MSSQL\DATA\NewName_Data.MDF',
MOVE 'LogicalNameofLDF' TO 'E:\MSSQL\DATA\NewName_Log.LDF'

You can run RESTORE FILELISTONLY to see the logical names:

RESTORE FILELISTONLY
FROM DISK = 'F:\MSSQL\Backup\SomeFile.BAK'

You only need to run this once so that you can figure out what you need for the WITH MOVE.

Tara

mapage
Starting Member

9 Posts

Posted - 2005-07-21 : 12:32:19

I like that better than the solution I found... I switched the Max with Min and it worked. I hate arbitrary solutions like that, though.

Thanks to your help, I have a working solution tested and scheduled to run every day. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-21 : 12:34:27

Glad to help.

Tara

mapage
Starting Member

9 Posts

Posted - 2005-07-22 : 12:11:03

Another problem with this... I can run the script I created based on your information without problem in the Query Analyzer, but when I try to run it in a scheduled job it fails with the message that it can't get an exclusive lock on the database. Do you know how I can fix that? I don't have query analyzer open and there shouldn't be anyone accessing the database at 3 AM when I have the job run..

I'm grateful for any help you can give...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-22 : 12:14:37

Well someone is connected. Perhaps the job itself. Make sure master is selected in the job step. But anyway, do this just to be sure you kick everyone out:

ALTER DATABASE DBNameGoesHere SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

GO

RESTORE...

Tara

mapage
Starting Member

9 Posts

Posted - 2005-07-22 : 12:19:21

Once the job has finished, will it automatically allow multi-user or do I have to run another alter database command?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-22 : 12:22:07

It will take on the settings of the database that was backed up. So if the source database is how you want things to look like after the restore of your training database, then it'll look just like that. Otherwise, you'll need to add commands to the script.

Tara

mapage
Starting Member

9 Posts

Posted - 2005-07-22 : 12:23:18

Interesting... I changed the database from mine to Master in the job step and now it works fine. Goofy program.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-22 : 12:25:22

Yes you should always have master selected when restoring a database.

Make sure to include the ALTER DATABASE command anyway to cover all bases.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources