If the account that the SQL Service is running under has access to any network shares, then you can use database backup to a shared folder, and restore from there as well.
BACKUP DATABASE [dbname] TO DISK = '\\servername\sharedfoldername\dbname.bak'
RESTORE DATABASE [dbname] FROM DISK = '\\servername\sharedfoldername\dbname.bak'
Or you can use bcp.exe. Use SSMS Object Explorer to script out all the tables, and execute the script on the desired database
Use TSQL to build out all the bcp.exe commands, something like this:
select 'bcp.exe ' + quotename(object_schema_name(object_id)) + '.' + quotename(name) + ' out "\\server\someshare\' + name + '.bcp" -n -S ' + @@servername + ' -d "sourceDb" -T ' from sys.tables order by name;
select 'bcp.exe ' + quotename(object_schema_name(object_id)) + '.' + quotename(name) + ' in "\\server\someshare\' + name + '.bcp" -S "DestinationInstance" -d "destinationDb" -T ' from sys.tables order by name;
(I would have assumed there was already a question on this, but I couldn't find a general one for SQL Server, just narrowly tailored ones, or ones for other platforms.)
The method I chose would probably depend on how big the tables are, and also whether there are any other objects associated with the tables (like indexes, triggers, constraints, etc.). But here are a few methods that might work:
- Use the "Import/Export Wizard".
This is probably the simplest. Right click the source database and go to Tasks > Export Data... I'm not going to walk through all the steps of the wizard here, but you can select the source and destination databases, then choose which tables you want to copy. Here's a page that walks through a simple example, although they are using it for importing data from Excel.
After copying the data, add any necessary indexes/constraints/etc.
- Do a
SELECT INTO
With very small tables, I'll just do a very quick:
SELECT *
INTO myDestinationDB.dbo.tablename
FROM mySourceDB.dbo.tablename
Replacing with your database names and table names, of course.
This creates the destination tables on the fly. You'll need to add indexes/constraints yourself afterwards. Also be aware that this won't always work for some limited situations like identity columns.
- Script out the table definition, then do an
INSERT INTO
Right-click the table in the source database, and choose "Script Table as > Create To > New Query Editor Window"
Then change the USE databasename at the top to your destination database, and run the CREATE statement to create the table. Now is your chance to also add indexes or constraints, if necessary. Then to copy the data, do something like:
INSERT INTO myDestinationDB.dbo.tablename
SELECT * FROM mySourceDB.dbo.tablename
If these tables have millions and millions of rows, definitely use the 1st method above, or use an advanced technique like a custom SSIS package (not covered in this answer), otherwise you might fill up your transaction log.
Hope this leads you in the right direction. Good luck.
Best Answer
In short, Intellisense isn't as intelligent as its name might suggest.
Temp Tables
If you're working with temp tables across different databases or separate query windows, Intellisense doesn't think the table exists, because, well, it doesn't as far as it knows. There are usually two scenarios this occurs:
At run time, we know that a first command will execute and by the time we get to running an additional command that the table will exist. But asking Intellisense to be aware of things that haven't happened yet is impossible. This usually involves the temp table being created by a different process, query window, etc. It generally only knows the scope of its own query window and objects in non-tempdb databases.
If it does already exist, being aware of everything already in the tempdb is probably a performance concern (just conjecture) because things move around in there fast, from moment to moment, and it'd be hard/unlikely to get a reliable enumeration of objects that was valid for more than a few seconds. Regardless, it just doesn't know about much outside of that single query window with regards to tempdb objects.
Linked Servers
This one sort of makes sense. Do you really want Intellisense going and querying your linked servers every time you are writing queries that involve remote objects? It just doesn't know and so assumes it doesn't exist.
Synonyms
If the synonyms are referring to linked server objects, then they'll suffer from the issue described above. On their own, however, they should work fine with local database objects.
Bottom Line
Learn to ignore it, or use a different IDE if possible (one that doesn't use Intellisense). Most people seem to like Intellisense despite its flaws, but you could always turn it off completely as well.
There is a hacky work-around for temp tables at this SO question but I personally would never do that, as you're increasing the complexity and reducing readability of your code for...a red squiggly line.