Solved

SQL 2008 R2 - Restoring a database

Posted on 2014-01-19
12
215 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Max Consumption Rate (MCR) 3 32
SQL Query stumper 3 36
Updating a table from a temp table 4 26
SQL Server stored proc 2 10
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach 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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

914 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

21 Experts available now in Live!

Get 1:1 Help Now