Moving a SQL Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Moving a SQL Database

Author

Topic

Burntchips
Starting Member

4 Posts

Posted - 2010-07-05 : 13:17:31

Hi All,

I'm new to sql server and am having loads of frustration moving a sql server database.

I do not have access to the machines directly (share hosting) I only have an account that has db_owner access to my database on the server. I've tried doing a backup but it looks like I can only backup to the local machine which I don't have access to. So then I tried in Management Studio going Tasks->Export Data which moved all the data over to the other database but is missing all the identify info.

How do I move this in sql server? In MSSQL I would run mssqldump.exe and it would generate a huge script to recreate the entire database, does sql server not have this?

Kristen
Test

22859 Posts

Posted - 2010-07-05 : 13:32:54

"generate a huge script to recreate the entire database, does sql server not have this?"

It does in SQL 2008, not before.

There are scripts around that will create an "INSERT" script for all the data (pretty sure there is one in the SCRIPTS forum here on SQL Team) - so combination of using SQL Server to script the DDL and one of the 3rd party scripts to generate an "INSERT" script for the data might do you?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 14:33:13

MVP Mladen Prajdic wrote the SSMS Tools Pack which does the insert script for you.

N 56°04'39.26"
E 12°55'05.63"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-07-06 : 02:19:54

Unfortunately I don't know the original author of this script but it's something I have laying around. Notice the parameter @seedCnt which is the number of rows that will get scripted, set it to a high number if you have a lot of data.

DECLARE	@tableName varchar(100),
	@seedCnt int
	
SET @tableName = 'calls'
SET @seedCnt = 50

DECLARE	@execStr0 varchar(8000),
	@execStr1 varchar(8000),
	@execStr2 varchar(8000),
	@execStr3 varchar(8000),
	@execStr4 varchar(8000),
	@execStr5 varchar(8000),
	@execStr6 varchar(8000),
	@execStr7 varchar(8000),
	@execStr8 varchar(8000),
	@execStr9 varchar(8000)

-- Display warning for unsupported types
IF EXISTS(SELECT *
	FROM	INFORMATION_SCHEMA.COLUMNS
	WHERE	    TABLE_NAME = @tablename 
		AND DATA_TYPE NOT IN 
				('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname', 
				'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 
				'money', 'bit', 'smallint', 'real', 'bigint'))
BEGIN
	SELECT DISTINCT DATA_TYPE + ' Type not supported'
	FROM	INFORMATION_SCHEMA.COLUMNS 
	WHERE	    TABLE_NAME = @tablename 
		AND DATA_TYPE NOT IN 
				('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname', 
				'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 
				'money', 'bit', 'smallint', 'real', 'bigint')
END
-- Build column translations
SELECT
	@execStr1 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr2 ELSE @execStr1 END,
	@execStr2 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr3 ELSE @execStr2 END,
	@execStr3 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr4 ELSE @execStr3 END,
	@execStr4 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr5 ELSE @execStr4 END,
	@execStr5 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr6 ELSE @execStr5 END,
	@execStr6 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr7 ELSE @execStr6 END,
	@execStr7 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr8 ELSE @execStr7 END,
	@execStr8 = CASE WHEN LEN(@execStr8) > 7500 THEN NULL ELSE @execStr8 END,

	@execStr8 = IsNull(@execStr8 + ' +' + CHAR(13) + CHAR(10),'') +
		CONVERT(varchar(8000),
			CASE 
			WHEN DATA_TYPE IN ('uniqueidentifier') 
				THEN CHAR(9) + '''' + COLUMN_NAME 
					+ '=''+IsNull('''''''' + CONVERT(varchar(50),' + COLUMN_NAME + ') + '''''''',''null'')+'', '''
			WHEN DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'sysname') 
				THEN CHAR(9) + '''' + COLUMN_NAME 
					+ '=''+IsNull('''''''' + REPLACE(' + COLUMN_NAME + ','''''''','''''''''''') + '''''''',''null'')+'', '''
			WHEN DATA_TYPE IN ('datetime') 
				THEN CHAR(9) + '''' + COLUMN_NAME 
					+ '=''+IsNull('''''''' + CONVERT(varchar,' + COLUMN_NAME + ',121)+'''''''',''null'') + '', '''
			WHEN DATA_TYPE IN ('tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 'money', 'bit', 'smallint', 'real', 'bigint') 
				THEN CHAR(9) + '''' + COLUMN_NAME 
					+ '=''+IsNull(CONVERT(varchar,' + COLUMN_NAME + '),''null'')+'', ''' 
			ELSE
				' ** DATA TYPE ' + DATA_TYPE + ' NOT SUPPORTED **'
			END)
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	    TABLE_NAME = @tablename 
	AND DATA_TYPE IN 
			('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname', 
			'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 
			'money', 'bit', 'smallint', 'real', 'bigint')
ORDER BY ORDINAL_POSITION

SELECT	@execStr0 = 'Select top ' + CONVERT(varchar, @seedCnt) + CHAR(13) + CHAR(10) 
			+ '''INSERT INTO ' + REPLACE(@tableName,' ','') + ' SELECT '' + ' 
			+ CHAR(13) + CHAR(10)
SELECT	@execStr1 = IsNull(@execStr1+', ', '')
SELECT	@execStr2 = IsNull(@execStr2+', ', '')
SELECT	@execStr3 = IsNull(@execStr3+', ', '')
SELECT	@execStr4 = IsNull(@execStr4+', ', '')
SELECT	@execStr5 = IsNull(@execStr5+', ', '')
SELECT	@execStr6 = IsNull(@execStr6+', ', '')
SELECT	@execStr7 = IsNull(@execStr7+', ', '')
SELECT	@execStr8 = left(@execStr8, len(@execStr8)-4) + ''''''
SELECT	@execStr9 = CHAR(13) + CHAR(10) + 'from [' + @tableName + ']'

-- Comment in for Debug
-- Select @execStr0, @execStr1, @execStr2, @execStr3, @execStr4, @execStr5, @execStr6, @execStr7, @execStr8, @execStr9

EXEC (@execStr0 + @execStr1 + @execStr2 + @execStr3 + @execStr4 
	+ @execStr5 + @execStr6 + @execStr7 + @execStr8 + @execStr9)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources