Hello,

I need to be able to automatically backup an entire database on one server and restore it on another.

Currently I am doing this manually in Enterprise Manager by right clicking on the database going to All Tasks and Backup. I back it up to a file. Copy it over to the other server using a network drive. And then Use the Enterprise Manager on the other server to Restore the database.

In all actuality, the Enterprise Manager can see both servers on the destination server, but I have just been following process that was set by someone else.

I need a simple way to automatically copy the entire database from one server to another every night/early morning.

I either just need a simple batch file or I was looking at Replication might be designed for this but seemed overly complicated for what I need.

Any suggestions?

Thanks,

Neofree

You should be able to set this up as a DTS or SSIS job very easily.

You could use the Copy SQL Server Objects in DTS \SSIS

OK both of these are looking to be overly complicated for what I need. Unless you have some examples or links that makes this seem easier than it looks. I tried googling..

Thanks,

Neofree

You need a simpler method of copying databases than DTS or SSIS?
Perhaps a database gnome that will transcribe each day's log files onto tiny four-leaf clovers and hand them to a pixie who will carry them to the remote server and slip them through the cooling vent.

In searching I found this:

So this seems like a *simple* solution, but I couldn't find a way to Restore just as easily. Once I figure that out a simple batch file and scheduled task are no brainers..

Thanks,

Neofree

Also just to comment. Someone who never used DTS/SSIS, or even is a DBA or regular SQL user (I mainly do general IT stuff, but have written the most basic of SQL statements - still the most "experienced" on our team lol), it'd probably take me a few hours to read up on DTS/SSIS, based on the search results I found from Google.. It looks like it might be an easy visual system or something, but if you don't already "know" a lot before you look at it, it's suddenly not simple. Perhaps a Youtube video of someone doing this would make it all look simple.

Anyhow.. Any help would be highly appreciated.

Thanks,

Neofree

Really, its not that hard for simple tasks such as data loading or database copying. Though database copying in SSIS is a bit more quirky than in DTS, and certainly more quirky than it needs to be.
We're trying to help you out with best-practices here.
Give it a try. Set up a sample database and try using an ETL tool to transfer it. If you run into a specific error, we can help you trouble-shoot it.
Are you running 2000, or 2005?

Pretty much the same command, but RESTORE Database with move...

i.e.:

RESTORE DATABASE SecurityMaster
FROM DISK = N'\\servername\sharename\Backup\SecurityMaster.bak '
WITH MOVE 'securitymaster_Data' to 'C:\MSSQL\DATA\SecurityMaster_data.mdf',
MOVE 'securitymaster_Log' to 'C:\MSSQL\DBLogs\SecurityMaster_log.ldf',
STATS=10

Thanks, I got by with a simpler RESTORE command. I don't really have time to study SQL much more right now.. SQL 2000 btw.

Thanks,

Neofree

If it were only that easy. (-; Either I'm going about it the wrong way (which I think is the case) or something else is out of whack.

I have two SQL 2005 SP3 servers. I want to have an SSIS job that copies 4 DBs from Server A to Server B then do a bit of Data Scrubbing.

I started with the Transfer Database Task. I get Errors about it either cannot create files, or users do or dont exist...

If I write a script to import all the users, then the users reference a DB that is not there yet. If the Transfer Task does it, then there are users that already do exist and it does not like that either.

Suggestions?

Thanks

Idea here only
what about write a script that stop the SQL Xcopy the database files
start the SQL again

then on the remote computer stop that SQL Xcopy the File to the SQL DATABASE folder
and start the SQL again

The source needs to be online.

I think I've gotten past the Copy Issue. Though it was a mis-mash of manually creating some of the users on the destination server.

Now if there was a way to do something like:

delete * from pr*

where pr* is any table that starts with PR...

You can write dynamic SQL to do that, referencing the sys.tables schema object.

No. This is bad bad bad. It must be a teeny tiny prod environment that lets you kill the service. Then the databases have to go into recovery and start applying the t-log stuff that has not made it to the db and with this carries the risk of torn pages and going into suspect mode. Have fun with this loaded gun. I am glad I am not a DBA anymore.