How can we help you today? How can we help you today?
fhanlon
Activity overview Badges (0) Posts (9) Comments (8)

Comments

Sort by recent activity
Here the stored proc I use. It was originall posted in www.sqlservercentral.com by Greg Larsen. I modified to work with SQL Backup. Take the output and paste it an new QA window to do the restore. -- build_restore_script 'MDSHC' create procedure build_restore_script @dbname sysname = '' --restrict restore statements to 1 database as -- -- This stored procedure was written by Greg Larsen for Washington State Department of Health. -- Date: 12/16/2001 -- -- Description: -- This stored procedure generates TSQL script that will restore all the databases -- on the current SQL Server. This stored procedure takes into account when the last -- full and differential backups where taken, and how many transaction log backups -- have been taken since the last database backup, based on the information in -- the msdb database. -- -- Modified: -- Date Who Description -- 3 Jan 2005 fhanlon Added @dbname parameter -- 8 Mar 2005 fhanlon Added support for compressed backups -- -- Declare variables used in SP declare @cmd nvarchar (1000) declare @cmd1 nvarchar (1000) declare @db nvarchar(128) declare @filename nvarchar(128) declare @cnt int declare @num_processed int declare @name nvarchar(128) declare @physical_device_name nvarchar(128) declare @backup_start_date datetime declare @... char(1) -- Turn off the row number message set nocount on -- SECTION 1 -- Define cursor to hold all the different databases for the restore script will be built IF @dbname = '' declare db cursor for select name from master.dbo.sysdatabases where name not in ('tempdb', 'model') ELSE declare db cursor for select name from master.dbo.sysdatabases where name = @dbname -- Create a global temporary table that will hold the name of the backup, the database name, and the type of database backup. create table ##backupnames ( name nvarchar(100), database_name nvarchar(100), type char(1) ) -- Open cursor containing list of database names. open db fetch next from db into @db -- Process until no more databases are left @FETCH_STATUS = 0 BEGIN -- Subsection 1A -- initialize the physical device name set @physical_device_name = '' -- get the name of the last full database backup select @physical_device_name = physical_device_name , @backup_start_date = backup_start_date from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id join msdb..backupmediafamily c on a.media_set_id = c.media_set_id where type='D' and backup_start_date = (select top 1 backup_start_date from msdb..backupset where @db = database_name and type = 'D' order by backup_start_date desc) -- Did a full database backup name get found if @physical_device_name <> '' begin -- Build command to place a record in table that holds backup names select @cmd = 'insert into ##backupnames values (' + char(39) + @physical_device_name + char(39) + ',' + char(39) + @db + char(39) + ',' + char(39) + 'D' + char(39)+ ')' -- Execute command to place a record in table that holds backup names exec sp_executesql @cmd end -- Subsection 1B -- Reset the physical device name set @physical_device_name = '' -- Find the last differential database backup select @physical_device_name = physical_device_name, @backup_start_date = backup_start_date from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id join msdb..backupmediafamily c on a.media_set_id = c.media_set_id where type='I' and backup_start_date = (select top 1 backup_start_date from msdb..backupset where @db = database_name and type = 'I' and backup_start_date > @backup_start_date order by backup_start_date desc) -- Did a differential backup name get found if @physical_device_name <> '' begin -- Build command to place a record in table that holds backup names select @cmd = 'insert into ##backupnames values (' + char(39) + @physical_device_name + char(39) + ',' + char(39) + @db + char(39) + ',' + char(39) + 'I' + char(39)+ ')' -- Execute command to place a record in table that holds backup names exec sp_executesql @cmd end -- Subsection 1C -- Build command to place records in table to hold backup names for all -- transaction log backups from the last database backup set @cmd = 'insert into ##backupnames select physical_device_name,' + char(39) + @db + char(39) + ',' + char(39) + 'l' + char(39) + 'from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id join ' + 'msdb..backupmediafamily c on a.media_set_id = c.media_set_id ' + 'where type=' + char(39) + 'L' + char(39) + 'and backup_start_date > @backup_start_dat and' + char(39) + @db + char(39) + ' = database_name' -- Execute command to place records in table to hold backup names -- for all transaction log backups from the last database backup exec sp_executesql @cmd,@params=N'@backup_start_dat datetime', @backup_start_dat = @backup_start_date -- get next database to process fetch next from db into @db end -- close close db -- Section B open db -- Get first recod from database list cursor fetch next from db into @db -- Generate Heading in Restore script print '-- Restore All databases' -- Process all databases @FETCH_STATUS = 0 BEGIN -- define cursor for all database and log backups for specific database being processed declare backup_name cursor for select name,type from ##backupnames where database_name = @db -- Open cursor containing list of database backups for specific database being processed open backup_name -- Determine the number of different backups available for specific database being processed select @cnt = count(*) from ##backupnames where database_name = @db -- Get first database backup for specific database being processed fetch next from backup_name into @physical_device_name, @... -- Set counter to track the number of backups processed set @num_processed = 0 -- Process until no more database backups exist for specific database being processed @FETCH_STATUS = 0 BEGIN -- Increment the counter to track the number of backups processed set @num_processed = @num_processed + 1 -- Is the number of database backup processed the same as the number of different backups -- available for specific database being processed? -- If so, is the type of backup currently being processed a transaction log backup? if UPPER(@type) = 'L' -- build restore command to restore the last transaction log -- If extension is 'sqb' assume backup is compressed if LOWER(RIGHT(@physical_device_name,3)) ='sqb' select @cmd = 'EXEC master.dbo.sqlbackup ' + char(39) + '-SQL "RESTORE LOG ' + rtrim(@db) + char(13) + + ' FROM DISK = ' + char(39) + char(39) + RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) + char(39) +char(39) + char(13) + ' WITH RECOVERY"' + char(39) else select @cmd = 'RESTORE LOG ' + rtrim(@db) + char(13) + ' FROM DISK = ' + char(39) + RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) + char(39) + char(13) + ' WITH RECOVERY' else -- Last backup was not a transaction log backup -- Build restore command to restore the last database backup -- If extension is 'sqb' assume backup is compressed if LOWER(RIGHT(@physical_device_name,3)) ='sqb' select @cmd = 'EXEC master.dbo.sqlbackup ' + char(39) + '-SQL "RESTORE DATABASE ' + rtrim(@db) + char(13) + + ' FROM DISK = ' + char(39) + char(39) + RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) + char(39) +char(39) + char(13) + ' WITH REPLACE, RECOVERY"' + char(39) else select @cmd = 'RESTORE DATABASE ' + rtrim(@db) + char(13) + ' from disk = ' + char(39) + RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) + char(39) + char(13) + ' WITH REPLACE, RECOVERY' if @cnt <> @num_processed -- add norecovery clause if not last statement select @cmd = REPLACE(@cmd, 'RECOVERY', 'NORECOVERY') -- if it is master comment line out if @db = 'master' set @cmd = '/* ' + char(13) + @cmd + char(13) + '*/' -- Generate the restore command and other commands for restore script print @cmd print 'go' print ' ' -- Get next database backup to process fetch next from backup_name into @physical_device_name, @... end -- Close and deallocate database backup name cursor for current database being processed close backup_name deallocate backup_name -- Get next database to process fetch next from db into @db end -- Close and deallocate cursor containing list of databases to process close db deallocate db -- Drop global temporary table drop table ##backupnames GO / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Automated dB Restore
Here the stored proc I use. It was originall posted in www.sqlservercentral.com by Greg Larsen. I modified to work with SQL Backup. Take the output and paste it an new QA window to do the restor...
0 votes
I too have noticed the same thing. I will run some backup in QA. The informational message will say the backup took 1-2 seconds, but the query actually took 30 or so seconds. Here's an actual example: 1. EXEC master..sqlbackup '-SQL "BACKUP DATABASE CME_TRAIN_GLL TO DISK = ''C:\BACKUPS\DB\<AUTO>'' WITH ERASEFILES=2,COMPRESSION=1, VERIFY" -E"' the output from this command says: BACKUP DATABASE successfully processed 21009 pages in 2.490 seconds (69.115 MB/sec). But in QA it actually took 10 seconds to execute. 2. backup database CME_TRAIN_GLL to DISK='c:\BACKUPS\DB\CME_TRAIN_GLL_FULL_20050609.BAK' with init The output from this command says BACKUP DATABASE successfully processed 21009 pages in 5.664 seconds. (30.384 MB/sec) QA says it actually took 6 seconds to execute which is about right. The SQL Backup version is smaller but it doesn't seem to execute in less time despite what it says. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Slow Transaction log backup performance
I too have noticed the same thing. I will run some backup in QA. The informational message will say the backup took 1-2 seconds, but the query actually took 30 or so seconds. Here's an actual e...
0 votes
We use SQL compare to promote changes to objects from Dev, to stage, to Prod.  This helps ensure consistency in the various environments.  We have on more than one occasion found objects that someone "forgot" in stage and never promoted up.  It is a valuable tool in our change process. / comments
  1. Community
  2. General Forum
  3. Competition: Win a three month subscription to Pluralsight!
