Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mldardy
Starting Member
13 Posts |
 Posted - 2009-10-01 : 15:35:53
|
| I need to know what is the best way and how to create a script or procedure where I can create a backup of a database on one server(production) and restore that database onto another server(test). The database currently exists on both servers but I want to keep the data on test up to date with production server. Does anyone know the best way to do this and possibly how to do this.Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-01 : 15:46:46
|
| you can schedule a backup job which takes a backup from production db and restores it to test db and make it run once overnight daily |
 |
|
|
mldardy
Starting Member
13 Posts |
Posted - 2009-10-01 : 16:15:05
|
| Where do I schedule the backup job. Is that just a simple right click on the database name and select backup in SSMS or do I do that somewhere else? I looked at the backup in SSMS and didn't see where to schedule it at. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-01 : 16:24:17
|
| nope..you need to schedule it in sql server agenthttp://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx |
|
|
|
mldardy
Starting Member
13 Posts |
Posted - 2009-10-01 : 16:30:25
|
| OK now here is my other problem. I have SQL server agent but only on the production SQL server in SSMS. It doesn't exist on my test or development server so how will I restore the backup to the development server using SQL Server Agent if it is only on the prod server. |
|
|
|
mldardy
Starting Member
13 Posts |
Posted - 2009-10-02 : 09:54:15
|
| visakh16,Can you provide assistance on my problem in my last post before this one. |
|
|
|
mldardy
Starting Member
13 Posts |
Posted - 2009-10-05 : 23:02:33
|
| Does anyone have any ideas on what I could do with this. I really could use some serious help with doing this. I know there is a way I just need to know how. |
|
|
|
sanoj_av
Posting Yak Master
118 Posts |
Posted - 2009-10-06 : 00:06:02
|
| You have to either enable the SQL Serever agent to do the restoration through a SQL server job or you can make use of the SQLCMD command line tool. Write a batch file that calls SQLCMD and execute your restoration script. Then setup a windows scheduled task to call that batch file whenever you require it. |
|
|
|
|
|
|
Need help with script to backup db diff. servers