| Author |
Topic |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2004-01-14 : 05:03:50
|
| hello all,thank you in advance for any info.basically my subject is what I need to accomplish.I want to backup one database, zip it up ( as it is 2 gigs in size), copy the zip file to another server, unzip the file, then restore the backup over another database.is this something I can do with DTS or just create a couple of jobs ?if so how ?thanks again for any helpJamie |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2004-01-14 : 05:31:07
|
| Is this gonna be a one time job or a reacurring event (let's say every day)? |
 |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-14 : 05:32:01
|
| a reacurring event.(thanks for your quick response) |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2004-01-14 : 05:49:31
|
| Hmm... I doubt that the described procedure could be acomplished with using only a SQL server job and that such solution would work 100%. It can be done though, but first of all you have to tell us why do you need to restore the DB on another server? Could a replication serve the purpose?RH |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-14 : 05:59:07
|
| I need to copy the data from our live server to a test server.I thought I could create a dts with a task for each step.however,replication could be the way forward. I'll look into that.thanks. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-14 : 06:05:52
|
| none of the servers have replication configured, I don't know how to, so a DTS would be better. although, would replication be faster ? |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-14 : 06:52:14
|
| I've started on a DTS package, I can copy a file from one server to another,(not doing any zipping just yet) but when I try to restore, I get an error, database is in use, system administrator needs exclusive use.I've looked in the current activity and nooone is connected !? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-01-14 : 07:12:12
|
| The zip/upzip stages may not be worth it.....you would want a lot of repetative data values to get worthwhile compression and thus a reduced backup/restore time-frame to overcome the zip/unzip times.A product like SQLLite may suit you more.....i've seen it advertised here or on SQLServerCentral.com. It seems to advertise fast (or compressed) backup/restore.No idea of the cost...or it's effectiveness. |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2004-01-14 : 07:20:05
|
| The way you describe the problem a transactional replication would do the job. At first it may be a little more difficult to set it up, but once you get it up and running it will be worth the trouble.I assume that you are the DBadmin and that you have full access to both servers. Set up both servers for replication. Look it up in BOL. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-14 : 07:30:09
|
| thanks rihardh, I'm currently working on the DTS, I can copy a file, still working on the restore, I just need to create a backup script on hopefully it will work, failing that I'll try the replication.andrew, thanks for the info. regarding zip, I'll check the time it takes to copy files, it might be ok.not really wanting to spend money sqllite. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-14 : 07:44:09
|
| Unless you have to move something across separate domains, you can always copy the backup file to a UNC path and restore from there (or even back up directly to the UNC, but it's not recommended). You don't need to zip or unzip anything. See this for some more info:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31789No need for DTS at all, in fact it's MUCH easier to do this in T-SQL (two or three commands at most) |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-14 : 07:55:06
|
| so say I have live server and test server,do I run the backup and restore from the live server, making sure the restore to \\test-server\sql_data? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-14 : 08:10:48
|
| Here's the shortest version I can think of. Run this on your production (live) server:BACKUP DATABASE myDB TO DISK='\\myUNCPath\morePath\myDB.back' WITH STATS=10Now go to your test server and run:RESTORE DATABASE myDB FROM DISK='\\myUNCPath\morePath\myDB.back' WITH REPLACE, STATS=10Both of these commands are run in Query Analyzer. You need the REPLACE option if you are replacing an existing database. If you are restoring it to a brand new database, you need to use the MOVE option. Books Online has all the details on BACKUP and RESTORE. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-14 : 08:14:53
|
| thats nice and simple, I'll give it ago.(however this relies on having query analyzer and accessing both servers), I plan to create one script or sp that a user can run from say an asp page with a click of a button.is it possible to jump from one server to another like we use :use databasego to connect to another db ?thanks for all your help. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-14 : 08:26:51
|
| You do not need to USE a database to back it up or restore it (in fact you cannot restore a database if anyone is using it) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-14 : 08:43:16
|
| DTS????Create a job (I would create a job on the source server to do the backup, zip then another job on the destination to do the copy, unzip, restore).Backup is simple - just a backup database command.Zip - use xp_cmdshell to call winzip with the command.copy - use xp_cmdshellunzip - as aboverestore - restore database command. You may have to kill users and move the files as part of this.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-14 : 13:01:34
|
| everyone seems to have a problem with DTS , why not use it for this ?I've created a dts package and all is working, the copying of the file from one server to another is really slow, how can I use winzip in a dts ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-14 : 13:08:01
|
| Because DTS is not needed. You are just going to make things slow. Why not go with the better and faster solution?If you are going to zip it up, then you'll need to use the command version of it and call it using xp_cmdshell as Nigel mentioned.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-14 : 13:11:36
|
| The problem with dts in this case is that it it is just an application executing commands that can be executed from sql server. Bit like creating a VB app to call a stored proc and scheduling that rather than scheduling the SP.Does it add anything to the process?Call winzip from a execute process task (or directly from a job step).c:\winzip\wzzip c:\data\mydb.zip c:\data\mydb.bak==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-01-15 : 09:34:21
|
| the thing I don't understand is how can I run 1 t-sql script from one server that will backup database from another server ?I don't really want to setup different jobs on different servers.just have one script that I run from the test server, that backsup the database on the live server... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-15 : 10:10:55
|
| You can connect to the other server to run the sql - over a linked server maybe.Same as dts will have to connect to the server to run the sql.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Next Page
|
backup, zip, move, unzip, restore