Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I keep getting "file is open in another program" when i try to copy LDFs from one production to dev. Is there another way to copy these ldfs to dev server?

share|improve this question
add comment

4 Answers

up vote 3 down vote accepted

You'll need to stop the SQL Server Service, or take the DB offline in order to copy the LDF files. SQL will maintain an open file handle to the LDF as long as the DB is up and running.

You could always just restore a DB backup to Dev and run any trans log backups to roll the DB forward. That's what I'd recommend actually over copying LDFs to a different server.

share|improve this answer
    
Thanks Brandon. Good suggestion but I have over 30 databases and it will be lots of steps over and over again. –  Monica Todd Jan 25 '12 at 17:09
    
You can restore backups from SQL, so you don't have to give yourself carpal tunnel. :) Just write a chunk of SQL that enumerates your DB list and restores each one. You can use the restore dialog to spit out a "sample" script (just hit the Script button up top). Then set that script up in a SQL Agent job, so you can kick it off when you want (or on a set schedule). –  Brandon Jan 25 '12 at 17:19
    
I see "Ship Transaction Log" option in my source db (msdn.microsoft.com/en-us/library/ms187103.aspx). Can I used this to copy the TL to destination dev server? –  Monica Todd Jan 25 '12 at 17:28
    
That's doubtful. Log shipping usually isn't what you want to use for Dev DBs simply because the database has to be in Standby mode and won't let you write to the server. That's typically not idea from a development standpoint, since you'll want to change stuff normally. –  Brandon Jan 25 '12 at 18:34
    
Great. I am going to have the db guy backup and restore all the sharepoint databases from source to target db server. Thanks for all your help. –  Monica Todd Jan 25 '12 at 19:03
add comment

Take a step back and ask, why are we copying log files? SQL Server LDF files contain transaction log data, but they're not terribly useful by themselves. To work with a database, you need both the data files and log files. In order to access those files directly via the file system, though, you'll either need to stop SQL Server or use snapshot technologies like VSS.

If you're trying to synchronize transactions across multiple databases, check out technologies like transactional replication.

If you're trying to back up the database, use the SQL Server BACKUP DATABASE command.

share|improve this answer
    
Thanks for the overview. It really helps. –  Monica Todd Jan 25 '12 at 19:02
add comment

Just to add to the list of methods:

You can also detach your DB and then copy the files. Tip: Before you detach your DB run sp_helpdb 'db_name' to find the location of your data/log files.

share|improve this answer
add comment

If you don't want to roll back your database to a point in time and you don't need transaction history - creating a database from a database backup will be just fine. You can check wheter your database is in Simple recovery model. If it is, then you don't need transaction history and can go with recovery from database backups.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.