Quickly Moving Databases

  • Dominique DUCHEMIN

    SSC-Addicted

    Points: 457

    I tried this but it does not work... any ideas?

    exec sp_attach_single_file_db N'MyxxDb' , N'E:\SQLServerData\MSSQL\Data\xx_Data.MDF'

    I got the error and the log file is not created...

    I tried also:

    EXEC sp_attach_single_file_db @dbname = N'Altiris',

       @physname = N'E:\AeXNS_Data.mdf'

    EXEC sp_attach_db @dbname = N'AeXNS_Data',

       @filename1 = N'E:\AeXNS_Data.mdf',

       @Filename2 = N'F:\AeXNS_Log.LDF'

    nothing is working...

     

  • Paul Mak-346751

    SSC Enthusiast

    Points: 121

    I wonder if the same methods can be applied to move an OLAP database. I have a task to move an OLAP database from the "processing" server to the "production" server. If so what is the Sql code to move the OLAP database. Thanks.

  • RichB

    SSCrazy Eights

    Points: 9651

    Try looking at create database for attach.

    You cant restore a database with the same name as an existing database - but you can restore it with a different name, apply the transaction logs, stop connections to the original, backup its tlog and apply that to the new one, then rename both. Which if you get right would only be a couple of seconds.

    One caveat to detaching databases to move them - Logshipped databases dont seem to like this - if you attach a standby database it seems to get fully recovered. Less than ideal. Anyone know a way around that?

  • Chris_P

    SSCommitted

    Points: 1753

    Good article but not practical for many environments. Like some other posters mentioned I think backup/restore should have been mentioned but with a slight twist. We use Litespeed to speed up and compress backups (faster copying) if your employer won't pony up the bucks , I think you can grab a free copy of SQL Safe from Idera which will give the same result. (more or less)

    For larger databases working with a compressed backup can be faster than any of the methods mentioned above.

    Cheers.

    Chris.

    Chris.

  • cgunner

    Ten Centuries

    Points: 1068

    THIS STATEMENT IS NOT TRUE: "You should note that you cannot re-attach more than 16 files for a single database"

    I regularly attach our databases that are 4TB in size and have 25 files!!! :w00t:

  • RichB

    SSCrazy Eights

    Points: 9651

    BOL 2000

    "sp_attach_db should only be executed on databases that were previously detached from the database server using an explicit sp_detach_db operation. If more than 16 files must be specified, use CREATE DATABASE with the FOR ATTACH clause."

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715370

    Is that more than 16 data files? Or is it with log files?

    sp_attach_db has hardcoded 16 file limit because of the parameters. Look at the code. If you're doing more than that, someone must have modified your procedure.

  • arnold-491793

    Mr or Mrs. 500

    Points: 599

    Is there any risk in using sp_detach in production database? I would like to move some of my data file in different array partition to have more disk space. I'm just worried about the uncommitted transaction in my production database.

    If i have a downtime schedule let say tomorrow at 8:00 am then i detach my database at 8:30 am. What will happen to the transactions? Is the sp_detach will not delete the uncomitted transcation then this will comit after sp_attach? i know the transaction is in logs, I just want to make sure that i will not do anything wrong in my data.

    Thanks for the expert advice.

Viewing 8 posts - 16 through 23 (of 23 total)

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