Sql-server – Automate Backup of SQL Server Database Structure

This Is a Striped down version of the script I use to restore a great many databases at the same time. Hopefully I took out all of the parts that make it work for my scenario and left them with appropriate hints as to what to place in your version.

    -- Remove old DBs:
use [master]
go

DROP Database [YourDBName]
DROP Database [YourNextDBName]
DROP Database [AndSoOn]
DROP Database [AndSoForth]



GO

-- Create Restore List

Insert into [dbo].[BackupList] (DBName) Values ('YourDBName')
Insert into [dbo].[BackupList] (DBName) Values ('YourNextDBName')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoOn')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoForth')


GO

declare @counter [int]
declare @maxcounter [int]
declare @DBName [varchar](50)
declare @SQL1 [varchar](4000)
declare @SQL2 [varchar](4000)
declare @SQL3 [varchar](4000)
declare @SQL4 [varchar](4000)
set @counter = 0
select @maxcounter = max(id) from [dbo].[BackupList]


while @counter <= @maxcounter
BEGIN
                select @DBname = DBname from  .[dbo].[BackupList] where ID = @Counter

                IF @DBname LIKE '%WhateverYoursIsLike' 
                BEGIN
                SET @SQL1 = 'RESTORE DATABASE '+ QUOTENAME(@DBName) + ' FROM  DISK = ''YourBack-UpLocation' + @DBName + '.bak'' WITH  FILE = 1,  
                MOVE N''YourFileName'' TO ''YourRestoreLocation' + @DBName + '.mdf'', 
                MOVE N''YourFileName_log'' TO ''YourRestoreLocation' + @DBName + '.ldf'''
                exec(@sql1)
                print(@sql1)
                END



                set @counter = @counter + 1
END

GO
Use [em_support]
GO
truncate table [em_support].[dbo].[BackupList]
GO
DBCC CHECKIDENT (BackupList, RESEED, 0)
GO


--> Modify DB properties
DECLARE @DBName SYSNAME
DECLARE @Login SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE curModifyProperties CURSOR FOR
                SELECT [name]
                FROM sys.databases
                WHERE [name] LIKE '%WhateverYoursIsLike'

OPEN curModifyProperties
FETCH NEXT FROM curModifyProperties
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

                --> BEG - Set trustworthy property
                SET @SQL = 'ALTER DATABASE '+QUOTENAME(@DBName)+' SET TRUSTWORTHY ON'
                EXEC (@SQL)
                --> END - Set trustworthy property

                --> BEG - Set database owner
                SET @SQL = QUOTENAME(@DBName)+'.[dbo].[sp_changedbowner] ''sa'''
                EXEC (@SQL)
                --> END - Set database owner

FETCH NEXT FROM curModifyProperties
INTO @DBName
END
CLOSE curModifyProperties
DEALLOCATE curModifyProperties 
GO

I hope that helps or at least points you in the right direction.

There are many free tools - native and opensource available for comparing schema and data between 2 databases (on same or different servers):

1. Open DBDiff ==> This is on Codeplex and works great.
2. SQL Admin Studio ==> This is now a free tool.
3. Hidden Gem from SQL 2005 and up : tablediff.exe (you can find this in the COM directory of your SQL Server install folder) Compare schemas: Regular or Strict
4. Powershell - You have to write your own code or build up on existing ones
5. If you are using SQL Server 2012, SSDT is also an option.
6. I have not tried this one, but just to put it in place SqlDiffFramework

From my experience, its worth to invest in Redgate's Schema and data compare tools as they have commandline options and they integrate well with Powershell as well.