March 11, 2011 at 8:01 am
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.
March 11, 2011 at 9:29 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2011 at 9:57 am
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
March 11, 2011 at 11:14 am
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.March 11, 2011 at 11:29 am
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
)
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')
)
March 11, 2011 at 12:05 pm
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.November 8, 2012 at 3:59 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply