MSSQL 762 - CREATE CPY CO. IN ENTERP MGR 2

[leishad]

USING MSSQL 7.6.2

PROCESS: CREATE A COPY OF EXISTING DATABASE FOR 'DEMO' PURPOSES

PROBLEM: CANNOT LOCATE PROPER DOCUMENTATION ON COMPLETE METHOD

---------------------------------------------------------------------------------------
WHAT I HAVE DONE:

PROGRESSION SETUP UTILITY - ADD A NEW DATABASE 010

MSSQL ENTERPRISE MANAGER - GO TO CURRENT DATABASE RIGHT CLICK>ALL TASKS>EXPORT DATA
I SELECT MY TO AND FROM SOURCES
THEN NEXT SCREEN TO SPECIFY TABLE COPY OR QUERY THERE ARE THREE CHOICES:
1)COPY TABLE AND VIEWS.... (i used this one and i get an error on all files like ...fld,...frm,...rst)
2)USE A QUERY....
3)COPY OBJECTS AND DATA....


WHEN I FINISH IN ENTERPRISE MANAGER I LOGIN TO THIS NEW COMPANY - MAINTAIN - COMPANY SETUP AND CHANGE THE NAME.

I THEN WENT INTO VISUAL MENU BUILDER TO SYNCHRONIZE SECURITY SETTINGS.
----------------------------------------------------------------------------------------
Despite the errors on the copy, I went into order entry to enter an order and it gave me the following error:

"severe COM/ADO error occured in MacMSS.dll(ver.1.0.35.9)
DB Provider Error: Native Error number 515.
Error number: 0x80040e2f
Cannot insert the value Null into column A4GLIdentity
..........more........"

The other issue when copying was that when I selected my destination database it would not
let me use windows authentication - so I had to use the sa login. This had no impact on
logging in in Macola but I cannot view the database tables in enterprise manager because it see's
only my windows login.
----------------------------------------------------------------------------------------

IF THERE IS A SCREEN BY SCREEN WALK THRU DOC FOR THIS PROCESS I WOULD GREATLY APPRECIATE IT -
I COULD NOT LOCATE ANYTHING COVERING THIS METHOD IN THE MACOLA SUPPORT DOCS

 

[kscheeler]

First, you want to use the 3rd choice which is copy objects. On the following screen, you can keep the defaults which will make it drop the objects first, recreate them with the same rights as the original database and then replace all of the existing data with new data. This can be slow though depending on the amount of data you are copying.

Another approach I prefer is to restore a backup of the live database onto the test database. It's much faster than DTS but DTS can be scheduled to run say once a week to keep your test company updated.

The key thing in restoring a backup and overwriting a database is the "restore as database" field. This is where you have to type in the database you want to overwrite, like your test database. I then choose device and then browse to find the specific .bak file that was created from my db maint plan. Typically, the backup from the night before.

If you post your email, I could send you a doc on on restoring a backup.

Kevin Scheeler

 

[leishad]

Thank-you - my email is below. From what you have said it looks like I start by right-clicking (on the original database) and choosing All Tasks - Restore Database. On the next screen it currently says Restore Database As Data I would change it to Restore Database As Data_10(mytest). I also see that it already has last nights backup file listed in the restore box (this is definitely looking like the way to go) ---- I will wait for your documentation as I always err on the side of caution----------

leisha@archplastics.com

 

[kscheeler]

I'll send something tonight. When you first open up the restore screen, it shows you the backup's from the maintenance plan. Sometimes it can take a while to open that screen. You can choose the one on the main list or go to device and end up browsing in the backup folder for a specific .bak file which are the nightly full backups.

Also, always check the options tab to make sure it's changed the actual physical file name to restore to. For the most part, it works fine in SQL 2000 but I've seen issues with SQL 7 and not checking the options tab.

Kevin

 

[MacolaHelp]

I also find in creating a new company that you should go into setup & define the modules you want. This in turn initializes those form files you were having trouble with importing from the live company.

I totally agree on the restore of pre-existing db into your target company both for speed & ease of use. I haven't been that impressed with DTS, especially with large databases.