0

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.

3 Answers 3

3

Use SQL Server Management Studio

  1. right click on your database and choose Generate scripts... and hit Next
  2. choose Script entire database and all database objects and hit Next
  3. 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.
  4. 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

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

Comments

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!

2 Comments

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
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.
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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.