Solved

MS SQL -- Nightly DB copy/restore ?

Posted on 2013-06-27
6
367 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

776 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