How to copy database???

[bloomlight]

We are using MS SQL 2000 SP4. There are 2 database on the system, Live database and test database. We like to keep our Test database the same level as Live database so that our user can get into the test environment for practice purpose.

How can I copy Live database to Test database?

Many Thanks.

 

[SQLBill]

You can do replication, or you can do a backup and restore.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

I gave a pretty simplistic answer, since your question was pretty simple.

We would need to know a lot more, like how often does the Test database need to be refreshed - in other words, how current does the data need to be?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[bloomlight]

Thanks SQLBill.

It will be great if we could have Test data refreshed once a week. Can this be done automatically? Or do I have to do it manually? Thanks.

 

[SQLBill]

You are doing daily backups correct?

Since both databases are on the same server, you have two options for creating a job.

1. using the recent backup. Create a job that Restores the database backup. You will need to have the job use WITH MOVE to put the data/log files in the proper locations and with the proper names. Also make sure you put the new database name in the RESTORE DATABASE line. You will also need to use WITH REPLACE.

2. Have the job do a backup, then restore it as above.

You can either have the job scheduled or run it as needed.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[bloomlight]

Thanks again.

Yes, we do daily backups. I will try 2nd option.

If I want to have another test database, do I need to create an new database first before restoring data to 2nd test?

 

[SQLBill]

You can take the original backup and restore it as many times as you want.

Just change the database name and the file name in the restore script so you don't overwrite the original.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[bloomlight]

More questions about using option 2. When I opened Restore database screen, there are 2 tabs.

The 1st tab is called "General" where I can type name of the new database and select "From Device" then navigate the .bak backup file of the orignal database.

The 2nd tab is called "Options". What I suppose to do there? Can I give any names for Logical file and Physical file? Or do I need to give some specific names for the following areas?

Restore database as:
Logical file name, Move to Physical file name

Thanks.

 

[SQLBill]

I'm not sure. I've never used the GUI. I always script it, there are examples in the Books OnLine. But it's like this:

RESTORE DATABASE MyNwind
   FROM DISK = 'd:\backup_file_path\MyNwind.bak'
   WITH MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf', 
      MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[compuveg]

I've got a monthly the SQL Server Agent executes that does this...

BACKUP DATABASE [MyLiveDB] TO DISK = N'D:\monthend\MonthEndBackup.bkf' WITH NOINIT , NOUNLOAD , NAME = N'MyLiveDB backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='MyLiveDB'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='MyLiveDB')
RESTORE VERIFYONLY FROM DISK = N'D:\monthend\MonthEndBackup.bkf' WITH FILE = @i

then I can go in and restore it whenever to wherever, as long as no one is in the test DB.

I also nightly backup the DB, but the scheduled backup to disk is easier to restore. It also occurs at midnight, so I've go thte data partitioned off by month in our warehouse.

I used to take the DB offline, copy the MDF and LDF, then attach it as another DB... my backup copy. The scheduled backup to disk is easier though.