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?
Other Posts you might also like