Script to restore backups on other SQL Server

  • 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

  • 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

  • Hi,

    Here we go.

    http://blog.lavablast.com/post/2008/10/14/SQL-Server-Restore-a-bak-to-a-database-in-command-line.aspx

    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

  • 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" 😉

  • differential (copy only) backup & restore should also do but you need to schedule jobs on both the servers (same with log shipping).

  • 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.

  • 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

  • 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

  • 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.

  • 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

  • Type error: I do *not* get an error when restoring WITH STANDBY

  • 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?

  • 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