Moving Databases

  • Yelena Varshal


    Points: 34217


    Charles Evans is completely right.

    CLR, GAC etc.

    I was at the 2008 Launch event and was listening to Bob F. presentation on Office 2007 in Sharepoint 2007 on Windows Server 2008 development. Guess what: you may use CLR functions in Office 2007. BUT.... As you know Microsoft claims that the Sharepoint comtains everything including documents in the database. Right? No Way! After the presentation I stopped by and confirmed with the Microsoft guys that these Office CLR assemblies are stored in GAC.

    So if you are a DBA working for the Shareporint support team and your Sharepoint admin or the superusers did some Office CLR development, then when moving Sharepoint databases you will need to figure out what assemblies you need to move as well

    Regards,Yelena Varsha

  • sam bryant

    Right there with Babe

    Points: 786

    Doug Hora (5/12/2008)

    Item 3 mentions replication and the fact you have to remove replication first. If you're moving a file with merge replication on the same server (to one with my free space) and you're under SQL Server 2005 SP2, I know you don't need to remove replication.

    alter database [123456] set offline;

    --move the database to the new loacation......say J:\Data

    alter database [123456] modify file (Name='123456', filename='J:\DATA\123456.mdf');

    alter database [123456] set online;

    I've seen several articles stating replication must be removed, but I had this situation recently, consulted with Microsoft and was given this method and it has worked very well. I haven't tried this with transactional or snapshot replication, but wouldn't know why those also wouldn't work with this method.


    Very nice addition for replicated databases Doug!

  • David Burrows

    SSC Guru

    Points: 64698

    Nice article Andy.


    First, you need to know how to move logins. I won't go into details here, but read this MS article on how to move your logins

    I have a query that retrieves sql login info from system tables (I know not good practice) and outputs sql for sp_addlogin to enable the creation of the login on another server.

    When I tried this to copy logins from SQL2K on Windows 2000 to SQL2K (same collation) on Windows 2003, it did not work. The hex value on Windows 2003 for the same password is different.

    I have not researched this to find out why :blush:

    Far away is close at hand in the images of elsewhere.

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply