1. Open Visual Studio 2010
2. File/New Project
3. Go to the tree view of the New Project Dialog select
Database/SQL Server
4. In the list select SQL Server 2008 Database Project
- Choose an appropriate Solution Name
- The Name should be the same as the database you are putting into source control
5. [OK]
6. Right mouse click on the database project and select "Import Database Objects and Settings"
7. Click New Connection
8. Server Name = (local)
Database Name = the local database you are trying to version
[OK]
9. Select the following check boxs:
Script the collation only if it is different from the database collation
Import extended properties
10. [Start]
If you have multiple instances of SQL Server (eg 2005, 2008 etc) Then you need to create an Alias for the database instance that you are targeting
Aliases are created by:
1. Open Sql Server Configuration Manager
2. Click on the SQL Server Network Configuration and enable Named Pipes
3. Click on all versions of SQL Native Cient 10.0 Configuration
Add a new alias
- Alias Name: MyDB_LOCAL
- Pipe Name: \\localhost\pipe\MSSQL2008R2
- Protocol: Named Pipes
- Server: localhost\SQL2008R2
4. Repeat 3 for SQL Native Cient 10.0 Configuration (32bit)
The Database may be documented using extended properties
Extended properties are access by
1. Open the Microsoft SQL Server Management Studio ...
2. Right Mouse click and select properties
3. In the tree view select properties
4. Add the combination of Name and Value
Extended properties are available on the database level down to the table field level
After this you have imported the database schema into a Visual Studio Database project. The next step is to insert data. Depending on what you want to achieve there are several mechanisms for importing data.
The PostDeployment script is intended to be used for populating reference data. This can be found in the following way:
In the VS2010 database projects under the solution directory Scripts\PostDeployment\Script.PostDeployment.sql
In the file system under DatabaseProject\Scripts\Post-Deployment
There are several possibilities for inserting reference data:
1. If it's small you can use INSERT INTO eg
INSERT INTO [MyDatabase].[dbo].[DATABASE_VERSION] ([DATE],[MODEL_VERSION],[DATABASE_VERSION],[COMMENTS])
VALUES ('2011-01-07','4.1.0','1.0.0','A comment about what is new in this version')
GO
2. If it's big and in multiple tables any you are not looking to compare changes in reference data you can include a bakup of a database.
Then the script would look something like:
USE [master]
go
if (exists(select * from sys.databases where name=N'Testdata') )
begin
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Testdata'
ALTER DATABASE [Testdata] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Testdata]
end
go
-- Note that SolutionRoot is an environmental variable that can either be set in the control panel or via a batch file that opens the database project
RESTORE DATABASE [Testdata] FROM DISK = N'$(SolutionRoot)\MyDatabase\Scripts\Post-Deployment\Testdata.bak' WITH FILE = 1, MOVE N'Testdata' TO N'$(DefaultSqlFileLocation)\Testdata.mdf', MOVE N'Testdata_log' TO N'$(DefaultSqlFileLocation)\Testdata_1.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
INSERT [MyDatabase].[dbo].[StudentList] (StFName,StLName,StEmail,OrderID)
SELECT StFName,StLName,StEmail,OrderID FROM [Testdata].[dbo].[StudentList]
3. Here is another method using BCP and a binary file.
This can be useful when you don't want the inconvenience of creating a backup etc.
To create the bcp file (adjust the table in the from-clause, and possibly the server after the -S param):
bcp "select field1,field2,field3 from MyDatabase.dbo.MyTable order by Id asc" queryout MyTable.bcp -S localhost -T -E -N
The script looks something like:
:setvar EstimatedRowCount 5000000
ALTER TABLE MyDatabase.dbo.MyTable NOCHECK CONSTRAINT ALL
bulk insert MyDatabase.dbo.MyTable from '$(SolutionRoot)\MyDatabase\Scripts\Post-Deployment\MyTable.bcp' with (DATAFILETYPE = 'widenative', order(ModelEventId asc), keepidentity, KEEPNULLS, ROWS_PER_BATCH = $(EstimatedRowCount))
ALTER TABLE MyDatabase.dbo.MyTable WITH CHECK CHECK CONSTRAINT ALL
4. Here is another method using BCP and a text file.
With this it is possible to compare versions of reference data directly out of TFS
To create the text file
i. Right mouse click on the database name and select All Tasks/Export Data
ii. Select Next and then in the Choose a source dialog select
- Database source : SQL Server Native Client 10.0
- Server Name : The Server Name
- Database : The database
[Next]
iii In the Choose a destination dialog choose:
- Destination : Flat File Destination
- File name : path to tablename.txt
- Locale : English (United states)
- Code Page 1252 (NASI Latin1)
- Unicode is not checked
- Format : Delimted
- Text qualifier :
- Column names in the first data row is not checked
Next
iv - Copy data from one or more tables or views
Next
v . Source taböle or view : the table to be exported
- Row delimiter : {CR}{LF}
- Column delimiter : Semicolon {;}
Next
vi - Run immediately
Finish
Finish
Then to import script looks like:
BULK INSERT MyTable FROM '$(TFSROOT)\DataBases\MyDatabase\Scripts\Post-Deployment\MyTable.txt' WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' )
So now we have a Visual Studio 2010 Database project and data. Now suppose that you have a view that references another table in another database. You then create a sub database project within the database solution but when you want to deploy the database you get an error like
Error 2 SQL03006: View: [dbo].[MyView] has an unresolved reference to object [MyOtherDatabase].[dbo].[MyOtherTable]. D:\SOURCE\DataBases\MyDatabase\Schema Objects\Schemas\dbo\Views\MyView.view.sql 12 23 MyDatabase
Looking at the SQL
CREATE VIEW dbo.MyView
AS
SELECT dbo.MyTable.field1, MyOtherDatabase.dbo.MyOtherTable.Field
FROM dbo.MyTable INNER JOIN
MyOtherDatabase.dbo.MyOtherTable ON dbo.MyTable.Id = MyDatabase.dbo.MyTable.FKId
The problem is MyOtherDatabase.dbo.MyOtherTable.Field to a field in a table in another database. This is causing the deployment to fail. The solution to this can be found in an article on how to defining Cross-Database References at http://msdn.microsoft.com/en-us/library/bb386242.aspx
When adding the database reference you must define a database variable.
Fill out the Add Database Reference as follows:
- Database projects in the current solution: YourRefDB
- Database Reference Variables
- Name
- Uncheck Literal
Name $(IO_DB) Value YourRefDB
- Check Update the existing schema object definitions and scripts to use the database variables
- Check Suppress errors caused by unresolved references in the reference project
[OK]
Then you get to an opportunity to edit the modifications to the scripts
This resolves the compilation errors.
The next problem comes when we want to change the Deployment function from generating a script to deploying the script
Going to the solution view and looking at the properties of the database project and changing the Deploy action to "Create a deployment script (.sql) and deploy to database
Message 1 The deployment script was generated, but was not deployed. You can change the deploy action on the Deploy tab of the project properties. D:\SOURCE\DataBases\DBProj\DBName\sql\debug\DbName.sql 0 0 DBName
The solution to this is:
1 Right mouse click on the database project and select properties
2 Goto the Deployment tab
3 Edit Target connection
Server :(local)
Database name :MyDatabase
After this the deployment of the database project works…
I found some other curious effects. If you try and open this database project on a server where you have uninstalled SQL2008 R2 and installed SQL2008 SP3 you will get the following error
D:\SOURCE\DataBases\MyDBProject\DBName\DBName.dbproj : error : Could not load file or assembly 'Microsoft.SqlServer.Management.SqlParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
This problem is caused because the uninstall process removed some assemblies needed by Visual Studio to correctly interpret the database project. There are 2 ways to solve this. Either re-install VS2010 or run the following commands
Open a command line and goto: D:\Software\VS2010\DVD\WCU\SMO
Then execute
msiexec /i SQLSysClrTypes_amd64_enu.msi
msiexec /i SharedManagementObjects_amd64_enu.msi
Next change dir to D:\Software\VS2010\DVD\WCU\DAC and execute:
msiexec /i DACFramework_enu.msi
msiexec /i DACProjectSystemSetup_enu.msi
msiexec /i TSqlLanguageService_enu.msi
Another interesting thing happens when the MS SQL2008 Server has been setup with different paths.
To fix this problem go to the database project and then open the following solution folder path Schema Objects/Database Level Objects/Storage/Files. Here you will find 2 files with names like:
MyDatabase.sqlfile.sql
MyDatabase_log.sqlfile.sql
Within these files you have
ALTER DATABASE [$(DatabaseName)]
ADD FILE (NAME = [MyDatabaseName], FILENAME = 'G:\Data\MyDatabaseName.mdf', FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];
To make the database project run on any setup you need to modify the path using the environmental variables DefaultDataPath and DefaultLogPath. For example:
ALTER DATABASE [$(DatabaseName)]
ADD FILE (NAME = [$(DatabaseName)], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf', FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];
By the way if you want to use environmental variables within a Query in MS SQL Server Manager you need to:
1. Goto the Query drop down menu
2. Select SQLCMD Mode
3. Cut and paist script....
Then you can use commands like
:setvar somevariable somevalue
