Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a very large database (400GB) that is now corrupted. I tried repairing it with the repair_allow_data_loss option, but that did not fix the problem so my next option is to restore to a previous version. What I want to do first it to create a new fresh copy of the database. So a database with all the tables/views/procedures/permissions like the existing one and then restore to it an older backup.

How can I create this fresh copy?

share|improve this question

migrated from stackoverflow.com Jun 3 '13 at 4:19

This question came from our site for professional and enthusiast programmers.

    
Are you expecting this "fresh copy" to have the structure of the database but no data? –  Scott Chamberlain Jun 2 '13 at 22:59
    
Use the generate scripts feature and run the generated script on an empty db –  Ron.B.I Jun 2 '13 at 23:07
1  
The question is "Why" you wanting to restore a empty database if you are going to restore a old backup, the act of restoring the backup will get rid of any "clean copy" you make and replace it with whatever was in the backup. –  Scott Chamberlain Jun 2 '13 at 23:11

4 Answers 4

Taking into account a 400GB database, its upto you to choose any of the below routes :

Method 1 : Backup and Restore

Depending on your hardware and the amount of activity going on, it will be slower than Method 2 - BCP OUT / BCP IN

Below is the script that will help you :

/************************************************************************************************************************************************
Author      :   KIN SHAH    
Purpose     :   Restore database on the same server with different Name
DATE        :   06-03-2013

Note        :   1. Change as per your environment by replacing the bits marked by "--- CHANGE HERE !!"
                2. If you are using SQL 2008 and up depending on Enterprise or Standard Edition, you can add "COMPRESSION" to the backup command as well.
                3. As a cleanup task, this script requires xp_cmdshell to delete the backup file on the server. I have commented that out for now.
************************************************************************************************************************************************/
declare @dbname NVARCHAR(MAX)

set @dbname = 'test1' --- CHANGE HERE !!

if object_id('tempdb..#temp1') is not null
            begin
            drop table #temp1
            END
if object_id('tempdb..#temp2') is not null
            begin
            drop table #temp2
            END
if object_id('tempdb..#temp3') is not null
            begin
            drop table #temp3
            END

 --backup path goes here
DECLARE @path NVARCHAR (MAX)
-- Here the path is hard-coded  as all the server has 'D:\2restore' folder. This can be made as input parameter also !
SET @path = 'C:\test' --- CHANGE HERE !!
 -- generates Archive database name 
DECLARE @archivedbname NVARCHAR(MAX)
SET @archivedbname = @dbname+'_Archive'+'_'+DATENAME(MONTH, GETDATE())+'_'+ CAST(DATEPART(YEAR, GETDATE()) as VARCHAR(MAX))
--PRINT @archivedbname

-- check that the database to be archived is  there and is not a system database ...

