September 2, 2010 at 8:11 am
Hi All,
I was working on a task, and had to automate the below process:
1) Take a backup of a database DB-1 from Server A.
2) Copy the bak file of DB-1 from Server A to Server B.
3) Restore the bak as DB-1_new in Server B.
Which is the best way to do this? SSIS or a batch file or could this be scripted in? Please advise.
Thanks,
VM
September 2, 2010 at 8:25 am
I have a process in place now to copy a production database to a development database.
I do this in an SSIS that consists of a SQL task that runs the backup, a system task that copies the file and another SQL task that restores the file. this method has worked very well for me.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 8, 2010 at 1:18 pm
You can do this by using following ways:
1. copy file using batch script.
2. restore database using tsql script.
3. fix logins using tsql.
Put these steps in sql server agent job steps. Then setup the notification for monitoring purpose.
thanks
AKP
September 8, 2010 at 1:29 pm
I'd do this with scripting, as that could easily be embedded in an SSIS package if needed.
I'd do my backup on the first server, using a separate file/name for each day. Then use a script to copy this to the remote server with a standard name. Then a job could restore this with a standard name and WITH MOVE option each day.
September 8, 2010 at 1:42 pm
- We create the backup file on the production server.
- Copy that file to safezone. (next step of the backup job)
This is scheduled at 19:30
On some of our dev servers, a restore job is scheduled at 01:00.
That should give the prod job time enough to complete and copy.
Small bak files are copied locally (from safezone) and then restored.
For large bak files we restore using the UNC ref of the safezone.
After restore Security is being fixed.
- prod windows groups are being replaced by dev windows groups (naming conventions !)
- other windows users are not being granted sqlinstance access !!
- SQLusers are mapped using "sp_change_users_login @Action = ''Update_One'"
Off course this means we don't adapt for "new" sqluser ids.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply