locked
Scripting Live database to local RRS feed

  • Question

  • User1905799959 posted

    Back in the say, with VS 2010 it was easy to right click on the database in database explorer and generate a script to transfer the aspnetdb.mdf database to SQLServer at my host.

    Now I have the opposite problem.  Due a computer wipeout, I need to generate  a script from the live site  using SQL Server 2012 Management Studio, and then replicate that database on my local SQL Server Express.

    I would greatly appreciate a step-by-step since I have never done this before.

    Tuesday, September 8, 2015 8:57 AM

Answers

All replies

  • User753101303 posted

    Hi,

    Seems the same except the other way round: https://technet.microsoft.com/en-us/library/ms188664(v=sql.105).aspx

    My personal preference is to just do a backup/restore. Edited but for example I got some inspiration from a prod-dev.sql script :

    IF @@SERVERNAME='PROD' backup database XX to disk='\\somewhere\xx.bak' with init
    GO
    USE master
    GO
    IF @@servername='DEV'
    	RESTORE DATABASE xx FROM disk='\\somewhere\xx.bak' WITH
    	MOVE 'xx_dat' TO 'a:\xx.mdf',
    	MOVE 'xx_log' TO 'b:\xx.ldf'
    GO
    USE xx
    GO
    EXEC sp_change_users_login 'Auto_Fix','xx'
    

    So I load the script on the PROD server and it does the backup and load it on the DEV server and it does the restore and I can do the same again and again at anytime without bothering much. Of course double check the script is ok before doing the first restore.

    It also depends which level of control you have on those servers (if this is an hosting company they should have something in place so that you can grab a backup file).

    Tuesday, September 8, 2015 9:16 AM
  • User1905799959 posted

    Thank you for the prompt response

    I have generated a script from the live database

    I then created a local database, a new query, pasted the script in and tried to execute it.

    I got errors, and a message saying the database does not exist or is not in a state that allows access checks. If I can fix that I should be home free ---

    Tuesday, September 8, 2015 9:27 AM
  • User1905799959 posted

    Hmm, my first reply does not show up, so here it is again

    I have successfully generated a script from the live site, created a new database on my local sql express, generated a new query, pasted in the script an tried to run it.

    I get errors that say "the database does not exist or is not in a state that allows access checks.

    Is there some SQLExpress setting I need to fix?

    Tuesday, September 8, 2015 9:41 AM
  • User-166373564 posted

    Hi,

    Here are some reference to script database to local, see:

    How to: Script Replication Objects

    Publish a Database (SQL Server Management Studio)

    Copy a database from SQL Server on a webserver to local SQL Server Express instance 

    hope it helps you.

    With regards,

    Angie

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 15, 2015 2:59 AM