June 25, 2014 at 8:08 am
We have SQL Server 2012 running on Windows 2008 Server. We need to copy five databases from our 'sandbox' to our test server and then to our production server. The database backup file sizes are 3 MB, 20 MB, 344 MB, 645 MB and 17 GB. We are planning on using the backup and restore method since we already have full backups and the scripts to recreate the logins/users/permissions. We believe this method provides more flexibility and control over the process. However, we have a few jobs, maintenance plans and ssis packages.
To get the jobs to the new server instance, the plan is to script out the jobs on the 'sandbox' and execute the scripts on the test server instance. Is this the best or only way to handle the jobs?
How to get these maintenance plans to the new server instance? (There is no 'script out' maintenance plan option.) We may have to just recreate them on the new server instance. Is this the best or only way to handle the maintenance plans?
We have a few ssis packages. How to handle getting the ssis packages over to the new server instance (using the backup restore method)? These packages use the Project Deployment Model. Therefore, should we restore the SSISDB or open up the package file using VSS on the new server instance and change the connection information to point to the new server instance.
Just wondering if there is any reason or advantage to use the Detach and Attach method or Copy Database Wizard method? I have read where the Copy Database Wizard method handles the database's dependent objects like logins, jobs, maintenance plans, user-defined error messages and shared objects from the master database.
Are there any other move/copy database methods to consider? Just trying to make sure we have thought out everything and are using the best method to copy our databases over to another server instance.
Thanks in advance, Kevin
June 25, 2014 at 1:23 pm
You can do the task in multiple ways. After doing some analysis, I found that following method is simplest way to refresh the databases from production to test on daily basis. If it is one time activity then I prefer backup/restore or attach/detach based on the database size.
Regarding the maintenance packages; as you mentioned only few standard maintenance packages I would prefer to recreate it manually due to limitation scripting of maintenance packages
(you can also try with SSIS packages).
If it is daily maintenance activity then I will follow the below approach.
1.In my environment what I have done is take back up of the databases from production server and copy the backup to test server share through a job. (I have used robo copy to copy the backup’s from production to test server share)
2.Another job on the test servers scripts the logins and stores in a file.
3.Send a mail to the team that database restore started.
4.I have created another job in test server to restore the database the databases from the share where we have copied.
5.Restore the logins in the test server.
6.Once whole process is completed just kickoff job to send a message to application team that database available for use.
June 26, 2014 at 11:17 am
Thanks Hema Kuman.
I am also wondering if all three of these method (backup/restore, detach/attach, & copy database wizard) would automatically perform a database upgrade? For instance, if you are restoring a SQL Server 2005 Backup to a SQL Server 2008 Instance.
Thanks, Kevin
June 27, 2014 at 8:02 am
kevinsql7 (6/26/2014)
Thanks Hema Kuman.I am also wondering if all three of these method (backup/restore, detach/attach, & copy database wizard) would automatically perform a database upgrade? For instance, if you are restoring a SQL Server 2005 Backup to a SQL Server 2008 Instance.
Thanks, Kevin
Yes they would, they'd have to in order to bring the database online.
Maintenance plans are basically SSIS packages, so move them the same way you would any other package
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply