Solved

MS SQL -- Nightly DB copy/restore ?

Posted on 2013-06-27
6
370 Views
Last Modified: 2016-02-11
How can I do something like the below automatically ?

 1. backup "MyDBName" database on DBPROD server
 2. restore to "MyDBName" database on DBTEST server
-------------------------------------------------------------------------------
I always want the most current database values
so I can test issues that might of happened.

I also keep a DBDEV database that has different
table structures for continued development.
0
Comment
Question by:finance_teacher
[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
  • 4
6 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 167 total points
ID: 39281255
This can be achieved a number of ways.

1 - Use SSIS to automate the entire process of backup, copy, restore
2 - Use SQL Server mirroring and create nightly snapshots if you only want to read the data
3 - SQL Serve agent job which does a backup, cope, restore using cmdexec to connect and restore to the remote instane.
0
 
LVL 8

Assisted Solution

by:virtuadept
virtuadept earned 167 total points
ID: 39281389
We use method 3.

On prod we have a SQL Server Agent job that does a nightly backup, and copies it somewhere where TEST server can see it.

On test we have a SQL Server Agent job that restores the backup to a test database.

Caveat, make sure you put some good error checking in the agent jobs so that it will not try to restore a backup that is bad, or something to recover the database if the restore fails from the previous backup. Otherwise you'll come in and find that your test database is not ready and you'll have to do it manually anyway.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39281398
Probably also best to put some functionality in place to ensure you kill all active users on the destination before restoring. I think the cleanest way is probably SSIS if you know how as you can connect to multiple instances quite easily.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 166 total points
ID: 39282042
Another thing is that you need to make sure that the logins that have access to the database on source exist on destination server as well and you will have to syng the SIDs between the login and the ccoresponding user on the database. Users come with the database an they will have the SIDs from the source server, which are different from the destination  (they are GUIDs) even if the names are the same.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39283631
Another thing is that you need to make sure that the logins that have access to the database on source exist on destination server as well and you will have to syng the SIDs between the login and the ccoresponding user on the database. Users come with the database an they will have the SIDs from the source server, which are different from the destination  (they are GUIDs) even if the names are the same.

With regard to orphaned users, with a windows database user, as long as the login has been created at the SQL Server level the user will not become orphaned but SQL Server logins GUID will not match as per Zberteoc's comment. To resolve this you can use the SP sp_change_users_login below is the syntax for this proc.

http://msdn.microsoft.com/en-us/library/ms174378(v=sql.90).aspx
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39293266
Is this question sufficiently answered?
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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