We use SQL compare to promote changes to objects from Dev, to stage, to Prod.  This helps ensure consistency in the various environments.  We have on more than one occasion found objects that someo...
0 votes
Check why there is lots of space.  Are there particular tables with indexes that are very large having lots of unused space.  If so you can try to reorg indexes of ever drop unused indexes to free up space.  There are lots of scripts available that will help you find space used by table as well as space used by indexes.  Look at some of your larger tables first but look at tables that have indexes with large amounts of unused space as well. / comments
  1. Community
  2. General Forum
  3. Which Tool for a Large Database?
Check why there is lots of space.  Are there particular tables with indexes that are very large having lots of unused space.  If so you can try to reorg indexes of ever drop unused indexes to free ...
0 votes
If this is still an issue, check where is the bloat.  Often indexes contribute to this.  You can try reorging or rebuilding indexes to see if this helps.  Reorging an index is process that can be stopped and restarted later.  Reindexing rolls back so this is different but at least this process can be done bit by bit.  Stat with finding some of your large tables and checking out the index sizes. / comments
  1. Community
  2. General Forum
  3. Which Tool for a Large Database?
If this is still an issue, check where is the bloat.  Often indexes contribute to this.  You can try reorging or rebuilding indexes to see if this helps.  Reorging an index is process that can be s...
0 votes
Thanks. I tried it again and it works as expected / comments
  1. Community
  2. SQL Compare 8 beta
  3. Export Comparision results
Thanks. I tried it again and it works as expected
0 votes
When I run C:\Program Files\Red Gate\SQL Backup\SQBServerSetup.exe I enter in all information including userid/password of cluster id I get an error that read: Internal error'RunTime Error' at 117:829 Generic failure. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Upgrading to 5.1 causes object cannot be cast from DBNull
When I run C:\Program Files\Red Gate\SQL Backup\SQBServerSetup.exe I enter in all information including userid/password of cluster id I get an error that read: Internal error'RunTime Error' at ...
0 votes
I get the same error but my database has a recovery model of full / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Transaction Log backup fails
I get the same error but my database has a recovery model of full
0 votes
Total activity
24
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
7