Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best practices for sending a database to your client (schema & data)


Best practices for sending a database to your client (schema & data)

Author
Message
LightningFingers
LightningFingers
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 65
We currently use SQL 2008's "Generate Scripts" feature to package our data into a series of scripts. We then have a batch file that uses sqlcmd to run each script against the target database.

I am looking for alternatives because this method has become quite cumbersome as the database continues to grow and grow and grow!

One possibility seems to be to create a .bak file for the database. But we've run into issues with collation when moving .bak files between servers that aren't configured the same. Not sure how to fix that either Unsure

Any help is appreciated, thanks.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32253
You might want to start to look at tools like Visual Studio Team System or Red Gate SQL Source Control as a mechanism for implementing deployment processes that allow you to move things in an incremental manner.

If you just have to move the whole database every time, I'd go with backup and restore. It's probably the safest and most efficient. As far as collation errors, supply some documentation as to which collation should be used. That one is just a bit outside your control.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jim Mace
Jim Mace
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 107
If your clients are using SQL Server 2005, I think, and later, you could use the .net framework and SMO to create an exe that restores the backup or runs the scripts that they can run from the commandline.

I am an application developer, and we use Advanced Installer (http://www.advancedinstaller.com/) to install our ASP.net applications that are all front ends to a SQL Server 2005 or later database. The MSI that it creates executes the SQL scripts against the SQL Server. It works pretty slick, so this would be another option. You can use this in two manners: 1) Generate scripts for your database, including data, and have the MSI execute the scripts, or 2) Have the MSI restore your database. I have done the option 1 with success, but knowing the functionality of Advanced Installer, option 2 is doable.

You may also want to make sure that you are compacting your backups to help with size.

Hope this helps,
Jim
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
LightningFingers (3/11/2011)
One possibility seems to be to create a .bak file for the database. But we've run into issues with collation when moving .bak files between servers that aren't configured the same.


Out of curiosity, may I ask what kind of issues?

Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
LightningFingers
LightningFingers
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 65
PaulB-TheOneAndOnly (3/11/2011)

Out of curiosity, may I ask what kind of issues?

Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.


I don't completely understand collations, to be honest. And unfortunately we don't have a DBA where I work to help guides us through things like this. The problems began when the developers all received new workstations. The local SQL Server install used a different collation than our staging server. I don't specifically remember what the issues were, just error messages when running stored procedures. Some tables had a specific collation applied to them that was different than the database collation. Its all quite messed up to be honest. But we found that as long as we use scripts to generate the database we didn't encounter the issues.

I wrote the following script to modify the collation for each table in a database, but didn't have the confidence in its implications to ever run it anywhere but on my local DB.


DECLARE @DB_Collation nvarchar(50)
SET @DB_Collation = CAST(DATABASEPROPERTYEX('TargetDbName', 'Collation') as nvarchar)

-- Display the rows before the updated
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE (
   COLLATION_NAME IS NOT NULL AND
   COLLATION_NAME != @DB_Collation AND
   EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE')
)

DECLARE dynamicSqlCursor CURSOR
   FOR SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + char(10) +
          'ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar'
THEN '(' +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1
THEN 'MAX'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH as varchar)
END + ')'
          END + char(10) + 'COLLATE ' + @DB_Collation + char(10) +
   CASE IS_NULLABLE
      WHEN 'NO' THEN 'NOT NULL'
      WHEN 'YES' THEN 'NULL'
   END + char(10)
   FROM INFORMATION_SCHEMA.COLUMNS c
   WHERE (
      TABLE_SCHEMA != 'dbo' AND -- Avoid system tables
      COLLATION_NAME IS NOT NULL AND -- Null collation will fall back on database collation anyways
      COLLATION_NAME != @DB_Collation AND -- This is our true target: old collation values
      EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE') -- Alter tables only
   Wink

DECLARE @SQL nvarchar(MAX)

OPEN dynamicSqlCursor
FETCH NEXT FROM dynamicSqlCursor
INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN

   PRINT @SQL

   -- Execute the query
   EXEC sp_executeSql @SQL

   FETCH NEXT FROM dynamicSqlCursor
   INTO @SQL
END

CLOSE dynamicSqlCursor
DEALLOCATE dynamicSqlCursor

-- Verify the results. The following query should return 0 rows.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE (
   COLLATION_NAME IS NOT NULL AND
   COLLATION_NAME != 'SQL_Latin1_General_CP1_CI_AS' AND
   EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE')
)


PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
LightningFingers (3/11/2011)
I don't completely understand collations, to be honest. And unfortunately we don't have a DBA where I work to help guides us through things like this.


It appears like you are in between a rock and a hard place.

You may want to check document below "How to transfer a database from one collation to another collation in SQL Server"; it includes a nice explanation about what "collation" is about - hope this helps.
http://support.microsoft.com/kb/325335

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
craig 81366
craig 81366
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 486
PaulB-TheOneAndOnly (3/11/2011)
LightningFingers (3/11/2011)
One possibility seems to be to create a .bak file for the database. But we've run into issues with collation when moving .bak files between servers that aren't configured the same.


Out of curiosity, may I ask what kind of issues?

Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.


As Paul says, those techniques don't change your collation.
So a restored database's collation will be consistent with its source, the database's default collation should also be the same.

However, if the server uses a different collation to your database, then there is a small inconvenience you're likely to run into:
Whenever you create a temp table, it will go to tempdb, and use temdb's collation as its default.

If you then try compare data between the temp tables and your regular tables, you will get collation errors.
To resolve this, create your temp tables specifying a collation clause COLLATE DATABASE DEFAULT for each column. This will create temp tables with the same default as your current database.

If this doesn't work, then you may already have inconsistent collation in your database; either due to changing the database defalt at some stage or explicitly specifying collation on some tables.
Resolve this by simply explicitly specifying the exact same collation when you create the temp tables.

There is one other thing worth knowing: you can explicitly indicate which collation to use on comparison expressions. E.g. Table1.Col1 = Table2.Col1 COLLATE XXXX
E.g. You might do this to force a case-sensitive comparison on case insensitive columns. But be warned, the technique can prevent the use of indexes, because indexes are stored in collation order.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search