Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

SQL 2008 R2 - Restoring a database

I have a production database and a test database.  I need to refresh the test database with the lastest data from the production database.  How do I do this?
0
pstre
Asked:
pstre
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
Anthony PerkinsCommented:
There are many ways to do this and depend entirely on your specific requirements and environment.  They include:
Backup and restore.
Third party tools such as Red-Gate's SQL Compare and SQL Data Compare.
Writing a custom app.
0
 
pstreAuthor Commented:
I need to do a backup and restore.  I have backed up the source database and not sure what to do next.  Thank you!
0
 
Jim P.Commented:
Copy the bak file to the test server.

Then using the SQL Management studio connect to the test SQL Server. Right Click and then under Tasks there should be a Restore Database option. Follow the prompts to find the backup file and do the restore.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
jaLoudenCommented:
Hey Pstre,

the easiest way (for me) is via the query window. If your prod database was called prod, and you wanted to restore it to a database called tst then I would follow the below steps

On the production database I would issue the following command

BACKUP DATABASE PRD TO DISK='C:\temp\prd_backup.bak" with COMPRESS

Open in new window


this will create a file in c:\temp called prd_backup.bak that is compressed. You don't need compression but if you are copying the file around slow network links this helps.

Lets assume you have copied the backup file to c:\temp on the test server. I'd run the following command

 RESTORE DATABASE TST 
FROM DISK='C:\temp\prd_backup.bak 
WITH MOVE 'LogicalDataName' to D:\datafiles\tst.mdf',
MOVE 'LogicalLogName' to L:\logfiles\tst.mdf'
 

Open in new window


This will restore your databse with the name of tst. The move commands allow you to relocate the files to different directories than prod, but IMO the most important thing they do is rename the files to tst. I've found that having datafiles that have the name of PRD but relating to a TST database can be confusing, hence I always rename them to the name of that database.

The LogicalDataName and LogicalLogName are the logical names given to the data and log file. If you are unsure what they are, on the production database run the following

use prd
go
select name, physical_name from sys.database_files
go

Open in new window


Hope this helps.

Cheers
JL
0
 
pstreAuthor Commented:
I'm almost there.  I backed up the dataase and trying to restore using your commands but not sure what to put in the lines 3 and 4.  I am restoring ZTEST from TWO

 RESTORE DATABASE ZTEST
FROM DISK='C:\temp\TWO_backup.bak
WITH MOVE 'LogicalDataName' to D:\datafiles\tst.mdf', (not sure what to put here
MOVE 'LogicalLogName' to L:\logfiles\tst.mdf' (not sure what to put here)

GPSTWODat.mdf      S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSTWODat.mdf
GPSTWOLog.ldf      S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSTWOLog.ldf
0
 
Jim P.Commented:
To find the logical and physical filenames and locations do a
Restore filelistonly
from DISK='C:\temp\TWO_backup.bak'

Open in new window


Generally the command would look like

RESTORE DATABASE ZTEST
FROM DISK='C:\temp\TWO_backup.bak'
WITH replace, 
MOVE 'GPSTWODat' to 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSTWODat.mdf'
MOVE 'GPSTWOLog' to 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSTWOLog.ldf'

Open in new window

0
 
pstreAuthor Commented:
Lines 3 and 4 are not working.  What would I move TWO to TWO when I am trying to restore the ZTEST database with the source of TWO

RESTORE DATABASE ZTEST
FROM DISK='C:\temp\TWO_backup.bak'
WITH replace,
MOVE 'GPSTWODat' to 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSTWODat.mdf'
MOVE 'GPSTWOLog' to 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSTWOLog.ldf'

error message: Msg 3023, Level 16, State 5, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Msg 3204, Level 16, State 1, Line 1
The backup or restore was aborted.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 
jaLoudenCommented:
Hey pstre,

You are missing a comma at the end of the first line

MOVE 'GPSTWODat' to 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSTWODat.mdf',
MOVE 'GPSTWOLog' to 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSTWOLog.ldf'
0
 
Jim P.Commented:
error message: Msg 3023, Level 16, State 5, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.


No your SQL Server's peabrain thinks another restore is running. So check and see if any are still going.

If you can't find one that is running, then the easiest way is just to resart the SQL Sever Services and try again.
0
 
pstreAuthor Commented:
the information in the above posts for lines 3 and 4 were incorrect.  That is why I was getting error messages.  I finally figured it out and it is attached below.  thanks for everyone's help in getting me started on this.  I love doing it this way!!!

RESTORE DATABASE ZTEST
FROM DISK='C:\temp\TWO_backup.bak'
WITH replace,
MOVE 'GPSTWODat.mdf' to 'S:\MSSQL\Data\ZTEST.mdf',
MOVE 'GPSTWOLog.ldf' to 'L:\MSSQL\Data\ZTEST.ldf'
0
 
pstreAuthor Commented:
I was having problems with lines 3 and 4 ( getting error messages).  I finally just kept playing with it and found the right text to put in lines 3 and 4.  The answers got me started and I am thankful for those who contributed..
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now