January 31, 2015 at 5:18 am
Hi,
I need help in getting pointers to how to back up a database into a .SQL file. The procedure should be able to given any database it should get all the data including SP's, Views, ETC.... The output should be a SQL script.
Any help is greatly appreciated.
Regards,
Ashok T
February 1, 2015 at 3:28 am
Just so you're clear on terminology.
A backup is a page-by-page copy of the database including all the database objects and the data inside the database.
You're asking for a script for recreating the database. There isn't a command for automating exporting a script inside SQL Server. You need to look to other choices. First, SQL Server Management Studio can script database. You just right click on a database and select the generate scripts option from the menus. If you do want to automate things, you can look to Powershell to generate a script. Here's an example.
After that, you're looking at third party tools.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2015 at 1:27 am
Before you go that route, consider how such a file would be used.
Management Studio dies with files over a few hundred MB, so if you script out (and that's what it is, it's not a backup) a 500MB database, you'll get a text file much larger than 500MB. SQLCMD should be able to handle such a file, but it's not the first thing people try.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply