SQL database tips
Microsoft SQL-express/SQL-server database tips:

Now click through the dialogs (leave settings at default)
Note: replace "mydatabase" with the actual name of the database you want to backup!
First we need to add the following line to the 'backup.bat' batch-file:
To restore, just run the installer.
Use windows 'Scheduled Tasks" and run your batch-file. Now you won't forget to run a regular database backup anymore!
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.

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: ...'
Open a commandprompt and enter the following command:
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
backup database schema
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"
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)
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.sqland create another script named "backup.sql" which looks like this:
use master backup database mydatabase to disk = 'C:\Database\backup\mydatabase.db' go exit goThe 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.sqlcreate 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, moveWith the above back-file, we can restore the database contents.to C:\database\db\mydatabase.mdf, move to c:\database\db\mydatabase.ldf go exit go
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.issNow 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

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; goNote: '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 proceduresOpen a commandprompt and enter the following command:
bcp "EXEC mydb.dbo.sp_test '1' " queryout test.txt -c -TIf 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;