We help IT Professionals succeed at work.

Which db-files to copy from server to server?

joop123456
joop123456 asked
on
Medium Priority
312 Views
Last Modified: 2008-03-06
I would like to play with the backup from our original server.
Which files to copy?

my_db.mdf
my_db.idf

These as well?
mssqlsystemresource.mdf
mssqlsystemresource.idf

Are there more which I need to copy?
(Installations: both SQL Express adv)
Comment
Watch Question

Commented:
Take a backup from the server and then restore it to the new server. That would be the best way to do this.

Regards,

dduser

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2006

Commented:
Hi,

It is not advisable to use a file based backup for reasons please see http:Q_20912467.html "Simple File Based Backup"

below should give you some pointers on how to properly backup your data

http:Q_21710245.html "Proper SQL backup procedures"
http:Q_21698500.html "Backup and Recovery"
http:Q_21348116.html "Move databases files"

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I see .idf, but the transaction log files are usually .ldf.
to be sure which files to "copy" for a database, issue this query:

select * from my_db.dbo.sysfiles;

now, as noted above, to simply COPY the files will give you problems unless you first detached the database or shutdown the sql server before copying.
backup + restore is much simpler, and will ensure you get all the data.
Goodangel MatopeSoftware Architect
CERTIFIED EXPERT

Commented:
I am a praranoid man, and as a result, I strongly recommend that you backup the database as follows:

Run this on the live server in query analyzer

BACKUP DATABASE <dbname> to disk = 'c:\dbbackup.bak'

copy c:\dbbackup.bak to your development server and run this statement in query analyzer on that server

RESTORE DATABASE <dbname> FROM DISK = 'c:\dbbackup.bak'

Wala!! very easy and safe!! :-)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.