Avoid common problems in dumping and loading
databases, and replicating structures
Is it safe to dump a database from one server and load it on another?
Cross-server database dump-and-load operations are straightforward, as long
as you avoid what we call the cases of mistaken identity and mismatched devices.
Let's assume you're using a cross-server dump-and-load operation to move your
customer database from your development server to the production server. The
mistaken identity scenario occurs when user JohnSmith on the development server
magically turns into JaneDoe on the production screen. How can mistaken
identities even happen? The answer is that SQL Server stores login and database
user information in two system tables: syslogins and sysusers (as you can see in
Figure 1). The syslogins table is in the master database and controls
who can access the server. The sysusers tables exist in every database and
control who can access a particular database.
You add logins with sp_addlogin, which creates a new row in syslogins and
assigns an internal system ID (suid) to the login. This suid is a foreign key in
the sysusers table for each database the login has access to, so the suids must
match properly. (Foreign keys are the mechanism SQL Server and other relational
database management systems--RDBMSs--use to support the concept that each value
in a set of columns must have a corresponding value in the primary or unique key
of another value. SQL Server uses foreign keys during JOIN operations and as the
basis for enforcing referential integrity.) Loading a user database from
one server to another can easily cause these suids to get out of sync,
effectively turning your database access security into Swiss cheese!
Fortunately, SQL Server 6.5 introduced a new stored procedure, sp_change_users_login,
that simplifies fixing the problems. This procedure offers
three options (Auto_Fix, Report, and Update_One) for re-establishing foreign key
relationships from the syslogins to sysusers and sysalternates tables in cases
where cross-server dump-and-load database activity has broken the relationships.
Here's the syntax:
sp_change_users_login {Auto_Fix | Report | Update_One}
[, '<UserNamePattern>' [, '<LoginName>']]
Auto_Fix makes a best guess at what the relationships are by matching
usernames with login names. Report tells you which suids are orphaned or appear
to be out of sync. Update_One lets the systems administrator manually adjust
individual login and user entries, even if the login names don't match.
The mismatched devices problem occurs if you load a database dump into a new
database that had data and log space allocated in a different order from the
original database. As you see in the example shown in Figure 2, when
you load the first server's dump into the second server, SQL Server writes some
data-file data into the second server's log file and some log file data into the
second server's data file. To avoid the problem of mismatched devices, make sure
that the new database has data and log space allocated in the same order as the
original database before you load a database dump.
Replicating database structures. Another new SQL Server 6.5
procedure, Sp_help_revdatabase, analyzes an existing database and creates a
Transact SQL (T-SQL) script that you can use to replicate the database structure
on another server. Here's the syntax:
sp_help_revdatabase [<DBNamePattern>]
We advise incorporating this procedure in every backup and recovery plan
because it ensures that you can properly recover the database on a new server if
necessary.
I'd like to load data with bulk copy program (bcp--SQL Server's
oft-maligned bulk copy utility) directly from a T-SQL batch. What are my
options?
Depending on your needs, one of two easy solutions can help you. The
xp_cmdshell extended procedure is great for a quick and dirty solution, but the
bulk copy distributed management object (DMO) is a much better choice if your
task is repeatable or requires robust error-checking. The xp_cmdshell procedure
lets you run commands as if you were typing at an ordinary DOS command prompt.
Running bcp from T-SQL with xp_cmdshell can be as simple as typing
xp_cmdshell "bcp pubs..authors out authors.txt Usa
P c"
Readers familiar with bcp will recognize what's inside the quotes as a
simple bcp command that dumps data from the authors table in pubs to a text file
called authors.txt. This technique is simple to use, but it might not be
appropriate for batch jobs that must accurately report success or failure. The
problem is that SQL Server can think xp_cmdshell ran just fine, even if the
underlying bcp command didn't work. For example, the command
xp_cmdshell "bcp pubs..authors JUNK JUNK JUNK JUNK!!!!"
returns successfully even though the bcp command obviously isn't going to do
anything.
The SQL-DMO bulk copy object is a much better solution for regularly
scheduled batch jobs because the object can properly detect accurate error
codes. SQL-DMO exposes all the SQL Server administrative capabilities as an
Object Linking and Embedding (OLE) Automation Server object accessible from any
OLE Automation client, including these seven stored procedures that are new in
SQL Server 6.5:
sp_OACreate
sp_OADestroy
sp_OAGetErrorInfo
sp_OAGetProperty
sp_OAMethod
sp_OASetProperty
sp_OAStop