Hi,
I would like to know how to create a script for an exsisting database such
that the script also contains statements to recreate all the data in the
database aswell.
regards,
Ahmed.Ahmed Shafi wrote:
> Hi,
> I would like to know how to create a script for an exsisting database
> such that the script also contains statements to recreate all the
> data in the database aswell.
There was a post about a free tool http://www.sqlscripter.com in
sqlserver.tools recently.
You might also want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
There is also really a good one in Vyas's site,
http://vyaskn.tripod.com/code/generate_inserts.txt
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create insert statements via any of the following tools
Largo SQL Tools -- http://www.largosqltools.com/
ObjectScripter -- http://www.rac4sql.net/
QALite -- http://www.rac4sql.net/
Lockwood Tech -- http://www.lockwoodtech.com/
You can create the database script via some of these tools. You can =
also use Enterprise Manager to script the DDL for you.
With that said, what is your goal? If you want to "save" or "move" your =
database, an easier method might be BACKUP and RESTORE. You can execute =
a Transact-SQL backup command to the database server. This command will =
backup your database (tables, data, stored procedures...) to a file =
that you can backup to tape or burn to CD. You can also use this file =
to recreate your entire database.
--=20
Keith
"Ahmed Shafi" <aflatoon99 At hotmail DOT com> wrote in message =
news:OxMW4t5BEHA.1236@.TK2MSFTNGP11.phx.gbl...
> Hi,
>=20
> I would like to know how to create a script for an exsisting database =
such
> that the script also contains statements to recreate all the data in =
the
> database aswell.
>=20
> regards,
> Ahmed.
>=20
>|||Hi Keith & Ahmed,
take a look at this script we give away
http://www.innovartis.co.uk/downloa..._statements.zip
you may want to take a look at what else we have to offer...|||>> you may want to take a look at what else we have to offer...
You are certainly good at copying others' code and claiming it as your own.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:61AD85AE-450A-4599-9503-EAB1CD7AF58B@.microsoft.com...
Hi Keith & Ahmed,
take a look at this script we give away
http://www.innovartis.co.uk/downloa..._statements.zip
you may want to take a look at what else we have to offer...|||I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
MODIFIED/IMPROVED!
GET BENT!!!
>--Original Message--
offer...
>You are certainly good at copying others' code and
claiming it as your own.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:61AD85AE-450A-4599-9503-
EAB1CD7AF58B@.microsoft.com...
>Hi Keith & Ahmed,
>take a look at this script we give away
>http://www.innovartis.co.uk/downloa...reate_insert_st
atements.zip
>you may want to take a look at what else we have to
offer...
>
>.
>|||First of all, who are you to 'give away' my code, on your website?
I did look at the code, and you did make a few modifications here and there.
But it is a minimum courtesy in software industry, to credit the original
author when using his/her code.
You wrote "There is no claim that it is your code". But hey, the code is
hosted on your website. You haven't bothered giving credit to me. Isn't that
implying that it's your code?
If you want to continue offering that code on your website, you better add
my name and URL in the header of that script. Or else, remove it from your
site.
I hate plagiarism.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:c3cc01c408f1$201cdec0$a301280a@.phx.gbl...
I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
MODIFIED/IMPROVED!
GET BENT!!!
>--Original Message--
offer...
>You are certainly good at copying others' code and
claiming it as your own.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:61AD85AE-450A-4599-9503-
EAB1CD7AF58B@.microsoft.com...
>Hi Keith & Ahmed,
>take a look at this script we give away
>http://www.innovartis.co.uk/downloa...reate_insert_st
atements.zip
>you may want to take a look at what else we have to
offer...
>
>.
>|||mark baekdal wrote:
>I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
>MODIFIED/IMPROVED!
>
>
Yeah, right. Read your own site:
<begin quote from your site>
The DB Ghost Process
*What you do, as a one-time process, is to:*
* Script out, using Enterprise Manager, your entire production
database's schema and static data into individual scripts. To
script your static data use this _stored procedure_ [link to
Vyas's sp] <downloads/sp_create_insert_statements.zip>. Some
companies actually market products to do this...
<end quote from your site>
I guess you find it easier to steal products to do this, remove the
copyright and authorship info and then throw a tantrum when you get
found out.
People like you suck.
Vyas's original code:
BEGIN
/ ****************************************
***********************************
********************************
Procedure: sp_generate_inserts (Build 22)
(Copyright 2002 Narayana Vyas Kondreddi. All rights reserved.)
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some
other location.
This procedure is also useful to create a database setup, where
in you can
script your data along with your table definitions.
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Acknowledgements:
Divya Kalra -- For beta testing
Mark Charsley -- For reporting a problem with scripting
uniqueidentifier columns with NULL values
Artur Zeygman -- For helping me simplify a bit of code for
handling non-dbo owned tables
Joris Laperre -- For reporting a regression bug in handling
text/ntext columns
Tested on: SQL Server 7.0 and SQL Server 2000
Date created: January 17th 2001 21:52 GMT
Date modified: May 1st 2002 19:50 GMT
Email: vyaskn@.hotmail.com
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL
Server 2000's sql_variant data types
Whenever possible, Use @.include_column_list parameter to ommit
column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data
(Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables
in this procedure to their respective unicode counterparts
like nchar and nvarchar
The stolen code on your site:
BEGIN
/ ****************************************
***********************************
********************************
Procedure: sp_create_insert_statements
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some
other location.
This procedure is also useful to create a database setup, where
in you can
script your data along with your table definitions.
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL
Server 2000's sql_variant data types
IMPORTANT: Whenever possible, Use @.include_column_list parameter
to ommit column list in the INSERT statement, for better results
>GET BENT!!!
>
>
>offer...
>
>claiming it as your own.
>
>wrote in message
>
>EAB1CD7AF58B@.microsoft.com...
>
>atements.zip
>
>offer...
>|||The code has been modified extensively.
Deletes have been added.
A transaction has been created to make it atomic/durable.
Triggers have been turned off and then turned back on to
strop unwanted inserts.
Error handling has been added for completeness.
Transaction checking has been added for portability.
All of the above has added value to the script - that was
my intention. I did try to contact Vyas originally (about
two years ago) through the contact on
http://planetsourcecode.com/ where I originally found it,
however no-one got back to me. If I was trying to sell it
then I could understand your comments, but it is posted
here to help, and it is free, and it has been
modified/improved which may well help Ahmed - the whole
point of the excercise.
I will put in comments crediting Vyas for the original
work done, it has moved on though and it is not my
intention to ever profit from the work - I repeat - I
think I added value to it and it will always remain free.
>--Original Message--
>First of all, who are you to 'give away' my code, on
your website?
>I did look at the code, and you did make a few
modifications here and there.
>But it is a minimum courtesy in software industry, to
credit the original
>author when using his/her code.
>You wrote "There is no claim that it is your code". But
hey, the code is
>hosted on your website. You haven't bothered giving
credit to me. Isn't that
>implying that it's your code?
>If you want to continue offering that code on your
website, you better add
>my name and URL in the header of that script. Or else,
remove it from your
>site.
>I hate plagiarism.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:c3cc01c408f1$201cdec0$a301280a@.phx.gbl...
>I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
>MODIFIED/IMPROVED!
>GET BENT!!!
>
>offer...
>claiming it as your own.
>wrote in message
>EAB1CD7AF58B@.microsoft.com...
t
>atements.zip
>offer...
>
>.
>|||And another modification made
An order by clause was added to make sure the insert
statements are ordered by the primary key if it exists.
Previous to this contact - I hadn't recieved any even
though I did ask - therefore I could not ask anyone
requiring answers to questions to contact Vyas as I
couldn't get them. This is why there wasn't any
references. That has since changed and I have talked with
Vyas who seems happy with the arrangement as it now
stands.
I for one never want to have plagiarist next to my name.
And the end result is everyone has a damn fine piece of
code for free - that must be a good ending to this story!sql
Showing posts with label along. Show all posts
Showing posts with label along. Show all posts
Friday, March 30, 2012
Tuesday, March 20, 2012
Script out whole SQL SERVER Database including INSERT
Hi All,
I am trying to script out a whole SQL SERVER database with all objects
in it along with INSERT Statments to populate data into the table. My
goal is to have one single file which I can just run and produce the
same structure in a new system.
I do not know if there is a way to do that. If there is, I would really
appreciate if any of you can let me know.
Thanks
-- CCDon=B4t know which SQL Server version you are using but for SQL2k5 the
new namespace from SSIS offers something to do that programmatically.
Anyway, if you don=B4t want the tools and want to do that the old
fashioned way, there is a script from vyaskn which produces the
statement from TSQL:
http://vyaskn.tripod.com/code.htm#inserts
HTH, Jens Suessmeyer.
I am trying to script out a whole SQL SERVER database with all objects
in it along with INSERT Statments to populate data into the table. My
goal is to have one single file which I can just run and produce the
same structure in a new system.
I do not know if there is a way to do that. If there is, I would really
appreciate if any of you can let me know.
Thanks
-- CCDon=B4t know which SQL Server version you are using but for SQL2k5 the
new namespace from SSIS offers something to do that programmatically.
Anyway, if you don=B4t want the tools and want to do that the old
fashioned way, there is a script from vyaskn which produces the
statement from TSQL:
http://vyaskn.tripod.com/code.htm#inserts
HTH, Jens Suessmeyer.
script out table definition and data
Is there any free download tool that I could use to script out table
definition along with the data?
There is a tool by vyas but it was not scripting out correctly. I don't mean
to say wrong things about the tool but may be it was "my bad"!!
When I used that tool, it was chopping out some of the characters at the
very end of script lines.
Please let me know if there is any other free tool out there that I could us
e.
ThanksThat was most likely due to the setting of the # of characters returned per
column in Query Analyzer. Change that setting "Tools - Options" and you
will most likely get the desired results. But note you will always be
limited to 8000 bytes per column in SQL 2000 QA.
Andrew J. Kelly SQL MVP
"sqlster" <nospam@.nospam.com> wrote in message
news:14C21109-D426-468A-A321-C28150271812@.microsoft.com...
> Is there any free download tool that I could use to script out table
> definition along with the data?
> There is a tool by vyas but it was not scripting out correctly. I don't
> mean
> to say wrong things about the tool but may be it was "my bad"!!
> When I used that tool, it was chopping out some of the characters at the
> very end of script lines.
> Please let me know if there is any other free tool out there that I could
> use.
> Thanks|||I'm not familiar with the tool but if you're using Query Analyzer to execute
it, you'll need to change the output results setting.
Tools | Options. Click Results tab. Set the Maximum number of characters
per column to 8192 (the max).
Just a thought,
Joe
"sqlster" wrote:
> Is there any free download tool that I could use to script out table
> definition along with the data?
> There is a tool by vyas but it was not scripting out correctly. I don't me
an
> to say wrong things about the tool but may be it was "my bad"!!
> When I used that tool, it was chopping out some of the characters at the
> very end of script lines.
> Please let me know if there is any other free tool out there that I could
use.
> Thanks|||Joe and Andrew,
Thank you very much and it works great.
I knew it was "my bad" !!!
"Joe from WI" wrote:
> I'm not familiar with the tool but if you're using Query Analyzer to execu
te
> it, you'll need to change the output results setting.
> Tools | Options. Click Results tab. Set the Maximum number of characters
> per column to 8192 (the max).
> Just a thought,
> Joe
> "sqlster" wrote:
>
definition along with the data?
There is a tool by vyas but it was not scripting out correctly. I don't mean
to say wrong things about the tool but may be it was "my bad"!!
When I used that tool, it was chopping out some of the characters at the
very end of script lines.
Please let me know if there is any other free tool out there that I could us
e.
ThanksThat was most likely due to the setting of the # of characters returned per
column in Query Analyzer. Change that setting "Tools - Options" and you
will most likely get the desired results. But note you will always be
limited to 8000 bytes per column in SQL 2000 QA.
Andrew J. Kelly SQL MVP
"sqlster" <nospam@.nospam.com> wrote in message
news:14C21109-D426-468A-A321-C28150271812@.microsoft.com...
> Is there any free download tool that I could use to script out table
> definition along with the data?
> There is a tool by vyas but it was not scripting out correctly. I don't
> mean
> to say wrong things about the tool but may be it was "my bad"!!
> When I used that tool, it was chopping out some of the characters at the
> very end of script lines.
> Please let me know if there is any other free tool out there that I could
> use.
> Thanks|||I'm not familiar with the tool but if you're using Query Analyzer to execute
it, you'll need to change the output results setting.
Tools | Options. Click Results tab. Set the Maximum number of characters
per column to 8192 (the max).
Just a thought,
Joe
"sqlster" wrote:
> Is there any free download tool that I could use to script out table
> definition along with the data?
> There is a tool by vyas but it was not scripting out correctly. I don't me
an
> to say wrong things about the tool but may be it was "my bad"!!
> When I used that tool, it was chopping out some of the characters at the
> very end of script lines.
> Please let me know if there is any other free tool out there that I could
use.
> Thanks|||Joe and Andrew,
Thank you very much and it works great.
I knew it was "my bad" !!!
"Joe from WI" wrote:
> I'm not familiar with the tool but if you're using Query Analyzer to execu
te
> it, you'll need to change the output results setting.
> Tools | Options. Click Results tab. Set the Maximum number of characters
> per column to 8192 (the max).
> Just a thought,
> Joe
> "sqlster" wrote:
>
Script Master DB Restore
I will be transferring database from one server to another along with the ma
ster and msdb database for use as a disaster recovery server. On the second
server, I'd like to script the loading of all the databases (including the m
aster). I can stop the SQL
Service using NET STOP MSSQLSERVER. Then, I can place the database in single
user mode using the command "sqlservr -m". Next, I used OSQL to restore the
master database (which restores the database and stops the sql server - inh
erently). The problem is th
at the command "sqlservr -m" leave the command shell open. I must close the
window by manually typing <ctrl-c> and exiting the shell. I want this to be
a hands-off operation. Any suggestions?
Thanks!
AdamFirst I believe if you just create a cmd file and run your sqlservr -m from
there I think it will kill the window.
Jeff Duncan
MCDBA, MCSE+I
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||Have you tried the "exit" command?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed.
If you create a .bat file with the commands "sqlservr -m" followed on the n
ext line by "exit", the "exit" command would not be executed until the <cntr
l-c> was entered to end the
"sqlservr -m" command.|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed.
If you create a .bat file with the commands "sqlservr -m" followed on the n
ext line by "exit", the "exit" command would not be executed until the <cntr
l-c> was entered to end the
"sqlservr -m" command.|||"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
Try using:
start sqlservr -m|||Try using the TSQL "SHUTDOWN" command inside the script you run through OSQL
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <ahafner@.itape.com> wrote in message news:B4188C24-EC76-40F1-BD02-A583C87A3075@.micros
oft.com...
> I will be transferring database from one server to another along with the master a
nd msdb database for use
as a disaster recovery server. On the second server, I'd like to script the
loading of all the databases
(including the master). I can stop the SQL Service using NET STOP MSSQLSERVE
R. Then, I can place the database
in single user mode using the command "sqlservr -m". Next, I used OSQL to re
store the master database (which
restores the database and stops the sql server - inherently). The problem is
that the command "sqlservr -m"
leave the command shell open. I must close the window by manually typing <ct
rl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||Hi,
Since you are setting up a Disaster recovery server, instead of restoring
the databases one one by , you have a alternate method which is very easy.
I have done this many times and succeeded always.This will work out if
you're moving to a different machine with the same logical drive layout
(Same folder structure) same as production.
Steps:-
1. Install the SQL Server and the same service pack as on your existing
machine.
2 Stop the SQL service on your old machine. Copy the entire SQL7/SQL 2000
folder and it's subfolders,
plus any separate folders containing logs or data, to the new machine.
Also copy over any other folders containing
data that you want to move to the new machine.
3. Start the SQL Server service in new machine. Login to query analyzer and
execute sp_dropserver <drop server> and use sp_addserver <new server>,Local
4. Stop and Start the SQL Server service
5. Check all the database are running fine.
6. Start the SQL Server in production server.
Have a look into Vyas's site
http://vyaskn.tripod.com/moving_sql_server.htm
Thanks
Hari
MCDBA
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||I can not bring down the production server at any time during the day (24x7)
.
ster and msdb database for use as a disaster recovery server. On the second
server, I'd like to script the loading of all the databases (including the m
aster). I can stop the SQL
Service using NET STOP MSSQLSERVER. Then, I can place the database in single
user mode using the command "sqlservr -m". Next, I used OSQL to restore the
master database (which restores the database and stops the sql server - inh
erently). The problem is th
at the command "sqlservr -m" leave the command shell open. I must close the
window by manually typing <ctrl-c> and exiting the shell. I want this to be
a hands-off operation. Any suggestions?
Thanks!
AdamFirst I believe if you just create a cmd file and run your sqlservr -m from
there I think it will kill the window.
Jeff Duncan
MCDBA, MCSE+I
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||Have you tried the "exit" command?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed.
If you create a .bat file with the commands "sqlservr -m" followed on the n
ext line by "exit", the "exit" command would not be executed until the <cntr
l-c> was entered to end the
"sqlservr -m" command.|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed.
If you create a .bat file with the commands "sqlservr -m" followed on the n
ext line by "exit", the "exit" command would not be executed until the <cntr
l-c> was entered to end the
"sqlservr -m" command.|||"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
Try using:
start sqlservr -m|||Try using the TSQL "SHUTDOWN" command inside the script you run through OSQL
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <ahafner@.itape.com> wrote in message news:B4188C24-EC76-40F1-BD02-A583C87A3075@.micros
oft.com...
> I will be transferring database from one server to another along with the master a
nd msdb database for use
as a disaster recovery server. On the second server, I'd like to script the
loading of all the databases
(including the master). I can stop the SQL Service using NET STOP MSSQLSERVE
R. Then, I can place the database
in single user mode using the command "sqlservr -m". Next, I used OSQL to re
store the master database (which
restores the database and stops the sql server - inherently). The problem is
that the command "sqlservr -m"
leave the command shell open. I must close the window by manually typing <ct
rl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||Hi,
Since you are setting up a Disaster recovery server, instead of restoring
the databases one one by , you have a alternate method which is very easy.
I have done this many times and succeeded always.This will work out if
you're moving to a different machine with the same logical drive layout
(Same folder structure) same as production.
Steps:-
1. Install the SQL Server and the same service pack as on your existing
machine.
2 Stop the SQL service on your old machine. Copy the entire SQL7/SQL 2000
folder and it's subfolders,
plus any separate folders containing logs or data, to the new machine.
Also copy over any other folders containing
data that you want to move to the new machine.
3. Start the SQL Server service in new machine. Login to query analyzer and
execute sp_dropserver <drop server> and use sp_addserver <new server>,Local
4. Stop and Start the SQL Server service
5. Check all the database are running fine.
6. Start the SQL Server in production server.
Have a look into Vyas's site
http://vyaskn.tripod.com/moving_sql_server.htm
Thanks
Hari
MCDBA
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||I can not bring down the production server at any time during the day (24x7)
.
Script Master DB Restore
I will be transferring database from one server to another along with the master and msdb database for use as a disaster recovery server. On the second server, I'd like to script the loading of all the databases (including the master). I can stop the SQL
Service using NET STOP MSSQLSERVER. Then, I can place the database in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which restores the database and stops the sql server - inherently). The problem is th
at the command "sqlservr -m" leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I want this to be a hands-off operation. Any suggestions?
Thanks!
Adam
First I believe if you just create a cmd file and run your sqlservr -m from
there I think it will kill the window.
Jeff Duncan
MCDBA, MCSE+I
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Have you tried the "exit" command?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
Try using:
start sqlservr -m
|||Try using the TSQL "SHUTDOWN" command inside the script you run through OSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <ahafner@.itape.com> wrote in message news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the master and msdb database for use
as a disaster recovery server. On the second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP MSSQLSERVER. Then, I can place the database
in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which
restores the database and stops the sql server - inherently). The problem is that the command "sqlservr -m"
leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Hi,
Since you are setting up a Disaster recovery server, instead of restoring
the databases one one by , you have a alternate method which is very easy.
I have done this many times and succeeded always.This will work out if
you're moving to a different machine with the same logical drive layout
(Same folder structure) same as production.
Steps:-
1. Install the SQL Server and the same service pack as on your existing
machine.
2 Stop the SQL service on your old machine. Copy the entire SQL7/SQL 2000
folder and it's subfolders,
plus any separate folders containing logs or data, to the new machine.
Also copy over any other folders containing
data that you want to move to the new machine.
3. Start the SQL Server service in new machine. Login to query analyzer and
execute sp_dropserver <drop server> and use sp_addserver <new server>,Local
4. Stop and Start the SQL Server service
5. Check all the database are running fine.
6. Start the SQL Server in production server.
Have a look into Vyas's site
http://vyaskn.tripod.com/moving_sql_server.htm
Thanks
Hari
MCDBA
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||I can not bring down the production server at any time during the day (24x7).
Service using NET STOP MSSQLSERVER. Then, I can place the database in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which restores the database and stops the sql server - inherently). The problem is th
at the command "sqlservr -m" leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I want this to be a hands-off operation. Any suggestions?
Thanks!
Adam
First I believe if you just create a cmd file and run your sqlservr -m from
there I think it will kill the window.
Jeff Duncan
MCDBA, MCSE+I
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Have you tried the "exit" command?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
Try using:
start sqlservr -m
|||Try using the TSQL "SHUTDOWN" command inside the script you run through OSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <ahafner@.itape.com> wrote in message news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the master and msdb database for use
as a disaster recovery server. On the second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP MSSQLSERVER. Then, I can place the database
in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which
restores the database and stops the sql server - inherently). The problem is that the command "sqlservr -m"
leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Hi,
Since you are setting up a Disaster recovery server, instead of restoring
the databases one one by , you have a alternate method which is very easy.
I have done this many times and succeeded always.This will work out if
you're moving to a different machine with the same logical drive layout
(Same folder structure) same as production.
Steps:-
1. Install the SQL Server and the same service pack as on your existing
machine.
2 Stop the SQL service on your old machine. Copy the entire SQL7/SQL 2000
folder and it's subfolders,
plus any separate folders containing logs or data, to the new machine.
Also copy over any other folders containing
data that you want to move to the new machine.
3. Start the SQL Server service in new machine. Login to query analyzer and
execute sp_dropserver <drop server> and use sp_addserver <new server>,Local
4. Stop and Start the SQL Server service
5. Check all the database are running fine.
6. Start the SQL Server in production server.
Have a look into Vyas's site
http://vyaskn.tripod.com/moving_sql_server.htm
Thanks
Hari
MCDBA
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||I can not bring down the production server at any time during the day (24x7).
Subscribe to:
Posts (Atom)