Backup LIVE data, restore to TEST 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 2008 Forums

SQL Server Administration (2008)

Backup LIVE data, restore to TEST database

Author

Topic

SQLReid
Starting Member

3 Posts

Posted - 2011-02-08 : 12:17:48

I'm fairly new at SQL and would like to test out program features using our TEST database.

Using SSMS 2008, I tried restoring our TEST database using LIVE database's backup. It produced an error: the backup belonged to the LIVE database and it's different from the TEST database.

Altering the SQL.MDF and SQL_LOG.LDF files for the LIVE backup (in the OPTIONS) didn't work either.

I tried using the query:

RESTORE DATABASE TESTDATABASE
FROM DISK 'e:\backup\LIVEDATA.BAK
WITH REPLACE

..and it too got hung up.

I tried exporting/importing, and it gave an error too.

Is there a simple way to copy data to the TEST database. Of course, the end result would not affect the LIVE data in any way.

Any help is much appreciated. Thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-08 : 12:40:32

1. Get the logical file names from:


RESTORE FILELISTONLY
FROM DISK =  'e:\backup\LIVEDATA.BAK';

Then move/rename the files. Something like:


RESTORE DATABASE Test
FROM DISK = 'e:\backup\LIVEDATA.BAK'
WITH RECOVERY, REPLACE
	-- Syntax is MOVE LogicalName TO PhysicalName
	,MOVE 'LIVEDATA' TO 'F:\SQLData\Test.mdf'
	,MOVE 'LIVEDATA_log' TO 'G:\SQLData\Test_log.ldf'

SQLReid
Starting Member

3 Posts

Posted - 2011-02-08 : 14:39:19

Thanks Ifor. Now, SQL is complaining that there's not enough space on the target drive.

The filelist shows that the source database data is only 800 MB, but the log file is sized at 92 GB.

Is there a way I can reduce the log size?

Thanks again.

SQLReid
Starting Member

3 Posts

Posted - 2011-02-08 : 14:42:39

Please disregard my 2nd post - I think I found the answer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources