October 5, 2011 at 3:00 am
Hello,
I am using the cheaper version of SQL (2008 R2 Web) that doesn't include replication. I need to replicate the databases on the production-server to another SQL Server (the reporting-server), that will be used for heavy reporting. For this reporting it is okay to have data up to "yesterday". Meaning, I can restore the nightly backups from the production-server to the reporting-server, and it doesn't matter that "today's" data is not included.
Question: does anyone have a script (or other method) to automate the process of restoring?
Thanks,
Raymond
October 5, 2011 at 3:14 am
Raymond
When I've needed to do stuff like this, I've used SSIS. The control flow allows you to handle the restore and all the associated tasks such as getting the most recent backup, killing all connections to the target database, and ensuring the permissions are corect after the restore.
John
October 5, 2011 at 3:15 am
Hi,
Here we go.
But you need to schedule a task in the server to execute that batch file (defined in this article) whenever u need to restore the DB.
MI
http://raresql.com
October 5, 2011 at 3:17 am
Raymond van Laake (10/5/2011)
Hello,I am using the cheaper version of SQL (2008 R2 Web) that doesn't include replication. I need to replicate the databases on the production-server to another SQL Server (the reporting-server), that will be used for heavy reporting. For this reporting it is okay to have data up to "yesterday". Meaning, I can restore the nightly backups from the production-server to the reporting-server, and it doesn't matter that "today's" data is not included.
Question: does anyone have a script (or other method) to automate the process of restoring?
Thanks,
Raymond
why dont you use Log Shipping?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2011 at 5:40 am
differential (copy only) backup & restore should also do but you need to schedule jobs on both the servers (same with log shipping).
October 5, 2011 at 5:51 am
overlooked the fact 'If DIFFERENTIAL and COPY_ONLY are used together, COPY_ONLY is ignored, and a differential backup is created.'
But differential backup / restore would be single operation on both the servers.
October 6, 2011 at 5:14 am
We do a similar process:ftp backup from host, decompres, restore in-house. There is a SSIS package which controls the process and we have stored procedures to do the restores which boil down to
(full backup)
RESTORE DATABASE <database name>
FROM DISK = '<backup location>'
WITHSTANDBY = '<recovery file name>',
Replace,
MOVE '<logical log name>' TO '<physical location and log file name>',
MOVE '<logical db name>' TO '<physical location and db name>'
(differential backup)
RESTORE DATABASE <database name>
FROM DISK = '<backup location>'
WITHSTANDBY = '<recovery file name>'
hih
October 7, 2011 at 5:23 am
Hi,
I tried the following:
CREATE DATABASE [mydb] ON ( NAME = [mydb], FILENAME = ''E:\SQLData\mydb.mdf'' )
RESTORE DATABASE [mydb] FROM DISK = N'\\vs667\Backup Servers\SQLBACKUP\mydb\mydb_backup_2011_10_07_000104_2109063.f.bak' WITH NORECOVERY, FILE = 1, REPLACE
Booh: It restores the database but I can't access it, because it stays in mode "Restoring..."
CREATE DATABASE [mydb] ON ( NAME = [mydb], FILENAME = ''E:\SQLData\mydb.mdf'' )
RESTORE DATABASE [mydb] FROM DISK = N'\\vs667\Backup Servers\SQLBACKUP\mydb\mydb_backup_2011_10_07_000104_2109063.f.bak' WITH RECOVERY, FILE = 1, REPLACE
Booh: It restores the database and I can access it, but I can't add additional transaction logs because when i try I get the message "The log or differential backup cannot be restored because no files are ready to rollforward."
What I want is to restore the database to another server with these steps:
1) create the database on the targetserver if it doesn't exist yet
2) restore "last night's" full backup from the productionserver, in a way that the targetserver can be accessed (read-only, for reporting)
3) during the day, add transaction logs
I also tried this:
CREATE DATABASE [mydb] ON ( NAME = [mydb], FILENAME = ''E:\SQLData\mydb.mdf'' )
RESTORE DATABASE [mydb] FROM DISK = N'\\vs667\Backup Servers\SQLBACKUP\mydb\mydb_backup_2011_10_07_000104_2109063.f.bak' WITH STANDBY, FILE = 1, REPLACE
but it doesn't actually restore the database (it finishes within a second or so, with an error)
So, what should I do?
Thanks in advance
October 7, 2011 at 5:51 am
The issue is that NORECOVERY leaves the database waiting for more backups. Using WITH STANDBY leaves the database in a read-only state, but ready to restore additional backups. It creates a "standby server", usually used in a failover situation. In my case (and it sounds like in yours) we're creating a "reporting" server - that is, the database is available for reading but can not be modified.
October 7, 2011 at 6:29 am
Raymond van Laake (10/7/2011)
it finishes within a second or so, with an error
The error message would assist with diagnosis of your problem. By the way, you don't need the CREATE DATABASE statement - the RESTORE statement will do that for you if the database doesn't already exist.
John
October 7, 2011 at 6:30 am
Type error: I do *not* get an error when restoring WITH STANDBY
October 7, 2011 at 6:32 am
Indeed, I need a report server.
John, I made a typo error, sorry for that. I meant to say: I do *not* get an error.
What I do not understand at all, is that when I restore "WITH STANDBY" that I do not get an error, but nothing is restored at all. I can open the database, but none of the user table are there. My exact code is:
SET @strExec = 'CREATE DATABASE ['+@ccdb+'] ON ( NAME = ['+@ccdb+'], FILENAME = ''E:\SQLData\'+@ccdb+'.mdf'' )'
EXEC (@strExec)
SET @strExec = 'RESTORE DATABASE ['+@ccdb+'] FROM DISK = N''\\vs667\Backup Servers\SQLBACKUP\'+@ccdb+'\'+@restorefileName+''' WITH STANDBY, FILE = 1, REPLACE'
EXEC (@strExec)
Why isn't anything getting restored?
October 7, 2011 at 7:09 am
Think I got it.... wrong syntax! I have to use: STANDBY = N'E:\SQLData\mydb_undofile.bak'
Good articles on this subject:
http://sqlserverpedia.com/wiki/Restore_With_Standby
http://www.devx.com/getHelpOn/10MinuteSolution/16503/1763/page/3
Thanks to all
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply