CODEWITHSUNDEEP
sqlsql-server
user154366
user154366

Reputation: 581

How to Sql Backup or Mirror database?

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?

Upvotes: 0

Views: 1046

Answers (4)

Kevin Dahl
Kevin Dahl

Reputation: 772

Check out SQL Source Control from RedGate, it can be used to keep schema and data in sync with a source control repository (docs say supports SVN). It supports the datbase on a centrally deployed server, or many developer machines as well.

Scripting out the data probably won't be a fun time for everyone depending on how much data there is, but you can also select which tables you're going to do (like lookups) and populate any larger business entity tables using SSIS (or data generator for testing).

Upvotes: 0

dnord
dnord

Reputation: 1742

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.

Upvotes: 1

Tamil.SQL
Tamil.SQL

Reputation: 262

  1. You can create a schedule back up and restore
  2. You don't have to developer PC for backup, coz. SQL server has it's own back up folder you can use it.
  3. 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

Upvotes: 0

LaszloG
LaszloG

Reputation: 719

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.

Upvotes: 1

Related Questions