Solved

SQL 2008 R2 - Restoring a database

Posted on 2014-01-19
12
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 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