Post reply

Cusror Loop to attach databases which are numbered from 1 to 294

  • February 25, 2011 at 2:31 am

    #247436

    Hi everyone,

    i need to attach databases automatically by cursor loop. my databases are like ac_001 till ac_294.

    if anyone has idea can you please share with me.

    thankyou

  • February 25, 2011 at 4:04 am

    #1291443

    bsouji2006 (2/25/2011)

    Hi everyone,

    i need to attach databases automatically by cursor loop. my databases are like ac_001 till ac_294.

    if anyone has idea can you please share with me.

    thankyou

    Try to write a dynamic SQL and passs the db name from sysdatabase.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • February 25, 2011 at 5:43 am

    #1291473

    here's an example command for attaching a database; you'd have to do as suggested and script out the dynamic portions...the database name and the file path.

    it all starts with the list of databases(you specificaly said ATTACH...not CREATE); it's got to come from somewhere, and since they are detached they are no longer listed int eh tables on the server, like sys.sysaltfiles ...

    you've got to come up with the lsit before you can script them out.

    you can go to the command prompt(outside of sql) and do something like dir C:\DirectoryWithAllTheAttachableDatabases\*.* > contents.txt

    that will create a text file with all the filenames; Then with the info in that contents.txt file, you could then use something like excel, a macro in EditPlus, or TSQL to generate the 290+ commands you need.

    USE [master]

    GO

    CREATE DATABASE [SB2] ON

    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SB2.mdf' ),

    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SB2_1.LDF' )

    FOR ATTACH

    GO

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 27, 2011 at 11:52 pm

    #1292163

    Thanks Lowell its sysaltfiles.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

Viewing 4 posts - 1 through 3 (of 3 total)

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