Passing variables to .sql script file - using batch script and SQLCMD

1.Create  "Test.bat" file as given below script
 
Set DatabaseName=TestDB
sqlcmd -S "InstanceName" -i "C:\Createdb.sql"

2.--Createdb.sql
CREATE DATABASE $(DatabaseName) ON  PRIMARY
( NAME = N'TestDB', FILENAME = N'C:\DATA\TestDB11.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TestDB_log', FILENAME = N'C:\DATA\TestDB11_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

3.Run the "Test.bat" file on command line.Then TestDB database will create it on SQL Server instance.

-------------
Other Examples:

USE AdventureWorks2012;
GO
SELECT x.$(ColumnName) FROM Person.Person x  WHERE c.BusinessEntityID < 5;

You can then specify the name of the column that you want returned by using the -v option:
sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql

To return a different column by using the same script, change the value of the ColumnName scripting variable.
sqlcmd -v ColumnName ="LastName" -i c:\testscript.sql


C:\>SET tablename=Person.Person
C:\>SET col1=FirstName
C:\>SET col2=LastName
C:\>SET title=Ms.
C:\>sqlcmd -d AdventureWorks2012
1> SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
2> FROM $(tablename)
3> WHERE Title ='$(title)'
4> GO

SQL Server Database Backup and Restore -- for windows Batch scripts

--Backup batch file script

SQLCMD -S SQLInstance1 -E -i "c:\test\Backupquery.sql"

--Backupquery.sql script
--Get Date format to YYYYMMDDHHMM
Declare @BkpFiledate varchar(255)
set @BkpFiledate=CONVERT(VARCHAR(20), GETDATE(), 112) +CONVERT(VARCHAR(20), DATEPART(hour, GETDATE()))+CONVERT(VARCHAR(20), DATEPART(minute, GETDATE()), 112)

--print 'BkpFiledate'+@BkpFiledate
DECLARE @BkpFilePath NVARCHAR(255), @BkpFileName NVARCHAR(255), @BkpFullPath NVARCHAR(255)

SET @BkpFilePath = 'C:\Test\'

print '['+convert(varchar(25),Getdate())+'] '+'Start BACKUP DATABASE TestDB ';
SET @BkpFileName = 'Backup\TestDB_backup_'+@BkpFiledate+'.BAK'
SET @BkpFullPath = @bkpFilePath + @bkpFileName
BACKUP DATABASE TESTDB TO DISK = @BkpFullPath WITH INIT, STATS=10
print '['+convert(varchar(25),Getdate())+'] '+'End BACKUP DATABASE TestDB ';
-------------------------------------------------------------------------------------

--Restore batch file script
set bkpLocation=C:\Test\Backup
set Data1Location=C:\Test\Data1
set Log1Location=C:\Test\LOG1

set DBName=TestDB2
REM Finds the FOR loop for recent backup file on %BackupLocation%\%DBName%\ location
FOR /F %%I IN ('DIR "%BkpLocation%\%DBName%\*.BAK" /B /O:D') DO set RecentBackupFile=%%I
SQLCMD -S SQLInstance2 -E -d master -Q "RESTORE DATABASE %DBName% FROM DISK=N'%BkpLocation%\%DBName%\%RecentBackupFile%' WITH FILE = 1,
MOVE N'%DBName%_Data' TO N'%Data1Location%\%DBName%.mdf',
MOVE N'%DBName%_Log' TO N'%Log1Location%\%DBName%_1.ldf',
REPLACE,STATS=10"

Backcp & Restore minimum Permissions

1.Backup Permissions
  --User/Batch id should have db_backupoperator role or SysAdmin role
2.Restore Permissions
 -- User/Batch id should have dbcreator role  or SysAdmin
 -- Or If the user/Batch id Administror privileges on windows server, then can restore databases without   
     'SysAdmin' or 'dbcreator' roles.

SSIS Package Permissions 2005 and 2008

http://benchmarkitconsulting.com/colin-stasiuk/2009/05/01/running-ssis-packages-security/

Microsoft SQL Server Constraints

Good article:
http://odetocode.com/articles/79.aspx

FOR loop for Batch script

--For loop for btach scripts
For /f "tokens=*" %%a in ('dir /b D:\temp\Scripts\*.SQL') DO (

  echo %%a
 )

How to Downgrade SQL Server Database from higher version to lower version

http://www.mytechmantra.com/LearnSQLServer/Downgrade_SQL_Server_Database_P1.html

http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/32a67469-0636-4ded-923f-2e257f7e68e1/


Options:
1.Create a blank data base in Sql 2005 and import data from sql 2008 to 2005 using import wizard.
2.Generate all script from sql 2008, and seperate primary key and foriegn key scripts from the generated script file.
3.Run that script in your Sql 2005 database.(To set PK and FK in 2005)