Moving and Copying Databases

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

   Previous  [1]  2  Next