Copying Database From One SQL Server to Another

Question

Greetings.

We are running Microsoft SQL Server 2008 on one machine with a single license. We need to create an identical development instance of a database held on this server, including tables, triggers, default values, data, views, keys, constraints and indexes.

As a temporary solution, I downloaded and installed SQL Server 2008 Express R2 along with the SQL Server 2008 Toolkit on a separate machine. I then used DTSWizard.exe and pointed it at the remote host as the data source and the local machine as the target.

Transfer of data at first appeared to be fine as the tables, indexes, etc. were created but after a little more digging, I realized it was NOT transferring/setting the default values of any fields! Many of the fields have "NOT NULL" constraints and we're interfacing with a COM API (Response RCK) which does not allow us to manually edit the queries so we're stuck with how they have interface with the database/insert entries (including the use of default values circumventing the NOT NULL constraints.)

As a second option we used the "Generate Script" option and exported all tables, constraints, indexes, default values, data, etc as a .SQL file but now I'm not sure how to load this SQL file into SQL Server because it is 4.9GB - All of which is required, no circumventing the size of this monster.

So my questions are: - Is there a way I can make a complete copy of SQL database to another server including default values? - Or is there a way to import a .SQL file without copying and pasting it as a New Query?

P.S: Apologize if my "Microsoft" lingo is not perfect, I'm a Linux guy familiar with PostgreSQL and mySQL.

Anders Zommarin · Answer 1 · 2011-02-20T14:51:59.830

2

Why not just take a complete backup of the database and restore it to the new server? That will include everything including default values?

Here is some SQL that should make it happen (edit paths and logical file names to fit your needs):

-- On the source server run:
BACKUP DATABASE [TestDb] 
    TO DISK = N'C:\TEMP\TestDb.bak' 
    WITH 
        NOFORMAT, 
        NOINIT,  
        NAME = N'SourceDb-Full Database Backup', 
        SKIP, 
        NOREWIND, 
        NOUNLOAD,  
        STATS = 10
GO

-- On the other server run
RESTORE DATABASE [DestDb] 
    FROM DISK = N'C:\Temp\TestDb.bak' 
    WITH  
        FILE = 1,  
        MOVE N'TestDb' TO N'C:\TEMP\DestDb_data.mdf',  
        MOVE N'TestDb_log' TO N'C:\TEMP\DestDb_log.ldf',  
        NOUNLOAD,  STATS = 10
GO

and you need to move the backup file between the servers if it is not accessible over the network...

edited Feb 20 '11 at 14:51

answered Feb 17 '11 at 20:37

Anders Zommarin

7,094
2
25
24

Backup option does not appear to be working, it gives me the error that the target database is not the source database. – Joshua Burns Feb 18 '11 at 16:34
Then you are doing it wrong, you first need to create a backup to a file that then is restored into the new server. You might need to specify new physical files for the database logical files in order to get is as you want to, but for casual usage it is normally not needed. Backup/restore is fundamental for SQL server and must work! – Anders Zommarin Feb 20 '11 at 13:46

score 1 · Accepted Answer · answered Feb 18 '11 at 16:58

Finally came across a solution that works.

In SQL Server 2008, there appears to be a bug when either exporting a database in which DEFAULT values are not carried with the table structures.

Here is my solution for circumventing this:

Right-click on the database you wish to backup.
If "Welcome to the Generate SQL Server Scripts wizard" dialog appears, click next. Otherwise continue to next step.
Select the database you wish to transfer.
There key things to ensure you select properly are as follows:
- Set Script Defaults to True
- Script USE DATABASE to False
- Script Data to True
- Script Indexes to True
- Script Primary Keys to True
- Script Triggers to True
- Script Unique Keys to True
Once you've finished setting other optional parameters, click Next >.
Check Stored Procedures, Tables and View (do not check Users unless you want to/need to.) and click Next >.
Click Select All to select all Stored Procedures and click Next >.
Click Select All to select all Tables and click Next >.
Click Select All to select all Views and click Next >.
Under Script mode, select Script to file.
Click the Browse... Button and select the folder and filename you wish to save the SQL script under. In this example we'll use my_script.sql.
Click Finish.

Now that we have the entire database backed up including tables, views, stored procedures, indexes, data, etc. it's time to import this data into a new database.

On the machine you wish to restore this information to, perform the following steps:

Open your command prompt by clicking Start -> Run... or Pressing Windows (Super) + R on your keyboard.
Type "cmd" without the quotes in the Run dialog and click OK.
Browse to the directory your SQL file is located at. In my case, cd "C:\Documents and Settings\Administrator\Desktop"
Type "sqlcmd -s [server][instance] -i my_script.sql" ... [server] is whatever the name of your Windows machine and [instance] is whatever the name of your SQL instance is. For SQLExpress it is "SQLEXPRESS" without the quotes.
Press Enter and you're on your way!

Hope this helps someone else who has encountered the maraud of issues!

score 0 · Answer 3 · edited Aug 04 '17 at 20:15

Is possible to run a both query from a single server

-- On the source server run:

BACKUP DATABASE [TestDb] 
    TO DISK = N'C:\TEMP\TestDb.bak' 
    WITH 
        NOFORMAT, 
        NOINIT,  
        NAME = N'SourceDb-Full Database Backup', 
        SKIP, 
        NOREWIND, 
        NOUNLOAD,  
        STATS = 10 GO

-- On the other server run

RESTORE DATABASE [DestDb] 
    FROM DISK = N'C:\Temp\TestDb.bak' 
    WITH  
        FILE = 1,  
        MOVE N'TestDb' TO N'C:\TEMP\DestDb_data.mdf',  
        MOVE N'TestDb_log' TO N'C:\TEMP\DestDb_log.ldf',  
        NOUNLOAD,  STATS = 10
GO

Copying Database From One SQL Server to Another

3 Answers3

Linked