Hello, We are not hosting our databases. Right now, One person is manually creating a .bak file from the production server. The .bak then copied to each developer's pc. Is there a better apporach that would make this process easier? I am working on build project right now for our team, I am thinking about adding the .bak file into SVN so each person has the correct local version? I had tried to generate a sql script but, it has no data just the schema?
A:
http://msdn.microsoft.com/en-us/library/ms191239.aspx
This is a good link to look at it.
Tamil.SQL
2009-08-25 13:50:19
How does this provide an alternative?
Jeff O
2009-08-25 14:54:10
+1
A:
If the production server has online connectivity to your site you can try the method called "log shipping".
This entails creating a baseline copy of your production database, then taking chunks of the transaction log written on the production server and applying the (actions contained in) the log chunks to your copy. This ensures that after a certain delay your backup database will be in the same state as the production database.
Detailed information can be found here: http://msdn.microsoft.com/en-us/library/ms187103.aspx
As you mentioned SQL 2008 among the tags: as far as I remember SQL2008 has some kind of automatism to set this up.
LaszloG
2009-08-25 13:53:56
if this "log shipping" method is chosen then everyone needs to stop doing those standard .bak backups and use COPYONLY backups instead. as far as i know, a standard backup can "interrupt" log shipping. i may be wrong about it, but i'd be careful.
djangofan
2009-08-25 19:10:52
A:
- You can create a schedule back up and restore
- You don't have to developer PC for backup, coz. SQL server has it's own back up folder you can use it.
- Also you can have restore script generated for each PC from one location, if the developer want to hold the database in their local system.
RESTORE DATABASE [xxxdb] FROM
DISK = N'\xxxx\xxx\xxx\xxxx.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
Tamil.SQL
2009-08-25 13:56:04
+1
A:
Developers can't share a single dev database?
Adding the .bak file to SVN sounds bad. That's going to keep every version of it forever - you'd be better off (in most cases) leaving it on a network share visible by all developers and letting them copy it down.
You might want to use SSIS packages to let developers make ad hoc copies of production.
You might also be interested in the Data Publishing Wizard, an open source project that lets you script databases with their data. But I'd lean towards SSIS if developers need their own copy of the database.
dnord
2009-08-25 13:57:27
Right now each developer is running sql server 2008 developer. Right now we are not hosting any sql server databases. My worry is that I we are doing unit testing and I want make sure everyone has the same version on the desktop? I want it to be the same for all people.
2009-08-25 14:08:40
+1: Sounds like a good idea to me. It's also unlikely the each developer would need a FULL copy of the production database. Use the latest schema but with test data.
John Sansom
2009-08-25 14:09:34
@Skurge: On an ad-hoc basis i.e. prior to a major release, absolutely but did you know you do not actually need the database data to performance tune a database! For example, the Database Tuning Advisor can be used to test a workload on a completely separate server. This is because tuning is performed using a shell database that does not contain the actual data. See here for reference: http://msdn.microsoft.com/en-us/library/ms190389.aspx
John Sansom
2009-08-25 15:28:25