copying data base from producction server to backup server -- sql server dts

Andre Gibson
1/13/2006 5:36:05 AM

Good Morning all I am a bit new to the sql world. I am using a dts package to
copy a db from our production server to our backup server. I followed the
instructions I found on this site to the letter
https://help.maximumasp.com/SmarterTicket/Customer/KBArticle.aspx?articleid=659

1. log on via EM to your current database, right click the db, all tasks,
export data.
2. Next, next, then type the sql db specifics for the database that you want
to export to.
3. Choose "copy objects and data....", next. (If you choose "tables and
views" instead of "copy objects and data", you will lose data)
4. Uncheck "use default options", click options, under security, uncheck all
check boxes, then click ok.
5. Click next, then next again.

Everything worked well until I hit a view that the system did not like. The
packagfe kicked back this error message and aborted "[Microsoft][ODBC SQL
Driver][SQL Server] Invalid object name 'STOREROOM1'."
Here is the view:

CREATE VIEW STOREROOM1
AS
SELECT MSTY as STOREROOM, MSTYDESC as DESCRIPT, MSTYVALUE as STOREGROUP,
BIZUNITNO =
CASE
WHEN MSTYVALUE LIKE '%FFF%' THEN '2551'
WHEN MSTYVALUE LIKE '%AAA%' THEN '2560'
WHEN MSTYVALUE LIKE '%CCCC%' THEN '2563'
END
FROM MISC
WHERE MISC.MSCL = 'ST'

Have any of you seen this before and how can I resolve the issue? The sql
seems fine to me. What am I missing. Please remember I am a rookie so break
it down as simply as possible.

Thanks

dre

Ryan
1/13/2006 2:05:16 PM

If it's a copy of a production database you're after you might consider
using a different approach.

Familiarise yourself with the following in SQL Books Onlie :-

BACKUP DATABASE

RESTORE DATABASE

--
HTH. Ryan
[quoted text, click to view]

sql server dts : copying data base from producction server to backup server