Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Washington DC area

    Smile Unanswered: Load database copy from backup

    Hello Ladies and Gents,

    I have SQL Server 2005 Database on a production box. No, documentation and no test copy. I"m task with making change directly to the production database. I informed the (office manager) that this is a very risky proposition seeing the that there is no documentation, diagram or road map to follow. It's better to get a copy of the database backup and restore it to a test copy of 2005 on my desktop to test any changes prior to loading them in production. Oh, bye the way. The database is the backbone of a .NET application. No diagrams of table relationships tells me the data is drivin by the application rather-than relationship among data-tables.

    Please advise the process of loading the copy of the backup to my test copy.


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 11
    backup the production database with the copy_only option (so you don't interfere with any differential backups....they do have backups, right?), and restore it to your laptop. The worst you will have to do is recreate a few user accounts, but you can make up your own passwords for those on your laptop.

    Make sure to keep all of your changes in a script form, so you can apply those to their production environment, when you think they are done.

  3. #3
    Join Date
    Nov 2003
    Hi Garry,

    Replace all info in brackets below with your own data (without brackets). You will need to check the logical and physical names of your db files for the restore step. After you've restored the db to your dev machine, you'll probably need to fix orphaned users. Google "sql server" and "restore" and "orphaned users" and you will find quite a few guides for this annoying step.

    --on original server
    BACKUP	DATABASE [YourDatabase] 
    TO	DISK = N'[\\NetworkLocation\YourDatabase.bak]' 
    	NAME = 'Your Database Full Backup', 

    --on new server
    RESTORE DATABASE [YourDatabase] 
    FROM	DISK = N'[\\NetworkLocation\YourDatabase.bak]'
    WITH 	MOVE '[YourDatabase_Data]' TO '[d:\mssql\data\YourDatabase_data.mdf]', 
     	MOVE '[YourDatabase_log]' TO '[d:\mssql\data\YourDatabase_log.ldf]',
    Kit Lemmonds

  4. #4
    Join Date
    Nov 2004
    Provided Answers: 4
    Quote Originally Posted by MCrowley View Post
    Make sure to keep all of your changes in a script form, so you can apply those to their production environment, when you think they are done.

    Don't be tempted to use the graphical editor to change the database in an interactive way. To err is human.

    When you are done with your script, restore your production database in a second test database. Then run your SQL scripts on that test database. Run tests. If you find errors, correct your scripts, restore your production database in the second test database, run tests, ...
    Repeat this until everything works flawless.

    Now apply your scripts on your production system.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts