database backup

SQL database tips

Microsoft SQL-express/SQL-server database tips:

backup database schema
backup database contents
restore database query
restore database with inno-setup
scheduled database backup
Create stored procedures
run stored procedures from command-line

To backup the sql schema (database layout) start "SQL Management Studio". Go to the object-explorer and right-click your database. Select "Tasks/Generate Scripts"

export database schema, sql backup database

export database schema, sql backup database

Now click through the dialogs (leave settings at default)

Select "Script Entire Database and all database objects"
Either save to file, clipboard or query window (depending on your needs)

To restore the database schema, open a 'new query', copy & paste the results of the backup in this window and execute it.

backup database contents

To backup the contents of your database to a file, create a batch-file named 'backup.bat' which looks like this:
Note: replace "mydatabase" with the actual name of the database you want to backup!

rem If you get "[SQL Server Native Client 10.0] Named Pipes Provider: Could not open a connection to SQL Server [2]."
rem Run the sql server configuration manager and check that tcp/ip is enabled and assigned to port 1433!
mkdir C:\database\backup
icacls C:\database\backup /GRANT "NETWORK SERVICE":(OI)(CI)(F)
osql -E -r -i backup.sql

and create another script named "backup.sql" which looks like this:

use master
backup database mydatabase to disk = 'C:\Database\backup\mydatabase.db'
go
exit
go

The only thing remaining to is to make the actual database backup by running your script.

restore database query

Execute the following script in sql management studio to restore your database backup:

 Restore FILELISTONLY FROM DISK='C:\database\backup\mydatabase.db'

To restore a database we need to find the "logical name" and "log file" to restore the database to a new path. This query allows us to get these names:

osql -E .\SQLEXPRESS -r -i restore.sql

create a batch-file called "restore.sql" and use the logical name and the log name we got from the above command:

use master
alter database mydatabase set offline with rollback immediate
go
sp_detach_db mydatabase,TRUE,TRUE
go
restore database mydatabase from disk = 'C:\Database\backup\mydatabase.db' with replace, move  to C:\database\db\mydatabase.mdf, move  to c:\database\db\mydatabase.ldf
go
exit
go

With the above back-file, we can restore the database contents.

restore database with inno-setup

If you like convenience, then we can automate the above backup/restore steps a bit futher. When creating a backup, we can additionally create an inno-setup installer. To restore the database you only run the installer and click 'next' a few times - done. Anyone can backup and restore a database with that system!

First we need to add the following line to the 'backup.bat' batch-file:

rem change the path to inno-setup as necessary!
if exist "C:\Program Files\Inno Setup 5\iscc.exe" "C:\Program Files\Inno Setup 5\iscc.exe" restore.iss

Now we need the restore.iss script, which looks like this:

[Setup]
AppId={{D60ABCCF-93DF-4D7C-BCA6-7960578B7D2D}
AppName=Database restore
AppVerName=Database Restore
DefaultDirName={pf}
OutputDir=.
OutputBaseFilename=setup
AppPublisher=Kalmiya
AppCopyright=
AppPublisherURL=http://www.kalmiya.com/
AllowNoIcons=yes
PrivilegesRequired=admin
Compression=lzma
SolidCompression=yes
Uninstallable=false
ShowLanguageDialog=no
LanguageDetectionMethod=none
DisableProgramGroupPage=yes
DisableDirPage=yes
DisableReadyPage=yes
ArchitecturesInstallIn64BitMode=x64 ia64

[Languages]
Name: "english"; MessagesFile: "compiler:Default.isl"

[Dirs]
Name: "C:\Databases"; AfterInstall: SetPermissions;

[Files]
Source: "restore.sql"; DestDir: "{tmp}"; AfterInstall: UnpackDB(); Flags: ignoreversion
Source: "C:\Databases\mydatabase.db"; Flags: dontcopy

[Run]
Filename: "OSQL";	StatusMsg: "Installing database..."; Parameters: "-E {code:server} -r -i {tmp}\restore.sql"; Flags: runhidden

[Registry]
Root: HKLM; Subkey: "SOFTWARE\dbconfig"; ValueType: string; ValueName: "dbcfg"; ValueData: "{code:dbcfg}"

[Code]

var
  Page: TInputQueryWizardPage;

procedure InitializeWizard;
begin 
	Page := CreateInputQueryPage(wpWelcome,'Configure SQL Server', 'Please define sql-settings','');
	Page.Add('SQL Server Instance Name (usually . or .\SQLEXPRESS)',False);
	Page.Values[0] := ExpandConstant('{reg:HKLM\SOFTWARE\dbconfig,dbcfg|server=.\SQLEXPRESS;}');
end;

function dbcfg(Param: String): String;
begin
  Result := Page.Values[0];
end;

function server(sx:string):string;
var
 s:string;
 i,j:integer;
begin
  // example regkey contents: datalayer=SqlServer;server=.\SQLEXPRESS; database=mydatabase;user id=sa;password=mypassword;trusted_connection=True
  s := ExpandConstant('{reg:HKLM\Software\dbconfig,dbcfg|;server=.;}');
	s := Copy(s,Pos('server=',s)+7, 1000);
	Delete(s,Pos(';',s),1000);
	Result := '-S' + s;
end;

procedure UnpackDB();
var s: string;
rc : integer;
begin
	ExtractTemporaryFile('mydatabase.db');
	if not Exec('icacls','. /grant "NETWORK SERVICE":(OI)(CI)(F)',ExpandConstant('{tmp}'),SW_HIDE,ewWaitUntilTerminated,rc) then
	begin
	   MsgBox(SysErrorMessage(rc),mbError,MB_OK);
	end;

	// To figure value is needed behind the 'move' parameter look, run the below query in SQL management studio:
	// restore FILELISTONLY FROM DISK='C:\databases\mydatabase.db'

	s := #13#10 + 'restore database mydatabase from disk = ''';
	s := s + ExpandConstant('{tmp}\mydatabase.db');
	s := s + ''' with replace, move ''mydatabase'' to ''c:\Databases\mydatabase.mdf''';
	s := s + ', move ''mydatabase_log'' to ''c:\Databases\mydatabase.ldf''' + #13#10;
	s := s + 'go' + #13#10;
	s := s + 'exit' + #13#10;
	s := s + 'go' + #13#10;

	SaveStringToFile(ExpandConstant('{tmp}\restore.sql'), s, True);
	
end;

procedure SetPermissions();
var
  rc : integer;
begin
	if not Exec('icacls','. /grant "NETWORK SERVICE":(OI)(CI)(F)','C:\Databases',SW_HIDE,ewWaitUntilTerminated,rc) then
	begin
	   MsgBox(SysErrorMessage(rc),mbError,MB_OK);
	end;
end;

function InitializeSetup(): Boolean;
var
	s : string;
begin
	// version checks and such can be done here...
	Result := True;	
end;

If you run the backup.bat now, it will generate an inno-setup installer containing a backup of the database.
To restore, just run the installer.

scheduled database backup (windows)

Use windows 'Scheduled Tasks" and run your batch-file. Now you won't forget to run a regular database backup anymore!

create stored procedures

A store procedure is like a function running on the server, which we can call from the client. So for example, instead of doing a query, receiving results over the network and using these results to execute another query, stored procedures can execute both queries on the server and only return the final result, thus minimizing network load.

Another advantage is that stored procedures give an abstraction between the actual data-layout and the requested queries. If the database layout changes, you can locally go through your stored procedures and adjust them - instead of going through the entire codebase adjusting sql queries (note your compiler will not complain at compile-time about missing columns or tables - but rather at-runtime errors. In other words: you need extensive QA after database changes ).

Creating simple stored procedures for retrieving data.

Create a new database named 'mydb'
Create a table named 'mytable'
Add a solumn 'my_column' of type integer
Add a few entries

In the Object explorer, locate your database and browse to ' programmability / stored procedures'. We will create a stored procedures accepting one single parameter of type 'int'.

create stored procedures

After clicking "new stored procedure' remove the predefined text from the script-window and replace it with the sql query below. The "CREATE PROCEDURE" will actually insert the procedure into the sql database. Normally you can then execute it, but since it's a bit inconvenient to develop stored procedures, we will add code which executes the query with a hardcoded constant and then removes the sql stored procedures again. When you are done developing, you can remove everything after the 'test: ...'

-- create stored procedure, with 1 input parameter of type 'int'
USE [mydb]
GO
create PROCEDURE usp_test
@_my_param int 
AS

-- the sql query which should be executed
SELECT * from dbo.mytable where @_my_param=dbo.mytable.my_column;
go

-- test: execute using '1' as value for my_param
exec usp_test @_my_param=1; 
go

-- drop the stored procedure from the database
drop procedure usp_test;
go

Note: 'sp_' is reserved in sql for internal stored procedures, which means that when you prefix it like this you will get a (small) performance penalty. This is why our stored procedure is prefixed with "usp_" (for User-Stored-Procedure).

Running stored procedures from commandline

With SQL express comes a tool named 'bcp' (Bulk Copy Program). This commandline-tool can run your stored procedures
Open a commandprompt and enter the following command:

bcp "EXEC mydb.dbo.sp_test '1' " queryout test.txt -c -T

If you want the output to be in xml, you can change stored procedures like this:

-- the query which should be executed
SELECT * from dbo.mytable where @_my_param=dbo.mytable.my_column FOR XML RAW;

Main Menu