How to deploy database in production server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How to deploy database in production server

How to deploy database in production server

Rate Topic

Display Mode

Topic Options

Author

Message

karan457-979314

Posted Wednesday, March 11, 2009 12:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, June 11, 2009 4:04 AM
Points: 43, Visits: 269

Hi i need to deploy my final database from staging server to production

How to deploy database in production server

Post #673010

Mohit K. Gupta

Posted Wednesday, March 11, 2009 12:59 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 12:37 PM
Points: 941, Visits: 1,041

Few ways ....

1) If you have all the scripts for all objects.. then create new db run the scripts.

2) Backup database and restore on to production

3) Deattach-reattach to production.

If this is new application most likely #2 will work just fine for you :). You'll have to do security bit after that .. that would go for each case :).

Mohit.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth

How to ask for help .. Read Best Practices here.

Post #673021

maechismo_8514

Posted Wednesday, March 11, 2009 5:16 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157

karan457 (3/11/2009)

Hi i need to deploy my final database from staging server to production

How to deploy database in production server

The fastest operation is to detach it and attach on your production server.

Post #673098

steveb.

Posted Wednesday, March 11, 2009 5:20 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

Krishna (3/11/2009)

karan457 (3/11/2009)

Hi i need to deploy my final database from staging server to production

How to deploy database in production server

The fastest operation is to detach it and attach on your production server.

If you are going to use this method or backup/restore then make sure you have cleaned out any test data that may be in the database first..

Post #673101

maechismo_8514

Posted Wednesday, March 11, 2009 5:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157

Yep, that's true sorry did not mention it.

Post #673104

Ratheesh.K.Nair

Posted Wednesday, March 11, 2009 6:05 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:21 AM
Points: 2,415, Visits: 3,370

Hope the staging and production servers both are SQL Server 2005.

Use backup and restore if you want to keep database in staging server.

Post #673143

rholland82

Posted Wednesday, March 11, 2009 8:03 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:21 PM
Points: 24, Visits: 215

and in case you have trouble with orphaned users, this link may come in handy

http://support.microsoft.com/kb/918992

Post #673269

Doug Stevens

Posted Thursday, March 31, 2011 5:48 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 8:52 AM
Points: 72, Visits: 114

Another suggestion or two:
Think about the future, after the initial backup/restore, detach/attach; updating production can be tricky. Make plans on how changes will be performed to production. (approval process, logical steps, physical steps, etc.) "If you fail to plan, then you plan to fail." Trust me on this, spend some time thinking and documenting this.

In addition, get a third party tool that can compare two databases (security settings, users, structure, etc. etc.); and can generate scripts that will get the databases in sync. Since many production database are directly related to the bottom line ($$$), it should be easy to justify the expense.

$0.02

Post #1086772

Robert Hines

Posted Sunday, April 03, 2011 5:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:27 AM
Points: 1, Visits: 6

You might try the SQL Server Publishing Wizard. Works with SQL Server 2000 & 2005. Integrated into Visual Studio 2010 for SQL Server 2008.

Post #1087883

GilaMonster

Posted Sunday, April 03, 2011 11:30 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

Please note: 2 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1087906

« Prev Topic | Next Topic »

Permissions