Installing MSDE - User Security Issues...

From: Liddle Feesh (._at_com.com)
Date: 12/12/03 

Date: Fri, 12 Dec 2003 15:15:18 -0000

Hi,

Could someone point me in the direction of a step-by-step tutorial for
replicating a SQL Server 2000 database as an MSDE database in a seperate
machine, with all tables (including data), users/security, etc.

Currently I'm taking a backup (I can't script the data from Ent. Mgr) and
using oSQL to restore the database to an MSDE install on a machine.

It's done in two steps - installing MSDE and restoring the backup. However,
after the backup has been restored (the user restores correctly) - I am
unable to connect to the database as the default user that I created in the
backup. If I remove the user and re-add him via Ent. Mgr then I can
connect... does anyone know why this is?

First Step - Install MSDE script:
^^^^^^^^^^^^^^^^^^^^^^^^
Set objShell = CreateObject("WScript.Shell")
Set fso = createobject("scripting.filesystemobject")
Set WshNetwork = WScript.CreateObject("WScript.Network")

objShell.Exec(fso.getabsolutepathname(".") & "\setup.exe BLANKSAPWD=1
SAPWD="""" SECURITYMODE=SQL TARGETDIR=""C:\Program Files\Microsoft SQL
Server\Mssql\Binn\"" DATADIR=""C:\Program Files\Microsoft SQL
Server\Mssql\Data\"" instancename=""" & WshNetwork.ComputerName &
"\DatabaseName""")

Second Step - Restore the database backup:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Set objShell = CreateObject("WScript.Shell")
set fso = createobject("scripting.filesystemobject")
Set WshNetwork = WScript.CreateObject("WScript.Network")

currentpath = fso.getabsolutepathname(".")

' Start the msde service
objShell.Exec("net start MSSQL$" & WshNetwork.ComputerName &
"\DatabaseName")

' Make local data and backup directories
'objShell.Exec("md c:\SQL_Databases\DATA")
'objShell.Exec("md c:\SQL_Databases\BACKUP")

' Copy this Repository.bak to the SQL_Databases DatabaseName.bak
fso.CopyFile currentpath &
"\Repository.bak","c:\SQL_Databases\BACKUP\DatabaseName.bak"

' Restore
objShell.Exec(currentpath & "\osql.exe -S" & WshNetwork.ComputerName &
"\DatabaseName -E -Q ""restore database DatabaseName from disk =
'C:\SQL_Databases\BACKUP\DatabaseName.bak'""")

------------

All I want to do is reproduce one of my SQL Server databases AS IS on
several client workstations by way of an MSDE installer.

Any help gratefully appreciated !

Thanks,

Mike

xposted to closely related groups: .sqlserver.msde, .sqlserver.security,
.sqlserver.setup 

-- 
Liddle Feesh
*fap fap fap fap*
<><   <><    <><  <><    ><>
         <><    <><     <><    <><  <><  <><
   .            ,
             .:/
   .      ,,///;,   ,;/
     .   o:::::::;;///
        >::::::::;;\\\
          ''\\\\\'" ';\
               ';\

Relevant Pages

  • Backup and Restore Files
    ... What is the best way to backup and restore a runtime database file? ... pszDisplayName As String ...
    (microsoft.public.access.gettingstarted)
  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Restoring SQL Server Backups
    ... ORIGINAL BACKUP & RESTORE (MSDE Database) ... occurrenceof cachestore flush for the 'Object Plans' cachestore (part ...
    (microsoft.public.sqlserver.server)
  • Re: Restoring SQL Server Backups
    ... ORIGINAL BACKUP & RESTORE (MSDE Database) ... occurrenceof cachestore flush for the 'Object Plans' cachestore (part ...
    (microsoft.public.sqlserver.server)
  • Re: POINT IN TIME RESTORE
    ... This is what you should have done in order to do the restore as you wish: ... > 3- erase data ... > 5- restore database with norecovery ... The BACKUP DATABAE can of course be at an earlier point in time, ...
    (microsoft.public.sqlserver.server)