Solved

SQL 2008 R2 - Restoring a database

Posted on 2014-01-19
12
213 Views
Last Modified: 2014-01-25
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
Comment
Question by:pstre
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39792682
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
 

Author Comment

by:pstre
ID: 39792912
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39792929
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
 
LVL 2

Accepted Solution

by:
jaLouden earned 500 total points
ID: 39793000
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
 

Author Comment

by:pstre
ID: 39793124
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 38

Expert Comment

by:Jim P.
ID: 39793140
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
 

Author Comment

by:pstre
ID: 39793190
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
 
LVL 2

Expert Comment

by:jaLouden
ID: 39793240
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39793241
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
 

Assisted Solution

by:pstre
pstre earned 0 total points
ID: 39794177
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
 

Author Closing Comment

by:pstre
ID: 39808413
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now