Post reply

creating a copy of database with an ndf file

  • March 28, 2023 at 10:11 pm

    #4166180

    I need to send a copy of this to our customer but do not want to send all of the data so I need to remove some tables.  Generally, I just make a copy of the latest backup rename it and delete the table that I don't want to send over and then send that to the client after I am able to remove other prorietary information.  This database has an .ndf file attached to it as we are storing resumes in the database and it is blocking me from doing this.   This is the error message I am getting

    The file 'S:\Databases\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ftrow_resumes.ndf' cannot be overwritten.  It is being used by database 'TheDatabaseIAmCopying'

     

    Can I rename the .ndf file that it is using to something else ?

    RESTORE DATABASE NewDatabaseName
    FROM DISK = 'Z:\SQLBackups\MSSQL12.MSSQLSERVER\MSSQL\Backup\OldDatabaseName.BAK'
    WITH REPLACE, MOVE ‘OldDatabaseName’  TO 'S:\Databases\MSSQL\DATA\NewDatabaseName.MDF',
    MOVE ‘OldDatabaseName_Log' TO 'S:\Databases\MSSQL\DATA\NewDatabaseName_Log.LDF'

  • March 29, 2023 at 6:39 am

    #4166273

    Check out TSQL Restore Database, and you'll find you can to specify new locations for all files making the database

    Just add "move .." for the location of the concerned file of the restored database.

    use originaldb; 
    Select * from sys.database_files

    or starting from the db backup:

    restore filelistonly from disk='<backup file location>\YourOriginalDbFull.BAK'

     

    edited: When using a restore approach, you could also opt for a partial restore, if the objects of your concern are stored in their own filegroup(s). Check out Piecemeal restore

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • March 29, 2023 at 2:37 pm

    #4166433

    Thanks!!

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

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