How to generate SQL Server database scripts
With the help of the Generate Scripts Wizard in dbForge Studio for SQL Server, you can create scripts to migrate database structure, data, or both from one instance of SQL Server or Azure SQL to another.
Note
The server to which you wish to transfer the database must contain a database that shares the exact same name as the one being transferred.
The logic of the process includes such steps:
1. Generate scripts, for example, for the whole database on one server.
2. Connect to the server where you want to deploy this database.
3. Run the generated scripts.
It’s possible to generate scripts for a database located on a local database, a remote SQL Server database, and a database on Microsoft Azure SQL Database.
Generate a script
1. In Database Explorer, expand the node for the instance containing the database to be scripted.
2. Point to Tasks, and then click Generate Scripts.
3. If you have already had the generated *.backup file, click Open to select it. If you do not have it, skip this step.
4. Choose the required server and database.
5. Specify the path to save the file containing the generated scripts by clicking three dots.
6. In the Output file name text box, specify a name of the file.
7. Optionally, you can select:
- Append timestamp to the file name option to add date-time parameters to the file name.
- Use compression (ZIP) option to compress the file.
8. Click Next and on the Script content page, select what you want to migrate. You can also include or exclude specific database objects.
9. Proceed to the next step and on the Options page, specify your preferred settings for the script generation process.
10. Click Next and on the Error handling page, specify errors processing behavior and logging options.
11. Finally, click Generate. The scripts generation process will start.
12. If you want to open the file with the generated scripts, select Open script and click Finish.
The file will be opened in dbForge Studio for SQL Server.
Schedule a Windows task
1. Start Task Scheduler for Windows.
2. On the Action menu, select Create Basic Task.
3. Provide Name for the new task. Click Next.
4. Select one of the options when you want the task to be started. Click Next.
5. Select time and frequency for starting the task. Click Next.
6. Select Start a Program and click Next.
7. Enter the dbforgesql.com file path in the Program/script field. (The default path is C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com.)
8. Specify the arguments in the Add arguments field as follows:
/script /connection:"Data Source=DBMSSQL\MSSQL2012;Integrated Security=False;User ID=yourusername" /projectfile:"D:\Scripts\AdventureWorks.backup"
9. Click Next.
10. Check all specified settings once again and, if no changes are required, click Finish. Otherwise, click Back and make the needed changes.
Arguments used in the command line
Select corresponding command line switches to perform the required operations:
| Argument | Action and Usage |
|---|---|
| /connection | This switch is used to specify a connection string. Command usage looks like:/connection:<connection_string> |
| /database | This switch is used to specify a database to script. It overrides the database specified in the template or in the connection string. |
| /outputfile | This switch is used to specify a file name for storing the database script. It overrides the file name specified in the template. |
| /password | This switch is used to specify a server password. It overrides the password specified in the connection string. Command line usage looks like: /password:<yourpassword> |
| /projectfile | This switch is used to specify a script template to use. Some template options can be overridden by switches. |
By the way, you can find instructions on configuring SQL Server backups on a Windows system in the How to Set Up Every Day Database Auto Backup in SQL Server article.
Want to Find out More?
Overview
All features
Request a demo
