08-18-11, 14:33 #1Registered User
- Join Date
- Aug 2011
Unanswered: backing up clustered database, restore locally
I need to have a local copy in SQL Server 2005 of a SQL Server 2000 database that is in a cluster and located and controlled external. I have tried different things, but I can not make a backup. Enterprise manager keeps complaining about the place I want it to place the backup file. Someone here told me it's impossible for us to make backups, it all has to happen externally. I have never before took a backup or done a restore.
I have extracted the DDL of all the tables, triggers, SP's, ... (that was easy) and executed that on the 2005 database: that worked. "All" I need more is the data. I tried to export the files to text files manually. But there are so many tables, I wrote a SP to extract them.Code:
CREATE PROCEDURE dbo.P_BCP_TITables_2_TextFiles ( @FilePath NVARCHAR(100) -- path where outputfiles must be written to eg. ''\\asp\ead\TST\TI\' ) -- Export all tables in the current database to csv files to the directory @FilePath. The filename(s) = tablename.csv -- eg. \\asp\ead\TST\TI\tableName.csv AS BEGIN SET NOCOUNT ON DECLARE @FileName varchar(250) -- full path name of the output file DECLARE @TableNamePrev varchar(250) -- previous table name DECLARE @TableName varchar(250) -- current table name DECLARE @SQLStr varchar(500) -- SQL-query to extract data from database (in case of BCP 'output' switch) OR -- tablename to export (in case of BCP 'out' switch) DECLARE @bcpCommand varchar(2000) -- the BCP command DECLARE @ReturnCode_xp_cmdshell int -- return code of xp_cmdshell if @FilePath IS NULL BEGIN RAISERROR ('The @FilePath is missing.', 16, 1) RETURN END SET @TableNamePrev = '' SELECT @TableName = MIN(name) from dbo.sysobjects where xtype = 'U' and name > @TableNamePrev WHILE @TableName IS NOT NULL BEGIN SET @TableNamePrev = @TableName SET @FileName = @FilePath + @TableName + '.csv' --PRINT '@Filename = ' + @FileName SET @SQLStr = db_name() + '.dbo.' + @TableName --PRINT '@SQLStr = ' + @SQLStr SET @bcpCommand = 'bcp ' + @SQLStr + ' out "' + @FileName + '" -e ' + @FilePath + 'TI_BCP.log ' + ' -T -c -S VI1\IU_BI' PRINT '@bcpCommand = ' + @bcpCommand EXEC @ReturnCode_xp_cmdshell = master..xp_cmdshell @bcpCommand IF (@ReturnCode_xp_cmdshell <> 0) BEGIN RAISERROR ('bcpCommand1 "%s" returned with an error.', 16, 1, @bcpCommand) RETURN END SELECT @TableName = MIN(name) from dbo.sysobjects where xtype = 'U' and name > @TableNamePrev END SET NOCOUNT OFF END -- PROCEDURE dbo.P_BCP_TITables_2_TextFiles GO EXEC dbo.P_BCP_TITables_2_TextFiles '\\asp\ead\TST\TI\'
@bcpCommand = bcp TI_Test.dbo.dtproperties out "\\asp\ead\TST\TI\dtproperties.csv" -e \\asp\ead\TST\TI\TI_BCP.log -T -c -S VI1\IU_BI SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
What are my problems:
- I'm totally new to this. I learned a lot about BCP today, but I don't know what the differences between the [-n] [-c] [-N] [-w] flags mean. What should I choose? I used -c only because I saw it in an example.
- I really don't know how to handle this. Is my attempt to create a copy by first creating the database structure and then the data by importing the txt (csv) files the best way? Is there any better way to do this?
- what is wrong with the generated BCP command?
08-18-11, 14:56 #2Registered User
Provided Answers: 13
- Join Date
- Jan 2003
I back up databases on SQL 2000 and SQL 2005 databases just about every day. OK. Not so much the SQL 2000 ones anymore, but it is entirely possible. You just need to back up the databases to either one of the shared drives of the cluster, or to a UNC path that is accessible to the service account running SQL Server.
08-19-11, 01:40 #3Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Make a backup on the SQL2000 server
Copy the backup file to the SQL2005 server
Restore the backup on the SQL2005 server
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.