Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: backing up clustered database, restore locally

    Hi,

    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\'
    It seemed to me it generates the correct BCP commands, but BCP thinks otherwise This is the BCP code it generated for the "dtproperties" table.
    Code:
    @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
    When I execute the command in a dos box it also fails and gives the same error. I hoped the error messages it would write to an error log file would be more meaningful. I Googled, but I could not find an example of it. The log file isn't even created.

    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?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Make a backup on the SQL2000 server
    Copy the backup file to the SQL2005 server
    Restore the backup on the SQL2005 server

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •