Microsoft SQL Server 2005 backup the database in *.sql

Question

I work in a small company which is running M/S SQL Server 2005 Now our head office is asking me to give the whole database backup with the table schema in a single file of *.sql

please help me to backup my database in a *.sql including the table schema.

Thanks in advance.

peterm · Accepted Answer · 2013-07-23 09:44:30Z

3

Use SQL Server Management Studio

right click on your database and choose Generate scripts... and hit Next
choose Script entire database and all database objects and hit Next
choose Save to file and enter a path and a file name for your future sql script file. On the same screen choose Advanced and change the Types of data to script property value from Schema only to Schema and data. Hit OK. Hit Next.
and hit Next again.

You can download, install, and use SQL Server Management Studio that comes free with Microsoft® SQL Server® 2012 Express for that

edited Jul 23, 2013 at 9:44

answered Jul 23, 2013 at 4:10

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jmoyer8 · Accepted Answer · 2013-07-23 03:25:31Z

1

I would go out and download Microsoft SQL Server Management Studio Express.

http://www.microsoft.com/en-us/download/details.aspx?id=8961

It is free. You will be able to connect to the database, drill down into Databases, right click and under Tasks, pick Backup Database. Make sure you pick full...CHoose Disk as the place you want to write it to and Execute...Look thru your options as well...

Hope this helps!

answered Jul 23, 2013 at 3:25

jmoyer8

112 bronze badges

2 Comments

newcomer Over a year ago

Thanks for your reply. I try it and it backup in a *.mdf but I want to backup it ina *.sql including the table schema

jmoyer8 Over a year ago

You might want to talk to your parent company and see what they will be using to import it. Using my steps above will give you a complete backup of the database. They would be able to import it and see exactly what you see. You can name this file with any extension you want. Finding out what they are really looking for might be a great place to start.

Joel Cuff · Accepted Answer · 2013-07-23 09:45:19Z

0

I’d go with the method peterm suggested but note that this also has flaws. Problem is that SSMS doesn’t order the scripts in correct execution order.

For example, it might happen that DDL for stored procedure P is before DDL for table T that is used in P.

All you need to do is to review your script and make sure there are no such cases. If there are you can try fixing this yourself or using some third party tool to generate script that is ordered correctly.

answered Jul 23, 2013 at 9:45

Joel Cuff

1,03811 silver badges7 bronze badges

Collectives™ on Stack Overflow

Microsoft SQL Server 2005 backup the database in *.sql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related