Sql Server Backup Script

Well, hello, this is my first ‘real’ post.  Hopefully there’ll be more..

A problem I came up against a while ago was with trying to copy a database from an old development sql server to a new one, but being able to change the table structure of the original one, and still be able to copy it all across.

I created a script that could adjust to changes on the database structure, and use bcp (bulk copy) so that it would be nice and speedy. The bcp script will remember the id’s that are on the source database so that it all works correctly.

I decided to use Powershell because I have been looking into it a bit, and it’s already on Vista.

So here is the main Powershell script:

   1: $destServer = "DESTINATION SERVER"

   2: $sourceServer = "SOURCE SERVER"

   3: $destDBase = "DESTINATION DATABASE"

   4: $sourceDBase = "SOURCE DATABASE"

   5: $ScriptsPath = "SCRIPTS LOCATION"

   6: $ExportLocation = "EXPORTED FILES LOCATION"

   7: $NumberofTables = 31 # The number of tables in you database

   8:

   9: cd 'c:\Program Files\Microsoft Sql Server\90\Tools\Binn'

  10: # All this recreates the database

  11:

  12: SqlCmd -S $destServer -E -i "$ScriptsPath\DetachDBase.sql" -v dbasename = $destDBase

  13: SqlCmd -S $destServer -E -i "$ScriptsPath\reattachDBase.sql" -v dbasename = $destDBase

  14: SqlCmd -S $destServer -E -i "$ScriptsPath\DropDBase.sql"

  15: SqlCmd -S $destServer -E -i "$ScriptsPath\CreateDBase.sql"

  16: # This is generated by Sql Server Management Studio using the 'Generate Scripts' facility

  17: SqlCmd -S $destServer -E -d $destDBase -i "$ScriptsPath\CreateTablesAndSPs.sql"

  18: Sqlcmd -S $sourceServer -E -d $sourceDBase -i "$ScriptsPath\CreateImportExportScript.sql" -v dbasename = $sourceDBase ExportLocation = $ExportLocation serverName = $sourceServer -o "$ScriptsPath\ExportScript.bat" -w 250 -h -1 -k 1 # specifying width stops the commands going onto separate lines

  19: # Couldn't work out how to filter any number of rows affected (that is - the number of tables in the database)

  20: # I replace ..User with ..[User] because I stupidly had a user table in my database

  21: # The last replace removes extra spaces

  22: (Get-Content "$ScriptsPath\ExportScript.bat") | where {$_ -ne "($NumberOfTables rows affected)"} | Foreach-Object {$_ -replace "..User ", "..[User] "} | Foreach-Object {$_ -replace "  ", ""} | Set-Content "$ScriptsPath\ExportScript.bat"

  23: Sqlcmd -S $sourceServer -E -d $sourceDBase -i "$ScriptsPath\CreateImportExportScript.sql" -v dbasename = $destDBase ExportLocation = $ExportLocation serverName = $destServer -o "$ScriptsPath\ImportScript.bat" -w 250 -h -1 -k 1

  24: (Get-Content "$ScriptsPath\ImportScript.bat") | where {$_ -ne "($NumberOfTables rows affected)"} | Foreach-Object {$_ -replace "..User ", "..[User] "} | Foreach-Object {$_ -replace "  ", ""} | Set-Content "$ScriptsPath\ImportScript.bat"

  25: cd "$ScriptsPath"

  26: .\ExportScript.bat

  27: .\ImportScript.bat

The script references a number of other scripts.  The first 5 are created using Sql Server Management Studio.  Note these scripts are for the destination databse.  So we have a detach, reattach, drop, and create script.  The dettach and reattach ensure that there are no other connections to the database.

The 5th script is created by right-clicking on the source database in Sql Server Management Studio, and selecting ‘Tasks’ –> ‘Generate Scripts’.

Unfortunately I do not know how to generate these scripts automatically from the command line – anyone got any ideas to make the script (almost) perfect?

There are is one other script that does the ‘dirty’ work.  CreateImportExportScript.sql:

   1: select '"c:\Program Files\Microsoft Sql Server\90\Tools\binn\bcp.exe" $(dbasename)..'+name+' out "$(ExportLocation)'+name+'.tab" -n -S $(serverName) -E -T' from sysObjects

   2: where xtype = 'U'

So to summarise what the powershell script does:

Dettach, reattach, drop, and recreate the database, and then generate the tables and stored procedures. Then generate import and export scripts.  Before using them, the script cleans up the scripts so that they will work (removing the number of lines returned and excess spaces).

I think that this is probably the quickest way of getting a database copy up and running from an existing database.

Enjoy!

Advertisement