IF  @dbname in (SELECT NAME FROM MASTER..sysdatabases WHERE DB_ID(NAME)>4)
    BEGIN
        select 'The database is correct. starting Archiving Process .....'
            BEGIN TRY
                        select * INTO #temp1
                            FROM MASTER.sys.master_files
                            WHERE database_id = cast(DB_ID(@dbname) AS NVARCHAR(MAX))

                    -- now get the logical and physical names of the database to be archived
                    -- type 0 = data
                    CREATE TABLE #temp2 (ldata NVARCHAR(MAX), pdata NVARCHAR(max))
                    DECLARE @ldata NVARCHAR(MAX)
                    DECLARE @pdata NVARCHAR(MAX)
                    SELECT  @ldata = 'select [name],[physical_name] from #temp1 where  type = 0 and database_id ='+ cast(DB_ID(@dbname) AS NVARCHAR(MAX))
                    INSERT INTO #temp2
                    EXEC (@ldata)

                    SELECT @ldata = ldata FROM #temp2
                    --PRINT @ldata
                        SELECT @pdata = pdata FROM #temp2
                        SELECT @pdata = left(@pdata, len(left(@pdata,LEN(@pdata)-4))-len(@ldata))+@archivedbname+'.mdf'
                        --PRINT @pdata
                        -- type 1 = log
                        CREATE TABLE #temp3 (llog NVARCHAR(MAX), plog NVARCHAR(max))
                        DECLARE @llog NVARCHAR(MAX)
                        DECLARE @plog NVARCHAR(MAX)
                        SELECT  @llog = 'select [name],[physical_name] from #temp1 where  type = 1 and database_id ='+ cast(DB_ID(@dbname) AS NVARCHAR(MAX))
                        INSERT INTO #temp3
                        EXEC (@llog)
                        SELECT @llog = llog FROM #temp3
                        --PRINT @llog
                        SELECT @plog = plog FROM #temp3
                        SELECT @plog = left(@plog, LEN(left(@plog,LEN(@plog)-4))-LEN(@llog))+@archivedbname+'_log.ldf'
                        --PRINT @plog

                        -- now we will take backup of the database that is specified ....
                        select 'Taking backup of database ' + @dbname

                        DECLARE @sql NVARCHAR (MAX)
                        -- use compression using Redgate backup 
                        select @sql = 'backup database '+@dbname+' to disk ='''+@path+ '\'+ @dbname+'_FULL_'+convert(varchar(10),getdate(),112)+'.bak'+''' with init, stats = 10'

                        --print @sql
                        exec(@sql)

                        select 'The backup is done for ' + @dbname
            END TRY
            BEGIN CATCH

                    SELECT
                        ERROR_NUMBER() AS ErrorNumber,
                        ERROR_SEVERITY() AS ErrorSeverity,
                        ERROR_STATE() AS ErrorState,
                        ERROR_PROCEDURE() AS ErrorProcedure,
                        ERROR_LINE() AS ErrorLine,
                        ERROR_MESSAGE() AS ErrorMessage

            END CATCH

                -- now restore the database as archive database          
        BEGIN TRY
         select 'Starting restore part for ' + @archivedbname

             SELECT @sql = 'restore database '+@archivedbname + ' from disk = ''' + @path +'\'+ @dbname+'_FULL_'+ CONVERT(VARCHAR(8), GETDATE(), 112)+'.bak'+''' with recovery, stats = 10 '+','+ ' move '''+@ldata+''' '+'to ' +''''+ @pdata +''''+','+ ' move '''+@llog+''' '+ ' to '+ ''''+ @plog +''''
                --print (@sql)
                exec (@sql)

                select 'Restore is done sucessfully ! And the new database name is '+@archivedbname + '!!'

        END TRY
        BEGIN CATCH

                SELECT
                    ERROR_NUMBER() AS ErrorNumber,
                    ERROR_SEVERITY() AS ErrorSeverity,
                    ERROR_STATE() AS ErrorState,
                    ERROR_PROCEDURE() AS ErrorProcedure,
                    ERROR_LINE() AS ErrorLine,
                    ERROR_MESSAGE() AS ErrorMessage
        END CATCH

select 'Database is restored as Archive, so now lets do some clean up task ...'
-- delete the backup file
select @sql= 'exec master.dbo.xp_cmdshell ''Del '+@path+'\'+ @dbname+'_full_'+ CONVERT(VARCHAR(8), GETDATE(), 112)+'.bak'''

print (@sql)
--EXEC (@sql) --- CHANGE HERE !! (if you want to delete the backup file after the restore is done !!


    END

ELSE
    BEGIN
            select ' The database is INCORRECT ! Check if the database exists or is not a system database'
    END       
GO

Method 2 - BCP OUT / BCP IN

Script out the database SCHEMA_ONLY and recreate an empty database on the same server with a different Name.

Use BCP OUT and BULK INSERT to insert data. Below script will help you with that :

    /************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another or same server but different database ***************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat 

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe  --- CHANGE HERE !!
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
        +  QUOTENAME(name)  
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored --- CHANGE HERE !!
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
        +  REPLACE(name,' ','') 
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format --- CHANGE HERE !!
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
order by schema_name(schema_id)                         



--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in --- CHANGE HERE !!
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.' 
        +  QUOTENAME(name) 
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path --- CHANGE HERE !!
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','') 
        +'.dat'' 
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',  
        TABLOCK
        )'  + char(10) 
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go' 
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema 
order by schema_name(schema_id) 
share|improve this answer

Right click on your database, under Tasks choose Generate scripts, generate it to an SQL script and execute it on a new empty database.

Of course, as Scott mentioned, restoring an old backup to a clean database will render creating a clean copy useless.

share|improve this answer
    
So the best method is to actually restore the database without creating a fresh copy like I am trying to do? –  Mike Jun 2 '13 at 23:22
    
Yes, but having creation scripts available is always handy in case your backup is corrupted and whatever other evil reason you might think of. –  Ron.B.I Jun 2 '13 at 23:24
    
Comments Removed Keep the dialog constructive, please. –  JNK Jun 3 '13 at 16:21

You are adding an unnecessary step. Just restore the old copy as a new name (and WITH MOVE for the data/log files), then when you are comfortable with that, drop the old database and rename the new one. You may want to make sure the "previous version" is up to date in terms of logins, users, procedures, etc. For much of this you can use a variety of tools while both databases are up (assuming parts of the old database are still accessible).

share|improve this answer

You don’t need to extract scripts or anything else if you have backup that you can restore and if you can allow for some data loss. Just restore in separate database and you’ll be all good.

You might expect some storage issues here - how much space you actually have remaining because we’re talking 400GB database + transaction log file + additional space SQL Server uses during the process.

If your existing database can still be used then you might want to check third party tools to help you synchronize databases and/or recover data from MDF file…

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.