Import / Export Data Problem with Sql Server Management Studio

Question

I'm new to all things Microsoft...and I've been struggling for hours figuring out how to use SQL Server Management Studio 2008 to export a database and import it into another database server. I'm a LAMP developer, so if this were a mysql database, I'd type the following into shell terminal:

mysqldump -u src_dbuser -psrc_password -h src_dbserver src_dbname > export.sql
mysql -u dest_dbuser -pdest_password -h dest_dbserver dest_dbname < export.sql

How do I perform the equivalent task in SQL Server? I've tried so many things and they all failed. Example of things my team tried in SQL Server Management Studio:

1) My client right clicked the database, chose "tasks", chose "generate scripts...", then followed the steps outlined in subsequent pop up windows. In doing so, we got an sql file of the db-schema and all the stored procedures, but it was missing all the data. We didn't see options to export the data.

2) So my client decided to export the data as an excel file. When I tried to import the excel file, one of the entity records violated a db constraint. The excel file had an "empty cell" for a field that was to be mapped to a non-nullable column in the corresponding database table. This one record stopped and exited from the remaining import process. I couldn't figure out how to tell Sql Server Management Studio to ignore this one record and proceed with importing the rest of the data.

3) I then tried to do an export myself remotely from home, just to see if my client missed something. On reproducing the steps outlined in Step 2), I got a new error when exporting

Microsoft.SqlServer.Management.Smo.SmoException: Could not read metadata, possibly due to insufficient access rights. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

I think I have all the access rights because I can successfully execute queries that perform CRUD operations. Not sure how else to confirm if I do or don't have access rights.

I've wasted so much time on this... I am so frustrated... Is Microsoft trying to make things exceedingly difficult? Can someone please tell me how to successfully import/export a database so that I can get back to doing what I love doing??? ie. DEVELOPING!!!

Additional information

OK, I tried the backup method as suggested by some people. That didn't work because SSMS was creating the backup file on the database server, as opposed to the HD of the computer I am working from. I do NOT have access to the file system on the database server, so that's why SSMS kept complaining it didn't have the privileges to write to any directories.

Have you tried carrying out a backup and restore? – danielfishr Mar 30 '11 at 00:36 — danielfishr, Mar 30 '11 at 00:36

score 3 · Answer 1 · answered Mar 30 '11 at 00:39

3

Take a full backup of the database. You can do it from the SQL Server Management Studio (right-click on the database, select 'tasks' and select 'backup'. Then fill in the blanks. Or you can do it via a SQL Query, something like this one:

backup database sandbox
to disk='C:\sql-backup\sandbox.bak'
with name='sandbox-Full Database Backup' ,
     noformat ,
     init     ,
     skip     ,
     norewind ,
     nounload ,
     stats=10

Copy it to the destination SQL server -- FTP, burn it on CD, etc. Might want to zip it, first, though, depending on how big the thing is.

Open SQL Server management studio on the destination server. In the object explorer, right-click on the databases tab, select 'Restore Database...' and you'll get a 2 panel dialog. Under 'General', you'll need to

On the 'General' pane:
- supply a name for the new database
- pick the 'device' (filename) to restore from
- pick the backup set(s) to be restored (file contents)
On the 'Options' pane:
- if a database already exists with that name, you'll want to check the 'overwrite existing database' option.
- You may want to override the default data/log file locations (but the defaults are usually workable).
- Make sure recovery state is set to 'leave the database ready to use' (unless you have reason to do otherwise.

Then hit OK.

You also need to ensure the default collation sequence is the same on the 2 SQL Server instances. You will almost certainly encounter problems otherwise.

answered Mar 30 '11 at 00:39

Nicholas Carey

71,308
16
93
135

ok...I tried the script you posted in ssms, and here's the error message: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'C:\Users\john'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.. – John Mar 30 '11 at 00:55
what's the equivalent of "sudo chmod 777 C:\Users\john" in Windows? Man...so hard... – John Mar 30 '11 at 00:56
security in windows is...different...than *nix B^) You probably want to back up to the default SQL Server backup location, and then copy it from there. But a lot depends on the box has been configured. – Nicholas Carey Mar 30 '11 at 01:04
It's generally easier just to do it through the interface. I would try it the first way Nicholas has mentioned. Windows is your friend don't be afraid to use the GUI. – Cole W Mar 30 '11 at 01:06
OK, I tried to do things through the GUI. I see that the back up destination is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\db1511.bak. I pressed OK and got a pop up saying successful back up. But when I go to the directory, no back up file is there. There's no hidden files in that directory either...Did I do something wrong? – John Mar 30 '11 at 01:10
I feel so stupid...I haven't used anything Microsoft in ages. I feel like I went to the future and can't figure out how to use the simplest of technologies that everyone else has taken for granted....what was wrong with bash??? – John Mar 30 '11 at 01:13
Yah...I think I need the equivalent of chmod 777 here. I remember back when i used windows 95, you can right click a file to see a 3x3 matrix to set the read write and execute privileges. Why did they remove that in windows 7? – John Mar 30 '11 at 01:22
ooh, i figured out why the back up wasn't working...i'll add more details to my question – John Mar 30 '11 at 02:02
Yes...Sorry I didn't mentnioni: the backup runs on the server, not on the client (and it runs under the credentials under which SQL Server runs). I don't believe there's any way around that. You can, though, backup to a network drive or UNC path to which the SQL Server account has write access (e.g. `\\mybox\share\sqlserver-backup.bak`). If the database is large, however, it is likely to be painful. – Nicholas Carey Mar 30 '11 at 16:57

score 0 · Accepted Answer · answered Apr 14 '11 at 09:56

I ended up doing things brute force. I exported the Excel file to csv files, one csv file for each work sheet that represented a db table. Then I manually edited the csv files...I converted every line item into an insert query statement. I copy and posted the insert queries into sql server management studio, and debugged each insert statement as needed.

It was infuriating

score 0 · Answer 3 · edited May 23 '17 at 10:28

0

How to get script of SQL Server data?

That link shows you how you can export data as a series of sql insert statements. You can then just execute these statements on your new dbase.

edited May 23 '17 at 10:28

Community

1
1

answered Jun 10 '11 at 17:37

Rich MacDonald

895
6
5

Import / Export Data Problem with Sql Server Management Studio

3 Answers3