Need T-SQL for to restore and rename database in one step.

Posted on 2008-11-14
Last Modified: 2012-05-05
Hello.  Using SQL 2000 I need to restore a database to a different database name and move the .mdf and .ldf files to a different location.  I'm getting an error with the code below.   I am doing this one time with many databases and do not wish to use the task wizard in Enterprise Manager.

Please correct :)
RESTORE DATABASE sp_rename(ComcastVOD,ComcastVodTest)

FROM DISK = 'F:\temprestsource\ComcastVODBackup111308.bak'

WITH MOVE 'ComcastVOD_Data' TO 'F:\temporaryrestore\ComcastVOD_Data.MDF',

MOVE 'ComcastVOD_Log' TO 'F:\temporaryrestore\ComcastVOD_Log.LDF'

Open in new window

Question by:Lawrence Barnes
    LVL 142

    Accepted Solution

    you give the restore database name you want:
    FROM DISK = 'F:\temprestsource\ComcastVODBackup111308.bak'
    WITH MOVE 'ComcastVOD_Data' TO 'F:\temporaryrestore\ComcastVOD_Data.MDF',
    MOVE 'ComcastVOD_Log' TO 'F:\temporaryrestore\ComcastVOD_Log.LDF'

    Open in new window

    LVL 5

    Author Closing Comment

    by:Lawrence Barnes
    Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now