Post reply

Copy database with a backup/Restore script

  • August 25, 2010 at 6:06 am

    #225192

    Hi All

    I have been searching for a way to copy a production databasen into a test database. So far I found that the most popular way is to backup the production database and the restore into the test, and of course change the file locations.

    But we are running a Full, Differential and log file backup throughout the week. Wouldn't the backup/restore scheme mentioned above ruin the normal backup process? I would think that when we manually run a backup job, alle previous log and defferential backups would be obsolete. Since we are not saving the manual full backup, we would not be able to restore the database before the last log file backup before the manual full backup.

    I look forward to hear from you.

    Kind regards

    Bo

  • August 25, 2010 at 7:13 am

    #1211611

    one of the options when you do a full backup is the "Copy Only Backup" command, which allows you to take a full backup without disrupting the existing trnasaction/differential backups that might be already in place;

    i think it's important to send that backup to a file that is not currently being used, of course.

    here's a script, and a screenshot to help visualize:

    BACKUP DATABASE [PERFECT900]

    TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\PERFECT900.bak'

    WITH COPY_ONLY,

    NOFORMAT,

    NOINIT,

    NAME = N'PERFECT900-Full Database Backup',

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    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!

  • August 25, 2010 at 7:33 am

    #1211627

    You may use the COPY_ONLY option while taking FULL backups. This does not break the log chain. Please refer this.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • August 25, 2010 at 7:37 am

    #1211633

    Hi All

    Thank you all for this. 😀

    Regards

    Bo

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

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