Microsoft SQL Server Backup and Migration Scripts

Highly rated SQL Server 2005 and newer backup scripts: http://ola.hallengren.com/sql-server-backup.html

Microsoft's sample code: stored proc, batch file, and scheduld task: http://support.microsoft.com/kb/2019698

Steps to "migrate" a database from a newer SQL Server to an older one

I have used this to copy a database from SQL Express 2012 to SQL Express 2008 (R2 I think)

Script the database Objects and restore them on the server.

Export Steps:

Use Generate Scripts Task in SQL Server management studio.
Right Click on the database - Tasks - Generate Scripts
Run through the Wizard.
** In the set scripting options step in the generate scripts wizard click on ADVANCED and select the following options: **
- For ‘types of data to script’ option Select ‘Schema and data’
- For ‘Script for version’ select the required target SQL Server version
The file appears on the machine that is running MSSMS

Restore Steps:

Generate the scripts and run the generated scripts on the destination server. [e.g. with OSQL -E -d thedbname -i thescript.sql]
The DATABASE NAME IS EMBEDDED in the SQL file, thus, it's easiest if the database name is the same on both servers. (Worse, the entire file is processed before it gives you the error... hours later...)
You may have to run the script multiple times when you face an error.
Create a new database every time you rerun the script.
Fix the errors that you may face due to order in which objects are created (or) data is inserted into tables different tables.

Based on: http://sqlserverlearner.com/2011/restore-higher-version-database-backup-on-lower-version-sql-server-restore-problem

tags: sql, backup, msde, sqlexpress, restore, migrate,