Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005


:P
On this page:

Sql Server Backup and Restore operations are a drag. Well sort of. I've been moving over my databases from my old development machine to my new machine and the process is a lot more painful than it should. I can't quite figure out how to easily move files between two machines, but the process I've been using goes something like this:

 

Do a full backup to file

Then restore from the backup a query window like this. First:

RESTORE FILELISTONLY

FROM DISK = 'c:\sqlbackups\webstore.bak'

 

to check for the correct export names, then:

 

RESTORE DATABASE WebStore

 FROM DISK = c:\sqlbackups\webstore.bak'

WITH MOVE 'WebStore' TO 'd:\wwapps\sqldatabases\webstore.mdf',

  move 'webstore_log' TO 'd:\wwapps\sqldatabases\webstore_log.ldf'

 

this works OK and creates a new database properly.

 

However, when I do this I've run into a few issues with Logins. If I have logins that existed in the old database, but not in the new the new DataBase will be scripted with the login, but the login doesn't exist in the global user logins section.

 

At this point I can't create the global Login because it already exists, and I can't delete the restored Login because it's attached to database objects.

 

Anybody know how to delete the restored login or how to create a new login with the same name and attach it to the restored one?

 

BTW, the move has been from SQL 2000 to SQL 2005 and I have to say that the new SQL Studio in SQL 2005 is a lot snappier than Enterprise Manager was especially with remote connections. Administering my database at my ISP is a heck of a lot faster than it used to be which is great.

 

For the most part the move to 2005 has been completely painless. I even found that SQL Compare from RedGate continues to work for the most part. I was able to sync several database structures and data with no problems from the live site down to my local machine, which is great.

 

I did run into one problem with an older database that was structurally very different and

 

Invalid Object Name 'sysproperties'

 

Note sure why this should prop up given that both databases are now on SQL 2005 and both are running in Sql 2000 mode.

 

I suspect there are subtle issues in Sql 2005 that will break existing tools, which is a bummer. It wasn't too long ago I bought the RedGate Toolkit and I'm not too happy about upgrading again for Sql 2005 and features that are functionally equivalent to the 2000 version. It also looks like RedGate is updating again in the not too distant future for a more full blown SQL 2005 version. I really don't appreciate this major upgrade cycle <g>…

 

As I was looking around I thought while I'm looking at tools I should take a look at some other tools. Ran across the APEX suite and note and noticed they are giving a free copy of their tools to Microsoft MVP's, which is a cool deal. Fringe benefits at last <g>…

 

What are you using for synchronization tools? Or other useful SQL tools in general?


The Voices of Reason


 

Richard Lawrence
February 13, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

You can use sp_change_users_login to fix the login when restoring a database from one server to another. We make this part of our standard database build process.

Haacked
February 13, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

I usually just detach and then attach a database when I need to move it. It is way faster than backup and restore.

Rick Strahl
February 13, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

Thanks Richard,

exec sp_change_users_login 'Auto_Fix','WebStore';

did the trick.

I'm just realizing how I know only a small fraction of the system sp's available and don't even know when to look for them <g>...

Joe Brinkman
February 14, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

As an MVP, you should be eligible for the Friends of Redgate program which gives you access to all their tools. Send me an email at joe DOT brinkman AT dotenetnuke DOT com and I will be happy to put you in contact with Kathryn Lye who runs the program. They have a little blurb on their site (http://www.red-gate.com/about/friends.htm) that gives a little hint about the program.

Roger Jakobsson
February 14, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

Didn't know of sp_change_users_login, I always use sp_revokedbaccess to remove stale users.

Martin Möbius
February 14, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

I have found the RedGat stuff easier to use than APEX. They seem to handle dependend objects better. And the Data-Compare looked better from an UI point of view.
No more tools in use.

If anybody knows a tool converting plain SQL-statements into compound C#-strings and back again, I would be happy.

Richard Lawrence
February 14, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

"If anybody knows a tool converting plain SQL-statements into compound C#-strings and back again, I would be happy."

I don't have it on any of my current machines, but I remember several years ago writing a VS macro to paste text as a multi-line string in C#. IIRC, it only took a few lines of code.

steve
February 14, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

I really like SQlExaminer by www.sqlaccessories.com.

Detaching and copying the MDF and LDF and then reattaching is by far the easiest. As an aside -- If you don't care about logging, set Recovery Model to "Simple", and you can delete the LDF. When you reattach the database (MDF only) SQL Server may complain but will make a new (small) LDF file.

DTS packages and any Management jobs will have to be moved separately.

John
August 23, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

For documentation BI Documenter (http://www.bidocumenter.com) supports complete metadata documentation of SQL databases, Analysis Services databases and Integration Services projects, including SQL Server 2005.

mmitchmcp
October 14, 2006

# re: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005

I experience the same issue with logins when upgrading from SQL-2000 to SQL-2005. For a database called mesonet.mdf with 5 global logins, how would the stored procedure look to re-synch logins?

mmitchmcp

# DotNetSlackers: Moving SQL Server Databases and a few random thoughts on moving to SQL Server 2005


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024