Solved

MS SQL -- Nightly DB copy/restore ?

Posted on 2013-06-27
6
372 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

617 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